Question 284211 in PostgreSQL Question posted in
The official documentation can be found here.

How to use postgresql default values with spring jpa?

I have a simple enough question. I have some entity (I’ve deliberately cut it down) that has id, name and another identifier(publicId) in the form of uuid, but which is stored in the database as a string.

@Entity
@Table(name = "some_entity")
@Getter
@Setter
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class SomeEntity extends BaseEntity<Long> {
    @Id
    @Column(name = "id", updatable = false, nullable = false)
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "public_id", nullable = false)
    private String publicId;

    @Column(name = "name", nullable = false)
    private String name;
  }

For this entity I have a sql format migration for liquibase. It looks like the following.

CREATE TABLE some_entity
(
    id          BIGINT GENERATED ALWAYS AS IDENTITY
        CONSTRAINT expense_card_pk PRIMARY KEY,
    public_id   VARCHAR(50)              NOT NULL DEFAULT uuid_generate_v4(),
    name        VARCHAR(50)              NOT NULL
);

As you can see, for the public_id field I use the default function from the "uuid-ossp" package – uuid_generate_v4().

Now, I’m trying to save the SomeEntity object that i created with lombok builder. In the builder, I have specified only the name field. And with the help of spring repository I am saving the object. BUT! I get an error when saving the item.

ERROR: null value in column "public_id" of relation "expense_card" violates not-null constraint

And I understand why it’s happening. Builder creates an object, all fields of which have null, except for name. I try to save such an object, and it turns out that I save null in the not null field. But isn’t that bullshit? Shouldn’t postgres handle this?

Anyway can you please tell me how I can create an identifier for the public_id field in uuid format automatically, without doing it in the builder every time I create an object?

Ideally, I want this responsibility to be on the database. As a last resort, maybe there are some jpa annotations for this?

Yes, by the way, I want to store this identifier in string format.

Tags:

2

Answers


  1. 0

    Since you are using migration tool annotate your entity class with
    @DynamicInsert and remove nullable=false in your public_id entity definition (in POJO).
    You must force Hibernate to omit the value public_id when creating insert statement. Then DB will take care to create default value. Otherwise you’ll get the constraint violation due to you explicitly tell DB to save null in non-null field

    DynamicInsert causes that your insert statements omit fields that were not assigned/changed within transaction

    Login or Signup to reply.
  2. 0

    You may want to check out uuid_generate_v4() – can use the uuid_generate_v4() function as a default value for the column.

    CREATE TABLE some_entity
(
    id          BIGINT GENERATED ALWAYS AS IDENTITY
        CONSTRAINT expense_card_pk PRIMARY KEY,
    public_id   VARCHAR(50) DEFAULT uuid_generate_v4()::VARCHAR(50) NOT NULL,
    name        VARCHAR(50) NOT NULL
);

    this makes sure that uuid_generate_v4() is cast to VARCHAR(50) before being assigned to the public_id column; doing so, you don’t need to specify the publicId field in your builder when creating a new SomeEntity object.

    And, don’t forget to annotate it!

    @Column(name = "public_id", nullable = false, columnDefinition = "VARCHAR(50) DEFAULT uuid_generate_v4()::VARCHAR(50)")
private String publicId;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search