[SQL] Populate unique column with default value
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='' 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. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Populate unique column with default value
I guess what i'm looking for is something like the following, but i don't know how i'd do it. "UPDATE usertable SET username='SELECT extension FROM usertable WHERE id=' WHERE username ISNULL", Is this possible? Thanks, Jon. On 10/1/07, Jon Horsman <[EMAIL PROTECTED]> wrote: > 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='' 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. > ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Populate unique column with default value
On 10/1/07, Jon Horsman <[EMAIL PROTECTED]> wrote: > ... Is there an elegant way i can set the username > to be the extension of the current row ... UPDATE usertable SET username = extension WHERE username IS NULL; ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Populate unique column with default value
> On 10/1/07, Jon Horsman <[EMAIL PROTECTED]> wrote: > > ... Is there an elegant way i can set the username > > to be the extension of the current row ... > > UPDATE usertable > SET username = extension > WHERE username IS NULL; Hmm, that simple eh, don't I feel stupid =) Thanks a ton for the quick response. Jon. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
