CODEWITHSUNDEEP
postgresqlsequence
kipsoft
kipsoft

Reputation: 359

Can you create a sequence on a column that already exists in Postgres

I have a table linelevelpmts with a column seq (Int4) which is to be used as a sequence.

I know I can delete the column and recreate it as type serial, but can I modify the existing column to be used as a sequence.

ALTER TABLE "public"."linelevelpmts" ALTER COLUMN "seq" SET DEFAULT nextval('linelevelpmts_seq_seq'::regclass);

This code generates an error: Relation linelevelpmts_seq_seq does not exist.

Upvotes: 6

Views: 8569

Answers (1)

user330315
user330315

Reputation:

This code generates an error: Relation linelevelpmts_seq_seq does not exist.

Well you need to first create the sequence you want to use for the default value:

create sequence linelevelpmts_seq_seq;
ALTER TABLE public.linelevelpmts 
    ALTER COLUMN seq SET DEFAULT nextval('linelevelpmts_seq_seq'::regclass);

If you want the same effect as if it was created as serial you also need to change the "owner" of the sequence:

alter sequence linelevelpmts_seq_seq owned by linelevelpmts.seq;

Edit

Igor's comment is a good one: if you already have values in the column seq you should adjust the starting value of the sequence:

select setval('linelevelpmts_seq_seq', (select max(seq) from linelevelpmts));

Upvotes: 16

Related Questions