Find not-integer values in a string column

Question

How to select records which are having non-integer values in a particular column?

I tried like:

SELECT * FROM tableName WHERE status !~ '^\d+?\$'

I want to find all records not storing exact integer representations.

  Column   |         Type           | Modifiers 
------------+-----------------------+-----------
 status    | charecter varying(25)  |

Answer 1

Since you're looking for non-integer values, if the status contains anything that isn't a digit (i.e. a letter, decimal point, etc.), it's not an integer, so this regex should work:

select * from foo where status ~ E'[^\\d]'

Note the double-escape of the backslash and the use of the negated character class.

Here's an sqlfiddle.

Answer 2

Postgres uses signed integer, we need to allow an optional leading minus or plus sign (+-). Not valid as integer literals:

SELECT * FROM tbl WHERE status !~ '^[+-]*\d+$'

Note that leading and trailing whitespace is tolerated and trimmed, but not nested whitespace.

fiddle
_{Old sqlfiddle.}