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.
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? |