To add to the other answer, the behaviour of Postresql about case-sentivity of identifiers (table names and column names) is :
- If the name is not quoted, it is converted to lowercase. Otherwise, it's left untouched.
- Afterwards, a case sensitive match is attempted.
This applies not only for queries, but also for schema manipulation; in particular: table creation.
The rule of gold is consistency:
If you want to write portable applications you are advised to always quote a particular name or never quote it
The posted problem arose, probably, because the tables and columns names were quoted at creation time (hence, they were not converted to lowercase). So, now they must be quoted (and case-sensitive) in all queries.
Normally, all works ok.
db=# create table Xxx (id integer); -- unquoted, will be converted to lowercase
CREATE TABLE
db=# select * from xXx; -- this works ok
id
----
(0 rows)
db=# create table "Xxxx" (id integer); -- will be left untouched
CREATE TABLE
db=# select * from xxxx; -- bad
ERROR: relation "xxxx" does not exist
LINE 1: select * from xxxx;
db=# select * from Xxxx; -- bad
ERROR: relation "xxxx" does not exist
LINE 1: select * from Xxxx;
^
db=# select * from "Xxxx"; -- ok
id
----
(0 rows)
db=# \dt *xx*
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | Xxxx | table | postgres
public | xxx | table | postgres