clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2591697 fiddles created (45708 in the last week).

SELECT version();
version
PostgreSQL 13.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit
 hidden batch(es)


CREATE TABLE current (current_data TEXT NOT NULL);
 hidden batch(es)


CREATE TABLE expected_output ( shared INT NOT NULL, expected_data TEXT NOT NULL );
 hidden batch(es)


INSERT INTO current (current_data) VALUES ('992,1005,1007,1008'), ('44,1005,1110'), ('13,44,1005,10078'), ('11,1203,6666'), ('1,11,99,2222'), ('1234'); -- note the singleton!
6 rows affected
 hidden batch(es)


SELECT c.current_data AS d, ROW_NUMBER() OVER () AS rn FROM current c;
d rn
992,1005,1007,1008 1
44,1005,1110 2
13,44,1005,10078 3
11,1203,6666 4
1,11,99,2222 5
1234 6
 hidden batch(es)


SELECT UNNEST(STRING_TO_ARRAY(c.current_data, ','))::INT AS d, ROW_NUMBER() OVER () AS rn FROM current c ORDER BY rn, d;
d rn
992 1
1005 1
1007 1
1008 1
44 2
1005 2
1110 2
13 3
44 3
1005 3
10078 3
11 4
1203 4
6666 4
1 5
11 5
99 5
2222 5
1234 6
 hidden batch(es)


SELECT d, COUNT(d) FROM ( SELECT REGEXP_SPLIT_TO_TABLE(c.current_data, ',')::INT AS d, ROW_NUMBER() OVER () AS rn FROM current c ) AS t01 GROUP BY d HAVING COUNT(d) > 1;
d count
11 2
44 2
1005 3
 hidden batch(es)


SELECT DISTINCT t02.d AS num, UNNEST(t03.d2) AS val FROM ( SELECT d, COUNT(d) AS cnt FROM ( SELECT UNNEST(STRING_TO_ARRAY(c.current_data, ','))::INT AS d, ROW_NUMBER() OVER () AS rn FROM current c ) AS t01 GROUP BY d HAVING COUNT(d) > 1 ) AS t02 JOIN ( SELECT STRING_TO_ARRAY(c.current_data, ',')::INT[] AS d2 FROM current c ) AS t03 ON t02.d = ANY(t03.d2);
num val
44 1005
44 1110
11 99
11 2222
1005 992
1005 1008
11 11
44 10078
11 6666
11 1
1005 44
44 13
1005 1007
1005 1110
1005 13
11 1203
44 44
1005 10078
1005 1005
 hidden batch(es)


SELECT num AS "Token", -- the token is not required and can be omitted STRING_AGG(val::TEXT, ',' ORDER BY val::INT) AS "The string" FROM ( SELECT DISTINCT t02.d AS num, UNNEST(t03.d2) AS val FROM ( SELECT d, COUNT(d) AS cnt FROM ( SELECT UNNEST(STRING_TO_ARRAY(c.current_data, ','))::INT AS d, ROW_NUMBER() OVER () AS rn FROM current c ) AS t01 GROUP BY d HAVING COUNT(d) > 1 ) AS t02 JOIN ( SELECT STRING_TO_ARRAY(c.current_data, ',')::INT[] AS d2 FROM current c ) AS t03 ON t02.d = ANY(t03.d2) ) AS t04 GROUP BY num ORDER BY num;
Token The string
11 1,11,99,1203,2222,6666
44 13,44,1005,1110,10078
1005 13,44,992,1005,1007,1008,1110,10078
 hidden batch(es)


EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT num AS "Token", STRING_AGG(val::TEXT, ',' ORDER BY val::INT) AS "The string" FROM ( SELECT DISTINCT t02.d AS num, UNNEST(t03.d2) AS val FROM ( SELECT d, COUNT(d) AS cnt FROM ( SELECT UNNEST(STRING_TO_ARRAY(c.current_data, ','))::INT AS d, ROW_NUMBER() OVER () AS rn FROM current c ) AS t01 GROUP BY d HAVING COUNT(d) > 1 ) AS t02 JOIN ( SELECT STRING_TO_ARRAY(c.current_data, ',')::INT[] AS d2 FROM current c ) AS t03 ON t02.d = ANY(t03.d2) ) AS t04 GROUP BY num ORDER BY 1;
QUERY PLAN
GroupAggregate (cost=12077.82..13305.45 rows=200 width=36) (actual time=0.112..0.127 rows=3 loops=1)
Output: t02.d, string_agg(((unnest((string_to_array(c.current_data, ','::text))::integer[])))::text, ','::text ORDER BY (unnest((string_to_array(c.current_data, ','::text))::integer[])))
Group Key: t02.d
Buffers: shared hit=2
-> Unique (cost=12077.82..12411.95 rows=44550 width=8) (actual time=0.094..0.102 rows=19 loops=1)
Output: t02.d, (unnest((string_to_array(c.current_data, ','::text))::integer[]))
Buffers: shared hit=2
-> Sort (cost=12077.82..12189.20 rows=44550 width=8) (actual time=0.094..0.097 rows=25 loops=1)
Output: t02.d, (unnest((string_to_array(c.current_data, ','::text))::integer[]))
Sort Key: t02.d, (unnest((string_to_array(c.current_data, ','::text))::integer[]))
Sort Method: quicksort Memory: 26kB
Buffers: shared hit=2
-> ProjectSet (cost=581.20..8637.86 rows=44550 width=8) (actual time=0.056..0.084 rows=25 loops=1)
Output: t02.d, unnest((string_to_array(c.current_data, ','::text))::integer[])
Buffers: shared hit=2
-> Nested Loop (cost=581.20..8125.54 rows=4455 width=36) (actual time=0.054..0.073 rows=7 loops=1)
Output: c.current_data, t02.d
Join Filter: (t02.d = ANY ((string_to_array(c.current_data, ','::text))::integer[]))
Rows Removed by Join Filter: 11
Buffers: shared hit=2
-> Seq Scan on public.current c (cost=0.00..23.60 rows=1360 width=32) (actual time=0.007..0.008 rows=6 loops=1)
Output: c.current_data
Buffers: shared hit=1
-> Materialize (cost=581.20..584.71 rows=67 width=4) (actual time=0.006..0.007 rows=3 loops=6)
Output: t02.d
Buffers: shared hit=1
-> Subquery Scan on t02 (cost=581.20..584.37 rows=67 width=4) (actual time=0.031..0.035 rows=3 loops=1)
Output: t02.d
Buffers: shared hit=1
-> HashAggregate (cost=581.20..583.70 rows=67 width=12) (actual time=0.031..0.034 rows=3 loops=1)
Output: (((unnest(string_to_array(c_1.current_data, ','::text))))::integer), NULL::bigint
Group Key: ((unnest(string_to_array(c_1.current_data, ','::text))))::integer
Filter: (count((((unnest(string_to_array(c_1.current_data, ','::text))))::integer)) > 1)
Batches: 1 Memory Usage: 40kB
Rows Removed by Filter: 12
Buffers: shared hit=1
-> Result (cost=0.00..377.20 rows=13600 width=12) (actual time=0.010..0.022 rows=19 loops=1)
Output: ((unnest(string_to_array(c_1.current_data, ','::text))))::integer, NULL::bigint
Buffers: shared hit=1
-> ProjectSet (cost=0.00..105.20 rows=13600 width=32) (actual time=0.009..0.018 rows=19 loops=1)
Output: unnest(string_to_array(c_1.current_data, ','::text))
Buffers: shared hit=1
-> Seq Scan on public.current c_1 (cost=0.00..23.60 rows=1360 width=32) (actual time=0.004..0.005 rows=6 loops=1)
Output: c_1.current_data
Buffers: shared hit=1
Planning Time: 0.192 ms
Execution Time: 0.206 ms
 hidden batch(es)