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.

Answer 1

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.