On Mon, Jun 13, 2011 at 12:47 AM, Darren Duncan <darren@darrenduncan.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