I developed an Entity class in Spring boot of the following type, for a Postgresql database

import java.time.OffsetDateTime;
import java.util.Set;
import java.util.UUID;
import jakarta.persistence.*;

import lombok.*;
import org.apachemons.lang3.StringUtils;
import orgr.plantvocdb.enums.LeafHabitus;
import orgr.plantvocdb.enums.PlantsRanks;

@Entity
@AllArgsConstructor
@NoArgsConstructor
@Builder
@Getter
@Setter
@Table(name = "plants_voc")
public class PlantVocEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.UUID)
    @Column(name="id", length = 50, nullable = false, updatable = false)
    private UUID id;

    @Column(name="ipni", length = 50)
    private String ipni;

    @Setter(AccessLevel.NONE)
    @Column(name="full_name_plain", length = 50)
    private String fullNamePlain;

    @Setter(AccessLevel.NONE)
    @Column(name="full_name_no_authors_plain", length = 50)
    private String fullNameNoAuthorsPlain;

    @Setter(AccessLevel.NONE)
    @Column(name="plant_name", length = 30, nullable = false)
    private String name;

    @Setter(AccessLevel.NONE)
    @Column(name="family", length = 30, nullable = false)
    private String family;

    @Setter(AccessLevel.NONE)
    @Column(name="genus", length = 30, nullable = false)
    private String genus;

    @Setter(AccessLevel.NONE)
    @Column(name="species", length = 30, nullable = false)
    private String species;

    @Column(name="valid_nomenclature")
    private boolean validNomenclature;

    @Column(name="rank", length = 20)
    @Enumerated(EnumType.STRING)
    private PlantsRanks rank;

    @Column(name="leaf_habitus", length = 20)
    @Enumerated(EnumType.STRING)
    private LeafHabitus leafHabitus;

    @OneToMany(
            fetch = FetchType.EAGER,
            cascade = CascadeType.ALL,
            mappedBy = "plant"
    )
    private Set<PlantEmitterEntity> emitter;

    @ElementCollection
    @CollectionTable(
            name = "synonyms", // Nome della tabella intermedia
            joinColumns = @JoinColumn(name = "fk_synonyms_plant_id"),
            foreignKey = @ForeignKey(name = "FK_synonyms_plant")
    )
    @Column(name="synonyms")
    private Set<String> synonyms;

    @Column(name="created_datetime_utc", updatable = false) // creation_datetime_utc
    private OffsetDateTime createdDatetimeUTC;

    @Column(name="updated_datetime_utc")  // last_modified_datetime_utc
    private OffsetDateTime updatedDatetimeUTC;

    public void setName(String name) {

        this.name = StringUtils
                .normalizeSpace(name.toLowerCase());
    }

    public void setFamily(String family) {

        this.family = StringUtils
                .normalizeSpace(StringUtils
                        .capitalize(family.toLowerCase()));
    }

    public void setGenus(String genus) {

        this.genus = StringUtils
                .normalizeSpace(StringUtils
                        .capitalize(genus.toLowerCase()));
    }

    public void setSpecies(String species) {

        this.species = StringUtils
                .normalizeSpace(species.toLowerCase());
    }

    public void setFullNamePlain(String fullNamePlain) {
        this.fullNamePlain = StringUtils
                .normalizeSpace(fullNamePlain);
    }

    public void setFullNameNoAuthorsPlain(String fullNameNoAuthorsPlain) {
        this.fullNameNoAuthorsPlain = StringUtils
                .normalizeSpace(fullNameNoAuthorsPlain);
    }
}

and

import jakarta.persistence.*;
import lombok.Getter;
import lombok.Setter;

@Entity
@Table(name="emitters")
@Getter
@Setter
public class PlantEmitterEntity {

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

    @Column(name="emits", length = 50)
    private boolean emits;

    @Column(name="doi")
    private String doi;

    @ManyToOne
    @JoinColumn(
            name = "fk_emitters_plant_id",
            nullable = false,
            updatable = true,
            insertable = true,
            foreignKey = @ForeignKey(name = "FK_emitters_plant"))
    private PlantVocEntity plant;

}

What I want to do is to enter via a data.sql the data as a database initialization when starting the system for the first time

