Обсуждение: Bug in 7.2.1? -- ERROR: Adding columns with defaults is not implemented.

Using pg 7.1.2,  I can enter the following statement successfully:

# alter table manufacturer add column dummy integer not null default
1;

However, under 7.2.1, the same statement gets me --

ERROR:  Adding columns with defaults is not implemented.
        Add the column, then use ALTER TABLE SET DEFAULT.

My question is two-fold -- first, is this new behavior a feature, or a
bug?

Second, what features/fixes would i lose by reverting to 7.1.2?

I'd like to stick with 7.2.1, but this new behavior is making it
difficult for me.  I can't simply drop & recreate all my tables just
to add a column!

Any help would be appreciated!

adam

> Using pg 7.1.2,  I can enter the following statement successfully:
>
> # alter table manufacturer add column dummy integer not null default
> 1;
>
> However, under 7.2.1, the same statement gets me --
>
> ERROR:  Adding columns with defaults is not implemented.
>         Add the column, then use ALTER TABLE SET DEFAULT.
>
> My question is two-fold -- first, is this new behavior a feature, or a
> bug?

IIRC, the column's default was lost in past versions so the statement
never really worked.

> I'd like to stick with 7.2.1, but this new behavior is making it
> difficult for me.  I can't simply drop & recreate all my tables just
> to add a column!

Do it in two or three statements:
alter table manufacturer add column dummy integer;
alter table manufacturer alter column dummy set default 1;
-- if you want past rows to have 1 rather than null
update manufacturer set dummy=1;

adwolf1@yahoo.com (ad wolf) writes:
> Using pg 7.1.2,  I can enter the following statement successfully:
> # alter table manufacturer add column dummy integer not null default
> 1;

You might have been able to enter it, but it didn't do what the spec
says it should do.

The behavior PG can actually implement is equivalent to the spec
behavior for ADD COLUMN followed by a separate SET DEFAULT command,
and that's how you have to do it in 7.2.

            regards, tom lane

Although in 7.1  you did not get the error message, I do not believe the
setting default or NOT NULL actually did anything (I am certain for 7.0).

7.2 no longer silently ignores the issue, it aborts with an error message.

It is easy too set the default in a second statement:
ALTER TABLE t1 ALTER COLUMN c1 SET DEFAULT 'default';

To implement the NOT NULL you either have to drop and recreate the table
(which is what I do) or manually insert the appropriate trigger (Ugh).

What I do is add the column without the NOT NULL or default,
then use the ALTER TABLE to set the column default,
then use pg_dump -t tablename databasename > table_backup
then vi table_backup and change the NULL to NOT NULL for the column
then drop the table
then reload the table with the NOT NULL constraint with:
    psql -e database < table_backup

Hope this helps.

PS  I highly recommend 7.2, there is a lot of good usefull stuff in there,
eg outer joins

Terry Fielder
Network Engineer
Great Gulf Homes / Ashton Woods Homes
terry@greatgulfhomes.com

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of ad wolf
> Sent: Saturday, July 06, 2002 8:33 PM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Bug in 7.2.1? -- ERROR: Adding columns
> with defaults
> is not implemented.
>
>
> Using pg 7.1.2,  I can enter the following statement successfully:
>
> # alter table manufacturer add column dummy integer not null default
> 1;
>
> However, under 7.2.1, the same statement gets me --
>
> ERROR:  Adding columns with defaults is not implemented.
>         Add the column, then use ALTER TABLE SET DEFAULT.
>
> My question is two-fold -- first, is this new behavior a feature, or a
> bug?
>
> Second, what features/fixes would i lose by reverting to 7.1.2?
>
> I'd like to stick with 7.2.1, but this new behavior is making it
> difficult for me.  I can't simply drop & recreate all my tables just
> to add a column!
>
> Any help would be appreciated!
>
> adam
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

On Sat, Jul 06, 2002 at 05:33:29PM -0700, ad wolf wrote:
> Using pg 7.1.2,  I can enter the following statement successfully:
>
> # alter table manufacturer add column dummy integer not null default
> 1;

Yes, it succeeds.  But check the table after you're done: the "not
null" has not taken effect.  In fact, it's not implemented, and it
just fails silently in the 7.1 series.

>
> However, under 7.2.1, the same statement gets me --
>
> ERROR:  Adding columns with defaults is not implemented.
>         Add the column, then use ALTER TABLE SET DEFAULT.
>
> My question is two-fold -- first, is this new behavior a feature, or a
> bug?

A feature to address the previous bug (silent failure of a legal SQL
statement).

> Second, what features/fixes would i lose by reverting to 7.1.2?

There were a number of bugfixes in 7.2.  Also, the planner and
optimiser are much improved, and there is the nice new
statistics-gathering subsystem.

> I'd like to stick with 7.2.1, but this new behavior is making it
> difficult for me.  I can't simply drop & recreate all my tables just
> to add a column!

You can add a constraint to get the NOT NULL feature.

