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