Обсуждение: Drop table by something other than its name
Hello List, I have a table with a name that contains some akward letter in it so that pgAdmin fails to display it (just empty) and psqlomits that character. My problem is that I cannot query that table and I cannot drop it. Is there a way to get rid of this table? Thanks Felix Obermaier ------------------------------------------ Ingenieurgruppe IVV GmbH & Co. KG Dipl.-Ing. Felix Obermaier Oppenhoffallee 171 52066 Aachen Telefon: +49 (241) 94691-39 Telefax: +49 (241) 531622 eMail: obe/at/ivv-aachen.de Internet: http://www.ivv-aachen.de Sitz der Gesellschaft: Aachen Amtsgericht Aachen HRA 6212 GF: Bauassessor Dr.-Ing. Dieter Hölsken IVV-Management GmbH Amtsgericht Aachen HRB 12543
Felix Obermaier <obe@IVV-AACHEN.DE> writes:
> I have a table with a name that contains some akward letter in it so that pgAdmin fails to display it (just empty)
andpsql omits that character.
> My problem is that I cannot query that table and I cannot drop it. Is there a way to get rid of this table?
You probably just need to double-quote the table name.
regards, tom lane
On 17 March 2010 21:36, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Felix Obermaier <obe@IVV-AACHEN.DE> writes: >> I have a table with a name that contains some akward letter in it so that pgAdmin fails to display it (just empty) andpsql omits that character. >> My problem is that I cannot query that table and I cannot drop it. Is there a way to get rid of this table? > > You probably just need to double-quote the table name. I think he's having trouble finding out what the table name is in the first place. Maybe you can use something like this to get the name: select relname from pg_catalog.pg_class where relkind = 'r' and relname like 'some%thing'; where 'some%thing' is the table name with % for the part you don't know. Using "pg_dump -s" to dump the schema might be another way to find the name. If that doesn't help, you could perhaps get the table name like the using a perl/python/whatever program and then in the same program execute a DROP command using the table name you got previously. I'd make sure you have a good backup first, though, just in case you accidentally drop the wrong table. -- Michael Wood <esiotrot@gmail.com>
Thanks for your answers, 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 controlledby "client_encoding". pg_dump: The command was: LOCK TABLE public."hp_kreis_dã¼ren" IN ACCESS SHARE MODE pg_dump: *** aborted because of error If I enter: DROP TABLE "hp_kreis_dã¼ren" CASCADE; I get 'ERROR: invalid byte sequence for encoding "UTF8":0xfc' I'm sure the name of the table should have been "hp_kreis_düren". Any Ideas? Felix Obermaier -----Ursprüngliche Nachricht----- Von: Michael Wood [mailto:esiotrot@gmail.com] Gesendet: Mittwoch, 17. März 2010 22:34 An: Tom Lane Cc: Felix Obermaier; pgsql-novice@postgresql.org Betreff: Re: [NOVICE] Drop table by something other than its name On 17 March 2010 21:36, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Felix Obermaier <obe@IVV-AACHEN.DE> writes: >> I have a table with a name that contains some akward letter in it so that pgAdmin fails to display it (just empty) andpsql omits that character. >> My problem is that I cannot query that table and I cannot drop it. Is there a way to get rid of this table? > > You probably just need to double-quote the table name. I think he's having trouble finding out what the table name is in the first place. Maybe you can use something like this to get the name: select relname from pg_catalog.pg_class where relkind = 'r' and relname like 'some%thing'; where 'some%thing' is the table name with % for the part you don't know. Using "pg_dump -s" to dump the schema might be another way to find the name. If that doesn't help, you could perhaps get the table name like the using a perl/python/whatever program and then in the same program execute a DROP command using the table name you got previously. I'd make sure you have a good backup first, though, just in case you accidentally drop the wrong table. -- Michael Wood <esiotrot@gmail.com>
Felix Obermaier <obe@IVV-AACHEN.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
controlledby "client_encoding".
> pg_dump: The command was: LOCK TABLE public."hp_kreis_d��ren" IN ACCESS SHARE MODE
> pg_dump: *** aborted because of error
> If I enter:
> DROP TABLE "hp_kreis_d��ren" CASCADE;
> I get 'ERROR: invalid byte sequence for encoding "UTF8":0xfc'
> I'm sure the name of the table should have been "hp_kreis_d�ren".
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