ALTER TABLE ADD COLUMN column SERIAL -- unexpected

ALTER TABLE ADD COLUMN column SERIAL -- unexpected

Post by Peter Eisentra » Fri, 30 Nov 2001 05:49:45



Quote:Bruce Momjian writes:
>         o ALTER TABLE ADD COLUMN column SET DEFAULT should fill existing
>           rows with DEFAULT value or allow NULLs in existing rows

> My guess is that we will have to do spec behavior by default, and add a
> flag to allow NULLs in existing rows.

May I point you to the already existing set of commands that do exactly
what you want:

ALTER TABLE t1 ADD COLUMN name type;
ALTER TABLE t1 ALTER COLUMN name SET DEFAULT foo;

There's no reason to muck around with the spec here.

--

---------------------------(end of broadcast)---------------------------

 
 
 

ALTER TABLE ADD COLUMN column SERIAL -- unexpected

Post by Bruce Momji » Fri, 30 Nov 2001 06:22:25


Quote:> Bruce Momjian writes:

> >         o ALTER TABLE ADD COLUMN column SET DEFAULT should fill existing
> >           rows with DEFAULT value or allow NULLs in existing rows

> > My guess is that we will have to do spec behavior by default, and add a
> > flag to allow NULLs in existing rows.

> May I point you to the already existing set of commands that do exactly
> what you want:

> ALTER TABLE t1 ADD COLUMN name type;
> ALTER TABLE t1 ALTER COLUMN name SET DEFAULT foo;

> There's no reason to muck around with the spec here.

OK, so we will implement the spec, and allow people to avoid the
double-disk space by doing the steps manually and skipping the UPDATE.
Sounds good to me.  TODO updated.

--
  Bruce Momjian                        |  http://candle.pha.pa.us

  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

 
 
 

1. ALTER TABLE ADD COLUMN column SERIAL -- unexpected results

I am trying to find a way to get this information to users.  I have
modified command.c to output a different error message:

test=> alter table x add column z int default 4;
ERROR:  Adding columns with defaults is not implemented because it
        is unclear whether existing rows should have the DEFAULT value
        or NULL.  Add the column, then use ALTER TABLE SET DEFAULT.
        You may then use UPDATE to give a non-NULL value to existing rows.

How does this sound?  Peter, should I keep it for 7.3 so I don't mess up
the translations in 7.2?

--
  Bruce Momjian                        |  http://candle.pha.pa.us

  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

2. Progress versus BusinessObjects

3. ALTER TABLE ADD COLUMN column SERIAL -- unexpected

4. DBGrid easy question?

5. ALTER TABLE ADD COLUMN column SERIAL -- unexpected results

6. SQL 2000 onto an Active/Passive Cluster with a SAN

7. US-MA-Cambridge Programmer/Analyst, Sybase, SQL, Unix, Unix shell scripting (1215-ON2070)

8. add a column at a specific position in the table or alter a columns position

9. Invalid column after alter table adds column

10. add column between two existing columns with ALTER statement

11. Adding columns to a table and adding length to columns

12. Adding a serial column to a table. (fwd)