A

--
----
Andrew Sullivan                               87 Mowat Avenue
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M6K 3E3
                                         +1 416 646 3304 x110

On Sat, Jul 06, 2002 at 05:33:29PM -0700, ad wolf wrote:
> ERROR:  Adding columns with defaults is not implemented.
>         Add the column, then use ALTER TABLE SET DEFAULT.
>
> My question is two-fold -- first, is this new behavior a feature, or a
> bug?

IIRC, it's a feature -- I think it was always broken, we just tell you
about it now.

> I'd like to stick with 7.2.1, but this new behavior is making it
> difficult for me.  I can't simply drop & recreate all my tables just
> to add a column!

Read the error message: it says that you simply need to execute 2
commands: ALTER TABLE ADD COLUMN, followed by ALTER TABLE SET DEFAULT.

Cheers,

Neil

--
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC

On Sat, Jul 06, 2002 at 05:33:29PM -0700, ad wolf wrote:
> Using pg 7.1.2,  I can enter the following statement successfully:
>
> # alter table manufacturer add column dummy integer not null default
> 1;
>
> However, under 7.2.1, the same statement gets me --
>
> ERROR:  Adding columns with defaults is not implemented.
>         Add the column, then use ALTER TABLE SET DEFAULT.
>
> My question is two-fold -- first, is this new behavior a feature, or a
> bug?

The new behaviour is a feature. It's telling you that the default clause
doesn't work. 7.1.2 accepted the statement but ignored the default. So you
needed the second statement anyway. 7.2 just made it explicit.

>
> Second, what features/fixes would i lose by reverting to 7.1.2?
>
> I'd like to stick with 7.2.1, but this new behavior is making it
> difficult for me.  I can't simply drop & recreate all my tables just
> to add a column!

Read the message. It says to add the column (without the default) and then
use "alter table set default".

HTH,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.

----- Original Message -----
From: <terry@greatgulfhomes.com>
To: "'ad wolf'" <adwolf1@yahoo.com>; <pgsql-general@postgresql.org>
Sent: Monday, July 08, 2002 12:10 PM
Subject: Re: [GENERAL] Bug in 7.2.1? -- ERROR: Adding columns with defaults
is not implemented.


> To implement the NOT NULL you either have to drop and recreate the table
> (which is what I do) or manually insert the appropriate trigger (Ugh).

Or update the system table:

UPDATE pg_attribute SET attnotnull=True WHERE attrelid=(SELECT oid FROM
pg_class WHERE relname='tablename') AND attname='fieldname'

Where "tablename" is the name of the table to update and "fieldname" is the
name of the NOT NULL field.

> What I do is add the column without the NOT NULL or default,
> then use the ALTER TABLE to set the column default,
> then use pg_dump -t tablename databasename > table_backup
> then vi table_backup and change the NULL to NOT NULL for the column
> then drop the table
> then reload the table with the NOT NULL constraint with:
> psql -e database < table_backup

Ugh, I find the system table UPDATE to be much easier to swallow :)

Greg

Hey, if it's that easy to do, then why doesn't someone complete the command
ALTER TABLE ADD COLUMN  to include the additional parameters for NOT NULL
and DEFAULT???


Terry Fielder
Network Engineer
Great Gulf Homes / Ashton Woods Homes
terry@greatgulfhomes.com

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Gregory Wood
> Sent: Tuesday, July 09, 2002 3:50 PM
> To: terry@greatgulfhomes.com
> Cc: PostgreSQL-General
> Subject: Re: [GENERAL] Bug in 7.2.1? -- ERROR: Adding columns with
> defaults is not implemented.
>
>
>
> ----- Original Message -----
> From: <terry@greatgulfhomes.com>
> To: "'ad wolf'" <adwolf1@yahoo.com>; <pgsql-general@postgresql.org>
> Sent: Monday, July 08, 2002 12:10 PM
> Subject: Re: [GENERAL] Bug in 7.2.1? -- ERROR: Adding columns
> with defaults
> is not implemented.
>
>
> > To implement the NOT NULL you either have to drop and
> recreate the table
> > (which is what I do) or manually insert the appropriate
> trigger (Ugh).
>
> Or update the system table:
>
> UPDATE pg_attribute SET attnotnull=True WHERE
> attrelid=(SELECT oid FROM
> pg_class WHERE relname='tablename') AND attname='fieldname'
>
> Where "tablename" is the name of the table to update and
> "fieldname" is the
> name of the NOT NULL field.
>
> > What I do is add the column without the NOT NULL or default,
> > then use the ALTER TABLE to set the column default,
> > then use pg_dump -t tablename databasename > table_backup
> > then vi table_backup and change the NULL to NOT NULL for the column
> > then drop the table
> > then reload the table with the NOT NULL constraint with:
> > psql -e database < table_backup
>
> Ugh, I find the system table UPDATE to be much easier to swallow :)
>
> Greg
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
> majordomo@postgresql.org
>