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}$'

It works fine on 9.4 too. But surely it is a bad idea to store dates in text format. You should use date type to avoid such problems. — Egor Rogov
– Egor Rogov, Commented Dec 30, 2015 at 11:37
You just found another good reason to never, ever store dates as varchar. If the table had been created with a proper date column you wouldn't need the cleanup now. — user330315
– user330315, Commented Dec 30, 2015 at 11:46

Rahul Tripathi · Accepted Answer · 2015-12-30 11:41:16Z

4

Your regex is returning true but it is not good.

You should try to avoid storing dates as string/varchar. This leads to several problems.
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.

answered Dec 30, 2015 at 11:41

Rahul Tripathi

173k33 gold badges292 silver badges341 bronze badges

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

2 Comments

Vivek S. Over a year ago

What If my date is 30-dec-83 ?

Rahul Tripathi Over a year ago

@wingedpanther:- For such case use the Lower function

Collectives™ on Stack Overflow

postgres date validation regex

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related