How to select id, first_not_null(value1), first_not_null(value2).. on Postgresql

Question

I have a table like this:

+--+---------+---------+
|id|str_value|int_value|
+--+---------+---------+
| 1| 'abc'   |         |
| 1|         |    1    |
| 2| 'abcd'  |         |
| 2|         |    2    |
+--+---------+---------+

I need to get this:

+--+---------+---------+
|id|str_value|int_value|
+--+---------+---------+
| 1| 'abc'   |    1    |
| 2| 'abcd'  |    2    |
+--+---------+---------+

It seems to me that I need something like:

select id, first_not_null(str_value), first_not_null(int_value)
from table
group by id

Is there any acceptable way to do this? I use Postgresql 9.0.1.

Update: this should work with uuid types as well

If there are multiple non-null values per id, how would you define which one is "first"? — David Aldridge, Jun 20 '13 at 11:39
There aren't. Anyway I need "any" non-null value. In fact I'm trying to get all Ids that have all values filled — Alex, Jun 20 '13 at 12:47
ERROR: function max(uuid) does not exist LINE 1: select id, max(uuid_v) — Alex, Jun 21 '13 at 06:45
uuid. I used max(uuid_value::varchar(36)) as a temporary solution. This is a little bit slower but works. — Alex, Jun 21 '13 at 08:09

score 0 · Accepted Answer · answered Jun 20 '13 at 07:05

0

You should look at http://www.postgresql.org/docs/8.1/static/functions-aggregate.html for aggregate functions.

I guess max should do the work

EDIT: Working example

select id, max(col1), max(col2) from (
    select 1 as id, null as col1, 'test' as col2
    union 
    select 1 as id ,'blah' as col1, null as col2
)  x group by id

answered Jun 20 '13 at 07:05

zimi

1,586
12
27

Thanks! I used that as a temporary solution. But I suspect there will be some more uuid_value columns and your solution wont fit. – Alex Jun 20 '13 at 11:10
I used max(uuid_value::varchar(36)) with uuids. This is a little bit slower but works. Thank you. – Alex Jun 21 '13 at 08:43

How to select id, first_not_null(value1), first_not_null(value2).. on Postgresql

1 Answers1