[PostgreSQL] How to put strings in INT and other data types?

Kind time of day dear!

Have a database PostgreSQL, in one of the tables stored mixed values (i.e. can be both text and numbers and anything else), the field format is VARCHAR.

At some point of time, you need to select all rows where field is N, for example, more than 3, for example: SELECT * FROM table1 WHERE field1 > 3;
But there is a problem, because field1 is not always numeric. I know that in PostgreSQL there is type casting, and the query would look something like this:SELECT * FROM table1 WHERE field1::INTEGER > 3;
But as soon as the cast comes to non-numeric values - error, "syntax error".

Please tell me how to compile the query so that would be all that could lead to the desired type automatically would have been, and all that cannot be just skipped, without comparison/ghosts?
July 2nd 19 at 13:53
1 answer
July 2nd 19 at 13:55
Solution
Choose only what is a number.
SELECT field1::integer FROM table1 WHERE field1 ~ E'^\\d+$' AND field1::integer > 3;
This is good, thank you. You have no idea how to do the same with an array? - Salvado commented on July 2nd 19 at 13:58

Find more questions by tags PostgreSQL