How to migrate [DDL] from generation type identity to generation type sequence for postgres db

user_9247465 0
Viewed 784

As of now I am generating id using

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;

the sequence generated in DB is like:

CREATE SEQUENCE public.table_name_id_seq
  START WITH 1
  INCREMENT BY 1
  NO MINVALUE
  NO MAXVALUE
  CACHE 1;

ALTER SEQUENCE public.table_name_id_seq OWNED BY public.table_name.id;

ALTER TABLE ONLY public.table_name ALTER COLUMN id SET DEFAULT``nextval('public.table_name_id_seq'::regclass);

or at some places in definition itself:

CREATE TABLE "public.table_name" (
   "id" int8 NOT NULL DEFAULT nextval('table_name_id_seq'::regclass),
   "some_column" varchar(255),
   PRIMARY KEY ("id")
);

I am using hibernate as JPA provider, I needed to enable insert and update batching due to which I want to change the generation type to SEQUENCE from IDENTITY.

After changing to generation type sequence [in hibernate] and using older sequence [of postgres] i see weird id being generated. This is not the expected behavior.

@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "table_name_id_seq ")
private Long id;

enter image description here

The ids which are highlighted in yellow is created after using generation type sequence. I was expecting id after 23 to be 24 and not -22.

How can I fix this? How can I smoothly shift from IDENTITY to SEQUENCE? What is the proper DDL?

jpa postgresql hibernate spring
1 Answers

Answering to this part of your question:

How can I smoothly shift from IDENTITY to SEQUENCE? What is the proper DDL?

You should do the following things:

  1. Drop the default value from the table_name.id column:
alter table only table_name
alter column id drop default;
  1. Correct your mapping for the id field:
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "my_generator")
@SequenceGenerator(name = "my_generator", sequenceName = "table_name_id_seq", allocationSize = 1)
private Long id;

Please note that an allocationSize should be equal to the INCREMENT BY of your sequence definition.

user_62771040
Related