Обсуждение: [NOVICE] alter existing table column with primary key to auto-increment
I am just starting with psql.
I have created surrogate id keys in some tables.alter table customer add column id serial primary key;
I want to know how to alter the column instead of dropping it.Rounak Jain <rounakjainis@gmail.com> writes: > I am just starting with psql. > I have created surrogate id keys in some tables. > They are primary keys. > I want to add auto-increment to them. You want to read the definition of serial types here: https://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE-SERIAL If you already have the column, you can make the sequence object, add the default expression (ALTER TABLE ... ALTER COLUMN ... SET DEFAULT ... I think is the right syntax) and do the ALTER OWNED BY if you'd like the sequence to go away if the column is dropped. If there's data in the column already, you probably also need to tweak the current value of the sequence. regards, tom lane
yes, thanks for the help, martin.
create sequence product_id_seq;
alter table product alter column id set default nextval('product_id_seq');
alter sequence product_id_seq owned by product.id;
create sequence product_id_seq;
alter table product alter column id set default nextval('product_id_seq');
alter sequence product_id_seq owned by product.id;
On Fri, Feb 17, 2017 at 9:17 PM, Martin Steer <martinsteer@maxi.net.au> wrote:
On Sun, Feb 05, 2017 at 04:27:44PM +0530, Rounak Jain wrote:alter table customer add column id serial primary key;
I want to know how to alter the column instead of dropping it.
Consult the online help:
\h alter
\h alter table
\h alter sequence
M.