2

In my postgresql I have the values table generated from this sql:

CREATE TABLE public.values (
    id int4 NOT NULL DEFAULT nextval('my_rable_seq'::regclass),
    value varchar(150) NOT NULL,
    CONSTRAINT values_pkey PRIMARY KEY (id)
);

Or from this SQL:


CREATE TABLE public.values (
    id SERIAL,
    value varchar(150) NOT NULL,
    CONSTRAINT values_pkey PRIMARY KEY (id)
);

And I do not want the autoincrement feature any more. How I can alter the table without removing-affecting any data?

1 Answer 1

4

Just remove the default value on column and the corresponding sequence

alter table values alter column id drop default ;
drop sequence values_id_seq ;
4
  • 1
    Before dropping the sequence, we need to check if it is used elsewhere. It's not common but certainly possible for a sequence to be used by 2 or more columns in different tables. Oct 11, 2022 at 10:40
  • How I do that @ypercubeᵀᴹ Oct 11, 2022 at 14:12
  • Well, one hacky way would be to run the drop sequence and see the error message. If it succeeds, fine, there was no dependency. If it fails, you'll get an error like ERROR: cannot drop sequence values_id_seq because other objects depend on it DETAIL: default value for column <column_name> of table <table_name> depends on sequence values_id_seq Oct 11, 2022 at 15:21
  • There is surely a way to find dependencies in advance by querying the system catalog (I'd have to check exactly how) Oct 11, 2022 at 15:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.