Misleading ERROR: relation [“schema_name.table_name”] does not exist [closed]

Question

Should not it be ERROR: relation [“schema name”].["table_name"] does not exist instead? Lots of people use case sensitive relation names with dots and so forth. Eg:

t=# select * from public.t1;
ERROR:  relation "public.t1" does not exist
LINE 1: select * from public.t1;
                      ^
t=# create table "public.t1" (i int);
CREATE TABLE
t=# select * from public.t1;
ERROR:  relation "public.t1" does not exist
LINE 1: select * from public.t1;

I am used to getting this error so much that I did not notice that it is misleading. And yet today I hit it with relation.name and realized that error should not use double quotes like this. Is it intentional or should I address it as a bug?

Answer 1

You're creating a table with a dot identifier.

CREATE TABLE "public.t1" ( a int );
\d public.t1
Did not find any relation named "public.t1".

But, then if you quote it again there..

\d "public.t1"
   Table "public.public.t1"
 Column |  Type   | Modifiers 
--------+---------+-----------
 a      | integer | 

SELECT table_catalog, table_schema, table_name
FROM information_schema.tables
WHERE table_name LIKE '%t1%';
 table_catalog | table_schema | table_name 
---------------+--------------+------------
 test          | public       | public.t1

So instead use this if you want to be goofy,

SELECT * FROM "public.t1";

Or, simply never quote identifiers.

Quoted identifiers can contain any character other than a double quote itself. (To include a double quote, write two double quotes.) This allows constructing table or column names that would otherwise not be possible, such as ones containing spaces or ampersands. The length limitation still applies.

And, also periods.

This is agianst the convention of PostgreSQL. Unless you're doing something like an automated migration that needs to avoid collisions, I would higly suggest you avoid quoted identifiers entirely.

• Always create things unquoted.
• Always references things unquoted.

It's not just special characters either. Quoted identifiers make your schema caps-sensitive. This can make your life needlessly more difficult.