Question

[Solved] 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 *
    FROM "my_table"
' {}

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

Enquirer: patkil

||

Solution #1:

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
Respondent: patkil

Solution #2:

You can try:

SELECT * 
FROM public."my_table"

Don’t forget double quotes near my_table.

Solution #3:

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
 ...

db=> d COMMONDATA_NWCG_AGENCIES
Did not find any relation named "COMMONDATA_NWCG_AGENCIES".

???

Double quotes:

db=> d "COMMONDATA_NWCG_AGENCIES"
                         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;
 ID 
----
  1
(1 row)

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

DROP TABLE IF EXISTS "COMMONDATA_NWCG_AGENCIES";

CREATE TABLE "COMMONDATA_NWCG_AGENCIES" (
...
Respondent: Richie Rizal Amir

Solution #4:

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.

Respondent: dfrankow

Solution #5:

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);

Solutions:

  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

Respondent: Jeremy Thompson

Solution #6:

The error can be caused by access restrictions. Solution:

GRANT ALL PRIVILEGES ON DATABASE my_database TO my_user;
Respondent: dmigwi

Solution #7:

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,
    CONSTRAINT test_pk PRIMARY KEY (id)
)

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

Respondent: Marcel

Solution #8:

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

Respondent: Chewy

The answers/resolutions are collected from stackoverflow, are licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0 .

Most Popular

To Top
India and Pakistan’s steroid-soaked rhetoric over Kashmir will come back to haunt them both clenbuterol australia bossier man pleads guilty for leadership role in anabolic steriod distribution conspiracy