Обсуждение: Unexpected collation error in 9.1.1

I tried adding a not-null column in one step and got a collation
error for a different column. Adding the column in several steps
works:


itd=> alter table livedata add column pricechanged timestamp not null default current_timestamp;
ERROR:  no collation was derived for column "whois_b" with collatable type citext
TIP:  Use the COLLATE clause to set the collation explicitly.

itd=> \d livedata                               Table "public.livedata"    Column    |            Type             |
           Modifiers
 
--------------+-----------------------------+----------------------------------------- accessid     | integer
         | not null sp_b         | double precision            | default 0 csh_b        | double precision            |
default0 sp_a         | double precision            | default 0 csh_a        | double precision            | default 0
asw_b       | double precision            | default 0 asw_a        | double precision            | default 0 amount
 | character varying(25)       | default ' '::character varying bench        | character varying(25)       | default
NULL::charactervarying updated_b    | date                        | updated_a    | date                        |
whois_b     | citext                      | default '          '::character varying whois_a      | citext
      | default '          '::character varying b_orig       | double precision            | default 0 a_orig       |
doubleprecision            | default 0 lcontrol     | integer                     | not null default 0 rcontrol     |
integer                    | not null default 0 hlcleared    | boolean                     | not null default false
yield_b     | double precision            | default 0 yield_a      | double precision            | default 0
 

itd=> alter table livedata add column pricechanged timestamp;
ALTER TABLE
itd=> alter table livedata alter column pricechanged set default current_timestamp;
ALTER TABLE
itd=> update livedata set pricechanged = default;
UPDATE 6000
itd=> alter table livedata alter column pricechanged set not null;
ALTER TABLE

-- 
Christian

Christian Ullrich <chris@chrullrich.net> writes:
> I tried adding a not-null column in one step and got a collation
> error for a different column.

> itd=> alter table livedata add column pricechanged timestamp not null default current_timestamp;
> ERROR:  no collation was derived for column "whois_b" with collatable type citext
> TIP:  Use the COLLATE clause to set the collation explicitly.

That's pretty bizarre, but I can't reproduce it on the basis of the
supplied example:

regression=# create extension citext;
CREATE EXTENSION
regression=# create table foo (f1 int, f2 citext  default '          '::character varying);
CREATE TABLE
regression=# insert into foo values (1, 'one');
INSERT 0 1
regression=# insert into foo values (2, 'two');
INSERT 0 1
regression=# alter table foo add column pricechanged timestamp not null default current_timestamp;
ALTER TABLE

I tried adding UNIQUE and CHECK constraints too, and still no luck.
Are you sure you're using 9.1.1?
        regards, tom lane

* Tom Lane wrote:

> Christian Ullrich<chris@chrullrich.net>  writes:

>> I tried adding a not-null column in one step and got a collation
>> error for a different column.
>
>> itd=>  alter table livedata add column pricechanged timestamp not null default current_timestamp;
>> ERROR:  no collation was derived for column "whois_b" with collatable type citext
>> TIP:  Use the COLLATE clause to set the collation explicitly.
>
> That's pretty bizarre, but I can't reproduce it on the basis of the
> supplied example:
>
> I tried adding UNIQUE and CHECK constraints too, and still no luck.
> Are you sure you're using 9.1.1?

Yes, the EDB x64 Windows build. But I can't reproduce it now, either. I 
got that error twice today (out of only two attempts), while doing 
basically this:

- Dump database A
- Clear out database B by doing DROP SCHEMA CASCADE; CREATE SCHEMA
- Load dump into database B
- Replace column in B by DROPping it (it was BOOLEAN before) and then  ADDing the new one as a TIMESTAMP

There was no other activity on B while I was doing it.

I just tried doing that again, but it worked several times in a row. 
That may be because I changed the type in A in the meantime, so (among 
other things) the heap layout before the DROP COLUMN is different now. 
I'll give it another try with the original dump tomorrow when I'm back 
at work.

-- 
Christian