On Thu, Aug 1, 2013 at 7:10 PM, Richard Broersma <richard.broersma@xxxxxxxxx> wrote:
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.
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.