INSERT
INTO
    plants_voc (
    id,
    ipni,
    full_name_plain,
    full_name_no_authors_plain,
    plant_name,
    family,
    genus,
    species,
    valid_nomenclature,
    rank,
    leaf_habitus,
    created_datetime_utc,
    updated_datetime_utc

)
VALUES (
        DEFAULT,
        'urn:lsid:ipni:names:294902-1',
        'Alnus cordata (Loisel.) Duby',
        'Alnus cordata',
        'cordata',
        'Betulaceae',
        'Alnus',
        'cordata',
        TRUE,
        'SPECIES',
        'DECIDUOUS',
        NOW(),
        NOW()
       ) RETURNING id;

Unfortunately I get this error caused by:

Caused by: org.postgresql.util.PSQLException: ERROR: null value in column "id" of relation "plants_voc" violates not-null constraint

I confess to being very unfamiliar with Postgresql

# App config
server.port=8080
spring.application.name=plantvocdb

spring.datasource.url=jdbc:postgresql://localhost:5432/plantvocdb
spring.datasource.username=XXXX
spring.datasource.password=YYYY
spring.datasource.driver-class-name=org.postgresql.Driver

# JPA
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.generate-ddl=true

# This will create table automatically in your database
spring.jpa.hibernate.ddl-auto=create
# set none if you use schema.sqlxx and data.sql
# spring.jpa.hibernate.ddl-auto=none

spring.sql.init.mode=always

I developed an Entity class in Spring boot of the following type, for a Postgresql database

import java.time.OffsetDateTime;
import java.util.Set;
import java.util.UUID;
import jakarta.persistence.*;

import lombok.*;
import org.apache.commons.lang3.StringUtils;
import org.cnr.plantvocdb.enums.LeafHabitus;
import org.cnr.plantvocdb.enums.PlantsRanks;

@Entity
@AllArgsConstructor
@NoArgsConstructor
@Builder
@Getter
@Setter
@Table(name = "plants_voc")
public class PlantVocEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.UUID)
    @Column(name="id", length = 50, nullable = false, updatable = false)
    private UUID id;

    @Column(name="ipni", length = 50)
    private String ipni;

    @Setter(AccessLevel.NONE)
    @Column(name="full_name_plain", length = 50)
    private String fullNamePlain;

    @Setter(AccessLevel.NONE)
    @Column(name="full_name_no_authors_plain", length = 50)
    private String fullNameNoAuthorsPlain;

    @Setter(AccessLevel.NONE)
    @Column(name="plant_name", length = 30, nullable = false)
    private String name;

    @Setter(AccessLevel.NONE)
    @Column(name="family", length = 30, nullable = false)
    private String family;

    @Setter(AccessLevel.NONE)
    @Column(name="genus", length = 30, nullable = false)
    private String genus;

    @Setter(AccessLevel.NONE)
    @Column(name="species", length = 30, nullable = false)
    private String species;

    @Column(name="valid_nomenclature")
    private boolean validNomenclature;

    @Column(name="rank", length = 20)
    @Enumerated(EnumType.STRING)
    private PlantsRanks rank;

    @Column(name="leaf_habitus", length = 20)
    @Enumerated(EnumType.STRING)
    private LeafHabitus leafHabitus;

    @OneToMany(
            fetch = FetchType.EAGER,
            cascade = CascadeType.ALL,
            mappedBy = "plant"
    )
    private Set<PlantEmitterEntity> emitter;

    @ElementCollection
    @CollectionTable(
            name = "synonyms", // Nome della tabella intermedia
            joinColumns = @JoinColumn(name = "fk_synonyms_plant_id"),
            foreignKey = @ForeignKey(name = "FK_synonyms_plant")
    )
    @Column(name="synonyms")
    private Set<String> synonyms;

    @Column(name="created_datetime_utc", updatable = false) // creation_datetime_utc
    private OffsetDateTime createdDatetimeUTC;

    @Column(name="updated_datetime_utc")  // last_modified_datetime_utc
    private OffsetDateTime updatedDatetimeUTC;

    public void setName(String name) {

        this.name = StringUtils
                .normalizeSpace(name.toLowerCase());
    }

    public void setFamily(String family) {

        this.family = StringUtils
                .normalizeSpace(StringUtils
                        .capitalize(family.toLowerCase()));
    }

    public void setGenus(String genus) {

        this.genus = StringUtils
                .normalizeSpace(StringUtils
                        .capitalize(genus.toLowerCase()));
    }

    public void setSpecies(String species) {

        this.species = StringUtils
                .normalizeSpace(species.toLowerCase());
    }

    public void setFullNamePlain(String fullNamePlain) {
        this.fullNamePlain = StringUtils
                .normalizeSpace(fullNamePlain);
    }

    public void setFullNameNoAuthorsPlain(String fullNameNoAuthorsPlain) {
        this.fullNameNoAuthorsPlain = StringUtils
                .normalizeSpace(fullNameNoAuthorsPlain);
    }
}

