Обсуждение: Help with array constraints

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.

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

On Thursday 03 Apr 2003 3:48 pm, 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?

You've asked it to compare the whole array of single-chars with a single-char.
PG says it can't do that.

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

You might be able to add separate FK constraints (see CREATE TABLE or ALTER
TABLE) for each array element.

CREATE TABLE test (
...
CONSTRAINT first_letter FOREIGN KEY (letter[0]) REFERENCES _test(id),
CONSTRAINT first_letter FOREIGN KEY (letter[1]) REFERENCES _test(id),
CONSTRAINT first_letter FOREIGN KEY (letter[2]) REFERENCES _test(id)
)

Don't know if that will work - I'm wary of using arrays myself. I'm a bit
surprised that you can create a primary key on an array.

The other option is that you might need to alter your design. Either three
separate fields or a separate table with (letter_index, char_value) so you
only need the FK constraint on "char_value", and letter_index can be
constrained with a CHECK.

--
  Richard Huxton

> >
> > What must I do?
>
> You might be able to add separate FK constraints (see CREATE
> TABLE or ALTER
> TABLE) for each array element.
>
> CREATE TABLE test (
> ...
> CONSTRAINT first_letter FOREIGN KEY (letter[0]) REFERENCES _test(id),
> CONSTRAINT first_letter FOREIGN KEY (letter[1]) REFERENCES _test(id),
> CONSTRAINT first_letter FOREIGN KEY (letter[2]) REFERENCES _test(id)
> )
>
> Don't know if that will work - I'm wary of using arrays myself. I'm a bit
> surprised that you can create a primary key on an array.

Whoops, that's not actually in my application!

> The other option is that you might need to alter your design.
> Either three
> separate fields or a separate table with (letter_index,
> char_value) so you
> only need the FK constraint on "char_value", and letter_index can be
> constrained with a CHECK.

Design altering is a BAD thing because I am attempting a port from a DBMS
that supports arrays, and they are used quite extensively in places.
Adjusting the schema would be to take a large hit, and the powers that be
are already weary about doing the port in the first place. Seeing this was
now supported a big joy for me, because it makes it so much easier.

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

Probably write specialized triggers. As a note, if test is even marginally
large, the check for update/delete on _test is going to probably be
immensely painful unless you have an index on the individual elements of
the array rather than the array as a whole (AFAIK that'll index the
complete array, which means that it's probably not terribly useful for
searching for subelements).

If you only want to do insert/update on test time checks (and not worry
about update/delete from _test) this becomes somewhat easier.  You can
make a function to do the checks against _test for each element value and
use it in a check constraint.

On Thu, 3 Apr 2003, Jason Hihn wrote:

> > >
> > > What must I do?
> >
> > You might be able to add separate FK constraints (see CREATE
> > TABLE or ALTER
> > TABLE) for each array element.
> >
> > CREATE TABLE test (
> > ...
> > CONSTRAINT first_letter FOREIGN KEY (letter[0]) REFERENCES _test(id),
> > CONSTRAINT first_letter FOREIGN KEY (letter[1]) REFERENCES _test(id),
> > CONSTRAINT first_letter FOREIGN KEY (letter[2]) REFERENCES _test(id)
> > )
> >
> > Don't know if that will work - I'm wary of using arrays myself. I'm a bit
> > surprised that you can create a primary key on an array.
>
> Whoops, that's not actually in my application!
>
> > The other option is that you might need to alter your design.
> > Either three
> > separate fields or a separate table with (letter_index,
> > char_value) so you
> > only need the FK constraint on "char_value", and letter_index can be
> > constrained with a CHECK.
>
> Design altering is a BAD thing because I am attempting a port from a DBMS
> that supports arrays, and they are used quite extensively in places.
> Adjusting the schema would be to take a large hit, and the powers that be
> are already weary about doing the port in the first place. Seeing this was
> now supported a big joy for me, because it makes it so much easier.


Well that constraint reply didn't contain what I was expecting to see after I
started reading it so...

You probably want to write your own function(s) and install it(them) as
trigger(s).

create function letter_fk () returns trigger as '
declare
    ind    integer;
begin
    for ind in array_dims(NEW.letters) loop
        perform 1 from _test where id = NEW.letters[ind];
        if not found then
            raise exception ''My foriegn key constraint
violation'';
        end if;
    end loop;

    return NEW;
end;
' as language 'plpgsql';

create trigger my_array_fkey
 before insert
 on test
 for each row execute procedure letter_fk();


Or something like that anyway. Also that's only a start.


--
Nigel J. Andrews

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

Not acceptable, or I would have done it. ;-) My alternative is to break it
out into tables, but since each record has several arrays per, that means
there's many, many little tables (with key data) that I'd have to make and
maintain.

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

Well then what should I use? "char" is an internal type, and it is not
specified if it is approved for use outside the internal uses...

Has no one written a generic trigger to do this? Also could be implemented
is trigger based array bounding if the bounds can be known to the trigger.

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Nigel J. Andrews
> Sent: Thursday, April 03, 2003 11:19 AM
> To: Jason Hihn
> Cc: Richard Huxton; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Help with array constraints
>
>
> On Thu, 3 Apr 2003, Jason Hihn wrote:
> You probably want to write your own function(s) and install it(them) as
> trigger(s).
>
> create function letter_fk () returns trigger as '
> declare
>     ind    integer;
> begin
>     for ind in array_dims(NEW.letters) loop
>         perform 1 from _test where id = NEW.letters[ind];
>         if not found then
>             raise exception ''My foriegn key constraint
> violation'';
>         end if;
>     end loop;
>
>     return NEW;
> end;
> ' as language 'plpgsql';
>
> create trigger my_array_fkey
>  before insert
>  on test
>  for each row execute procedure letter_fk();
>
>
> Or something like that anyway. Also that's only a start.

Ah that's more like what I'm looking for. I have to attempt a select of the
new letter in the contrainted-to table. If it finds it, success! If the
contrainted-to table has an index, it should be very fast no matter how many
letters it's being constrained against...
"SELECT NEW.letter FROM constraint_table WHERE letter=id"
if null set raise exception.

I would have thought this would be built in? Can it be?

Jason Hihn wrote:
> Ah that's more like what I'm looking for. I have to attempt a select of the
> new letter in the contrainted-to table. If it finds it, success! If the
> contrainted-to table has an index, it should be very fast no matter how many
> letters it's being constrained against...
> "SELECT NEW.letter FROM constraint_table WHERE letter=id"
> if null set raise exception.

I didn't follow this thread in its entirety, but it sounds like you need
an "array contains element x" function for use as a trigger? I think
there may be something that does that already in contrib/array or
contrib/intarray (I see you're not using int arrays, but maybe you could
port the C code).


> I would have thought this would be built in? Can it be?
>

I'm currently working on a fairly major upgrade to array support. No
promises on exactly what will make it into 7.4, but I'm planning to look
at contrib/array and contrib/intarray and rewrite or move as much of it
as makes sense into the backend (subject of course to acceptance of the
patches).

Joe