Cannot Simply Use Postgresql Table Name ("Relation Does Not Exist")

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

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

psycopg2.errors.UndefinedTable: relation does not exist (join table)

To get it to work I reworked the query as:

UPDATED. Added WHERE clause.

UPDATE
    mytable
SET
    mycolumn = a.mycolumn::boolean
FROM
    mytable AS t
    INNER JOIN (
        VALUES (28625, '1'),
            (56614, '1'),
            (86517, '1')) AS a (id, mycolumn) ON a.id = t.id
    WHERE
        a.id = mytable.id
;

When I tried your original query I got:

ERROR: table name "t" specified more than once

When I tried my comment suggestion I got:

ERROR: column reference "id" is ambiguous

The docs from here UPDATE are somewhat confusing:

alias

A substitute name for the target table. When an alias is provided, it completely hides the actual name of the table. For example, given UPDATE foo AS f, the remainder of the UPDATE statement must refer to this table as f not foo.

from_item

A table expression allowing columns from other tables to appear in the WHERE condition and update expressions. This uses the same syntax as the FROM clause of a SELECT statement; for example, an alias for the table name can be specified. Do not repeat the target table as a from_item unless you intend a self-join (in which case it must appear with an alias in the from_item).

But given the error messages I figured the UPDATE portion needed the actual table name and the FROM needed the aliased name.

PostgreSQL error: relation Table does not exist

Before this two tables have you created this tables:

• Users
• Cinemas
• Actor
• Director
• UserComments

If you have not then both of this tables will not be created.

Also please check have you created a type called RATE.

If you have already created tables I have mentioned and datatype RATE make sure you have primary key's in this table so your foreign key's can reference them.

Then, if you have done all of this do check the comment from @TheImpaler: "DEFAULT '000' on a primary key (or any key) makes little sense.".

Also, when you have primary key on the column you do not need NOT NULL constraint.

After all of that you will have two codes that work:
https://dbfiddle.uk/?rdbms=postgres_12&fiddle=a91b77594583e6768360709ef7a9f494

After commenting with the OP I have discovered that he can create the table by referencing the schema before the table name like this:

create table schema_name.Series...

Undefined table: 7 ERROR: relation V5TableName does not exist

Your query should work. The use of double-quotes seems correct.

SELECT pg_size_pretty(pg_total_relation_size('"V5TableName"'));

First make sure you are connecting to the right database cluster (a.k.a. "server"). It's defined by its data directory, or equally unambiguous by hostname and port number. Read the manual here and here.

Then make sure you are connecting to the right database within that database cluster. A Postgres database cluster consists of 1-n databases. When connecting without specifying the actual database, you end up in the maintenance database named postgres by default. That's the most likely explanation. Check with:

SELECT current_database();

Then check for the right table and schema name:

SELECT * FROM pg_tables
WHERE tablename ~* 'V5TableName';  -- ~* matches case-insensitive

The first riddle should be solved at this point.

Check your DB spelling and possible near-duplicates with:

SELECT datname FROM pg_database;

The call is without double-quotes (like you tried correctly), but requires correct capitalization:

SELECT pg_size_pretty(pg_database_size('MyDbName'));

Note the subtle difference (as documented in the manual):

• pg_database_size() takes oid or name. So pass the case-sensitive database name without double-quotes.
• pg_total_relation_size() takes regclass. So pass the case-sensitive relation name with double-quotes if you need to preserve capitalization.

pg_database_size() has to differ because there is no dedicated object identifier type for databases (no regdatabase).

The gist of it: avoid double-quoted identifiers in Postgres if at all possible. It makes your life easier.

---

Related Topics