Postgres - how to match columns for both null values-CodePudding

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

CodePudding user response：

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))

CodePudding user response：

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.