Received: from relay02.esat.net (relay02.esat.net [192.111.39.21]) by hub.org (8.10.1/8.10.1) with ESMTP id e57EMOW93839 for ; Wed, 7 Jun 2000 10:22:24 -0400 (EDT) Received: from (ebeon.com) [193.120.161.91] by relay02.esat.net with esmtp id 12zgit-00025R-00; Wed, 07 Jun 2000 15:22:23 +0100 Message-ID: <393E6940.E3CDD1BD@ebeon.com> Date: Wed, 07 Jun 2000 16:24:49 +0100 From: Niall Smart X-Mailer: Mozilla 4.7 [en] (X11; I; Linux 2.2.12 i386) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Problem with subquery in CHECK constraint. Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200006/68 Hi, I'm using a general lookup table defined as following to avoid a proliferation of lookup tables: CREATE TABLE XREF ( XREF_GROUP VARCHAR(12) NOT NULL, XREF_CD VARCHAR(8) NOT NULL, XREF_VALUE VARCHAR(128), PRIMARY KEY (XREF_GROUP, XREF_CD) ); INSERT INTO XREF VALUES ('CUST_TYPE', 'EXP', 'Expired'); INSERT INTO XREF VALUES ('CUST_TYPE', 'ACTIV', 'Active'); INSERT INTO XREF VALUES ('CUST_TYPE', 'POT', 'Potential'); I'm trying to define a check constraint to validate lookup codes used, for example: CREATE TABLE CUST ( CUST_ID INTEGER NOT NULL, NAME VARCHAR(64) NOT NULL, TYPE_CD VARCHAR(8) NOT NULL, CONSTRAINT CUST_PK PRIMARY KEY (CUST_ID), CONSTRAINT TYPE_CD_OK CHECK ( EXISTS (SELECT 1 FROM XREF WHERE XREF_GROUP = 'CUST_TYPE' AND XREF_CD = TYPE_CD) ) ); However when trying to insert into CUST I get the following error: ERROR: ExecEvalExpr: unknown expression type 108 Is this a bug in PostGreSQL? I can work around it by defining a function and using it in the CHECK constraint for now. See the appended test file for example SQL. By the way, there was a massive performance difference in using: SELECT $2 IS NULL OR EXISTS (SELECT 1 FROM XREF WHERE XREF_GROUP = $1 AND XREF_CD = $2) versus the slower: SELECT $2 IN (SELECT XREF_CD FROM XREF WHERE XREF_GROUP = $1) for validation, is this to be expected? Here's the test case: DROP TABLE XREF; CREATE TABLE XREF ( XREF_GROUP VARCHAR(12) NOT NULL, XREF_CD VARCHAR(8) NOT NULL, XREF_VALUE VARCHAR(128), PRIMARY KEY (XREF_GROUP, XREF_CD) ); INSERT INTO XREF VALUES ('CUST_TYPE', 'EXP', 'Expired'); INSERT INTO XREF VALUES ('CUST_TYPE', 'ACTIV', 'Active'); INSERT INTO XREF VALUES ('CUST_TYPE', 'POT', 'Potential'); DROP FUNCTION VALID_XREF(VARCHAR(12), VARCHAR(8)); CREATE FUNCTION VALID_XREF(VARCHAR(12), VARCHAR(8)) RETURNS BOOLEAN AS 'SELECT $2 IS NULL OR EXISTS (SELECT 1 FROM XREF WHERE XREF_GROUP = $1 AND XREF_CD = $2)' LANGUAGE 'SQL'; -- -- Much slower version: -- -- 'SELECT $2 IN (SELECT XREF_CD FROM XREF WHERE XREF_GROUP = $1)' -- DROP TABLE CUST; CREATE TABLE CUST ( CUST_ID INTEGER NOT NULL, NAME VARCHAR(64) NOT NULL, TYPE_CD VARCHAR(8) NOT NULL, CONSTRAINT CUST_PK PRIMARY KEY (CUST_ID), CONSTRAINT TYPE_CD_OK CHECK ( EXISTS (SELECT 1 FROM XREF WHERE XREF_GROUP = 'CUST_TYPE' AND XREF_CD = TYPE_CD) ) ); INSERT INTO CUST VALUES (1, 'Joe Bloggs', 'POT'); DROP TABLE CUST; CREATE TABLE CUST ( CUST_ID INTEGER NOT NULL, NAME VARCHAR(64) NOT NULL, TYPE_CD VARCHAR(8) NOT NULL, CONSTRAINT CUST_PK PRIMARY KEY (CUST_ID), CONSTRAINT TYPE_CD_OK CHECK (VALID_XREF('CUST_TYPE', TYPE_CD)) ); INSERT INTO CUST VALUES (1, 'Joe Bloggs', 'ACTIV'); INSERT INTO CUST VALUES (2, 'Jim Smith', 'foo'); -- Niall Smart email: niall.smart@ebeon.com phone: (087) 8052390