From: | "Florian G(dot) Pflug" <fgp(at)phlo(dot)org> |
---|---|
To: | Postgresql-Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Check constraint on domain over an array not executed for array literals |
Date: | 2009-11-12 19:02:20 |
Message-ID: | 4AFC5BBC.90202@phlo.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi
While trying to create a domain over an array type to enforce a certain
shape or certain contents of an array (like the array being only
one-dimensional or not containing NULLs), I've stumbled over what I
believe to be a bug in postgresql 8.4
It seems that check constraints on domains are *not* executed for
literals of the domain-over-array-type - in other words, for expressions
like:
array[...]::<my-domain-over-array-type>.
They are, however, executed if I first force the array to be of the base
type, and then cast it to the array type.
Here is an example that reproduces the problem:
----------------------------------------
create domain myintarray as int[] check (
-- Check that the array is neither null, nor empty,
-- nor multi-dimensional
(value is not null) and
(array_length(value,1) is not null) and
(array_length(value,1) > 0) and
(array_length(value,2) is null)
);
select null::myintarray; -- Fails (Right)
select array[]::myintarray; -- Succeeds (Wrong)
select array[]::int[]::myintarray; -- Fails (Right)
select array[1]::myintarray; -- Succeeds (Right)
select array[1]::int[]::myintarray; -- Succeeds (Right)
select array[array[1]]::myintarray; -- Succeeds (Wrong)
select array[array[1]]::int[][]::myintarray; -- Fails (Right)
----------------------------------------
I guess the reason is that the "::arraytype" part of
"array[...]::arraytype" isn't really a cast at all, but instead part of
the array literal syntax. Hence, array[]::myintarray probably creates an
empty myintarray instance, and then adds the elements between the square
brackets (none) - with none of this steps triggering a run of the check
constraint.
I still have the feeling that this a bug, though. First, because it
leaves you with no way at guarantee that values of a given domain always
fulfill certain constraints. And second because "array[...]::arraytype"
at least *looks* like a cast, and hence should behave like one too.
best regards,
Florian Pflug
From | Date | Subject | |
---|---|---|---|
Next Message | Greg Smith | 2009-11-12 19:05:52 | Re: CommitFest 2009-11 Call for Reviewers |
Previous Message | Bernd Helmle | 2009-11-12 18:56:57 | Re: ALTER TABLE...ALTER COLUMN vs inheritance |