Spring Data JPA "null value in column xxx violates not-null constraint" on serial column with postgresql

Question

My entity has a mapOrder field which I want auto-increment like below:

@Entity
public class Map{
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(columnDefinition = "serial")
    private Long mapOrder;

    //.......
}

The sql generated seems good:

CREATE TABLE map
(
  id bigserial NOT NULL,
  map_order serial NOT NULL,
  ...
)

But when I save it with Spring Data JPA's repository, like this:

Map m=new Map();
repo.save(m);

will give me exception:

Caused by: org.postgresql.util.PSQLException: ERROR: null value in column "map_order" violates not-null constraint

Any ideas?

Take a look: http://stackoverflow.com/questions/4979746/mapping-postgresql-serial-type-with-hibernate-annotations — user3707125, Jun 07 '15 at 04:49
you are using object Long, so default value is null. Also you have to specify the generation method for map_order — Safwan Hijazi, Jun 07 '15 at 05:57
@user3707125 Thanks, the accepted answer works for me, though ```@Generated``` is not a JPA annotation. — July, Jun 07 '15 at 06:21

score 5 · Answer 1 · answered May 27 '18 at 12:58

@GeneratedValue works with identifiers and you can't use it with regular fields.

You can, for example, use some object for sequences (with any key generation strategy):

@Entity
public class JpaNoPkSequence {
    @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    @Column(name="id", nullable=false, updatable=false)    
    private Long id;
}

For using the strategy GenerationType.SEQUENCE the sequence should be created in the database:

CREATE SEQUENCE JPA_PK_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
ALTER SEQUENCE "JPA_PK_SEQ" OWNER TO something;

This should be specified in the key definition. You should also add a one-to-one relationship with the object that you will use to obtain sequences for regular fields:

@Entity 
public class Map {
    @Id
    @SequenceGenerator(name="jpaPkSeq", sequenceName="JPA_PK_SEQ", allocationSize=1, initialValue = 1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "jpaPkSeq")
    @Column(name = "id", nullable=false, updatable=false)
    private Long id;

    @OneToOne           
    private JpaNoPkSequence sequence;
    ...
}

Hope this helps.

score 4 · Answer 2 · edited May 23 '17 at 10:30

4

Try changing your code to this:

@GeneratedValue(strategy = GenerationType.SEQUENCE)

Reference: https://stackoverflow.com/a/29028369

edited May 23 '17 at 10:30

Community

1
1

answered Jan 22 '16 at 19:52

user3791688

41
2

score 0 · Answer 3 · edited Jul 22 '22 at 17:54

I had the same problem. In my case, the error was in creating the table.

You need to use SERIAL type instead of others (at least in Postgres).

Before (bigint type)

CREATE TABLE IF NOT EXISTS sample_table

(
    id BIGINT NOT NULL,
    ...
);

After (serial type)

CREATE TABLE IF NOT EXISTS sample_table

(
    id SERIAL NOT NULL,
    ...
);

The id in the entity class looked like this:

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

score 0 · Answer 4 · answered Jul 22 '22 at 18:02

This question is full of bad advice. The problem is the second column with a serial value. The problem is that Hibernate explicitly inserts a null there. You have to tell it not to.

@Column(columnDefinition = "serial", insertable = false)
private Long mapOrder;

Spring Data JPA "null value in column xxx violates not-null constraint" on serial column with postgresql

4 Answers4

Linked