Postgres 9.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE tbl (
  target_id integer NOT NULL
, machine_id integer NOT NULL
, dateread timestamp without time zone NOT NULL
, state text
, ftime text
, CONSTRAINT pk_sometable PRIMARY KEY (target_id, machine_id, dateread)
);


INSERT INTO tbl VALUES
  (60000, 30, '2021-09-29 15:20:00', '0|1|0', '850|930|32000')
, (60000, 31, '2021-09-29 16:35:13', '0|0|0', '980|1050|30000')
, (60000, 32, '2021-09-29 15:20:00', NULL, NULL)  -- added row to show NULL
;

INSERT 0 3


SELECT t.target_id, t.dateread, t.machine_id, u.state, u.tiempo
     , ord - 1 AS step
FROM   tbl t
LEFT   JOIN LATERAL unnest(string_to_array(state, '|')::int[]
                         , string_to_array(ftime, '|')::int[]) WITH ORDINALITY AS u(state, tiempo, ord) ON true
WHERE  target_id = 60000
AND    dateread = '2021-09-29 15:20:00'   -- adapted
ORDER  BY t.target_id, t.dateread, t.machine_id, step;


target_id
dateread
machine_id
state
tiempo
step


60000
2021-09-29 15:20:00
30
0
850
0

60000
2021-09-29 15:20:00
30
1
930
1

60000
2021-09-29 15:20:00
30
0
32000
2

60000
2021-09-29 15:20:00
32
null
null
null



SELECT 4


-- original (broken)
SELECT foo.target_id,
            dateread,
            foo.machine_id,
            foo.state,
            foo.ftime::integer,
            (row_number() OVER (PARTITION BY foo.dateread, foo.machine_id, foo.target_id)) - 1 AS step
           FROM ( SELECT target_id,
                machine_id,
                dateread
                unnest(string_to_array(state, '|'::text))::integer AS state,
                unnest(string_to_array(ftime, '|'::text))::integer AS tiempo
               FROM some_table
               WHERE target_id IN (6000) AND dateread = '2021-06-09')foo

ERROR:  syntax error at or near "("
LINE 11:                 unnest(string_to_array(state, '|'::text))::i...
                               ^

target_id	dateread	machine_id	state	tiempo	step
60000	2021-09-29 15:20:00	30	0	850	0
60000	2021-09-29 15:20:00	30	1	930	1
60000	2021-09-29 15:20:00	30	0	32000	2
60000	2021-09-29 15:20:00	32	null	null	null