PostgreSQL: выравнивание отношения с массивом для создания одной строки на запись массива

Учитывая таблицу, определенную как таковую:

CREATE TABLE test_values(name TEXT, values INTEGER[]);

...и следующие значения:

| name  | values  |
+-------+---------+
| hello | {1,2,3} |
| world | {4,5,6} |

Я пытаюсь найти запрос, который вернет:

| name  | value |
+-------+-------+
| hello | 1     |
| hello | 2     |
| hello | 3     |
| world | 4     |
| world | 5     |
| world | 6     |

Я просмотрел документацию по доступу к массивам, и попытался придумать, какое решение использовать unnest() function выглядела бы так, но оказалась пустой.

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


postgresql arrays sql lateral set-returning-functions
person Charles Duffy    schedule 13.08.2015    source источник
comment
select name, unnest(values) "value" from test_values; не работает?   -  person bgoldst    schedule 13.08.2015

Ответы (2)


arrow_upward
19
arrow_downward

Вы можете поместить возвращающую набор функцию unnest() в список SELECT, как предлагает Рафаэль. Но в Postgres 9.3 или более поздней версии используйте LATERAL присоединяйтесь вместо этого. Это более чистый, предпочтительный и совместимый со стандартом способ поместить функции, возвращающие множество, в список FROM, а не в список SELECT:

SELECT name, value
FROM   tbl, unnest(values) value;  -- implicit CROSS JOIN LATERAL

Одно тонкое отличие: это удаляет строки с пустым / NULL values из результата, поскольку unnest() возвращает нет строки, в то время как то же самое преобразуется в значение NULL в списке FROM и все равно возвращается. 100 % эквивалентный запрос:

SELECT t.name, v.value
FROM   tbl t
LEFT   JOIN unnest(t.values) v(value) ON true;
person Erwin Brandstetter    schedule 14.08.2015

arrow_upward
9
arrow_downward

Ну даешь ты данные, документ, так что... давайте смешаем ;)

select 
 name, 
 unnest(values) as value 
from test_values

см. SqlFiddle

person Raphaël Althaus    schedule 13.08.2015
comment
Незнакомый с семантикой выбора кортежа с последовательностью в качестве одного из его элементов, я могу сослаться. Я приму это, когда закончится время. - person Charles Duffy; 13.08.2015