Postgres error in batch insert : relation "hibernate_sequence" does not exist position 17

Question

I am performing hibernate jpa batch update and its giving me following error

2015-04-21 15:53:51,907 WARN  [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (Thread-283 (HornetQ-client-global-threads-462057890)) SQL Error: 0, SQLState: 42P01
2015-04-21 15:53:51,908 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (Thread-283 (HornetQ-client-global-threads-462057890)) ERROR: relation "my_seq_gen" does not exist

I am using postgres database and my ID is auto generated

  @Id
@SequenceGenerator(name="seq-gen",sequenceName="MY_SEQ_GEN"initialValue=205, allocationSize=12)
@GeneratedValue(strategy= GenerationType.SEQUENCE, generator="seq-gen")
@Column(name="\"ID\"",unique=true,nullable=false)
private int id;

This is my batch insert code snippet

getEm().getTransaction().begin();
System.out.println("transaction started--------------");
try {   
    for (Receipt ReceiptEntity : arrReceiptEntity) {
            getEm().persist(ReceiptEntity);
    }
    getEm().getTransaction().commit();
    System.out.println("commited");
} catch (Exception exception) {
    System.out.println("error----------------------------------------------------------------------");
    if(getEm().getTransaction().isActive())
        getEm().getTransaction().rollback();
    LOG.error(exception);
} finally {
    getEm().flush();
    getEm().clear();
    getEm().close();
}

I have added the following property in persistence.xml

         <property name="hibernate.id.new_generator_mappings" value="true"/>

Please suggest what i am doing wrong.

try GenerationType.IDENTITY instead of GenerationType.AUTO – Pratik Apr 21 '15 at 12:06 — Pratik, Apr 21 '15 at 12:06
http://stackoverflow.com/a/25279598/592355 – xerx593 Apr 21 '15 at 12:23 — xerx593, Apr 21 '15 at 12:23

score 118 · Answer 1 · edited Dec 31 '19 at 21:16

118

Try to annotate your id with @Id and @GeneratedValue(strategy = GenerationType.IDENTITY).

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

UPDATE: It will only work if your id column was declared as SERIAL or BIGSERIAL types.

edited Dec 31 '19 at 21:16

Youcef LAIDANI

55,661
15
90
140

answered Mar 12 '16 at 12:11

Vsevolod Poletaev

1,482
1
12
13

2

Isn't IDENTITY is bad option for Postgresql? https://stackoverflow.com/questions/10041938/how-to-choose-the-id-generation-strategy-when-using-jpa-and-hibernate – Erlan Oct 27 '17 at 13:25
@Erlan I see no reason why it should be bad, neither from your link, nor from my experience. Can you explain your point? – Vsevolod Poletaev Oct 28 '17 at 09:20
3

@VsevolodPoletaev. Answers from link mentioned that IDENTITY is useful when there is no option of SEQUENCE because it will disable batch execution. But then found out that in hibernate 5 IDENTITY will bind id to SERIAL's sequence, so it should be ok. Thanks for answer. – Erlan Oct 31 '17 at 10:19
@Mikhail, you are right. It's written in docs http://docs.jboss.org/hibernate/orm/5.2/userguide/html_single/Hibernate_User_Guide.html#identifiers-generators-identity . But it's not important in most usecases. – Vsevolod Poletaev Mar 28 '18 at 09:47
Thanks for the tip and update! Spring Boot does the Rest! ;) – gleitonfranco Jan 18 '21 at 23:59
As far as I am concerned, your answer did the trick for me! I simply added basic identifier properties like not null and unique and all was good!! THUMBS UP – avi.elkharrat Feb 03 '22 at 22:19

Beri · Answer 2 · 2021-10-10T16:04:24.040

If you don't want to change your entity definition, then you need to create a sequence in your postgreSQL schema with name hibernate_sequence.

CREATE SEQUENCE hibernate_sequence START 1;

UPDATE:

You are missing second sequence generatef, which you defined for your entity, just add it like previous one:

CREATE SEQUENCE my_seq_gen START 1;

What is a sequence?

Sequence is an ordered list of integers. The orders of numbers in the sequence are important. You can configure what is the min and max values, by what amount you should increment it:

CREATE SEQUENCE [ IF NOT EXISTS ] sequence_name
  [ AS { SMALLINT | INT | BIGINT } ]
  [ INCREMENT [ BY ] increment ]
  [ MINVALUE minvalue | NO MINVALUE ] 
  [ MAXVALUE maxvalue | NO MAXVALUE ]
  [ START [ WITH ] start ] 
  [ CACHE cache ] 
  [ [ NO ] CYCLE ]

No you can use functions like nextval('') in your SQL commands and in hibernate to get next value from the set. This is much cheaper than keepipng current primary key value in a sequence_table or looking for max PK value in existing table. So it provides an easy and cheap way to find next PK for given table.

All tables usually use a dedicated Sequance, and like in this example it was chosen as IdGenerator strategy.

Sore useful tutorial:

I have added sequence generator but its giving another error please check the updated question — kirti, Apr 21 '15 at 13:57
As your exception says, you are still missing one sequence my_seq_gen, add it as previously. — Beri, Apr 21 '15 at 19:14
Worked, like a charm, not sure what actually happens when you create the above sequence, if you could provide an explanation would be cherry on top. — xxx, Oct 06 '21 at 15:15

