Re: Add a NOT NULL column with default only during add

On Thu, Aug 1, 2013 at 7:10 PM, Richard Broersma <richard(dot)broersma(at)gmail(dot)com
> wrote:

> Notice :
> http://www.postgresql.org/docs/9.3/static/sql-altertable.html
> After you add a column to your table, you can latter *alter* this column
> to add, change, or remove the default expression. There's no need add
> temporary columns to manage this kind of change. In fact, all of the DDL
> that you've described can be achieved in one SQL command.
>

I think there has been a misunderstanding. I was describing the use of "add
column with default" and "drop default" commands; please see my SQL Fiddle.
It's only 2 ALTER commands; it doesn't use any temporary columns. It does
use a temporary constraint, but not a temporary column.

I'm not clear how you could do this in a single command. Are you suggesting
I could do something like this?

ALTER TABLE x
ADD COLUMN data2 VARCHAR(10) NOT NULL DEFAULT 'foo',
ALTER COLUMN data2 DROP DEFAULT;

At least in the 9.2.4 SQL Fiddle uses, that fails with this error: ERROR:
column "data2" of relation "x" does not exist. Has something changed in
9.3, or am I misreading you? A sample command of what you're suggesting
might be helpful. (Doesn't have to be perfect syntax or anything; just to
give me the gist.)

Thank you.