Обсуждение: Add a NOT NULL column with default only during add

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.

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

=/ That's the way I have it in the SQL Fiddle sample I provided. I was asking what Mr. Broersma was suggesting.

I appreciate the effort by both of you, but it seems my points aren't getting across. Is there something more I can do to clarify?

On 08/01/2013 04:59 PM, BladeOfLight16 wrote:
> On Thu, Aug 1, 2013 at 7:53 PM, Adrian Klaver <adrian.klaver@gmail.com
> <mailto:adrian.klaver@gmail.com>> wrote:
>
>     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.
>
>
> =/ That's the way I have it in the SQL Fiddle sample I provided. I was
> asking what Mr. Broersma was suggesting.
>
> I appreciate the effort by both of you, but it seems my points aren't
> getting across. Is there something more I can do to clarify?

What you want is a default that only works during ALTER ADD COLUMN. At
that point though, there is no data added and DEFAULT only works with
INSERTS. Your example of USING with ALTER data_type works because there
actually may be rows already existing and you are not creating a column.

--
Adrian Klaver
adrian.klaver@gmail.com

Correct me if I'm wrong, but I think you are saying that the use case I have presented is unrealistic. You're saying I would only add a column when there is no data in the table. However, what I'm describing can happen any time you need to make a change to a database with existing data. New features added to an existing application or even simply trying to preserve sample data during development come to mind as situations where you might need to add a NOT NULL column to a table with existing data, so this is a very real situation. The only reason I am bringing up the data type ALTER command is because it already has the feature I'm describing, so I thought it would be a good example of what I'm asking about.

