How to search when parameter is given but return all of the rows when it's null or empty?

Question

I want to write a query that returns all rows when a given parameter in a where condition is null but when the parameter is not null return rows that match the condition, i am using postgres 9.6 and here is what i have so far but no dice...

Edit

let me clarify the question

i have a prepared statement like this

select * from students where first_name like $1

what i want to do is when $1 is null ignore the where and return all students

Thanks

how are you calling the query from go? Check for null in go, and either execute the query with the filter or with no filter. — Haleemur Ali, Jan 09 '18 at 06:32
that was my first thought but if it's possible i want to handle it with a single query — zola, Jan 09 '18 at 06:36
You *could* encapsulate the logic in a server-side function. https://stackoverflow.com/questions/17353445/test-for-null-in-function-with-varying-parameters/17355733#17355733 — Erwin Brandstetter, Jan 09 '18 at 07:08

score 1 · Answer 1 · answered Jan 09 '18 at 06:31

1

You can achieve this as below:

@FirstName is a parameter passed to the query:

select * from students where first_name like @FirstName or @FirstName is null;

answered Jan 09 '18 at 06:31

Mittal Patel

2,732
14
23

score 0 · Answer 2 · answered Jan 09 '18 at 08:07

here is example of prepared statement with explicitly defined logic:

t=# prepare st(text) as select oid,datname 
from pg_database 
where case when $1 is null then true else datname like '%'||$1||'%' end;
PREPARE
t=# execute st('post');
  oid  | datname
-------+----------
 13505 | postgres
(1 row)

t=# execute st(null);
  oid  |  datname
-------+-----------
 13505 | postgres
 16384 | t
     1 | template1
 13504 | template0
 16419 | o
 16816 | db
(6 rows)

How to search when parameter is given but return all of the rows when it's null or empty?

2 Answers2