In Postgresql, What's the Difference a "Database" and a "Relation"? ('Error Relation X Does Not Exist', 'Error Database X Already Exists')

In postgresql, what's the difference a database and a relation? ('error relation x does not exist', 'error database x already exists')

My guess is that you really want to recursively GRANT the SELECT right to every relation (table and view) within the database angel_research_production. Correct?

How to grant on all tables in a database

If so, in PostgreSQL 9.0 and above you have:

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { [ TABLE ] table_name [, ...]
         | ALL TABLES IN SCHEMA schema_name [, ...] }
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

from the manual for GRANT. Note the ALL TABLES IN SCHEMA clause. Usage:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO angel_research;

If all your user-defined objects are in the public schema (see below) that'll do the trick.

In prior versions there is no such feature, but user defined functions exist as workarounds.

Pg 9.0 also has ALTER DEFAULT PRIVILEGES, which changes the default privileges assigned to newly created objects. It does not affect existing objects.

What does the error message mean?

As noted by TokenMacGuy, a relation is a table or view, not a database.

GRANT SELECT ON angel_research_production TO angel_research;

can be thought of as shorthand for:

GRANT SELECT ON TABLE angel_research_production TO angel_research
                ^^^^^

and that table(relation) doesn't exist, so you're getting the error reported above.

In the manual for GRANT or the psql \h GRANT output you'll see:

GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
    ON DATABASE database_name [, ...]
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

This shows that the privileges you can GRANT to a database are CREATE, CONNECT and TEMPORARY. There is no SELECT right on a database.

Relations? Schema? Huh?

There are four levels of organisation in Pg:

• Cluster - controlled by the postmaster, accepts connections on a given IP/port combo, contains one or more databases including the built-in template0, template1 and postgres databases. Controlled by postgresql.conf and pg_hba.conf. Your DB cluster is often created for you by an installer or package. Not to be confused with the normal meaning of cluster as a compute cluster or the general english language meaning.

• Database - contains one or more schemata or schemas. You connect to a specific database when connecting to Pg.

• Schema - contains objects including relations. If you don't specify otherwise, anything user-created goes into the public schema. Queries can reference objects in multiple schema explicitly or, via search_path, implicitly.

• Objects - Somewhat PostgreSQL specific, anything (including a relation) that exists in a schema.

  • Relations - Things that look and behave like tables, like views and tables

  • Other objects also reside in schemas, like functions, casts, indexes, sequences, operators, aggregates, etc.

relation does not exist in postgreSQL but already exist

The problem was that PostgreDB wants me to use SELECT colum FROM schema.table instead of SELECT colum FROM table. And that's all. Thanks everyone

PostgreSQL Error: Relation already exists

I finally discover the error. The problem is that the primary key constraint name is equal the table name. I don know how postgres represents constraints, but I think the error "Relation already exists" was being triggered during the creation of the primary key constraint because the table was already declared. But because of this error, the table wasnt created at the end.

Postgresql tables exists, but getting relation does not exist when querying

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

Cannot simply use PostgreSQL table name (relation does not exist)

From what I've read, 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;

See also http://www.postgresql.org/docs/8.3/static/ddl-schemas.html

DBeaver / PostgreSQL: Error: database already exists, but I can't find it

You can query catalog view pg_database to check if the database already exists:

select datname from pg_database WHERE datname = 'president'

And drop it with drop database:

drop database president;

Note that Postgres' drop database syntax supports the if exists clause, which may come handy in your use case:

drop database if exists president;

Relation department does not exist EF Core migration error

You created table "Department" but then used table Department. Those are not the same. Postgres folds all non-doubled quoted ("...") identifiers to lower case (as opposed to the SQL Standard of folding them to uppercase) but keeps the exact case when identifiers are double quoted. However when created with double quotes you must always double quote when referencing.

Error while testing postgresql database with python

If you're trying to see if a database exists:

curs.execute("SELECT exists(SELECT 1 from pg_catalog.pg_database where datname = %s)", ('mydb',))

It sounds like you may be confused by the difference between a database and a table.

---

Related Topics