Delete table with empty name in PostgreSQL

Question

I'm trying to remove one of my tables stored in PostgreSQL 8.3.8 32bit.

Strange is that my table doesn't have a name, it's empty. Where I do:

SELECT * FORM pg_catalog.pg_tables;

it says that me tablename name is null. When I try to delete my table:

DROP TABLE sde. ;

where sde is my schemaname, error appears, telling me that there is a syntax error.

ERROR: syntax error at or near ";"
LINE 1:drop table sde. ;

Is there any way to delete that table?

I also tried that

DROP TABLE sde.'';

But still error appears.

My table has OID. Is it possible to delete it by OID?

The best solution for me would be renaming that table, that I can save my data from that table.

Object names need double quotes, not single qutoes. Try: `drop table sde."";` — , Feb 18 '14 at 09:47
If I insert double quotes error appears: `ERROR: zero-length delimited identifier at or near """" LINE 1: DROP TABLE sde."";` — user2919480, Feb 18 '14 at 09:49
Maybe your tablename consists only of whitespace? (so it isn't actually empty, but e.g. one or several spaces) — , Feb 18 '14 at 09:53

Tomas Greif · Accepted Answer · 2014-02-18T11:34:02.997

1

You cannot create table with empty name:

tgr=# create table "" ();
ERROR:  zero-length delimited identifier at or near """"
LINE 1: create table "" ();

However you can create a table with a lot of spaces, including newlines:

create table "               

                   " ();

To work with such table:

When the previous fails, you can try to update pg_class directly. If you have table OID, try this:

update pg_class set relname = 'test'::name where oid = <<YourOID>>;

edited Feb 18 '14 at 11:34

answered Feb 18 '14 at 10:21

Tomas Greif

I found it in `information_schema.tables`, its name looks like this `''`. I've copied it and added double quotes: `DROP TABLE sde."''";`. But error appears telling me that `table "''" does not exist`. – user2919480 Feb 18 '14 at 10:34
I see. In 9.1.9 this is ok: `create table sda."''"(); drop table sda."''";` Maybe this is some bug in older PostgreSQL version. – Tomas Greif Feb 18 '14 at 10:39
Do you see this table in pgAdmin? If yes, then you can rename `''` table from there. Right-click on table name and then click on Properties. This also works for me. – Tomas Greif Feb 18 '14 at 10:57
Yes, I see it, but when I try to delete it, message windows appears: `ERROR: syntax error at end of input. LINE 1: drop table sde. ` – user2919480 Feb 18 '14 at 11:07
What about updating `pg_class` directly? I am not sure if this can have some side-effects, but it works for me. If you have OID then: `update pg_class set relname = 'test'::name where oid = <>;` – Tomas Greif Feb 18 '14 at 11:13

1 Answers1