check values exact format or not in postgres

Question

How to check date values is expected format or not I need to check date values is yyyy-mm-dd format or not if not then display status 0 dob and doj have datatype varchar

Table: Emp

eid | ename | dob        | doj
1   | abc   | 1900-01-19 | 2019-02-20
2   | dx    | 1900-29-02 | 2019-04-22
3   | xy    | 1989-10-26 | 2018-27-02
4   |ji     | 2000-23-01 | 2019-29-04

based on above data i want check date format should be yyyy-mm-dd if not flag will show like below:

eid | dobdateformatstatus | dojdateformatstatus
1   | 1                   | 1
2   | 0                   | 1
3   | 1                   | 0
4   | 0                   | 0

can you please tell me how to write query to achieve this task in PostgreSQL.

The correct solution is to store DATE values in a date column. Then you can always be sure that it's a valid date. Storing date (or timestamp or number) values in a varchar column is a really, really bad idea. Do you have any chance to fix that broken data model? — user330315
– user330315, Commented Apr 3, 2020 at 16:48
Unrelated to your problem, but: Postgres 9.2 is no longer supported you should plan an upgrade as soon as possible. — user330315
– user330315, Commented Apr 3, 2020 at 16:48

GMB · Accepted Answer · 2020-04-03 16:51:09Z

1

Validating a date is not an easy task, that you don't want to perform manually (you need to take in account the variable number of days per month, not to mention leap years).

Unfortunately Postgres does not have a try_cast() function (as in SQL Server or BigQuery for example): a failed cast is an error.

One method would be to create a user function:

create function try_cast_to_date(p_in text)
returns date 
as $$
begin
    begin
        return $1::date;
    exception 
        when others then return null;
    end;
end;
$$
language plpgsql;

You can then use it in your query:

select 
    eid, 
    ename, 
    (try_cast_to_date(dob) is not null) dobdateformatstatus, 
    (try_cast_to_date(doj) is not null) dojdateformatstatus  
from mytable

Demo on DB Fiddle:

eid | ename | dobdateformatstatus | dojdateformatstatus
--: | :---- | :------------------ | :------------------
  1 | abc   | t                   | t                  
  2 | dx    | f                   | t                  
  3 | xy    | t                   | f                  
  4 | ji    | f                   | f

answered Apr 3, 2020 at 16:51

GMB

224k25 gold badges103 silver badges151 bronze badges

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

1 Comment

harnithu Over a year ago

can we do without using function to validate data in postgres.if yes please tell me that query.donot have access to create function.without function need to achieve this task.

Collectives™ on Stack Overflow

check values exact format or not in postgres

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related