is there a way to check if a column has correct format of date in postgresql?-CodePudding

I have a date column in my table. I want to retrieve all the dates that are non valid (ex, 12/11 or 12/2002). the data type of column is not DATE. How can I approach it? Any help would be appreciated.

CodePudding user response：

Depending on the desired format of the data, you can use a regular expression:

SELECT * FROM tab
WHERE NOT col ~ '^\d{1,4}-\d{2}-\d{2}$';

This would be for dates in the format YYYY-MM-DD.

CodePudding user response：

Use the function:

create or replace function correct_date(text)
returns date language plpgsql immutable as $$
begin
    return $1::date;
exception when others then
    return null;
end $$;

Example:

with my_table(col) as (
values
    ('2021-02-01'),
    ('2021/02/01'),
    ('1-2-2021'),
    ('1/2/2021'),
    ('1/13/2021'),
    ('11/2021')
)

select col, correct_date(col)
from my_table
-- where correct_date(col) is null

    col     | correct_date
------------ --------------
 2021-02-01 | 2021-02-01
 2021/02/01 | 2021-02-01
 1-2-2021   | 2021-02-01
 1/2/2021   | 2021-02-01
 1/13/2021  |
 11/2021    |
(6 rows)

I do not recommend using the function on regular basis. Use it only to correct your data then alter the type of the column to date.