Re: Help with array constraints

On Thu, 3 Apr 2003, Jason Hihn wrote:

> 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?

There's no way to compare character string (char(1)) and character string
array (char(1)[3]) with '=' operator. PostgreSQL uses triggers to
implement foreign key constraints. Trigger creation fails because the type
of _test.id doesn't match that of test.letter.

> 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.

Split your fixed sized array to separate table columns and make them
foreign keys and it will work.

And probably you don't want to use char(1), because it uses at least 5
bytes of storage for each single character.

--
Antti Haapala