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@gmail.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.

On Thu, Aug 1, 2013 at 7:10 PM, Richard Broersma <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.)

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Thu, Aug 1, 2013 at 7:53 PM, Adrian Klaver <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?

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Thu, Aug 1, 2013 at 8:15 PM, Adrian Klaver <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.

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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/

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general