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

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?

edited Nov 20 '18 at 16:13

a_horse_with_no_name

295k46451546

asked Nov 20 '18 at 15:30

Kash Pourdeilami

3831523

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)

– a_horse_with_no_name
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 ?

– W-B
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

add a comment |

                 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?

edited Nov 20 '18 at 16:13

a_horse_with_no_name

295k46451546

asked Nov 20 '18 at 15:30

Kash Pourdeilami

3831523

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)

– a_horse_with_no_name
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 ?

– W-B
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

add a comment |

                 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?

edited Nov 20 '18 at 16:13

a_horse_with_no_name

295k46451546

asked Nov 20 '18 at 15:30

Kash Pourdeilami

3831523

                 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?

postgresql quoted-identifier

edited Nov 20 '18 at 16:13

a_horse_with_no_name

295k46451546

asked Nov 20 '18 at 15:30

Kash Pourdeilami

3831523

edited Nov 20 '18 at 16:13

a_horse_with_no_name

295k46451546

asked Nov 20 '18 at 15:30

Kash Pourdeilami

3831523

edited Nov 20 '18 at 16:13

a_horse_with_no_name

295k46451546

edited Nov 20 '18 at 16:13

a_horse_with_no_name

295k46451546

edited Nov 20 '18 at 16:13

a_horse_with_no_name

295k46451546

asked Nov 20 '18 at 15:30

Kash Pourdeilami

3831523

asked Nov 20 '18 at 15:30

Kash Pourdeilami

3831523

asked Nov 20 '18 at 15:30

Kash Pourdeilami

3831523

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)

– a_horse_with_no_name
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 ?

– W-B
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

add a comment |

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)

– a_horse_with_no_name
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 ?

– W-B
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

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)

– a_horse_with_no_name
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 ?

– W-B
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

add a comment |

1 Answer
1

active

oldest

votes

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""";

answered Nov 20 '18 at 15:40

a_horse_with_no_name

295k46451546

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

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53396358%2fpostgres-relation-does-not-exist-error-when-table-exists-on-public-schema%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

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""";

answered Nov 20 '18 at 15:40

a_horse_with_no_name

295k46451546

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

add a comment |

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""";

answered Nov 20 '18 at 15:40

a_horse_with_no_name

295k46451546

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

add a comment |

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""";

answered Nov 20 '18 at 15:40

a_horse_with_no_name

295k46451546

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""";

answered Nov 20 '18 at 15:40

a_horse_with_no_name

295k46451546

answered Nov 20 '18 at 15:40

a_horse_with_no_name

295k46451546

answered Nov 20 '18 at 15:40

a_horse_with_no_name

295k46451546

answered Nov 20 '18 at 15:40

a_horse_with_no_name

295k46451546

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

add a comment |

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

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

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

Search This Blog

Ufyukyu