Validate column using regular expression in postgre SQL

Question

I need to check whether a column in a table having a numeric value followed by decimal point and 3 precisions after the decimal point. Kindly suggest how to do using regular expression in postgre SQL (or) any other alternate method.

Thanks

Answer 1

The basic regex for digits, a period and digits is \d+\.\d{3}

You can use it for several things, for instance:

1. Add a Constraing to your Column Definition

ALTER TABLE mytable ADD (CONSTRAINT mycolumn_regexp CHECK (mycolumn ~ $$^\d+\.\d{3}\Z$$));

2. Find Rows that Don't Match

SELECT * FROM mytable WHERE mycolumn !~ $$^\d+\.\d{3}\Z$$;

3. Find Rows that Match

SELECT * FROM mytable WHERE mycolumn ~ $$^\d+\.\d{3}\Z$$;