Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

python - Psycopg2 doesn't like table names that start with a lower case letter

I am running ActiveState's ActivePython 2.6.5.12 and PostgreSQL 9.0 Beta 1 under Windows XP.

If I create a table with an upper case first letter (i.e. Books), psycopg2 returns the "Programming Error: relation "books" does not exist" error message when I run the select statement: execute("SELECT * FROM Books"). The same error is returned if I run: execute("SELECT * FROM books"). However, if I change the table to a lower case first name (i.e. books), then either of the above statements works.

Are tables name supposed to have a lower case first name? Is this a setting or a feature or a bug? Am I missing something obvious?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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 golden rule 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 as expected.

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...