CODEWITHSUNDEEP
postgresqlhibernate
firstpostcommenter
firstpostcommenter

Reputation: 2911

Postgres insert record with Sequence generates error - org.postgresql.util.PSQLException: ERROR: relation "dual" does not exist

I am new to Postgres database.

I have a Java Entity class with the below column for ID:

@Entity
@Table(name = "THE_RULES")
public class TheRulesEntity {
/** The id. */
    @Column(name = "TEST_NO", precision = 8)
    @SequenceGenerator(name = "test_no_seq", sequenceName = "TEST_NO_SEQ")
    @GeneratedValue(generator = "test_no_seq", strategy = GenerationType.AUTO)
    @Id
    private Long id;

/** The test val. */
    @Column(name = "TEST_VAL", nullable = false, length = 3)
    private String testVal;

Code:

rulesRepository.saveAndFlush(theRulesEntity)

Table:

CREATE TABLE THE_RULES
(
    TEST_NO         INT NOT NULL,
    TEST_VAL        VARCHAR(3) NOT NULL
)

CREATE SEQUENCE "TEST_NO_SEQ" START WITH 1000 INCREMENT BY 1;

When I try to insert a new record into the postgres database from my application (the ID value is null in Java code during Debug mode), then I get the below error:

Caused by: org.postgresql.util.PSQLException: ERROR: relation "dual" does not exist

But If I insert the record manually into database table and then update the record from my application, then the record is updated successfully (Probably because the application uses the same ID value so no need to refer to the Sequence TEST_NO_SEQ value anymore)

Looks like the database is not able to access the sequence from dual table. Could anyone help me how to fix this?

Thanks.

Upvotes: 3

Views: 9374

Answers (1)

firstpostcommenter
firstpostcommenter

Reputation: 2911

Thanks to Joop and a_horse_with_no_name, the issue is resolved

  1. I have used Oracle driver which is wrong. I have updated my code to use Postgres driver
  2. I created the Sequence again in the database with same name but without the Quotes
  3. I used all capital-case letters in my Java entity class to refer to the sequence correctly

Upvotes: 2

Related Questions