Re: Range Types and extensions

On Mon, Jun 13, 2011 at 12:47 AM, Darren Duncan <darren(at)darrenduncan(dot)net> wrote:
>>> If you're referring to the case
>>>  (1) Create table with text-range column and collation C1
>>>  (2) Add check constraint containing RANGE_EMPTY()
>>>  (3) Add data
>>>  (4) Alter column to have collation C2, possibly changing
>>>     the result of RANGE_EMPTY() for existing ranges.
>>> then that points to a problem with ALTER COLUMN.
>>
>> No, I'm saying that you might have a column containing  '[a, Z)', and
>> someone might change the collation of the column from en_US to C.
>> When the collation was en_US, the column could legally contain that
>> value, but now that the collation is C, it can't.  ALTER TABLE isn't
>> going to recheck the validity of the data when someone changes the
>> collation: that's only supposed to affect the sort order, not the
>> definition of what is a legal value.
>
> You can have the same collation problem even without range types.
>
> Consider the following:
>  (1) Create table with the 2 text columns {L,R} and both columns have the
> collation en_US.
>  (2) Add check constraint requiring "L <= R".
>  (3) Add a record with the value 'a' for L and 'Z' for R.
>  (4) Alter the columns to have the collation C.

Oh, good point.

rhaas=# create table sample (t text collate "en_US", check (t < 'Z'));
CREATE TABLE
rhaas=# insert into sample values ('a');
INSERT 0 1
rhaas=# alter table sample alter column t type text collate "C";
ERROR: check constraint "sample_t_check" is violated by some row

But interestingly, my Mac has a different notion of how this collation
works: it thinks 'a' > 'Z' even in en_US. :-(

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company