Relation does not exist postgresql ошибка

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.

This error means that you’re not referencing the table name correctly. One common reason is that the table is defined with a mixed-case spelling, and you’re trying to query it with all lower-case.

In other words, the following fails:

CREATE TABLE "SF_Bands" ( ... );

SELECT * FROM sf_bands;  -- ERROR!

Use double-quotes to delimit identifiers so you can use the specific mixed-case spelling as the table is defined.

SELECT * FROM "SF_Bands";

Re your comment, you can add a schema to the «search_path» so that when you reference a table name without qualifying its schema, the query will match that table name by checked each schema in order. Just like PATH in the shell or include_path in PHP, etc. You can check your current schema search path:

SHOW search_path
  "$user",public

You can change your schema search path:

SET search_path TO showfinder,public;

Read more about the search_path here: https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH

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"

What you had originally was a correct syntax — for tables, not for schemas. As you did not have a table (dubbed ‘relation’ in the error message), it threw the not-found error.

I see you’ve already noticed this — I believe there is no better way of learning than to fix our own mistakes

But there is something more. What you are doing above is too much on one hand, and not enough on the other.

Running the script, you

1. create a schema
2. create a role
3. grant SELECT on all tables in the schema created in (1.) to this new role_
4. and, finally, grant all privileges (CREATE and USAGE) on the new schema to the new role

The problem lies within point (3.) You granted privileges on tables in replays — but there are no tables in there! There might be some in the future, but at this point the schema is completely empty. This way, the GRANT in (3.) does nothing — this way you are doing too much.

But what about the future tables?

There is a command for covering them: ALTER DEFAULT PRIVILEGES. It applies not only to tables, but:

Currently [as of 9.4], only the privileges for tables (including views and foreign tables), sequences, functions, and types (including domains) can be altered.

There is one important limitation, too:

You can change default privileges only for objects that will be created by yourself or by roles that you are a member of.

This means that a table created by alice, who is neither you nor a role than you are a member of (can be checked, for example, by using \du in psql), will not take the prescribed access rights. The optional FOR ROLE clause is used for specifying the ‘table creator’ role you are a member of. In many cases, this implies it is a good idea to create all database objects using the same role — like mydatabase_owner.

A small example to show this at work:

CREATE ROLE test_owner; -- cannot log in
CREATE SCHEMA replays AUTHORIZATION test_owner;
GRANT ALL ON SCHEMA replays TO test_owner;

SET ROLE TO test_owner; -- here we change the context, 
                        -- so that the next statement is issued as the owner role

ALTER DEFAULT PRIVILEGES IN SCHEMA replays GRANT SELECT ON TABLES TO alice;

CREATE TABLE replays.replayer (r_id serial PRIMARY KEY);

RESET ROLE; -- changing the context back to the original role

CREATE TABLE replays.replay_event (re_id serial PRIMARY KEY);

-- and now compare the two

\dp replays.replayer
                                   Access privileges
 Schema  │   Name   │ Type  │       Access privileges       │ Column access privileges 
─────────┼──────────┼───────┼───────────────────────────────┼──────────────────────────
 replays │ replayer │ table │ alice=r/test_owner           ↵│ 
         │          │       │ test_owner=arwdDxt/test_owner │ 

\dp replays.replay_event
                               Access privileges
 Schema  │     Name     │ Type  │ Access privileges │ Column access privileges 
─────────┼──────────────┼───────┼───────────────────┼──────────────────────────
 replays │ replay_event │ table │                   │ 

As you can see, alice has no explicit rights on the latter table. (In this case, she can still SELECT from the table, being a member of the public pseudorole, but I didn’t want to clutter the example by revoking the rights from public.)