Add a NOT NULL column with default only during add

From: BladeOfLight16 <bladeoflight16(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Add a NOT NULL column with default only during add
Date: 2013-08-01 22:49:19
Message-ID: CA+=1U=UTAg61Rhi0pdm3RCiv8CGDP8jQ_XpaObZUTh4q+Xaf4w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Richard Broersma 2013-08-01 23:10:12 Re: Add a NOT NULL column with default only during add
Previous Message Gavin Flower 2013-08-01 20:38:57 Re: Why are stored procedures looked on so negatively?