Re: Drop table by something other than its name

Felix Obermaier <obe(at)IVV-AACHEN(dot)DE> writes:
> I've tried the pg_dump-approach with no success:

> pg_dump: SQL command failed
> pg_dump: Error message from server: ERROR: invalid byte sequence for encoding "UTF8": 0xe3bc72
> HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding".
> pg_dump: The command was: LOCK TABLE public."hp_kreis_dren" IN ACCESS SHARE MODE
> pg_dump: *** aborted because of error

> If I enter:
> DROP TABLE "hp_kreis_dren" CASCADE;

> I get 'ERROR: invalid byte sequence for encoding "UTF8":0xfc'

> I'm sure the name of the table should have been "hp_kreis_dren".

Oh, that's messy. Somehow you've gotten a table name in there that's
not valid in the database's encoding (UTF8, evidently). PG generally
tries to prevent this but the checks are probably not bulletproof,
especially not in older releases.

You could try setting client_encoding = SQL_ASCII in a psql session
and then seeing if it will take an ALTER TABLE RENAME command; although
typing the name of the table might be difficult. If that doesn't work,
it should work to do this as superuser:

1. Run
SELECT oid, relname FROM pg_class WHERE relname LIKE 'hp_kreis%'
to determine the OID of the problem table.

2. Run
UPDATE pg_class SET relname = 'whateveryouwant' WHERE oid = whatyoufoundabove

The bogus relation name might have propagated into the names of related
indexes, constraints, etc as well, in which case you'll probably need to
manually fix those too. Once you can pg_dump, use iconv or similar tool
to check for encoding problems in the pg_dump output.

regards, tom lane