Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 8BA2F9FA13E
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Mon,  1 Oct 2007 12:33:05 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 21257-01 for <pgsql-sql-postgresql.org@postgresql.org>;
	Mon,  1 Oct 2007 12:33:03 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5
Received: from py-out-1112.google.com (py-out-1112.google.com
 [64.233.166.176])
	by postgresql.org (Postfix) with ESMTP id 5E76F9FA131
	for <pgsql-sql@postgresql.org>; Mon,  1 Oct 2007 12:33:02 -0300 (ADT)
Received: by py-out-1112.google.com with SMTP id u77so7888255pyb
	for <pgsql-sql@postgresql.org>; Mon, 01 Oct 2007 08:33:01 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta;
	h=domainkey-signature:received:received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	bh=6f2t7NoL17+sB/4xbY1fKI+U9Mt62syd+uhZ9IB6vrk=;
	b=ZxLzlj8P0j7CEZxSVVufNy0YtuE2gDf92ij/92EMqvFD+gUYfqwcibxEyzIY49cRffwcJj6ttT/a/isE0Sbfq6CJmrNfM+eJ43d/FYiKiKLDVxypyN7sM1qhzBgmS1FYooFXrmPToDKeeLXHEuaMgG+hHOWLR5/XaVNtT3hWTaE=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=WDFFC1OMpBMKXZiAXZmWlKwh+wdVJncODwdfuP9F9cGO338Hare92KwmE8yTC+AjZHgq7qdPmjOmG4Vruuzu4kt5zfIVuUsBwOcFXLF84Mc8Rl+4KyRA63ANJNTvCqjKI5ZUIPEDJ4JwyGN7VS/rBlyL/31z7hYC0L35UFOQ84A=
Received: by 10.35.42.18 with SMTP id u18mr7970763pyj.1191252780826;
	Mon, 01 Oct 2007 08:33:00 -0700 (PDT)
Received: by 10.35.52.15 with HTTP; Mon, 1 Oct 2007 08:33:00 -0700 (PDT)
Message-ID: <4f4c2a010710010833v53afcaafn5221a3f7d4d964a3@mail.gmail.com>
Date: Mon, 1 Oct 2007 11:33:00 -0400
From: "Jon Horsman" <horshaq@gmail.com>
To: "sql pgsql" <pgsql-sql@postgresql.org>
Subject: Re: Populate unique column with default value
In-Reply-To: <4f4c2a010710010824t7a2fbd59kb9102c879b389a5d@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <4f4c2a010710010824t7a2fbd59kb9102c879b389a5d@mail.gmail.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200710/2
X-Sequence-Number: 29374

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=<current row>' WHERE username ISNULL",

Is this possible?

Thanks,

Jon.

On 10/1/07, Jon Horsman <horshaq@gmail.com> 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='<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.
>