PostgreSQL - casting varchar to int?

Question

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)

If you forsee a need to treat ID as a number, then consider storing it in a numeric column, not as text. — Tim Biegeleisen
– Tim Biegeleisen, Commented Oct 5, 2017 at 15:42
Trying your first solution i get invalid input syntax for integer: "" — Gambit2007
– Gambit2007, Commented Oct 5, 2017 at 15:44
The cast should work. Maybe you are doing something else or maybe you are using a really old version of Postgres. Let me post an answer. — Tim Biegeleisen
– Tim Biegeleisen, Commented Oct 5, 2017 at 15:46
It's because you have empty values on the column... and empty is not a number. — Dan
– Dan, Commented Oct 5, 2017 at 15:50

Gordon Linoff · Accepted Answer · 2017-10-05 19:31:46Z

4

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.

edited Oct 5, 2017 at 19:31

answered Oct 5, 2017 at 15:53

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gambit2007 Over a year ago

I'm working on somebody else's project, not mine. I didn't really understand your solution, how can i generalize it? Like 'WHERE ID > 70000'

Collectives™ on Stack Overflow

PostgreSQL - casting varchar to int?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related