Обсуждение: Case bug in SQL Constraint
The SQL query below reports an error if the value "uid" is provided in uppercase ("UID"). The error reported is: ERROR: CREATE TABLE: column "UID" named in key does not exist The problem is eliminated if "UID" is made "uid". Here is the full text of the query: CREATE TABLE PASSWD ( NAME VARCHAR(32) NOT NULL, PASSWD VARCHAR(32), UID INTEGER NOT NULL, GID INTEGER NOT NULL, GECOS VARCHAR(80) NOT NULL, DIR VARCHAR(256) NOT NULL, SHELL VARCHAR(256) NOT NULL, CONSTRAINT "PASSWD_PKEY" PRIMARY KEY ("UID") ) Thanks, Warren. Other Info: $ uname -a FreeBSD hibiscus.ve3wwg.org 4.4-RC FreeBSD 4.4-RC #1: Wed Oct 17 22:46:11 EDT 2001 root@hibiscus.ve3wwg.org:/usr/src/sys/compile/hibiscus i386 wwg=# select version(); version --------------------------------------------------------------------- PostgreSQL 7.2.1 on i386-portbld-freebsd4.6, compiled by GCC 2.95.3 (1 row) -- Warren W. Gay VE3WWG http://home.cogeco.ca/~ve3wwg
On Thu, 1 Aug 2002, Warren W. Gay VE3WWG wrote: > The SQL query below reports an error if the value "uid" is > provided in uppercase ("UID"). The error reported is: > > ERROR: CREATE TABLE: column "UID" named in key does not exist > > The problem is eliminated if "UID" is made "uid". > Here is the full text of the query: > > CREATE TABLE PASSWD ( > NAME VARCHAR(32) NOT NULL, > PASSWD VARCHAR(32), > UID INTEGER NOT NULL, Here you're giving an unquoted name so it gets casefolded (currently to lowercase). > GID INTEGER NOT NULL, > GECOS VARCHAR(80) NOT NULL, > DIR VARCHAR(256) NOT NULL, > SHELL VARCHAR(256) NOT NULL, > CONSTRAINT "PASSWD_PKEY" PRIMARY KEY ("UID") Here you're giving a quoted name so no casefolding occurs. In general you shoul be consistant about either quoting or not quoting table and column names, mixing them is dangerous.