9

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?

leonbloy
  • 73,180
  • 20
  • 142
  • 190
Count Boxer
  • 673
  • 3
  • 11
  • 25
  • 1
    Shouldn't the title say "Psycopg2 doesn't like table names that start with an *uppercase* letter"? – mic Aug 12 '19 at 20:02

3 Answers3

10

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
leonbloy
  • 73,180
  • 20
  • 142
  • 190
9

Read "Identifiers and Key Words" from the manual, especially the part about "quoted identifiers".

Milen A. Radev
  • 60,241
  • 22
  • 105
  • 110
  • 7
    Huzzah! So "execute('SELECT * FROM "Books"') works. And since I also capitalize my column names, I have to use "execute('SELECT "Title" FROM "Books"'). Is it the "standard" or accepted method to always use lower cases table and field names in PostgreSQL databases? – Count Boxer May 05 '10 at 15:37
  • Writing all identifiers in lower case (and separating words with underscore) is a kind of convention, mostly for convenience. Regarding the so-called "case-folding" by the parser - the SQL standard dictates such behaviour (with one difference - when unquoted the identifiers are folded to upper-case). – Milen A. Radev May 05 '10 at 17:56
  • 1
    Quoting identifiers is something MSSQL developers seem to have a penchant for. But most other database developers tend not to do that. If you don't quote the identifiers when creating the tables, views, functions then you don't have to worry about quoting them later. If you don't quote, Postgres will fold to lower case while Oracle will do upper case. – Scott Bailey May 05 '10 at 18:20
  • Just to add to what Scott said: the solution is to put a case sensitive table or column name in quotes. (use python's quotes within quotes idea) This if for those of you who don't know what an identifier is offhand. (like me :) – reabow Jan 14 '15 at 08:19
4

I often have to use Psycopg2 with tables that have been created by other people and they use a lot of case mixing in their column names.

The solution I found is to use

from psycopg2.extensions import AsIs

and to then put the column name in a variable like: 

column_name = '"Column_Mixed_Case"'#Mind the '" quotes combination !

and pass the variable to the sql as follows

data = AsIs(column_name)
sql = "select %s from table"
cur.execute(sql,data)
sal
  • 1,199
  • 1
  • 13
  • 31