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
smime.p7s
Description: S/MIME Cryptographic Signature
