Thread: Add quto increment to existing column
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
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
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-column-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
>
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
theprimary 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 --
setthe sequence using select setval('hist_id_seq', maxid) so that the next value you get from the sequence doesn't
collidewith existing values.
HTH,
Greg Williamson