Postgres Query: finding values that are not numbers

Question

I have been importing data from a Pandas Dataframe to a postgres DB using my own custom import script. Unfortunately, my data is not tidy which caused every single one of my columns to be parsed as a text. Is there any way where I could get the entries where a certain column value is NOT a number? My plan is to delete those records and convert the column to a numeric type

Thanks!

Answer 1

get the records which have no digits, then you would have to use the following regex:

DELETE FROM myrecords WHERE record ~ '^[^0-9]+$';

Here, the ^ character outside of square brackets means the beginning of the field, the $ character means the end of the field, and we require that all characters in between are non-digits. + indicates that there should be at least one such characters. If we would also allow empty strings, then the regex would look like ^[^0-9]*$.

If you want the records which would include digits and lower-case letter, then I would expect a regex like:

DELETE FROM myrecords WHERE record ~ '[0-9a-z]';