Thread: BUG #1674: CREATE TABLE "name" (with double quotes) and resulting behavior
The following bug has been logged online: Bug reference: 1674 Logged by: Herman Bos Email address: hbos@osso.nl PostgreSQL version: 7.4.7 Operating system: Ubuntu Linux 5.04 Description: CREATE TABLE "name" (with double quotes) and resulting behavior Details: for the create table in question: http://users.lichtsnel.nl/~spacey/school/vib/vib.sql entered into the database like this: psql -d vib -U vib < vib.sql results on my system vib=> \d List of relations Schema | Name | Type | Owner --------+----------+-------+------- public | SANCTIES | table | vib (1 row) vib=> select * from SANCTIES; ERROR: relation "sancties" does not exist vib=> \d SANCTIES Did not find any relation named "SANCTIES". After realizing that it occurred after putting the tablename between double qoutes. The same behavior happend with the column names. vib=> select * from "bedrag"; ERROR: relation "bedrag" does not exist vib=> select bedrag from sancties; ERROR: column "bedrag" does not exist vib=> select "bedrag" from sancties; ERROR: column "bedrag" does not exist vib=> select "BEDRAG" from sancties; BEDRAG -------- (0 rows) I was wondering if this behavior is correct. According to my local postgresql poweruser its a bug.
Herman Bos wrote: > vib=> select * from "bedrag"; > ERROR: relation "bedrag" does not exist Naturally: there is no such table, regardless of case. > vib=> select "BEDRAG" from sancties; > BEDRAG > -------- > (0 rows) Not with the SQL you provided: neilc=# select "BEDRAG" from sancties; ERROR: relation "sancties" does not exist > I was wondering if this behavior is correct. This behavior is intended, although it is true some people find it confusing. The logic is: - identifiers specified without double-quotes are folded to lower case - identifiers specified with double-quotes are not case folded So your DDL creates a table called SANCTIES since the table name is double-quoted, whereas "SELECT * FROM SANCTIES" is actually looking for a table called "sancties". See here for more info: http://www.postgresql.org/docs/8.0/interactive/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS -Neil