7

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

Robin clave
  • 608
  • 6
  • 16
  • 32

1 Answers1

16

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$$;
zx81
  • 41,100
  • 9
  • 89
  • 105
  • Is my understanding correct, that a single quote (`'`) could've been used instead of `$$`, because they are interchangeable? (from [What are '$$' used for in PL/pgSQL](https://stackoverflow.com/questions/12144284/what-are-used-for-in-pl-pgsql) and [4.1.2.4. Dollar-Quoted String Constants](https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING).) – toraritte Sep 05 '19 at 20:56
  • The link from the PostgreSQL manual that helped me decipher the matching part: [9.7.3. POSIX Regular Expressions](https://www.postgresql.org/docs/current/functions-matching.html#FUNCTIONS-POSIX-REGEXP) in "9.7 Pattern Matching". – toraritte Sep 05 '19 at 22:36