I have created a project to test PostgreSQL's inheritance feature with Spring Boot 3.2.5 with Java 21. I am using Flyway scripts to manually create the tables:

create table if not exists products
(
    id  int not null,
    name_product varchar not null,
    price float not null,
    CONSTRAINT users_pkey PRIMARY KEY (id)
    );

create table if not exists books
(
    LIKE products INCLUDING INDEXES, --optional, used to inherit parent's indexes
    author varchar not null,
    isbn varchar not null
    ) INHERITS (products); --mandatory, creates the inheritance

create table if not exists electronics
(
    LIKE products INCLUDING INDEXES, --optional, used to inherit parent's indexes
    brand varchar not null,
    warranty integer not null
    ) INHERITS (products); --mandatory, creates the inheritance

CREATE SEQUENCE products_seq START 1 INCREMENT BY 50;

The tables are created successfully, but when I try to save a new book during testing or while running the application, I encounter an error.

@SpringBootTest
public class BookRepositoryTest {

    @Autowired
    private BookRepository bookRepository;

    @Test
    public void testSaveBook() {
        Book book = new Book("bookname", 200, "none", "1234");
        bookRepository.save(book);
    }
}

Then I am getting this error : 

2024-05-20T15:05:28.582+02:00  WARN 9232 --- [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: 23502
2024-05-20T15:05:28.582+02:00 ERROR 9232 --- [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : ERROR: null value in column "name_product" of relation "books" violates not-null constraint
  Détail : Failing row contains (1, null, null, none, 1234).
[ERROR] Tests run: 1, Failures: 0, Errors: 1, Skipped: 0, Time elapsed: 7.290 s <<< FAILURE! -- in com.simplifyforme.tableinheritancepostgresql.repositories.BookRepositoryTest
[ERROR] com.simplifyforme.tableinheritancepostgresql.repositories.BookRepositoryTest.testSaveBook -- Time elapsed: 0.821 s <<< ERROR!
org.springframework.dao.DataIntegrityViolationException: 
could not execute statement [ERROR: null value in column "name_product" of relation "books" violates not-null constraint
  Détail : Failing row contains (1, null, null, none, 1234).] [insert into books (author,isbn,id) values (?,?,?)]; SQL [insert into books (author,isbn,id) values (?,?,?)]; constraint [name_product" of relation "books]

If I manually insert data directly into the database, the insertion is successful. However, when I attempt to insert data using my Spring Boot project, I encounter an error. What could be the reason behind this? ( full project source code https://github.com/simplifyforme/table-inheritance-postgresql )

Comment From: wilkinsona

I would compare the SQL that you're using when manually inserting data with the SQL that's generated by Hibernate, and work from there. The cause of the problem could be:

  1. Hibernate does not support Postgresql's table inheritance
  2. A bug in Hibernate
  3. A misconfiguration of Hibernate
  4. A bug in your entities
  5. A bug in Spring Data JPA

There's also a chance that the cause if a bug in Spring Boot, but that's unlikely as it isn't involved with SQL query generation.

Some Googling suggests that the first of the above is the most likely cause so I'm going to close this issue. If it turns out the Hibernate does in fact support Postgresql's inheritance and Spring Boot is causing it not to work, we can re-open this issue and take another look.