Filter rows based on multiple condition in where statement in PostgreSQL

Question

I have following table in Postgres 11.0

id              status
NCT00632827 true
NCT00632827 (null)
NCT00770185 false
NCT00770185 (null)
NCT00784303 (null)
NCT00784303 (null)

The id values are duplicated due to different value in status column. I would like to keep the rows with either true or false. If multiple rows have null value, I would like to keep only 1 row. The desired output is:

id              status
NCT00632827 true
NCT00770185 false
NCT00784303 (null)

I am trying following query:

select id, status
where status = 'false' or status  = 'true'
from table

How can I keep rows with null value (last row in the desired output)?

GMB · Accepted Answer · 2020-07-15 23:35:24Z

2

For this dataset, simple aggregation seems good enough to produce the expected results:

select id, bool_or(status) status from mytable group by id

answered Jul 15, 2020 at 23:35

GMB

224k25 gold badges103 silver badges151 bronze badges

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

4 Comments

rshar Over a year ago

In the output, rows with null are selected?

GMB Over a year ago

@rshar: for your sample dataset, this produces exactly the result that you asked for.

Laurenz Albe Over a year ago

This answer should work, because NULL is ignored in aggregate functions. Otherwise, FALSE OR NULL gives NULL.

rshar Over a year ago

Above query suggested by GMB works perfectly. My actual data has 50 columns so in order to filter those duplicated rows, how should I give so many columns in group by clause

Collectives™ on Stack Overflow

Filter rows based on multiple condition in where statement in PostgreSQL

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related