patkil patkil - 7 months ago 37
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:

SELECT *
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

You have to include the schema if isnt a public one

SELECT *
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

SELECT *
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
Comments