My SQL Fiddle (http://sqlfiddle.com/#!12/58750/1/0) demonstrates what happens when you ADD COLUMN with existing rows already in the table and use a DEFAULT clause; the existing rows are populated with the default value. This is what I want to happen; I am happy with the end result. However, in my opinion, it seems counter intuitive to add a DEFAULT constraint to a column purely to execute the ADD COLUMN, then have to execute a second DDL statement to remove that DEFAULT clause. The command pair is not representative of what I'm actually trying to accomplish, which hurts readability when others might examine my scripts down the line.

So my question is effectively this: Is there an existing, equivalent, single DDL statement to the following hypothetical SQL?

ALTER TABLE x
ADD COLUMN data2 VARCHAR(10) NOT NULL USING 'foo';

where "USING" here would indicate the same thing it does in an ALTER COLUMN data2 TYPE [data type] USING 'foo' command.

I suspect the answer is "No, this feature does not exist right now," and that's fine if so. I am just asking if my guess is correct or if I've missed the feature somewhere. Mr. Broersma's response suggested that this can be done in "one SQL command." I initially took that to mean that there is a single DDL statement that could accomplish this, but having taken a closer look at it, I might have misunderstood.

On 08/02/2013 10:03 AM, BladeOfLight16 wrote:
> So my question is effectively this: Is there an existing, equivalent,
> single DDL statement to the following hypothetical SQL?
>
> ALTER TABLE x
> ADD COLUMN data2 VARCHAR(10) NOT NULL USING 'foo';
>
> where "USING" here would indicate the same thing it does in an ALTER
> COLUMN data2 TYPE [data type] USING 'foo' command.
>
> I suspect the answer is "No, this feature does not exist right now,"
> and that's fine if so. I am just asking if my guess is correct or if
> I've missed the feature somewhere.

Your guess is unfortunately correct.

Vik

On 08/02/2013 01:03 AM, BladeOfLight16 wrote:
> On Thu, Aug 1, 2013 at 8:15 PM, Adrian Klaver <adrian.klaver@gmail.com
> <mailto:adrian.klaver@gmail.com>> wrote:
>
>     What you want is a default that only works during ALTER ADD COLUMN.
>     At that point though, there is no data added and DEFAULT only works
>     with INSERTS. Your example of USING with ALTER data_type works
>     because there actually may be rows already existing and you are not
>     creating a column.
>
>
> Correct me if I'm wrong, but I think you are saying that the use case I
> have presented is unrealistic. You're saying I would only add a column
> when there is no data in the table. However, what I'm describing can
> happen any time you need to make a change to a database with existing
> data. New features added to an existing application or even simply
> trying to preserve sample data during development come to mind as
> situations where you might need to add a NOT NULL column to a table with
> existing data, so this is a very real situation. The only reason I am
> bringing up the data type ALTER command is because it already has the
> feature I'm describing, so I thought it would be a good example of what
> I'm asking about.

No I am saying that in the ALTER data_type case the column is not being
created and USING is working on data(assuming data had actually been
entered already) that exists. What you propose is a two step process,
create a column and then fill it with a default value that goes away
after the ALTER TABLE ADD COLUMN statement. In fact what you are already
doing.

>
> My SQL Fiddle (http://sqlfiddle.com/#!12/58750/1/0) demonstrates what
> happens when you ADD COLUMN with existing rows already in the table and
> use a DEFAULT clause; the existing rows are populated with the default
> value. This is what I want to happen; I am happy with the end result.
> However, in my opinion, it seems counter intuitive to add a DEFAULT
> constraint to a column purely to execute the ADD COLUMN, then have to
> execute a second DDL statement to remove that DEFAULT clause. The
> command pair is not representative of what I'm actually trying to
> accomplish, which hurts readability when others might examine my scripts
> down the line.
>
> So my question is effectively this: Is there an existing, equivalent,
> single DDL statement to the following hypothetical SQL?
>
> ALTER TABLE x
> ADD COLUMN data2 VARCHAR(10) NOT NULL USING 'foo';

As Vik stated, no,

>
> where "USING" here would indicate the same thing it does in an ALTER
> COLUMN data2 TYPE [data type] USING 'foo' command.
>
> I suspect the answer is "No, this feature does not exist right now," and
> that's fine if so. I am just asking if my guess is correct or if I've
> missed the feature somewhere. Mr. Broersma's response suggested that
> this can be done in "one SQL command." I initially took that to mean
> that there is a single DDL statement that could accomplish this, but
> having taken a closer look at it, I might have misunderstood.

Not to put words in Richards mouth, but I suspect what he was saying was
to wrap the DDL changes and initial inserts in a single transaction:


BEGIN:
CREATE TABLE x
(
   id SERIAL PRIMARY KEY,
   data1 VARCHAR(10) NOT NULL
);

INSERT INTO x (data1) VALUES ('hello'), ('world'), ('sunshine');

ALTER TABLE x
ADD COLUMN data2 VARCHAR(10) NOT NULL DEFAULT 'foo';

ALTER TABLE x
ALTER COLUMN data2 DROP DEFAULT;

COMMIT;

INSERT INTO x (data1, data2) VALUES ('moonlight', 'baz');

--
Adrian Klaver
adrian.klaver@gmail.com

Adrian Klaver <adrian.klaver@gmail.com> writes:
> No I am saying that in the ALTER data_type case the column is not being
> created and USING is working on data(assuming data had actually been
> entered already) that exists. What you propose is a two step process,
> create a column and then fill it with a default value that goes away
> after the ALTER TABLE ADD COLUMN statement. In fact what you are already
> doing.

I do see a use-case that's not covered by ADD COLUMN ... DEFAULT
but could be covered with USING: when you want to initialize the new
column with data taken from some other existing column(s).

Whether this comes up often enough to justify a new feature isn't
clear.  You could get the same effect, for pretty much the same cost,
with
    1. ADD COLUMN new_col, not specifying any default;
    2. UPDATE ... SET new_col = some expression of other columns;
    3. ALTER COLUMN new_col SET DEFAULT, if needed.

If you need to make the column NOT NULL, that could be done after step 3,
but then you'd incur another table scan to verify this constraint.
So a USING clause could save you that extra scan.

But if you add another quantum of complication, namely that the new
column's data has to come from some other table, USING would fail at that;
you're back to having to do it with UPDATE.  So it seems like there's
only a pretty narrow window of applicability for this proposed feature.
I'm having a hard time getting excited about it.

            regards, tom lane

2013-08-02 16:58 keltezéssel, Tom Lane írta:
> Adrian Klaver <adrian.klaver@gmail.com> writes:
>> No I am saying that in the ALTER data_type case the column is not being
>> created and USING is working on data(assuming data had actually been
>> entered already) that exists. What you propose is a two step process,
>> create a column and then fill it with a default value that goes away
>> after the ALTER TABLE ADD COLUMN statement. In fact what you are already
>> doing.
> I do see a use-case that's not covered by ADD COLUMN ... DEFAULT
> but could be covered with USING: when you want to initialize the new
> column with data taken from some other existing column(s).
>
> Whether this comes up often enough to justify a new feature isn't
> clear.  You could get the same effect, for pretty much the same cost,
> with
>     1. ADD COLUMN new_col, not specifying any default;
>     2. UPDATE ... SET new_col = some expression of other columns;
>     3. ALTER COLUMN new_col SET DEFAULT, if needed.
>
> If you need to make the column NOT NULL, that could be done after step 3,
> but then you'd incur another table scan to verify this constraint.
> So a USING clause could save you that extra scan.
>
> But if you add another quantum of complication, namely that the new
> column's data has to come from some other table, USING would fail at that;
> you're back to having to do it with UPDATE.  So it seems like there's
> only a pretty narrow window of applicability for this proposed feature.
> I'm having a hard time getting excited about it.

If this feature also allows constants and non-volatile functions,
the window isn't so narrow anymore.

Best regards,
Zoltán Böszörményi

--
----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de
      http://www.postgresql.at/