Thread: Can't access table to describe, drop, or select, but it does exist

Howdy Folks,

I was dumping a database to test backups of the tsearch2 objects, and as I =
glanced through the output of pg_dump -Ft database > DBdata.bak, I found a =
table that I hadn't seen before in the table.  It's a table that's used in =
other databases, but not this one.  Somehow it had gotten created and popul=
ated with 40,000 or so rows of data.  No problem, I figured I'd drop it, an=
d that's where things started getting bizarre.

The reason I'd never noticed the table is because in doing a \d it doesn't =
show up in the table list.  If I try to do a \d TABLE_NAME, I can use <tab>=
 to autocomplete the name, but then it says the table doesn't exist.  I can=
't select any of those 40,000 rows while I'm in the database, and I can't d=
rop it, either.  The only evidence of the table I can find while I'm actual=
ly in the database is by doing a select * from pg_tables, and it shows up a=
s the following:

schemaname |      tablename      | tableowner | hasindexes | hasrules | has=
triggers
------------+---------------------+------------+------------+----------+---=
----------
 public     | ROOT_U_QUICK_LOOKUP| cp         | f          | f        | f

Any \d on the table gives:

Did not find any relation named "ROOT_U_QUICK_LOOKUP".

and any select/drop on the table gives:

ERROR:  Relation "root_u_quick_lookup" does not exist

So what's the deal?  If the pg_dump wasn't giving me so much data I'd be te=
mpted to just delete the row from pg_tables, but the rows are there, and I =
want to clobber them.  Any ideas?

Thanks,

Josh Eno

On Thu, 18 Sep 2003, Josh Eno wrote:

> The reason I'd never noticed the table is because in doing a \d it
> doesn't show up in the table list.  If I try to do a \d TABLE_NAME, I
> can use <tab> to autocomplete the name, but then it says the table
> doesn't exist.  I can't select any of those 40,000 rows while I'm in the
> database, and I can't drop it, either.  The only evidence of the table I
> can find while I'm actually in the database is by doing a select * from
> pg_tables, and it shows up as the following:

You need to say things like:
\d "ROOT_U_QUICK_LOOKUP"
or
select * from "ROOT_U_QUICK_LOOKUP"
(note the double quotes to prevent casefolding).

It's interesting that \d FOO
gives an error message referencing "FOO"
since it's actually looking for a table that's been
case-folded.  I think the error message is incorrect.