CODEWITHSUNDEEP
postgresql
John Chrysostom
John Chrysostom

Reputation: 3983

Phantom Postgres table exists but can't be dropped?

I seem to have some sort of phantom table in Postgres.

Suppose I do the following:

select * from information_schema.tables where table_schema = 'public';

I get:

table_name     | table_type | ...
phantom_table    BASE TABLE
...

So, I run:

drop table phantom_table cascade;

And I get:

ERROR: table "phantom_table" does not exist

Things I've tried:

  1. Checking for spelling errors and making sure the schema is correct (I've even copied/pasted table name out of information schema query results).
  2. vacuum
  3. Reconnecting.
  4. Killing other running processes from my user (nobody else is using the DB).
  5. Checking for active locks on the table (there aren't any).

Anybody have any other ideas for things I should try?

Upvotes: 0

Views: 548

Answers (1)

user330315
user330315

Reputation:

You probably have some white space at the end of the name.

The easiest way is to let the format() function generate you the correct table name and statement: 

select format('drop table %I.%I;', table_schema, table_name) as drop_statement
from information_schema.tables 
where table_schema = 'public'
  and table_name like '%phantom%';

Edit: it seems that psql on Windows isn't able to handle an identifier with a new line in a drop statement (it does when creating the table however).

To workaround that, you can use a DO block:

do
$$
declare
 l_stmt text;
begin
  select format('drop table %I.%I;', table_schema, table_name) as drop_statement
    into l_stmt
  from information_schema.tables 
  where table_schema = 'public'
    and table_name like '%phantom%';
  execute l_stmt;    
end;
$$
;

Note this code assumes that only a single table with that name exists.

Upvotes: 2

Related Questions