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 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;

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/

Thanks for the replies,

I have one question regarding this comment...

"You also need to add a DEFAULT expression and optionallymake 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

________________________________
Von: Boszormenyi Zoltan <zb@cybertec.at>
An: pgsql-general@postgresql.org
Gesendet: 13:43 Dienstag, 4.Oktober 2011
Betreff: Re: [GENERAL] auto-increment column

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