Al ejecutar esta consulta:
SELECT id,selected_placements FROM app_data.content_cardsObtengo una tabla como esta:
+----+-------------------------------+ | id | selected_placements | +----+-------------------------------+ | 90 | {162,108,156,80,163,155,NULL} | +----+-------------------------------+ | 91 | {} | +----+-------------------------------+Lo que quiero hacer ahora es obtener esta misma información pero con las matrices divididas en filas para obtener un resultado como este:
+----+---------------------+ | id | selected_placements | +----+---------------------+ | 90 | 162 | +----+---------------------+ | 90 | 108 | +----+---------------------+ | 90 | 156 | +----+---------------------+ | 90 | 80 | +----+---------------------+ | 90 | 163 | +----+---------------------+ | 90 | 155 | +----+---------------------+Como puede ver, no quiero obtener filas con valor nulo en "selected_placements".
Estoy usando PostgreSQL 8.0.2.
¡Muchas gracias!
Le sugiero que actualice su versión de Postgres. Todas las versiones admitidas admiten unnest() :
SELECT x.* FROM (SELECT id, UNNEST(selected_placements) as selected_placement FROM app_data.content_cards ) x WHERE selected_placement IS NOT NULL;En versiones anteriores, puede esforzarse por elegirlos uno a la vez. Lo siguiente está probado y funciona, aunque en 9.5:
with content_cards as ( select 1 as id, array['a', 'b', 'c'] as selected_placements ) SELECT id, selected_placements[num] as selected_placement FROM (SELECT cc.*, generate_series(1, ccup.maxup) as num FROM content_cards cc CROSS JOIN (SELECT MAX(ARRAY_UPPER(cc.selected_placements, 1)) as maxup FROM content_cards cc ) ccup ) x WHERE selected_placements[num] IS NOT NULL;