Horton
Menu

Thread

  1. Alter table add column ignores default

    PostgreSQL Bugs List <pgsql-bugs@postgresql.org> — 2001-05-05T16:23:37Z

    Vince Vielhaber (vev@hub.org) reports a bug with a severity of 3
The lower the number the more severe it is.

Short Description
Alter table add column ignores default

Long Description
Alter table add column ignores the default values in both v7.0x and 7.1.  In the example code the table is created with one column having a default value of 'none'.  An ALTER TABLE is done adding another varchar column also with a default value.  \d table only shows the default from the create, not the alter.  An insert also fails to add the default.



Sample Code
foo=# create table foobar(name varchar(20),nickname varchar(10) default 'none');
CREATE
foo=# \d foobar
              Table "foobar"
 Attribute |    Type     |    Modifier    
-----------+-------------+----------------
 name      | varchar(20) | 
 nickname  | varchar(10) | default 'none'

foo=# alter table foobar add column address varchar(50) default 'none';
ALTER
foo=# \d foobar
              Table "foobar"
 Attribute |    Type     |    Modifier    
-----------+-------------+----------------
 name      | varchar(20) | 
 nickname  | varchar(10) | default 'none'
 address   | varchar(50) | 

foo=# insert into foobar(name) values('Joe Postgres');
INSERT 313396 1
foo=# select * from foobar;
     name     | nickname | address 
--------------+----------+---------
 Joe Postgres | none     | 
(1 row)

foo=# 



No file was uploaded with this report

  2. Re: Alter table add column ignores default

    Nabil Sayegh <nsmail@sayegh.de> — 2001-05-08T13:38:27Z

    On 05 May 2001 12:23:37 -0400, pgsql-bugs@postgresql.org wrote:
> Vince Vielhaber (vev@hub.org) reports a bug with a severity of 3
> The lower the number the more severe it is.
> 
> Short Description
> Alter table add column ignores default
> 
> Long Description
> Alter table add column ignores the default values in both v7.0x and 7.1.  In the example code the table is created with one column having a default value of 'none'.  An ALTER TABLE is done adding another varchar column also with a default value.  \d table only shows the default from the create, not the alter.  An insert also fails to add the default.
> 
> 
> 
> Sample Code
> foo=# create table foobar(name varchar(20),nickname varchar(10) default 'none');
> CREATE
> foo=# \d foobar
>               Table "foobar"
>  Attribute |    Type     |    Modifier    
> -----------+-------------+----------------
>  name      | varchar(20) | 
>  nickname  | varchar(10) | default 'none'
> 
> foo=# alter table foobar add column address varchar(50) default 'none';


http://hermes.swu.bg/postgres/postgres/sql-altertable.htm
The correct syntax is:
ALTER TABLE foobar add column address varchar(50);
ALTER TABLE foobar alter column address set default 'none';

cu
--
 Nabil Sayegh

  3. Re: Alter table add column ignores default

    Vince Vielhaber <vev@michvhf.com> — 2001-05-08T14:31:17Z

    On 8 May 2001, Nabil Sayegh wrote:

> On 05 May 2001 12:23:37 -0400, pgsql-bugs@postgresql.org wrote:
> > Vince Vielhaber (vev@hub.org) reports a bug with a severity of 3
> > The lower the number the more severe it is.
> >
> > Short Description
> > Alter table add column ignores default
> >
> > Long Description
> > Alter table add column ignores the default values in both v7.0x and 7.1.  In the example code the table is created with one column having a default value of 'none'.  An ALTER TABLE is done adding another varchar column also with a default value.  \d table only shows the default from the create, not the alter.  An insert also fails to add the default.
> >
> >
> >
> > Sample Code
> > foo=# create table foobar(name varchar(20),nickname varchar(10) default 'none');
> > CREATE
> > foo=# \d foobar
> >               Table "foobar"
> >  Attribute |    Type     |    Modifier
> > -----------+-------------+----------------
> >  name      | varchar(20) |
> >  nickname  | varchar(10) | default 'none'
> >
> > foo=# alter table foobar add column address varchar(50) default 'none';
>
>
> http://hermes.swu.bg/postgres/postgres/sql-altertable.htm
> The correct syntax is:
> ALTER TABLE foobar add column address varchar(50);
> ALTER TABLE foobar alter column address set default 'none';

If this:

alter table foobar add column address varchar(50) default 'none';

is the incorrect syntax, why does it not fail or at least give a
warning?

Vince.
-- 
==========================================================================
Vince Vielhaber -- KA8CSH    email: vev@michvhf.com    http://www.pop4.net
         56K Nationwide Dialup from $16.00/mo at Pop4 Networking
        Online Campground Directory    http://www.camping-usa.com
       Online Giftshop Superstore    http://www.cloudninegifts.com
==========================================================================

  4. Re: Alter table add column ignores default

    Nabil Sayegh <nsmail@sayegh.de> — 2001-05-08T15:14:10Z

    On 08 May 2001 10:31:17 -0400, Vince Vielhaber wrote:
> > http://hermes.swu.bg/postgres/postgres/sql-altertable.htm
> > The correct syntax is:
> > ALTER TABLE foobar add column address varchar(50);
> > ALTER TABLE foobar alter column address set default 'none';
> 
> If this:
> 
> alter table foobar add column address varchar(50) default 'none';
> 
> is the incorrect syntax, why does it not fail or at least give a
> warning?



Sorry, I meant: Try this workaround :)


cu

--
 Nabil Sayegh

  5. Re: Alter table add column ignores default

    Vince Vielhaber <vev@michvhf.com> — 2001-05-08T15:19:26Z

    On 8 May 2001, Nabil Sayegh wrote:

> On 08 May 2001 10:31:17 -0400, Vince Vielhaber wrote:
> > > http://hermes.swu.bg/postgres/postgres/sql-altertable.htm
> > > The correct syntax is:
> > > ALTER TABLE foobar add column address varchar(50);
> > > ALTER TABLE foobar alter column address set default 'none';
> >
> > If this:
> >
> > alter table foobar add column address varchar(50) default 'none';
> >
> > is the incorrect syntax, why does it not fail or at least give a
> > warning?
>
>
>
> Sorry, I meant: Try this workaround :)

Ok, now I'm officially confused :)

Is it a bug that there's no error or warning or is it a bug that it
ignores the default?  Ok, I just found it:

---
In the current implementation, default and constraint clauses for the new
column will be ignored. You can use the SET DEFAULT form of
ALTER TABLE to set the default later. (You will also have to update the
already existing rows to the new default value, using UPDATE.)
---

Thanks!

Vince.
-- 
==========================================================================
Vince Vielhaber -- KA8CSH    email: vev@michvhf.com    http://www.pop4.net
         56K Nationwide Dialup from $16.00/mo at Pop4 Networking
        Online Campground Directory    http://www.camping-usa.com
       Online Giftshop Superstore    http://www.cloudninegifts.com
==========================================================================

  6. Re: Alter table add column ignores default

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-05-11T13:47:06Z

    Vince Vielhaber <vev@michvhf.com> writes:
> If this:
> alter table foobar add column address varchar(50) default 'none';
> is the incorrect syntax, why does it not fail or at least give a
> warning?

Because it's incompletely implemented: the parser takes the full syntax
but the ALTER TABLE routine doesn't do everything it should.

			regards, tom lane