Thursday, July 28, 2011

Postgresql unnest function can do many wonders

I saw an interesting Postgres question on Stackoverflow

How to know if all the elements of the array are NULL?

This code works if you want to test if all the elements of the array are number 8, but doesn't work on NULLs:

SELECT 8 = ALL(ARRAY[8,8,8,8]::int[]); -- returns true


Unfortunately, you cannot use that same construct for comparing nulls, this return null:
SELECT NULL = ALL(ARRAY[NULL,NULL,NULL,NULL]::int[]);

And this returns a syntax error:
SELECT NULL IS ALL(ARRAY[NULL,NULL,NULL,NULL]::integer[]);

Fortunately, there's a function to un-nest an array to row elements; then from there, we can use the ALL construct for rows

So given this:

create table x
(
y serial,
z int[]
);

create table x
(
y serial,
z int[]
);

insert into x(z) values
(array[null,null,null]::int[]),
(array[null,7,null]::int[]),
(array[null,3,4]::int[]),
(array[null,null,null,null]::int[]),
(array[8,8,8,8]::int[]);

q_and_a=# select * from x;
 y |           z
---+-----------------------
 1 | {NULL,NULL,NULL}
 2 | {NULL,7,NULL}
 3 | {NULL,3,4}
 4 | {NULL,NULL,NULL,NULL}
 5 | {8,8,8,8}
(5 rows)


Then let's try to unnest array z from x:
q_and_a=# select *, unnest(z) from x;
 y |           z           | unnest
---+-----------------------+--------
 1 | {NULL,NULL,NULL}      |
 1 | {NULL,NULL,NULL}      |
 1 | {NULL,NULL,NULL}      |
 2 | {NULL,7,NULL}         |
 2 | {NULL,7,NULL}         |      7
 2 | {NULL,7,NULL}         |
 3 | {NULL,3,4}            |
 3 | {NULL,3,4}            |      3
 3 | {NULL,3,4}            |      4
 4 | {NULL,NULL,NULL,NULL} |
 4 | {NULL,NULL,NULL,NULL} |
 4 | {NULL,NULL,NULL,NULL} |
 4 | {NULL,NULL,NULL,NULL} |
 5 | {8,8,8,8}             |      8
 5 | {8,8,8,8}             |      8
 5 | {8,8,8,8}             |      8
 5 | {8,8,8,8}             |      8
(17 rows)

We can compare the element directly from select clause:

q_and_a=# select y, unnest(z), unnest(z) is null from x;
 y | unnest | ?column?
---+--------+----------
 1 |        | t
 1 |        | t
 1 |        | t
 2 |        | t
 2 |      7 | f
 2 |        | t
 3 |        | t
 3 |      3 | f
 3 |      4 | f
 4 |        | t
 4 |        | t
 4 |        | t
 4 |        | t
 5 |      8 | f
 5 |      8 | f
 5 |      8 | f
 5 |      8 | f
(17 rows)


Now, given that information on hand, we could proceed with this query:

with a as
(
    select y, unnest(z) as b
    from x
)
select 
 y, every(b is null)
from a 
group by y
order by y

Output:
 y | every
---+-------
 1 | t
 2 | f
 3 | f
 4 | t
 5 | f
(5 rows)


But the following is a lot better than that:

select
 y, true = ALL (select unnest(z) is null)
from x


Output:
 y | ?column?
---+----------
 1 | t
 2 | f
 3 | f
 4 | t
 5 | f
(5 rows)

If you want to make your query flexible(e.g. by way of concatenated query), you could do the following:

select
y, coalesce( true = ALL (select b is null from unnest(z) as a(b)), false )
from x

Then you can change the expression b is null to something else:

select
y, coalesce( true = ALL (select b = 8 from unnest(z) as a(b)), false )
from x

3 comments:

  1. Very help, thanks. I followed this link from stack. I'm pretty new to unnest but loving it. Question - for this last query using b=8, can't that be done just with select z from x where 8 = ALL(z) ? Thanks

    ReplyDelete
    Replies
    1. Sorry for late reply. Your comment is mistakenly flagged as spam by blogspot.

      Yes select z from x where 8 = all(z) would work too. However, it would fail if the variable for 8 has null value instead.

      The last suggested query is the least invasive when it comes to substitution of values on query string be the value is non-null or null

      Delete