On 08/02/2013 01:03 AM, BladeOfLight16 wrote:
On Thu, Aug 1, 2013 at 8:15 PM, Adrian Klaver <adrian.klaver@xxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxx>> 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@xxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
