CODEWITHSUNDEEP
sqlpostgresqlpostgresql-10
John
John

Reputation: 5287

PostgreSQL filter entries by date but include missed ones with null date

Let's say that i have a simple table such as:

CREATE TABLE public.test
(
    id integer NOT NULL,
    d date NOT NULL
)

with data:

insert into test values(1, '2018-09-05'::date);
insert into test values(2, '2018-08-05'::date);
insert into test values(2, '2018-07-05'::date);

How could i, in a simplest way possible, get both entries with date being null for the record that doesn't meet date filter? E.G.

select id, d from
test where d > '2018-09-01'
union
select id, d from test;

Gives:

 2  "2018-07-05"
 2  "2018-08-05"
 1  "2018-09-05"

and i would like:

2   "null"
1   "2018-09-05"

Can't use distinct across union, not that it would help. I should maybe join this table to itself and do something but i am not sure what.

Upvotes: 0

Views: 136

Answers (2)

Michel Milezzi
Michel Milezzi

Reputation: 11115

If I'm understanding correctly you could move the condition to your select:

SELECT 
    DISTINCT
    id, 
    (case when d > '2018-09-01' then d end) as d 
FROM 
    test

Upvotes: 2

randomDude1001
randomDude1001

Reputation: 169

My solution:

select distinct 
  t.id,
  (select max(t_max.d) from test t_max where t_max.id = t.id and t_max.d > '2018-09-01')
from test t;

you can test it here.

Upvotes: 1

Related Questions