Postgres 9.6

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, '{}')
;

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;

id
value

1
1

1
2

1
3

2
null

3
null

SELECT 5

-- 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

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