[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:

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.

The error can be caused by access restrictions. Solution:

GRANT ALL PRIVILEGES ON DATABASE my_database TO my_user;

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

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.

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

You can try:

SELECT * 
FROM public."my_table"

Don't forget double quotes near my_table.

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