Tag Archives: select

【error】postgresql relation does not exist

There have been a lot of problems with PostgresQL lately.
postgresql relation does not exist
When querling AAA tables using PostgresQL, postgresQL relation does not exist, but

SELECT   tablename   FROM   pg_tables;

AAA tables exist. It’s weird.
The search turned out to be a problem with quotes. PostgreSQL itself is not case sensitive, so if you want to create a new table with uppercase letters, you must use quotation marks, and if you want to query it, you must use quotation marks.

select * from "AAA";

The following from: http://blog.csdn.net/dream20nn/article/details/51790106
A recently developed ETL tool for the WEB requires different data sources. The first time POSTGRESQL has found a problem with double quotes:
standard SQL is case-insensitive. But PostgreSQL allows case-sensitive definition and reference methods for the names of objects in the database. This is done by enclosing the name of the object you want to support size in double quotes in the DDL. For example, you want to create a table called AAA. If you use CREATE TABLE AAA (…) ; So the table that you create is actually aaa.
CREATE TABLE “AAA” (…) if you want to CREATE an upper case AAA TABLE. ; This is the double quote way of defining the object name. The disadvantage of writing
is that the query must also refer to the object name in double quotes. SELECT * FROM “AAA”; PostgreSQL doesn’t need to go to the AAA object and return an error that aaa doesn’t exist. It is important to note that not only tables can be defined and referenced in this way, but any object (column name, index name, etc.) in PostgreSQL is valid.
In fact, traditional SQL is case-insensitive, so there is no problem as long as DDL and DLLS manipulate database objects in the traditional (without double quotes) way. The problem is that if tables are created through PostgreSQL’s pgAdmin III tool, objects are created with double quotes by default, so the DLL must also be used with double quotes. However, this is not standard and is not supported if the database is accessed through some common library function.
Recommendation:
1. The tool pgAdmin III is not recommended for creating database objects. You should still write DDL statements by hand. 2. Double quotation marks in DDL are not recommended for creating case-sensitive objects. PostgreSQL recommends using uppercase for SQL key word and lowercase for all other names.