I wanted to search a table for values in a certain field which weren't valid positive integers. The values are strings, because this table is in the middle of being imported from a load of messy CSV files.
Here's what worked for me:
select distinct foo from bar where foo !~ '^[1-9][0-9]*$';
That is, where the value of foo doesn't consist of a digit
1–9 followed by zero or more other digits. I used the ^
and $ anchors to force a match of the whole string. You can
use \d to match digits, instead of [0-9], but
you have to double the backslash, because a regex is just a normal string
literal to Postgres:
select distinct foo from bar where foo !~ '^[1-9]\\d*$';
The !~ operator returns true if the regex doesn't match.
Use just ~ to return true when it does match. Add
a * to the end of the operator to make the matching
case-insensitive (i.e., ~* and !~*).
The regexes themselves are mostly POSIX compatible (unless you do some ugly tricks to change which ‘regex flavor’ you want). There are some borrowings from Perl as well, but they've changed things around a bit, so be careful.
see the official documentation on PostgreSQL regular expressions, for all the gory details.