Problematic nameless table in Postgresql

Question

I actually don't know how I've done it : but I got a nameless table into my postgres DB. Needless to say that such a table is problematic, it doesn't get erased, neither could you change it in any way.

Here is a picture of how it looks on the pgAdmin explorer :

As for its declaration, it goes like this :

CREATE TABLE 
(
  _id_ integer NOT NULL DEFAULT nextval('bu_b__id__seq'::regclass),
  pt_utilisateur integer,
  pt_date date,
  lon double precision,
  lat double precision,
  CONSTRAINT  PRIMARY KEY (_id_)
)

So, one simple question : how can I delete this table (since trying to delete it comes to using its name... which doesn't exist !) ?

Regards.

Answer 1

If this is NOT about a table which name is not displayable due to encoding / font issues you can do this:

• Determine the table OID from the system catalogues.
• Set a new table name in the system catalogues to a temporary name. Use the OID as an identifer
• Re-rename the table to something useful (or just drop it).

This of course assumes, that you have superuser access to the database.

The first step - determine the OID from the catalogue:

SELECT oid, relname FROM pg_class WHERE length(relname) <= 1;

If you don't get any result the table has a name - it is just not displayable. In this case just skip the WHERE part and look with your eyes.

The second step - change the system catalogue:

UPDATE pg_class SET relname = 'foo' WHERE oid = /*OID from step 1*/;

In any case - don't stop here, you must proceed.

The third step - cleanup

PostgreSQL stores the table name in more than one place (e.g. pg_type). Some tools might depend on this. To clean this up you must either drop the table (and hence the unrenamed stuff) OR you must rename the table again using the official tools.

ALTER TABLE foo RENAME TO /*somthing real*/;

or

DROP TABLE foo;

The usual warnings: You are munching in the internals of PostgreSQL - don't do silly things and do it on your own risk :-)