Search code examples
sqlpostgresql

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.

Solution

  • 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