Can't drop a table in postgres

Question

I'm new to postgresql and I can't seem to get it to drop a table.

db_dev=# \dt
          List of relations
 Schema |    Name     | Type  | Owner
--------+-------------+-------+-------
 public | DataSources | table | ted
 public | Emails      | table | ted
 public | Users       | table | ted
(3 rows)

When I try to delete the users table it gives an error:

db_dev=# drop table Users;
ERROR:  table "users" does not exist

What am I doing wrong?

do you mean `drop table public.Users;`? - that gave the same `ERROR: table "users" does not exist` — Ted Tomlinson, Jan 29 '14 at 18:49

score 33 · Accepted Answer · answered Jan 29 '14 at 19:19

The problem is that your Users table is mixed case (and object names in Postgres are case sensitive). Without quotes around the table name Postgres will case fold the supplied name to "users"-- which doesn't exist. Your solution of quoting the table name works, not because users is a reserved name but because, by quoting it, you are telling Postgres to drop the "Users" table rather than the "users" table.

score 6 · Answer 2 · answered Jan 29 '14 at 18:50

6

It seems that you are doing it right, but you can try doing this:

DROP TABLE IF EXISTS Users;

or this:

DROP TABLE IF EXISTS Public.Users;

If exists will be deleted, and if not exists you will know.

answered Jan 29 '14 at 18:50

carexcer

1,407
2
15
27

1

The table name is mixed case, you need to quote it. – gsiems Jan 29 '14 at 19:21
1

I don't know in other dbms, but it PostgreSQL you don't need quote the table name (just tested) – carexcer Jan 29 '14 at 19:24
Not if the table name was created mixed/upper case with the name in quotes. Try `create table "Users" as select current_user as username;` and see how well `drop table users;` works. – gsiems Jan 29 '14 at 20:30
Ok, nice contribution. +1 – carexcer Jan 29 '14 at 23:45

Can't drop a table in postgres

2 Answers2