Why does PSQL complain about unknown relation: org.postgresql.util.PSQLException: ERROR: relation "benannte_person" does not exist?

Question

When I try to run a SpringBootTest using Testcontainers, I get a

org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
...
org.postgresql.util.PSQLException: ERROR: relation "benannte_person" does not exist

when I run a findAll() on the DAO and I don't know the reason, since in my whole code no 'benanntee_person' can be found (not even in comments).

SQL DDL:

CREATE TYPE Erklaerungstyp AS ENUM ('aaaa', 'bbbb', 'cccc', 'dddd');
CREATE TYPE Geschlecht AS ENUM ('D', 'F', 'M');

DROP TABLE IF EXISTS Anschrift;
CREATE TABLE Anschrift (
  a_id          SERIAL PRIMARY KEY,
  Zusatz        VARCHAR(255),
  Strasse       VARCHAR(30) NOT NULL,
  Hausnummer    VARCHAR(30) NOT NULL,
  plz           VARCHAR(5) NOT NULL,
  Ort           VARCHAR(80) NOT NULL,
  Bundesland    VARCHAR(20),
  Land          VARCHAR(20) NOT NULL,
  create_Date   DATE NOT NULL,
  modify_Date   DATE
);
INSERT INTO Anschrift VALUES (1, null, 'Musterstrasse', '13M', '12345', 'Berlin', 'Berlin', 'Deutschland', '2001-09-28');
INSERT INTO Anschrift VALUES (2, 'bei Müller', 'Musterweg', '1-3', '54321', 'Musterhausen', 'Muster-Hausen', 'Deutschland', '2002-03-11');

DROP TABLE IF EXISTS ErklaerendePerson;
CREATE TABLE ErklaerendePerson (
  ep_id                 SERIAL PRIMARY KEY,
  Geschlecht            Geschlecht,
  Vorname               VARCHAR(30) NOT NULL,
  Familienname          VARCHAR(30) NOT NULL,
  Geburtsname           VARCHAR(30) NOT NULL,
  Titel                 VARCHAR(10),
  Geburtsdatum          Date NOT NULL,
  Geburtsort            VARCHAR(30),
  Anschrift             INTEGER REFERENCES Anschrift(a_id),
  Email                 VARCHAR(80),
  Telefon               VARCHAR(20),
  create_Date           DATE  NOT NULL,
  modify_Date           DATE
);
INSERT INTO ErklaerendePerson VALUES (1, 'M', 'Max', 'Mustermann', 'Mustermann', 'Dipl.-Inf.', '01.01.1901', 'Berlin', 1, 'Max.Mustermann@max.de',
'0111 12 34 56 789', '2001-09-28');

DROP TABLE IF EXISTS BenanntePerson;
CREATE TABLE BenanntePerson (
  bp_id         SERIAL PRIMARY KEY,
  Geschlecht    Geschlecht,
  Vorname       VARCHAR(30) NOT NULL,
  Familienname  VARCHAR(30) NOT NULL,
  Geburtsdatum  Date NOT NULL,
  Geburtsort    VARCHAR(30),
  Anschrift     INTEGER REFERENCES Anschrift(a_id),
  Telefon       VARCHAR(20),
  Email         VARCHAR(80),
  create_Date   DATE NOT NULL,
  modify_Date   DATE
);
INSERT INTO BenanntePerson VALUES (1, 'F', 'Maxine', 'Musterpaar', '12.07.1971', 'Berlin', 1, '0111 444 55 66', 'Maxine.Musterpaar@xxx.de', '2001-09-28');

DROP TABLE IF EXISTS Erklaerung;
CREATE TABLE Erklaerung (
  ose_id            SERIAL PRIMARY KEY,
  ep_id             INTEGER REFERENCES ErklaerendePerson(ep_id),
  bp_id             INTEGER REFERENCES BenanntePerson(bp_id),
  Anmerkung         VARCHAR(120),
  Erklaerungstyp    Erklaerungstyp NOT NULL,
  create_Date       DATE NOT NULL,
  modify_Date       DATE
);
INSERT INTO Erklaerung VALUES (1, 1, 1, 'blablabla', 'VOLLUMFASSEND', '2020.02.20');
COMMIT;

Entity BenanntePerson:

@Entity
@Table(name="BenanntePerson")
@Data
public class BenanntePerson implements Serializable {
    @Id
//    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "bp_id")
    private Integer bpId;

    @Column(name = "Vorname", nullable = true)
    private String vorname;

    @Column(name = "Familienname", nullable = true)
    private String familienname;

    @Temporal(TemporalType.DATE)
    @Column(name = "Geburtsdatum", nullable = false)
    private Date geburtsdatum;

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

    @OneToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "a_id", nullable = false)
    private Anschrift anschrift;

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

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

    @OneToMany(mappedBy = "benanntePerson")
    private Set<Erklaerung> erklaerungen;

    @Column(name = "create_Date", insertable = true, updatable = false, nullable = false)
    private Timestamp createDate;

    @Column(name = "modify_Date", nullable = false)
    private Timestamp modifyDate;
}

The Erklaerung entity for the relationship:

@Entity
@Table(name="Erklaerung")
@Data
public class Erklaerung implements Serializable {
    @Id
    //    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "ose_id")
    private Integer oseId;

    @OneToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "ep_id", nullable = false)
    private ErklaerendePerson erklaerendePerson;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "bp_id", nullable = false)
    private BenanntePerson benanntePerson;

    @Enumerated(value = EnumType.STRING)
    @Column(name = "Erklaerungstyp", nullable = false)
    private Erklaerungstyp erklaerungstyp;

    @Column(name = "Anmerkung", nullable = true)
    private String anmerkung;

    @Column(name = "create_Date", insertable = true, updatable = false, nullable = false)
    private Timestamp createDate;

    @Column(name = "modify_Date", nullable = false)
    private Timestamp modifyDate;
}

The corresponding JPA DAO:

@Repository
public interface BenanntePersonJpaDao extends JpaRepository<BenanntePerson, Integer> {
    @NotNull
    List<BenanntePerson> findByFamilienname(@NotNull String familienname);
    @NotNull
    List<BenanntePerson> findByVorname(@NotNull String vorname);
}

Why does PSQL complain??

Answer 1

I know it has been a while but I was stucked in the very same problem and found the solution : You miss the pg option stringtype=unspecified. This btw should currently be used when running without test container, seek it in spring.datasource.url property.

At some point in your config you should currently be using PostgreSQLContainer.getJdbcUrl(). You should replace it with PostgreSQLContainer.constructUrlForConnection("?stringtype=unspecified").

Answer 2

It's simply because @JoinColumn(name = "a_id", nullable = false) doesn't match with the database table because there is no 'a_id' column. It is called 'Anschrift' in the table definition. Hence, renaming 'Anschrift' to 'a_id' in the table definition solves the problem.