score 10 · Answer 3 · edited Feb 16 '16 at 00:42

10

I hope you get the answer but if you are still finding the answer this could be helpful.

I had same problem and resolved it annotating getter method of the id with @SequenceGenerator and @GeneratedValue.

@SequenceGenerator(name="seq-gen",sequenceName="MY_SEQ_GEN", initialValue=205, allocationSize=12)
@GeneratedValue(strategy= GenerationType.SEQUENCE, generator="seq-gen")
public int getId(){
    return id;
}

edited Feb 16 '16 at 00:42

biniam

8,099
9
49
58

answered Sep 08 '15 at 22:37

Sameer

109
1
3

No. It does not solve my problem. Still see `Hibernate: select nextval ('hibernate_sequence')` and this sequence does not exist initially in the schema. Have to create it manually like `CREATE SEQUENCE hibernate_sequence START 1;`, as the former answer. – WesternGun Feb 01 '19 at 13:55

score 4 · Answer 4 · answered Jan 14 '20 at 09:00

4

Check application.properties file.

make sure that spring.jpa.hibernate.ddl-auto = update

answered Jan 14 '20 at 09:00

Abdelsalam Megahed

1,281
12
13

this fixed my initial error same as in the question – Charlotte45 Dec 30 '20 at 22:05
2

Not recommended for production, preferred approach is to use Flyway with custom DDL scripts. – Alex May 31 '22 at 18:41

score 3 · Answer 5 · answered Apr 21 '15 at 12:32

3

Can you try following :

  @Id
  @GeneratedValue(strategy = GenerationType.AUTO, generator = "auto_gen")
  @SequenceGenerator(name = "auto_gen", sequenceName = "A")
  @Column(name = "ID")
  private int id;

Thanks

answered Apr 21 '15 at 12:32

Mayur

114
6

I have added sequence generator but its giving another error please check the updated question – kirti Apr 21 '15 at 13:57

score 2 · Answer 6 · edited Feb 09 '17 at 19:26

2

I was with the same problem.I solved puting auto increment on id column from the table on postgres it works, like that.

ALTER TABLE mytable ALTER COLUMN id SET DEFAULT nextval('mytable_id_seq');

ALTER SEQUENCE mytable_id_seq OWNED BY mytable.id;

edited Feb 09 '17 at 19:26

Nikolay Kostov

16,433
23
85
123

answered Feb 09 '17 at 19:07

Sergeant

21
2

score 2 · Answer 7 · answered Jan 02 '18 at 02:03

According to a post, hibernate could not get next sequence value , set your @GeneratedId column with strategy GenerationType.IDENTITY instead of GenerationType.SEQUENCE. So, you would have

@Id
@SequenceGenerator(name="seq-gen",sequenceName="MY_SEQ_GEN"initialValue=205, allocationSize=12)
@GeneratedValue(strategy= GenerationType.IDENTITY, generator="seq-gen")
@Column(name="\"ID\"",unique=true,nullable=false)
private int id;

score 2 · Answer 8 · answered Dec 23 '19 at 19:29

2

Add the following to your application.properties

# Hibernate ddl auto (create, create-drop, validate, update)
spring.jpa.hibernate.ddl-auto = update

This will explicitly set to create or update all tables during Spring startup.

answered Dec 23 '19 at 19:29

PrecisionLex

801
11
26

Vijay Vikranth · Answer 9 · 2017-05-08T07:21:50.127

1

In my case adding property name="hibernate.hbm2ddl.auto" value="update", solved the issue. You have to add the mentioned property in persistence.xml

edited May 08 '17 at 07:21

answered May 08 '17 at 05:04

Vijay Vikranth

11
3

1

Worked for me (but I tried with **create**). Watch out: This option deletes all the data from your database and is intended for development only. – El Mac May 02 '18 at 13:38

score 1 · Answer 10 · answered Dec 25 '18 at 05:17

Sometimes with @Id @GeneratedValue(strategy=GenerationType.IDENTITY) annotation you can have your sequence with empty intervals (after delete) and incorrect next autoincrement position. Try to set next autoincrement value to the position after biggest id value:

ALTER SEQUENCE schema.entity_id_seq RESTART WITH 40072;

score 1 · Answer 11 · answered Sep 18 '19 at 08:29

1

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

answered Sep 18 '19 at 08:29

Nyasha Chitiyo

11
1

score 1 · Answer 12 · answered Jun 07 '20 at 00:36

Recently I had the same issue and I have resolved it by using @GenericGenerator annotation. The issue was because I had to insert hundreds of rows with SQL statements in liquibase changelog and somehow hibernate does not pick up last row and its before generating id.

@Entity(name = "domain")
@Table(name = "domain", schema = "public")
public class DomainJpa {

    @Getter
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO, generator = "incrementDomain")
    @GenericGenerator(name = "incrementDomain", strategy = "increment")
    private Integer id;

}

hopefully this can be useful for someone. Cheers :)

Postgres error in batch insert : relation "hibernate_sequence" does not exist position 17

12 Answers12

Linked