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

Question

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.

ERROR: relation "dual" does not exist Postgres doesn't have a dual pseudo table. Maybe you are using an oracle driver? — joop
– joop, Commented Dec 29, 2016 at 16:14
Probably unrelated, but: avoid quoted identifiers. They are much more trouble then they are worth it — user330315
– user330315, Commented Dec 29, 2016 at 16:14
@a_horse_with_no_name: I tried with both small-case and capital-case in Java entity class but I get the same error. How to avoid quoted identifiers? — firstpostcommenter
– firstpostcommenter, Commented Dec 29, 2016 at 16:24
Never use double quotes in SQL, then you'll never have a problem. But the error message relates to the use of the dual table which does not exists in Postgres. You will probably hit the problem that "TEST_NO_SEQ" is a different name then TEST_NO_SEQ once your SQL doesn't use the dual table. See the manual for details on quoted identifiers: postgresql.org/docs/current/static/… — user330315
– user330315, Commented Dec 29, 2016 at 16:26

firstpostcommenter · Accepted Answer · 2016-12-29 19:47:52Z

2

Thanks to Joop and a_horse_with_no_name, the issue is resolved

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

edited Dec 29, 2016 at 19:47

user330315

answered Dec 29, 2016 at 16:41

firstpostcommenter

2,9556 gold badges43 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

firstpostcommenter Over a year ago

incase you are using jdbcTemplate stuff then it need to be changed.... for example do not use BlobWriter to save and retrieve stuff because postgres uses bytea datatype(bytea is byte[] array) ... and for sequence we need to use PostgresSequenceMaxValueIncrementer instead of OracleSequenceMaxValueIncrementer, etc.

firstpostcommenter Over a year ago

enterprisedb.com/edb-docs/d/jdbc-connector/user-guides/…

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related