clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3799290 fiddles created (41686 in the last week).

CREATE TABLE strings(string text); INSERT INTO strings VALUES ('I think Postgres is nifty') , ('And it keeps getting better');
2 rows affected
 hidden batch(es)


SELECT * FROM regexp_split_to_table('I think Postgres is nifty', ' ') WITH ORDINALITY x(word, rn);
word rn
I 1
think 2
Postgres 3
is 4
nifty 5
 hidden batch(es)


-- Postgres 9.3 or older -- Quick & dirty: SELECT *, row_number() OVER (PARTITION BY ctid) AS rn FROM ( SELECT ctid, unnest(string_to_array(string, ' ')) AS word FROM strings ) x; -- Slow & sure: SELECT arr[rn] AS word, rn FROM ( SELECT *, generate_series(1, array_upper(arr, 1)) AS rn FROM ( SELECT string_to_array(string, ' ') AS arr FROM strings ) x ) y;
ctid word rn
(0,1) I 1
(0,1) think 2
(0,1) Postgres 3
(0,1) is 4
(0,1) nifty 5
(0,2) And 1
(0,2) it 2
(0,2) keeps 3
(0,2) getting 4
(0,2) better 5
word rn
I 1
think 2
Postgres 3
is 4
nifty 5
And 1
it 2
keeps 3
getting 4
better 5
 hidden batch(es)