Thread: Alter Table Serial

Wondering if this is possible:

I'm trying to add a column to an existing database. This column would be
autoincrementing in the form of "bigserial". Now, since there are already
70,000 records in the database, I'd also like to have this assign a serial
number to those too, but understand if this is not possible. So far,
everything I've tried has come up with the message:

NOTICE:  ALTER TABLE will create implicit sequence 'shipper_serial_seq' for
SERIAL column 'shipper.serial'

NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index
'shipper_serial_key' for table 'shipper'

ERROR:  Adding columns with defaults is not implemented.

        Add the column, then use ALTER TABLE SET DEFAULT.


I'm still very new to this, so any help is greatly appreciated.

Thanks!

--= Aaron Eft =--
    60 S McKemy
Chandler, AZ 85226
   (480) 961-6407

The following might be what you want:
ALTER TABLE shipper ADD srl int8;
CREATE SEQUENCE shipper_srl_seq;
ALTER TABLE shipper ALTER COLUMN srl SET DEFAULT nextval(shipper_srl_seq);
UPDATE shipper SET srl=nextval('shipper_srl_seq');
hth,
- Stuart

> -----Original Message-----
> From: Eft, Aaron [mailto:Aaron.Eft@avnet.com]
> Sent: 26 August 2002 20:30
> To: 'pgsql-novice@postgresql.org'
> Subject: [NOVICE] Alter Table Serial
>
>
> Wondering if this is possible:
>
> I'm trying to add a column to an existing database. This
> column would be
> autoincrementing in the form of "bigserial". Now, since there
> are already
> 70,000 records in the database, I'd also like to have this
> assign a serial
> number to those too, but understand if this is not possible. So far,
> everything I've tried has come up with the message:
>
> NOTICE:  ALTER TABLE will create implicit sequence
> 'shipper_serial_seq' for
> SERIAL column 'shipper.serial'
>
> NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index
> 'shipper_serial_key' for table 'shipper'
>
> ERROR:  Adding columns with defaults is not implemented.
>
>         Add the column, then use ALTER TABLE SET DEFAULT.
>
>
> I'm still very new to this, so any help is greatly appreciated.
>
> Thanks!
>
> --= Aaron Eft =--
>     60 S McKemy
> Chandler, AZ 85226
>    (480) 961-6407
>
>