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

Question

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;

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?

@a_horse_with_no_name ,i think it is not duplicate. I have gone to the possible duplicate of the post it doesn't helped me. Can you please reopen it so that others can help ? — AConsumer, Jul 01 '20 at 17:47
I'm a little confused. Correct me if I'm wrong, but you want to change you database to generate your primary key values from a sequence, this is done by the ALTER TABLE command that you wrote in the question. — Leandro Cavalcante, Jul 01 '20 at 18:33
Identity and sequence are different id generation strategies in hibernate. The identity strategy use db sequence for id generation. But identity and sequence strategies are fundamentally different, perhaps sequence strategy also use db sequence but it is not auto incremented like identity. — AConsumer, Jul 01 '20 at 18:48
Have you tried changing the strategy to `GenerationTypeSEQUENCE` and what ddl hibernate generates for it? Once JPA gives the DDL, isn't it matter of let it start from where existing data's biggest Id? — Kavithakaran Kanapathippillai, Jul 01 '20 at 18:56
The start (and thus the current) value of the sequence was changed. See [here](https://stackoverflow.com/questions/244243/how-to-reset-postgres-primary-key-sequence-when-it-falls-out-of-sync) for a solution — , Jul 02 '20 at 06:15

score 2 · Accepted Answer · answered Jul 04 '20 at 16:55

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:

Drop the default value from the table_name.id column:

alter table only table_name
alter column id drop default;

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.

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

1 Answers1