Postgres 15

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

CREATE TABLE tbl (
  id      int
, values  int[]
);

INSERT INTO tbl VALUES
  (1, '{1,2,3}')
, (2, NULL)
, (3, '{}')
;

CREATE TABLE



INSERT 0 3


-- eliminates rows with empty or NULL array
SELECT id, unnest(values) AS value
FROM   tbl;


id
value


1
1

1
2

1
3



SELECT 3


-- this worked till Postgres 9.6
SELECT id, CASE WHEN values <> '{}' THEN unnest(values) END AS value
FROM   tbl;

ERROR:  set-returning functions are not allowed in CASE
LINE 2: SELECT id, CASE WHEN values <> '{}' THEN unnest(values) END ...
                                                 ^
HINT:  You might be able to move the set-returning function into a LATERAL FROM item.


-- proper syntax with LEFT JOIN LATERAL
-- works for all versions since Postgres 9.3 where LATERAL was added
SELECT t.id, v.value
FROM   tbl t
LEFT   JOIN LATERAL unnest(t.values) AS v(value) ON true;


id
value


1
1

1
2

1
3

2
null

3
null



SELECT 5


-- the same with minimal syntax
SELECT id, value
FROM   tbl
LEFT   JOIN LATERAL unnest(values) value ON true;


id
value


1
1

1
2

1
3

2
null

3
null



SELECT 5

id	value
1	1
1	2
1	3
2	null
3	null

id	value
1	1
1	2
1	3
2	null
3	null