Postgres Date checking - is_date fn shortcircuit

Question

I have a set of records in a table, with some records having invalid date. I wanted to ignore those invalid records and do a check with rest of the records. I framed a query like below but I don't find it working.

select * from tbl_name i
             where is_date(i.dob) and i.dob::date > CURRENT_DATE;

I got to know that sql doesn't short circuit so it also consider invalid record and end up in date/time out of range. Please help me alter this query in a way i could eliminate invalid dates and do date comparison on valid dates only.

There is no is_date() function in Postgres, so you must have written that yourself. Without seeing the source of that function it's impossible to tell what's going on. But if the column had been correctly defined as date this wouldn't be needed to begin with. The correct solution to the problem is to change the column to the date type — user330315
– user330315, Commented Nov 2, 2022 at 6:46
I wrote a function called is_date following stackoverflow.com/questions/25374707/…. dob is originally a string in table. — Gayathri
– Gayathri, Commented Nov 2, 2022 at 6:49

user330315 · Accepted Answer · 2022-11-02 09:00:43Z

2

There is no guarantee for short-circuiting in Postgres. Neither in a "plain" WHERE clause, nor when using a derived table (from (select ...) where ...). One way to force the evaluation in two steps would be a materialized common table expressions:

with data as materialized (
  select * 
  from tbl_name i
  where is_date(i.dob)
)
select *
from data
where dob::date > CURRENT_DATE;

The materialized keyword prevents the optimizer from pushing the condition of the outer query into the CTE.

Obviously this assumes that is_date() will never return false positives

answered Nov 2, 2022 at 9:00

user330315

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

Comments

Adrian Klaver · Accepted Answer · 2022-11-02 15:22:12Z

Using CASE in the WHERE to differentiate between a valid date and an invalid one and run the > comparison for valid date otherwise return FALSE.

create or replace function is_date(s varchar) returns boolean as $$
begin
  if s is null then
     return false;
  end if;
  perform s::date;
  return true;
exception when others then
  return false;
end;
$$ language plpgsql;

create table date_str (id integer, dt_str varchar);

insert into date_str values (1, '2022-11-02'), (2, '1234'), (3, '2022-12-03');
insert into date_str values (4, 'xyz'), (5, '2022-01-01'), (6, '2023-02-02');

select * from date_str;
 id |   dt_str   
----+------------
  1 | 2022-11-02
  2 | 1234
  3 | 2022-12-03
  4 | xyz
  5 | 2022-01-01
  6 | 2023-02-02

select current_date;
 current_date 
--------------
 11/02/2022


SELECT
    *
FROM
    date_str
WHERE
    CASE WHEN is_date (dt_str) = 't' THEN
        dt_str::date > CURRENT_DATE
    ELSE
        FALSE
    END;

 id |   dt_str   
----+------------
  3 | 2022-12-03
  6 | 2023-02-02

Collectives™ on Stack Overflow

Postgres Date checking - is_date fn shortcircuit

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related