Postgres - how to match columns for both null values

Question

I am having postgres version 9.6. In one query I have requirement to match null values in where conditions.

select src.* from source src, report prs
where src.id=prs.id and coalesce(src.batch_id, 'null')=coalesce(prs.batch_id, 'null')

Means, it should return all rows where column batch_id is matched or null in both the tables , along with matching id. I could achieve this by applying coalesce function but it will skip the index created in batch_id column.

Please suggest if there is any better way to do.

Thanks

Unrelated to your problem, but: Postgres 9.5 is no longer supported you should plan an upgrade as soon as possible. — user330315
– user330315, Commented Feb 14, 2022 at 12:00

wildplasser · Accepted Answer · 2022-02-14 11:15:02Z

1

It seems you want IS NOT DISTINCT FROM, which treats NULL as if it were an actual value:

SELECT src.*
FROM source src
JOIN report prs
  ON src.id = prs.id
 AND src.batch_id IS NOT DISTINCT FROM prs.batch_id
    ;

Also, since you are only interested in the src table's values, you could put the prs table reference in an EXISTS (...) condition. This will avoid generating duplicates in the result:

SELECT *
FROM source src
WHERE EXISTS ( 
    SELECT * 
    FROM report prs
    WHERE src.id = prs.id
    AND src.batch_id IS NOT DISTINCT FROM prs.batch_id
    )
    ;

NOTE: IS NOT DISTINCT FROM is a Postgres extension to sql. It is not part of standard SQL.

edited Feb 14, 2022 at 11:15

answered Feb 14, 2022 at 11:09

wildplasser

44.5k9 gold badges72 silver badges116 bronze badges

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

Comments

mshabou · Accepted Answer · 2022-02-14 11:08:54Z

0

NULL mean that the value of the colum is unknown, so NULL is not equal to NULL

postgres=# SELECT 'True' result WHERE NULL = NULL;
 result 
--------
(0 rows)

you need to use is null in your where conditions

select src.* from source src, report prs
where src.id=prs.id and ( (src.batch_id=prs.batch_id) or ( src.batch_id is null and prs.batch_id is null))

answered Feb 14, 2022 at 11:08

mshabou

5923 silver badges6 bronze badges

Collectives™ on Stack Overflow

Postgres - how to match columns for both null values

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related