and

import jakarta.persistence.*;
import lombok.Getter;
import lombok.Setter;

@Entity
@Table(name="emitters")
@Getter
@Setter
public class PlantEmitterEntity {

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

    @Column(name="emits", length = 50)
    private boolean emits;

    @Column(name="doi")
    private String doi;

    @ManyToOne
    @JoinColumn(
            name = "fk_emitters_plant_id",
            nullable = false,
            updatable = true,
            insertable = true,
            foreignKey = @ForeignKey(name = "FK_emitters_plant"))
    private PlantVocEntity plant;

}

What I want to do is to enter via a data.sql the data as a database initialization when starting the system for the first time

INSERT
INTO
    plants_voc (
    id,
    ipni,
    full_name_plain,
    full_name_no_authors_plain,
    plant_name,
    family,
    genus,
    species,
    valid_nomenclature,
    rank,
    leaf_habitus,
    created_datetime_utc,
    updated_datetime_utc

)
VALUES (
        DEFAULT,
        'urn:lsid:ipni.org:names:294902-1',
        'Alnus cordata (Loisel.) Duby',
        'Alnus cordata',
        'cordata',
        'Betulaceae',
        'Alnus',
        'cordata',
        TRUE,
        'SPECIES',
        'DECIDUOUS',
        NOW(),
        NOW()
       ) RETURNING id;

Unfortunately I get this error caused by:

Caused by: org.postgresql.util.PSQLException: ERROR: null value in column "id" of relation "plants_voc" violates not-null constraint

I confess to being very unfamiliar with Postgresql

# App config
server.port=8080
spring.application.name=plantvocdb

spring.datasource.url=jdbc:postgresql://localhost:5432/plantvocdb
spring.datasource.username=XXXX
spring.datasource.password=YYYY
spring.datasource.driver-class-name=org.postgresql.Driver

# JPA
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.generate-ddl=true

# This will create table automatically in your database
spring.jpa.hibernate.ddl-auto=create
# set none if you use schema.sqlxx and data.sql
# spring.jpa.hibernate.ddl-auto=none

spring.sql.init.mode=always

• java
• postgresql
• spring-boot
• hibernate
• initialization

• If iI skip id column I got this error: Caused by: org.postgresql.util.PSQLException: ERROR: INSERT has more target columns than expressions – Gianni Spear Commented yesterday
• I did and I got: Caused by: org.postgresql.util.PSQLException: ERROR: null value in column "id" of relation "plants_voc" violates not-null constraint – Gianni Spear Commented yesterday
• OP reported that replacing DEFAULT with gen_random_uuid() works. Asked OP to create an answer with that. Case resolved. BR – Roar S. Commented 23 hours ago

1 Answer 1

I recommend using a Sequence for the Generation Strategy. This will allow you to insert a row without using JPA. As the data.sql contains plain SQL you need to have the Sequence on the Database. The ID with the default value should also be skipped from the insert statement.

@Entity
@Getter
@Setter
@Table(name = "table")
@SequenceGenerator(
        name = "name_seq",
        sequenceName = "name_seq",
        allocationSize = 1)
public class EntityClass {

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

data.sql

INSERT
INTO
    plants_voc (
    ipni,
    full_name_plain,
    full_name_no_authors_plain,
    plant_name,
    family,
    genus,
    species,
    valid_nomenclature,
    rank,
    leaf_habitus,
    created_datetime_utc,
    updated_datetime_utc

)
VALUES (
        'urn:lsid:ipni.org:names:294902-1',
        'Alnus cordata (Loisel.) Duby',
        'Alnus cordata',
        'cordata',
        'Betulaceae',
        'Alnus',
        'cordata',
        TRUE,
        'SPECIES',
        'DECIDUOUS',
        NOW(),
        NOW()
       ) RETURNING id;