Skip site navigation (1) Skip section navigation (2)

Check constraint on domain over an array not executed for array literals

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: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers

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

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

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


pgsql-hackers by date

Next:From: Greg SmithDate: 2009-11-12 19:05:52
Subject: Re: CommitFest 2009-11 Call for Reviewers
Previous:From: Bernd HelmleDate: 2009-11-12 18:56:57
Subject: Re: ALTER TABLE...ALTER COLUMN vs inheritance

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group