Psql ошибка отношение не существует - Решение и исправление самых разных ошибок на TopOshibok.ru

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.

asked Apr 20, 2016 at 19:38

patkilpatkil

1,9493 gold badges16 silver badges17 bronze badges

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

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

answered Apr 20, 2016 at 19:44

You can try:

SELECT * 
FROM public."my_table"

Don’t forget double quotes near my_table.

4b0

22k30 gold badges95 silver badges142 bronze badges

answered Sep 3, 2019 at 2:13

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

answered Sep 26, 2019 at 21:57

dfrankowdfrankow

20.2k41 gold badges152 silver badges216 bronze badges

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.

answered Nov 13, 2020 at 2:29

Jeremy ThompsonJeremy Thompson

62.1k36 gold badges195 silver badges322 bronze badges

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

answered Sep 17, 2019 at 16:51

dmigwidmigwi

611 silver badge5 bronze badges

The error can be caused by access restrictions. Solution:

GRANT ALL PRIVILEGES ON DATABASE my_database TO my_user;

answered Oct 1, 2020 at 0:47

MarcelMarcel

2,8802 gold badges27 silver badges46 bronze badges

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

answered Oct 9, 2020 at 14:05

ChewyChewy

6516 silver badges21 bronze badges

Please ensure that:

Your password is non-empty
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.

answered Mar 6, 2022 at 8:21

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

answered Oct 25, 2021 at 8:00

ErickErick

311 silver badge4 bronze badges

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

answered Oct 30, 2020 at 12:03

samsrisamsri

1,10414 silver badges25 bronze badges

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

Toni

1,5554 gold badges15 silver badges23 bronze badges

answered Jul 1, 2021 at 17:36

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

answered Feb 5 at 18:11

Источник

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.

asked Apr 20, 2016 at 19:38

patkilpatkil

1,9493 gold badges16 silver badges17 bronze badges

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

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

answered Apr 20, 2016 at 19:44

You can try:

SELECT * 
FROM public."my_table"

Don’t forget double quotes near my_table.

4b0

22k30 gold badges95 silver badges142 bronze badges

answered Sep 3, 2019 at 2:13

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

answered Sep 26, 2019 at 21:57

dfrankowdfrankow

20.2k41 gold badges152 silver badges216 bronze badges

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.

answered Nov 13, 2020 at 2:29

Jeremy ThompsonJeremy Thompson

62.1k36 gold badges195 silver badges322 bronze badges

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

answered Sep 17, 2019 at 16:51

dmigwidmigwi

611 silver badge5 bronze badges

The error can be caused by access restrictions. Solution:

GRANT ALL PRIVILEGES ON DATABASE my_database TO my_user;

answered Oct 1, 2020 at 0:47

MarcelMarcel

2,8802 gold badges27 silver badges46 bronze badges

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

answered Oct 9, 2020 at 14:05

ChewyChewy

6516 silver badges21 bronze badges

Please ensure that:

Your password is non-empty
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.

answered Mar 6, 2022 at 8:21

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

answered Oct 25, 2021 at 8:00

ErickErick

311 silver badge4 bronze badges

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

answered Oct 30, 2020 at 12:03

samsrisamsri

1,10414 silver badges25 bronze badges

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

Toni

1,5554 gold badges15 silver badges23 bronze badges

answered Jul 1, 2021 at 17:36

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

answered Feb 5 at 18:11

Источник

PostgreSQL is an RDBM system that is used for creating databases that store data in tabular form. In PostgreSQL, tables are also referred to as relations. The relations must be named correctly, otherwise, users may encounter an error message. Moreover, the table names are essential for accessing data from PostgreSQL, as they help us retrieve information from the tables.

This guide will explain how to fix the “relation does not exist” error in the PostgreSQL database.

How to Fix the “relation does not exist” Error in Postgres?

The Error “relation does not exist” occurs in the PostgreSQL database when the user makes mistakes while calling the table name. The error message appears if the user has made a spelling mistake, uses the wrong spelling convention, etc. It can also give an error message if the relation is unavailable in the database. To fix the “relation does not exist” error in PostgreSQL databases, follow the simple steps mentioned below:

Prerequisite

To use the PostgreSQL databases and tables, it is required to connect to its server using the following command:

psql --username=postgres

Running the above command will prompt the user to enter the Master password for the PostgreSQL database:

After connecting to the Postgres user in the PostgreSQL server, head into the database by using the following command:

\c JOIN

Executing the above command will direct the user inside the selected database:

Use the following command to get the list of all the tables available in the database with their names:

\dt

Reason 1: Spelling Mistake

A common mistake a user can make while calling the table in the PostgreSQL database is typing the wrong spelling as the following code block contains:

SELECT * FROM Cars;

Running the above code displayed the error that the “relation “cars” does not exist”:

The spelling of the select table is “Car” not “Cars” so the user needs to write the exact spelling of the relations as displayed in the following code block:

SELECT * FROM "Car";

Reason 2: Table is Not Available/Exist

Access the data from the vehicles table using the following query:

SELECT * FROM Vehicles;

Running the above code has displayed the “relation “vehicles” does not exist” error:

Use the following command to get the list of all the tables available in the database:

\dt

The following are the tables available in the PostgreSQL database so the user can access only these tables:

Use the following command to access the “employee” table from the PostgreSQL database:

SELECT * FROM employee;

That’s all about solving the stated error in PostgreSQL when the query cant fetch a table from the database.

Conclusion

To fix the “relation does not exist” error in the PostgreSQL database, simply connect to the PostgreSQL server and head into the database. After that, check all the tables/relations available on the database by using the “\dt” command to get the names of all the tables. After getting the list of all the tables, simply use the correct spelling and case convention to call the table. This guide has explained the process to solve the “relation does not exist” error in PostgreSQL databases.

Источник

I just created a new user that I want to have access to a limited number of our public tables. The user is created and I granted privs to one public table for now. I then logged into the DB as that user and tried to run a SELECT on the table that the user should be able to get to but I must of missed a step or did something wrong because when I run the query I get:

relation [table] does not exist

Below are the steps I took, in order.

CREATE USER pqb2b WITH PASSWORD 'foo'


 select * from pg_user;
 usename  | usesysid | usecreatedb | usesuper | usecatupd |  passwd   | valuntil | useconfig
 ----------+----------+-------------+----------+-----------+----------+----         |
postgres |       10 | t           | t        | t         | ******** |          | 
 pgb2b   | 17267767 | t           | f        | f         | ******** | infinity |

(1 rows)

GRANT ALL on b_users to pgb2b;



SELECT
schemaname||'.'||tablename
FROM
pg_tables
WHERE
has_table_privilege (
    'pgb2b',
    schemaname||'.'||tablename,
    'select'
 )
AND
schemaname NOT IN (
    'pg_catalog',
    'information_schema'
 );

public.b_users
(1 row)



~ postgres$ psql -U pgb2b  mydb
psql (9.0.3)
Type "help" for help.

mydb=> select * from b_users;
ERROR:  relation "b_users" does not exist
LINE 1: select * from b_users;

 mydb=> \d+ b_users
 Did not find any relation named "b_users".

Источник

A quick explanation of how to solve PostgreSQL saying relation does not exist

If you have a PostgreSQL database and a table named Car for example and you try doing

SELECT * FROM Car

you’ll see an error saying

Query 1 ERROR: ERROR:  relation "car" does not exist
LINE 1: SELECT * FROM Car

One issue might be the table actually does not exist.

But if it does, this error appears because PostgreSQL raises errors on tables with mixed cases.

Use this syntax instead:

SELECT * FROM "Car"

Источник