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