PostgreSQL unsest с пустым массивом

Я использую postgreSQL 9.1. В моей базе данных есть таблица, которая выглядит как

id | ... | values
-----------------------
1  | ... | {1,2,3}
2  | ... | {}

где id - целое число, а значения - целочисленный массив. Массивы могут быть пустыми.

Мне нужно отключить этот список. Если я запрошу

select id, ..., unnest(values)
from table

Я получаю три строки для id = 1 (как и ожидалось) и строк для id = 2. Есть ли способ получить результат, например

id  | ... | unnest
-------------------
1   | ... | 1
1   | ... | 2
1   | ... | 3
2   | ... | null

то есть. запрос, который также содержит строки, которые имеют пустой массив?

Ответ 1

select id, 
       case 
         when int_values is null or array_length(int_values,1) is null then null
         else unnest(int_values)
       end as value
from the_table;

(обратите внимание, что я переименовал столбец values в int_values, поскольку values является зарезервированным словом и не должен использоваться как имя столбца).

SQLFiddle: http://sqlfiddle.com/#!1/a0bb4/1

Postgres 10 не позволяет больше использовать unnest().

Вам нужно использовать боковое соединение:

select id, t.i
from the_table
   cross join lateral unnest(coalesce(nullif(int_values,'{}'),array[null::int])) as t(i);

Пример в Интернете: http://rextester.com/ALNX23313

Ответ 2

После пересмотра этого вопроса мне показалось, что это может быть проще и быстрее.
Отмените логику в настоящее время принятого решения @a_horse:

SELECT id, CASE WHEN values <> '{}' THEN unnest(values) END AS value
FROM   tbl

Это возвращает строку с NULL в value для пустого массива, а также для массива NULL, потому что только массив с элементами в нем производит TRUE в тесте values <> '{}'.
Работает для массивов любого типа, так как литерал '{}' автоматически принуждается к соответствующему типу.
Без явной ветки ELSE CASE возвращает NULL, чего мы хотим в любом случае.
Массивы с элементами NULL будут возвращать строки независимо.
Однако. Я обнаружил там аномалию и разместил вопрос о том, что:
- NULL emements, потерянные при нажатии на результат unsest()
Оказалось, что это ошибка, которая была исправлена после моего отчета для pg 9.3+.

Ответ 3

select id,
    unnest (
        "values"
        ||
        (array[null]::integer[])[1:(array_upper("values", 1) is null)::integer]
    )
from "table"

Ответ 4

Вам нужно будет использовать self LEFT JOIN, как это (также на SQL Fiddle):

SELECT t.id, u.u
  FROM tab t
  LEFT JOIN (SELECT id, unnest(vals) u FROM tab) u
    USING (id);

Обратите внимание, что для больших таблиц запрос будет плохо работать.