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