Postgresql tables exists but getting relation does not exist when querying

Question

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

User · Answer

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

User · Answer

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

User · Answer

I had to include double quotes with the table name   db  gt   d                            List of relations  Schema                       Name                        Type    Owner  -------- ----------------------------------------------- ------- -------  public   COMMONDATA NWCG AGENCIES                        table   dan       db  gt   d COMMONDATA NWCG AGENCIES Did not find any relation named  COMMONDATA NWCG AGENCIES          Double quotes   db  gt   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  gt  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  gt  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  gt  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

User · Answer

You can try   SELECT    FROM public  my table    Don t forget double quotes near my table

User · Answer

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    Probably remove it or change that false to be true  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

User · Answer

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

User · Answer

The error can be caused by access restrictions  Solution  GRANT ALL PRIVILEGES ON DATABASE my database TO my user

User · Answer

You have to include the schema if isnt a public one  SELECT   FROM  lt schema gt   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     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

[sql] Postgresql tables exists, but getting "relation does not exist" when querying

Examples related to sql

Examples related to postgresql