[sql] 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.

This question is related to sql postgresql

The answer is

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

You can try:

FROM public."my_table"

Don't forget double quotes near my_table.

I had to include double quotes with the table name.

db=> \d
                           List of relations
 Schema |                     Name                      | Type  | Owner 
 public | COMMONDATA_NWCG_AGENCIES                      | table | dan

Did not find any relation named "COMMONDATA_NWCG_AGENCIES".


Double quotes:

                         Table "public.COMMONDATA_NWCG_AGENCIES"
          Column          |            Type             | Collation | Nullable | Default 
 ID                       | integer                     |           | not null | 

Lots and lots of double quotes:

db=> select ID from COMMONDATA_NWCG_AGENCIES limit 1;
ERROR:  relation "commondata_nwcg_agencies" does not exist
LINE 1: select ID from COMMONDATA_NWCG_AGENCIES limit 1;
db=> select ID from "COMMONDATA_NWCG_AGENCIES" limit 1;
ERROR:  column "id" does not exist
LINE 1: select ID from "COMMONDATA_NWCG_AGENCIES" limit 1;
db=> select "ID" from "COMMONDATA_NWCG_AGENCIES" limit 1;
(1 row)

This is postgres 11. The CREATE TABLE statements from this dump had double quotes as well:



I had the same problem that occurred after I restored data from a postgres dumped db.

My dump file had the command below from where things started going south.

    SELECT pg_catalog.set_config('search_path', '', false);


  1. Probably remove it or change that false to be true.
  2. Create a private schema that will be used to access all the tables.

The command above simply deactivates all the publicly accessible schemas.

Check more on the documentation here: https://www.postgresql.org/docs/9.3/ecpg-connect.html

The error can be caused by access restrictions. Solution:


I hit this error and it turned out my connection string was pointing to another database, obviously the table didn't exist there.

I spent a few hours on this and no one else has mentioned to double check your connection string.

In my case, the dump file I restored had these commands.

CREATE SCHEMA employees;
SET search_path = employees, pg_catalog;

I've commented those and restored again. The issue got resolved

I was using pgAdmin to create my tables and while I was not using reserved words, the generated table had a quote in the name and a couple of columns had quotes in them. Here is an example of the generated SQL.

CREATE TABLE public."Test"
    id serial NOT NULL,
    data text NOT NULL,
    updater character varying(50) NOT NULL,
    "updateDt" time with time zone NOT NULL,

TABLESPACE pg_default;

ALTER TABLE public."Test"
    OWNER to svc_newnews_app;

All of these quotes were inserted at "random". I just needed to drop and re-create the table again without the quotes.

Tested on pgAdmin 4.26

Questions with sql tag:

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database Export result set on Dbeaver to CSV How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? #1273 – Unknown collation: ‘utf8mb4_unicode_520_ci’ MySQL Error: : 'Access denied for user 'root'@'localhost' SQL Server IF EXISTS THEN 1 ELSE 2 How to add a boolean datatype column to an existing table in sql? Presto SQL - Converting a date string to date format What is the meaning of <> in mysql query? Change Date Format(DD/MM/YYYY) in SQL SELECT Statement Convert timestamp to date in Oracle SQL #1292 - Incorrect date value: '0000-00-00' Postgresql tables exists, but getting "relation does not exist" when querying SQL query to check if a name begins and ends with a vowel Find the number of employees in each department - SQL Oracle Error in MySQL when setting default value for DATE or DATETIME Drop view if exists Could not find server 'server name' in sys.servers. SQL Server 2014 How to create a Date in SQL Server given the Day, Month and Year as Integers TypeError: tuple indices must be integers, not str Select Rows with id having even number SELECT list is not in GROUP BY clause and contains nonaggregated column IN vs ANY operator in PostgreSQL How to insert date values into table Error related to only_full_group_by when executing a query in MySql How to select the first row of each group? Connecting to Microsoft SQL server using Python eloquent laravel: How to get a row count from a ->get() How to execute raw queries with Laravel 5.1? In Oracle SQL: How do you insert the current date + time into a table? Extract number from string with Oracle function Rebuild all indexes in a Database SQL: Two select statements in one query DB2 SQL error sqlcode=-104 sqlstate=42601 What difference between the DATE, TIME, DATETIME, and TIMESTAMP Types How to run .sql file in Oracle SQL developer tool to import database? Concatenate columns in Apache Spark DataFrame How Stuff and 'For Xml Path' work in SQL Server? Fatal error: Call to a member function query() on null

Questions with postgresql tag:

Subtracting 1 day from a timestamp date pgadmin4 : postgresql application server could not be contacted. Psql could not connect to server: No such file or directory, 5432 error? How to persist data in a dockerized postgres database using volumes input file appears to be a text format dump. Please use psql Postgres: check if array field contains value? Add timestamp column with default NOW() for new rows only Can't connect to Postgresql on port 5432 How to insert current datetime in postgresql insert query Connecting to Postgresql in a docker container from outside Is the server running on host "localhost" (::1) and accepting TCP/IP connections on port 5432? Postgresql tables exists, but getting "relation does not exist" when querying How can I start PostgreSQL on Windows? PostgreSQL INSERT ON CONFLICT UPDATE (upsert) use all excluded values Unable to create requested service [org.hibernate.engine.jdbc.env.spi.JdbcEnvironment] How to Extract Year from DATE in POSTGRESQL Auto-increment on partial primary key with Entity Framework Core psql: command not found Mac How to stop/kill a query in postgresql? PostgreSQL: role is not permitted to log in How to restart Postgresql IN vs ANY operator in PostgreSQL How to select a schema in postgres when using psql? HikariCP - connection is not available "psql: could not connect to server: Connection refused" Error when connecting to remote database PostgreSQL: Why psql can't connect to server? Postgres where clause compare timestamp How to customize the configuration file of the official PostgreSQL Docker image? How to increase the max connections in postgres? Create database from command line Postgres "psql not recognized as an internal or external command" Postgresql: error "must be owner of relation" when changing a owner object What is the difference between LATERAL and a subquery in PostgreSQL? You need to install postgresql-server-dev-X.Y for building a server-side extension or libpq-dev for building a client-side application How do I get the current timezone name in Postgres 9.3? How to find duplicate records in PostgreSQL PostgreSQL CASE ... END with multiple conditions Using COALESCE to handle NULL values in PostgreSQL How to list active connections on PostgreSQL? Postgres: How to convert a json string to text? Check Postgres access for a user How to perform update operations on columns of type JSONB in Postgres 9.4 How to create User/Database in script for Docker Postgres How to detect query which holds the lock in Postgres? Padding zeros to the left in postgreSQL Laravel: Error [PDOException]: Could not Find Driver in PostgreSQL How to calculate DATE Difference in PostgreSQL? How connect Postgres to localhost server using pgAdmin on Ubuntu? No function matches the given name and argument types Backup/Restore a dockerized PostgreSQL database