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
