Thread: auto-increment column

On Tue, Oct 04, 2011 at 12:30:48PM +0100, 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
columnthe 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;

alter table hist alter column hist_id set default nextval( 'hist_id_seq');

Best regards,

depesz

--
The best thing about modern society is how easy it is to avoid contact with it.
                                                             http://depesz.com/

2011-10-04 13:30 keltezéssel, Robert Buckley írta:

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;

You also need to add a DEFAULT expression and optionally
make the sequence owned by the column:

ALTER TABLE tablename ALTER COLUMN hist_id SET DEFAULT nextval('hist_id_seq');
ALTER SEQUENCE hist_id_seq OWNED BY tablename.hist_id;

The "ALTER SEQUENCE ... OWNED BY ..." will make the sequence
automatically dropped if this column or table is dropped.

thanks for any help,

Rob

-- 
----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de    http://www.postgresql.at/

Hi,

2011-10-04 14:05 keltezéssel, Robert Buckley írta:

Thanks for the replies,

I have one question regarding this comment...

"You also need to add a DEFAULT expression and optionally

make the sequence owned by the column:"

What difference does it make if a table "owns" a sequence of not?...does this contraint the use of the sequence to ONLY that one table?

The sequence will only be used to auto-increment the id column in order to have an automatic primary key. Could I then somehow use the sequence for all tables which need this?

yours,

Rob

please, don't use top-posting. It messes up the order of the messages.

The sequenced that is OWNED BY a column can of course be used
by other columns in other tables. You can specify nextval('same sequence')
for any number of columns' default value.

Best regards,
Zoltán Böszörményi

-- 
----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de    http://www.postgresql.at/

On 10/04/2011 08:05 AM, Robert Buckley wrote:
> Thanks for the replies,
>
> I have one question regarding this comment...
>
> "You also need to add a DEFAULT expression and optionally
> make the sequence owned by the column:"
>
> What difference does it make if a table "owns" a sequence of not?...does
> this contraint the use of the sequence to ONLY that one table?
>
> The sequence will only be used to auto-increment the id column in order
> to have an automatic primary key. Could I then somehow use the sequence
> for all tables which need this?

Making the table own the sequence causes the sequence to be dropped if
the table is dropped.  Making the sequence owned by the table happens
automatically if you had originally created the column with SERIAL
instead of adding the sequence with ALTER TABLE, but is not entirely
necessary.

That said, I'd think twice before using a sequence for more than one
table (unless they were part of an inheritance/partitioning scheme).

Joe