Error says table doesn't exist in database, while it exists

Question

I have extracted list of names of tables in database using this sql query:

"SELECT table_name from information_schema.tables"

I got this list:

    table_name
1   main_table
2   kp_table
3   ids_table
4   main_logs

Then i want to extract table ids_table:

"SELECT * from ids_table"

So desired result is to get that table, but i get this error:

Error: Failed to prepare query: ERROR:  relation "ids_table" does not exist
LINE 1: SELECT * from ids_table

Why it happens? Why i get from first query its name, but then it tells me that it doesn't exist?

Check schema like dbo.Ids_table (is default) other schema need wirte after table name — Amirhossein
– Amirhossein, Commented Jun 19, 2020 at 11:58
Can you add the schema name to the query from information_schema? — Mureinik
– Mureinik, Commented Jun 19, 2020 at 11:58
Have you tried adding the schema_name in front of the table name like SELECT * FROM schema.ids_table — Nafiu Lawal
– Nafiu Lawal, Commented Jun 19, 2020 at 12:00
If you don't know the schema you can display it using select table_schema, table_name from information_schema.tables — user330315
– user330315, Commented Jun 19, 2020 at 12:01

Gordon Linoff · Accepted Answer · 2020-06-19 12:01:49Z

2

In this case, I would say that the most common issue is that you are looking at two different databases. That is probably not the cause in this case.

Another common possibility is that the schema is something unexpected, and you should be referencing the schema. For that, include the schema in the query:

select table_schema, table_name
from information_schema.tables

Another possibility are hidden characters, such as spaces. You can see if this is the problem by adding delimiters so check the names:

select '|' || table_name || '|'
from information_schema.tables

answered Jun 19, 2020 at 12:01

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

french_fries Over a year ago

yeah it was schema reference problem

Jānis Freibergs · Accepted Answer · 2020-06-19 13:01:26Z

0

It looks like you are trying to select data from different schema, which by default owns by different user, but you have access to it. As this table is not created by user, which you are using for select * from ... , you must indicate schemas name before table name. Without schemas name it's trying to select date from users schema, but actually table are owned by completely different user.

answered Jun 19, 2020 at 13:01

Jānis Freibergs

363 bronze badges

Collectives™ on Stack Overflow

Error says table doesn't exist in database, while it exists

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related