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;
