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

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.

On Thu, Aug 1, 2013 at 3:49 PM, BladeOfLight16 <bladeoflight16(at)gmail(dot)com>wrote:

> When I want to add a new column with a NOT NULL constraint, I need to
> specify a DEFAULT to avoid violations. However, I don't always want to keep
> that DEFAULT; going forward after the initial add, I want an error to occur
> if there are inserts where this data is missing. So I have to DROP DEFAULT
> on the column. See this SQL Fiddle for a demonstration:
> http://sqlfiddle.com/#!12/58750/1/0. I could leave off the NOT NULL
> constraint, fill the new column with an UPDATE, and then add the NOT NULL
> constraint afterwards, but that, in my opinion, seems to be a somewhat
> messier alternative.
>
> By comparison, if I change data types, I can take advantage of the very
> useful USING clause to specify how to calculate the new value. As near as I
> can tell, there is no similar functionality for ADD COLUMN to specify a
> value (or means of calculating a value) only during the execution of the
> ALTER. I can understand why that might be the case. Without USING, changing
> the data type would force the creation of a new column instead in many
> cases, which is a much bigger hardship and makes the data type changing
> command far less useful.
>
> Am I missing something, or are the ways I mentioned the only ways to
> accomplish this with ADD COLUMN? It's true that neither possibility is
> particularly difficult to implement, but it doesn't seem like I should have
> to create a constraint I don't want or leave off a constraint I do want to
> add the column. I suppose in some cases, the fact that "fully creating" the
> column is non-atomic may be a problem. If I'm correct that this feature is
> not currently present, would adding it be a reasonable feature request? How
> would I go about making a feature request? (My apologies if there is a
> how-to on feature requests somewhere; my searching didn't turn it up.)
>
> Thank you.
>

--
Regards,
Richard Broersma Jr.