PostgreSQL filter entries by date but include missed ones with null date
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.
2 Answers
2
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
Look ok but i would use
(case when d > '2018-09-01'::date then d else null end) as d instead.. So PostgreSQL compares true dates types and not a date type vs a string type– Raymond Nijland
Sep 6 '18 at 16:48
(case when d > '2018-09-01'::date then d else null end) as d
This cast should be automatic. But I agree.
– Michel Milezzi
Sep 6 '18 at 16:49
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.
Thanks for contributing an answer to Stack Overflow!
But avoid …
To learn more, see our tips on writing great answers.
Required, but never shown
Required, but never shown
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
Not sure if i understand your expected results here.. Why is there is a NULL with a id of 2 there?
– Raymond Nijland
Sep 6 '18 at 16:42