current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 4 down vote favorite

I use postgreSQL 9.1. In my database there is a table which looks like

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

where id is an integer and values is an integer array. The arrays can be empty.

I need to unnest this list. If I query 

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

I get three rows for id = 1 (as expected) and no lines for id = 2. Is there a way to get a result like

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

i.e. a query which also contains the lines which have an empty array?

share|improve this question
1  
Very good question, very good answers. :) –  Erwin Brandstetter Mar 2 '13 at 17:11

4 Answers 4

up vote 5 down vote accepted 
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;

(note that I renamed the column values to int_values as values is a reserved word and should not be used as a column name).

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

share|improve this answer
    
Thanks, that works perfectly for my use case –  rams Mar 2 '13 at 14:52
up vote 1 down vote

You will need to use self LEFT JOIN, like this (also on SQL Fiddle):

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

Note, that for bigger tables query will be performing badly.

share|improve this answer
up vote 1 down vote 
select id,
    unnest (
        "values"
        ||
        (array[null]::integer[])[1:(array_upper("values", 1) is null)::integer]
    )
from "table"
share|improve this answer
up vote 0 down vote

Upon revisiting this question it struck me that this can be simpler and faster.
Reverse the logic of the currently accepted solution by @a_horse:

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

  • This returns a row with NULL in value for an empty array as well as for a NULL array, because only an array with elements in it produces TRUE in the test values <> '{}'.

  • Works for arrays of any type, since the literal '{}' is automatically coerced to a matching type.

  • Without explicit ELSE branch, CASE returns NULL, which is what we want anyway.

  • An array with a NULL element in it will returns a row anyway.
    However. I found an anomaly there and will post a question concerning that shortly.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.