1

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?

Kash Pourdeilami
  • 488
  • 2
  • 6
  • 24
  • Are you logged in as the table owner `postgres`? (btw: it's not a good idea to create tables using the superuser or use the superuser account for your application) –  Nov 20 '18 at 15:34
  • Yes I'm logged in as the `postgres` user. Thanks for the hint, I'm only doing this on my dev machine and on production I'm using a different user – Kash Pourdeilami Nov 20 '18 at 15:36
  • May I know why tag pandas here ? – BENY Nov 20 '18 at 15:58
  • The table was created with pandas' `to_sql` method and pandas could read it just fine using the `read_sql` method so I thought it might have something to do with it. I'll remove the tag now – Kash Pourdeilami Nov 20 '18 at 16:08

1 Answers1

3

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""";
  • Thanks a lot, this worked. I suspected that at first but tried doing `SELECT * FROM "\"e7b6a2e19789418e9e48fd34e981b036\""` which kept throwing syntax errors – Kash Pourdeilami Nov 20 '18 at 15:48