CODEWITHSUNDEEP
sqlpostgresql
Gambit2007
Gambit2007

Reputation: 4004

PostgreSQL - casting varchar to int?

I'm trying to execute the following query:

DELETE FROM table_name WHERE ID>9;

But i can't since the 'ID' field is of type 'varchar'. How can i cast it to 'int' and have it act properly? (deleting all rows with ID greater than 9 rather than converting it to a numeric varchar value)

Upvotes: 0

Views: 1789

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270893

I don't know why you would store a numeric value as a string. You might want something more like:

DELETE FROM table_name
    WHERE regexp_matches(ID, '^[1-9][0-9]');

This will delete from the table any id that starts with two digits, where the first is not 0. If you attempt a conversion, then you might get a conversion error if not all ids are numbers. This will also work for long numbers that would overflow an int (although numeric would fix that problem).

EDIT:

For a general solution, I think I would write it as:

where (case when regexp_matches(id, '^[0-9]+$')
            then id::numeric
       end) > 70000

The case should prevent any error on non-numeric ids.

Upvotes: 4

Related Questions