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.
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));
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With