patkil patkil - 2 years ago 170
SQL Question

Postgresql tables exists, but getting "relation does not exist" when querying

I have a postgresql db with a number of tables. If I query:

SELECT column_name
FROM information_schema.columns
WHERE table_name="my_table";

I will get a list of the columns returned properly.

However, when I query:

FROM "my_table";

I get the error:

(ProgrammingError) relation "my_table" does not exist
'SELECT *\n FROM "my_table"\n' {}

Any thoughts on why I can get the columns, but can't query the table? Goal is to be able to query the table.

Answer Source

You have to include the schema if isnt a public one

FROM <schema>."my_table"

Or you can change your default schema

SHOW search_path;
SET search_path TO my_schema;

Check your table schema here

FROM information_schema.columns

enter image description here

For example if a table is on the default schema public both this will works ok

SELECT * FROM parroquias_region
SELECT * FROM public.parroquias_region

But sectors need specify the schema

SELECT * FROM map_update.sectores_point
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download