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.broersma@gmail.com <mailto:richard.broersma@gmail.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.klaver@gmail.com