Postgres Group by ignore nulls

Question

What I want to do should really be basic but for some reason I can't figure it out. Consider the following:

ID, COL1, COL2
1   'A'   NULL
1   NULL  'B'

I want to group on ID and 'squeeze out' the gaps and get:

ID, COL1, COL2
1   'A'   'B'

I'm probably having a black out as this sounds a very sqly problem/solution. This on a very large data set with many rows and columns so efficiency would be important

Sorry, this behavior is not clear to me. Please edit your answer and add this use case and your expected output. — S-Man
– S-Man, Commented Oct 26, 2018 at 14:14
Sorry my response above was incorrect and I'm on a phone and can't format the question correctly. Essentially what I'm trying to do is reduce the size of this data set which has been comprised from a few sources. For the above example I only need 2 rows. 1 row comprising of A,B and one C,C. kind of distinct on col1, col2 but ignoring nulls — cem
– cem, Commented Oct 27, 2018 at 7:35
My problem is: Can there be more rows with same ID with values != NULL. For example: ID 1, but three rows with col1 values = A, NULL, C. If so, what value as to be chosen: A or C or {A,C} or MAX... I understood that you want two resulting rows with A and C. Is this correct? So you jsut want to eliminate the NULL values? But if you have to columns: Both of yours and a third row [1CC], two possible results can happen: [1AB, 1CC] or [1AC, 1CB]. It would be important to know what is your expected result and what is the algorithm how to chose the right one. It is far more complicated with more rows — S-Man
– S-Man, Commented Oct 27, 2018 at 8:36

S-Man · Accepted Answer · 2018-10-26 12:45:00Z

3

demo: db<>fiddle

You could simply use an aggregate function which "removes" NULL values. MAX doesn't chose NULL if there's another value for example:

SELECT 
    id, 
    MAX(col1) as col1, 
    MAX(col2) as col2
FROM my_table
GROUP BY id

answered Oct 26, 2018 at 12:45

S-Man

24k9 gold badges51 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres Group by ignore nulls

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related