If the parameter is null, select all records. Postgres

Question

I have a query, in which some parameters can sometimes arrive null, and when these arrive null, I want them to ignore those conditions.

SELECT DISTINCT on (i.empleado) i.id
FROM rrhh.imputacion as i 
  INNER JOIN rrhh.empleado as e ON e.id = i.empleado 
  JOIN commons.persona AS pe ON e.persona_comun = pe.id 
  INNER JOIN rrhh.parte__imputaciones as pi ON pi.imputacion = i.id 
  INNER JOIN rrhh.parte as p ON pi.parte = p.id 
  WHERE i.dia >= '2017-12-04' AND i.dia <= '2017-12-11' and p.borrador = false  AND e.fecha_fin_contrato IS NULL AND e.security_domain_id = 2 
  and i.empleado = 126
ORDER  BY i.empleado, i.dia DESC,i.id

I need if the value of my parameter:

"and i.empleado = 126" <- if the value is NULL, ignore this, and bring all the records, without filtering by i.empleado

I do not see concrete examples for postgres.

Thank you.

java: setParameter("empleado", specifitacion.getEmpleado()) (LONG) — Jose
– Jose, Commented Dec 16, 2017 at 10:51

Gurwinder Singh · Accepted Answer · 2017-12-17 16:32:10Z

3

There are two ways of achieving this

Check the parameter in application code and add the condition only when value is non-null.
Add an OR condition on the value to test its nullability. Something like so:
```
and (? is null or i.empleado = ?)
```
In Java, you'd have to apply the parameter twice in this case. But if you are using Spring's NamedParameterJdbcTemplate, you can get away with setting it only once:
```
and (:emp is null or i.empleado = :emp)
```

edited Dec 17, 2017 at 16:32

answered Dec 16, 2017 at 10:52

Gurwinder Singh

39.7k6 gold badges62 silver badges87 bronze badges

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

Collectives™ on Stack Overflow

If the parameter is null, select all records. Postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related