Help with array constraints

From: Jason Hihn <jhihn(at)paytimepayroll(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Help with array constraints
Date: 2003-04-03 14:48:26
Message-ID: NGBBLHANMLKMHPDGJGAPKEGGCGAA.jhihn@paytimepayroll.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Two tables (simplified):

CREATE TABLE _test (
id CHAR(1),
PRIMARY KEY(id)
);

INSERT INTO _test VALUES ('a');
INSERT INTO _test VALUES ('b');

CREATE TABLE test (
letter CHAR(1)[3] NOT NULL REFERENCES _test(id)
PRIMARY KEY(letter)
);

CREATE TABLE / PRIMARY KEY will create implicit index 'test_pkey' for table
'test'
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
check(s)
ERROR: Unable to identify an operator '=' for types 'character[]' and
'character'
You will have to retype this query using an explicit cast

Can someone please explain that in English? I want ALL the letter field
values to be checked against what is in the _test table id field when a row
is inserted. For example, 'a' and 'b' is in the _test table now, if I insert
an 'a' or 'b' into test, it will suceed. If I insert a 'c' or 'd' it should
fail.

What must I do?

I am on v7.2.

Thank you.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2003-04-03 14:48:36 Re: unable to dump database, toast errors
Previous Message Lonni J Friedman 2003-04-03 14:42:25 Re: unable to dump database, toast errors