CODEWITHSUNDEEP
sqlpostgresql
patkil
patkil

Reputation: 2089

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.

Upvotes: 171

Views: 442352

Answers (13)

Martijn Dirkse
Martijn Dirkse

Reputation: 667

In my case, it was enough to add quotes to the table name.

SELECT * FROM "otherMessage"

instead of

SELECT * FROM otherMessage

Upvotes: 0

Matheus de Oliveira
Matheus de Oliveira

Reputation: 11

I was using psql from PostgreSQL, and somehow I created the table in the "postgres=#" directory instead of first connecting to the database and creating it there.

So make sure that you connected to the database you want before creating tables

Upvotes: 0

Kritika
Kritika

Reputation: 41

Please ensure that:

  1. Your password is non-empty
  2. In case it is empty, do not pass the password param in the connection string

This is one of the most common errors when starting out with the tutorial.

Upvotes: 3

Erick
Erick

Reputation: 31

Keep all your table names in lower case because when you rollback and then go to latest, it's looking for lowercase apparently.

Upvotes: 2

terion_style
terion_style

Reputation: 11

Lets say we have database name as students and schema name as studentinformation then to use all the table of this schema we need to set the path first which we can do in postgresql like:

client.connect()
.then(()=>console.log("connected succesfully"))
.then(()=>client.query("set search_path to students"))
.then(()=>client.query("show search_path"))
.then(()=>client.query("set search_path to studentinformation"))
.then(()=>client.query("show search_path"))
.then(results => console.table(results.rows)) //setting the search path

Upvotes: 0

Jeremy Thompson
Jeremy Thompson

Reputation: 65534

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.

Upvotes: 29

samsri
samsri

Reputation: 1104

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

Upvotes: 1

Chewy
Chewy

Reputation: 651

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

Upvotes: 3

Marcel
Marcel

Reputation: 3255

The error can be caused by access restrictions. Solution:

GRANT ALL PRIVILEGES ON DATABASE my_database TO my_user;

Upvotes: 6

dfrankow
dfrankow

Reputation: 21357

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

Upvotes: 27

dmigwi
dmigwi

Reputation: 327

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

Upvotes: 6

Richie Rizal Amir
Richie Rizal Amir

Reputation: 855

You can try:

SELECT * 
FROM public."my_table"

Don't forget double quotes near my_table.

Upvotes: 69

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48177

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

Upvotes: 150

Related Questions