Postgres SQL - check if parameter is null

Question

I am using Postgres 12.6 in a Java7 application using a hibernate Native query.

I am trying to construct a SQL statement that reads results from a table journalheader where it receives one parameter. The parameter values can be 'Matched' or 'Unmatched'.

The following solution would be inadequate, because it does not cater for null values:

select * from journalheader where matched_status = ?1;

The table does have some rows with the matched_status column of null. I would like these rows to be part of the 'Unmatched' filter.

i.e.

If the parameter (?1) is 'Matched', then it will only return rows where matched_status = 'Matched'.
If the parameter (?1) is 'Unmatched', then it will only return rows where matched_status = 'Unmatched' or null.

Question

How do I also check for the null values?

Possible solutions

I think I may need to add a check on the parameter value, and if it is 'Unmatched' include or matched_status is null in the statement. I am just not sure how to check the value of the parameter in the sql statement.

select * from journalheader where matched_status = ?1 or <if ?1 equals 'Unmatched'> matched_status is null;

So the "Unmatched filter" would result in passing null as ?1? — user330315
– user330315, Commented May 20, 2022 at 8:05
@a_horse_with_no_name, yes that's kind of correct. If the parameter is 'Unmatched', it would need to filter on the table where matched_status values are either 'Unmatched' or null. — Richard
– Richard, Commented May 20, 2022 at 8:07

Scary Wombat · Accepted Answer · 2022-05-20 08:25:25Z

1

You can use the keyword COALESCE

select * from journalheader where COALESCE(matched_status, 'Unmatched') = ?1;

This mean use the value of matched_status unless the value is null, if the value is null then use the value Unmatched

answered May 20, 2022 at 8:25

Scary Wombat

44.9k6 gold badges38 silver badges67 bronze badges

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

Comments

halfer · Accepted Answer · 2022-10-09 12:37:14Z

1

You could try using COALESCE:

select * 
from journalheader 
where matched_status = COALESCE(?1,'Unmatched')

Or CASE WHEN:

select * 
from journalheader 
where matched_status =CASE WHEN ?1 is null THEN 'Unmatched' ELSE ?1 END

edited Oct 9, 2022 at 12:37

halfer

20.2k20 gold badges110 silver badges207 bronze badges

answered May 20, 2022 at 8:26

ScaisEdge

134k10 gold badges98 silver badges111 bronze badges

Collectives™ on Stack Overflow

Postgres SQL - check if parameter is null

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