Add quto increment to existing column

If you use the SERIAL (this is the auto-incrementing function that creates
sequences in the bankground for you) datatype you can accomplish it in one
go.

So:
DROP sequence hist_id_seq; -- Get rid of your old sequence

ALTER TABLE my_table DROP COLUMN hist_id; -- Remove id column

ALTER TABLE my_table ADD COLUMN hist_id SERIAL PRIMARY KEY; -- Recreate it
as Primary Key and quto-incrementing.

Btw: have you tried the http://www.pgadmin.org/ pgadmin gui for
postgres? It will help you do tasks like this and show you the SQL to do it
on the command line :)

Cheers,
Marc

Robert Buckley wrote:

Hi,

I have a column in a table called hist_id with the datatype "integer".
When I created the table I assigned this column the primary key constraint
but didn´t make it an auto-increment column.

How could I do this to an the already existing column?

I have created the sequence with the following command but don´t know how
to change the existing column to auto-increment.

$ create sequence hist_id_seq;

thanks for any help,

Rob

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Add-quto-increment-to-existing-column-tp4868404p4868544.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Hi

Dropping the column is a bit drastic if you already have data in there.

You could just set the default on the column:

alter table my_table alter hist_id set default nextval('hist_id_seq')

Also considder setting the sequence owner:
alter sequence hist_id_seq owned by my_table.hist_id;

This will mean if the table or collumn gets dropped so will the
sequence and if the table is moved between schemas, so to will the
sequence be moved.

Regards

Also remember to set the next value of the sequence:
Select setval('hist_id_seq,my_value);
Where my_value is probably:
Select max(hist_id) from my_table;

Mark Watson

-----Message d'origine-----
De : pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] De la part de Phil Couling
Objet : Re: [GENERAL] Add quto increment to existing column

Hi

Dropping the column is a bit drastic if you already have data in there.

You could just set the default on the column:

alter table my_table alter hist_id set default nextval('hist_id_seq')

Also considder setting the sequence owner:
alter sequence hist_id_seq owned by my_table.hist_id;

This will mean if the table or collumn gets dropped so will the
sequence and if the table is moved between schemas, so to will the
sequence be moved.

Regards

On 4 October 2011 14:38, marc_firth <marc.r.firth@gmail.com> wrote:

If you use the SERIAL (this is the auto-incrementing function that creates
sequences in the bankground for you) datatype you can accomplish it in one
go.

So:
DROP sequence hist_id_seq;  -- Get rid of your old sequence

ALTER TABLE my_table DROP COLUMN hist_id; -- Remove id column

ALTER TABLE my_table ADD COLUMN hist_id SERIAL PRIMARY KEY; -- Recreate it
as Primary Key and quto-incrementing.

Btw:  have you tried the   http://www.pgadmin.org/ pgadmin  gui for
postgres?  It will help you do tasks like this and show you the SQL to do

it

on the command line :)

Cheers,
Marc

Robert Buckley wrote:

Hi,

I have a column in a table called hist_id with the datatype "integer".
When I created the table I assigned this column the primary key

constraint

but didn´t make it an auto-increment column.

How could I do this to an the already existing column?

I have created the sequence with the following command but don´t know how
to change the existing column to auto-increment.

$ create sequence hist_id_seq;

thanks for any help,

Rob

--
View this message in context:

http://postgresql.1045698.n5.nabble.com/Add-quto-increment-to-existing-colum
n-tp4868404p4868544.html

Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 10.0.1410 / Virus Database: 1520/3937 - Release Date: 10/04/11

Robert --

Hi,

I have a column in a table called hist_id with the datatype "integer". When I created the table I assigned this column the primary key constraint but didn´t make it an auto-increment column.

How could I do this to an the already existing column?

I have created the sequence with the following command but don´t know how to change the existing column to auto-increment.

$ create sequence hist_id_seq;

Perhaps:

ALTER TABLE history_foo ALTER COLUMN hist_id SET DEFAULT nextval('hist_id_seq');

?? (untested)

You may need to to update the sequence to reflect your current highest value for hist_id (or whatever the column is -- set the sequence using select setval('hist_id_seq', maxid)  so that the next value you get from the sequence doesn't collide with existing values.

HTH,

Greg Williamson