How to delete multiple rows in a PostgreSQL database based on string id?

Question

So I have the following table:

qliu=# select id from api_member;
 id  
-----
 242
 236
 246
 251
 253
 9
 21
 185
 49
 188

I want to be able to delete rows with a range of ids with something like the following command:

delete from api_member where id between '0' and '10';

But it deletes nothing. Also if you were wondering

delete from api_member where id between 0 and 10;

I get the following error:

ERROR:  operator does not exist: character varying >= integer
LINE 1: delete from api_member where id between 0 and 10;
                                        ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

Never store numbers in a varchar column. The problem you have, stems directly from that bad design decision. — user330315
– user330315, Commented Jul 28, 2014 at 18:02

Sergey Kalinichenko · Accepted Answer · 2014-07-28 17:50:47Z

10

This is because your column's id is of type varchar, not an integer. You can solve this by casting it to integer, like this:

delete from api_member
where CAST(id as integer) between 0 and 10;

If the number of rows is large, this operation may be too slow. Consider changing the type of the id column to a numeric type.

answered Jul 28, 2014 at 17:50

Sergey Kalinichenko

729k85 gold badges1.2k silver badges1.6k bronze badges

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

Collectives™ on Stack Overflow

How to delete multiple rows in a PostgreSQL database based on string id?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related