Populate unique column with default value

From: "Jon Horsman" <horshaq(at)gmail(dot)com>
To: "sql pgsql" <pgsql-sql(at)postgresql(dot)org>
Subject: Populate unique column with default value
Date: 2007-10-01 15:24:00
Message-ID: 4f4c2a010710010824t7a2fbd59kb9102c879b389a5d@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Hey,

I'm updating the user table in our db to have a new column "username"
as follows.
ALTER TABLE usertable ADD COLUMN username varchar(64)
UPDATE usertable SET username='<extension of current row>' WHERE username ISNULL
ALTER TABLE usertable ALTER COLUMN username SET NOT NULL
ALTER TABLE usertable ADD CONSTRAINT usertable_username_key UNIQUE(username)

I want to essentially do what i have above but need to populate the
username field with some unique value so that the last ALTER will run.
I have another column in this table that is unique, an phone
extension, and am hoping to use this as a default username. Is there
an elegant way i can set the username to be the extension of the
current row or should i just write a little loop that goes threw and
populates my username with the users extension manually for each user
in my db and then run the last ALTER.

Thanks,

Jon.

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Jon Horsman 2007-10-01 15:33:00 Re: Populate unique column with default value
Previous Message Luis Carlos Ferreira 2007-09-28 16:21:37 Re: problems with copy