Postgres: relation does not exist error when table exists on public schema

Question

I have a table that was dumped to Postgres using Pandas and Pandas can read it just fine using the read_sql_table command but I can't seem to be able to access it using SQL. When I run the \dt command, I get the table listed under the public schema as one of the existing tables.

                 List of relations
 Schema |                Name                | Type  |  Owner   
--------+------------------------------------+-------+----------
 public | "e7b6a2e19789418e9e48fd34e981b036" | table | postgres

But when I run SELECT * FROM "e7b6a2e19789418e9e48fd34e981b036"; I get the relation does not exist error. I have tried the following:

• SELECT * FROM "e7b6a2e19789418e9e48fd34e981b036"
• SELECT * FROM "public"."e7b6a2e19789418e9e48fd34e981b036"
• Granted usage to public schema to the user by doing GRANT USAGE ON SCHEMA public TO postgres;
• Checked this stack overflow answer that suggest it might be the identifier length is too long but my identifier length is 32 bytes with Postgres allowing up to 63 bytes by default

When I run SHOW search_path; it shows "$user", public which is what it should be but for some reason Postgres keeps saying the relation does not exist.

Other helpful information:

• I'm running Postgres in a docker container from here

Any idea on what might be causing the error here?

Answer 1

Your table name contains double quotes.

Embedding double quotes in an identifier follows the same rules as embedding single quotes in a string literal: you need to double them:

So the table was created with something like this:

create table """e7b6a2e19789418e9e48fd34e981b036"""(...);

You need to use the same syntax when you select from it:

SELECT * 
FROM """e7b6a2e19789418e9e48fd34e981b036""";