How to enforce uniqueness in postgresql per row for a specific column-CodePudding

I have the following table (stripped down for demonstration)

products
- id
- part_number
- group_id

I want to be able to query against products and only return a single row per group_id (whichever is noticed first in the query is fine). All rows with group_id = null return as well.

Example:

ID      part_number    group_id
2314    ABB19          1
4543    GFH54          1
3454    GHT56          2
3657    QWT56          2
7689    GIT56          2
3465    HG567          null
5675    FG345          null

I would want to query against this table and get the following results:

ID      part_number    group_id
2314    ABB19          1
3454    GHT56          2
3465    HG567          null
5675    FG345          null

I have tried using group by but wasnt able to get it working without selecting the group_id and doing a group by on it which just returned a list of unique group_id's. Given the complexity of my real products table its important that I am able to keep using select * and not naming each column I need to return.

CodePudding user response：

row_number() and filtering might be more efficient than distinct on and union all, which incur two table scans.

select *
from (
    select p.*,
        row_number() over(partition by group_id order by id) rn
    from products p
) p
where rn = 1 or group_id is null

CodePudding user response：

I was able to solve this with a combination of DISTINCT ON and a UNION

SELECT DISTINCT ON (group_id) * from products 
WHERE group_id IS NOT NULL
UNION 
SELECT * FROM products 
WHERE group_id IS NULL