Question 169662 in PostgreSQL Question posted in
The official documentation can be found here.

Can I group by a column in Postgresql and return rows with nulls?

Let’s assume following data:

id parent_id
1 null
2 null
3 1
4 1
5 1

I’d like to return all records where parent_id is either null or "distinctized":

id parent_id
1 null
2 null
3 1

Thanks!

Tags:

3

Answers


  1. 0

    You can use UNION ALL :

    select id, parent_id
from mytable
where parent_id is null
UNION ALL
select min(id), parent_id
from mytable
where parent_id is not null
group by parent_id

    Result :

    id  parent_id
1   null
2   null
3   1

    Demo here

    Login or Signup to reply.
  2. 0

    You can use a ranking function like ROW_NUMBER + filtering:

    WITH cte AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY parent_id ORDER BY id) AS rn
    FROM tab
)
SELECT id, parent_id 
FROM cte
WHERE parent_id IS NULL OR rn = 1

    Output:

    id parent_id
    1 null
    2 null
    3 1

    Check the demo here.

    Login or Signup to reply.
  3. 0

    Try using distinct on as the following (supposing no negative values in the id, parent_id columns):

    select distinct on (coalesce(parent_id, -id)) id, parent_id
from tbl_name
order by coalesce(parent_id, -id), id

    To get the results sorted by id, we can wrap this query in a subquery and then order it as the following:

    select id, parent_id
from(
  select distinct on (coalesce(parent_id, -id)) id, parent_id
  from tbl_name
  order by coalesce(parent_id, -id), id
) t
order by id

    demo

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search