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

On 08/01/2013 04:25 PM, BladeOfLight16 wrote:
> On Thu, Aug 1, 2013 at 7:10 PM, Richard Broersma
> <richard(dot)broersma(at)gmail(dot)com <mailto: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.)

It fails because
ALTER TABLE x
ADD COLUMN data2 VARCHAR(10) NOT NULL DEFAULT 'foo',

end in , instead of ;

You have to add the column before you can alter it.

>
> Thank you.

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com