postgres date validation regex

Question

I have a large table in postgres in which most of the dates are in DD-Mon-YY format but some rows do not follow this pattern and can be regarded as junk values. When I try to run any date function on this column, the query fails with the error message that this junk value is not a valid date format.

My intent is to skip the rows containing junk values by writing a case statement. I will like to process only those rows which follow the valid date format i.e. 'DD-Mon-YY'.

I have written following regex pattern for date, which is working when I test it on online regex tester but is not working on postgres. I'm new to postgres so please let me know what I'm missing here.

The following snippet returns False when I'm expecting it to return True

SELECT '23-Dec-83' ~ '^\d{2}-[a-zA-Z]{3}-\d{2}$'

Answer 1

Your regex is returning true but it is not good.

1. You should try to avoid storing dates as string/varchar. This leads to several problems.
2. Try to use the correct datatype. In case of dates it would be date.

However a better regex to deal with your case could be:

SELECT '30-Dec-83' ~ '^(([0-2][0-9])|([3][0-1]))-(Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)-\d{2}$'

FIDDLE DEMO

Your regex will return True even for formats like

SELECT '23-Mon-83' ~ '^\d{2}-[a-zA-Z]{3}-\d{2}$'  //return True
SELECT '23-Abc-83' ~ '^\d{2}-[a-zA-Z]{3}-\d{2}$'  //return True
SELECT '23-Pqr-83' ~ '^\d{2}-[a-zA-Z]{3}-\d{2}$'  //return True

which is ideally wrong.