CODEWITHSUNDEEP
javasqlpostgresqljooq
Josh Padnick
Josh Padnick

Reputation: 3278

Inserting a New UpdatableRecord and Receiving Error on Duplicate Primary Keys

I'm trying to insert a new record using UpdatableRecords in jOOQ 3.4.2. The pattern is extremely concise and pleasant to use, except that the INSERT reads null values as no value and ignores default values or a generated index. How can I use the UpdatableRecord to do an insert that respects default values and generated indexes?

Here's my table:

CREATE TABLE aragorn_sys.org_person (
    org_person_id SERIAL  NOT NULL,
    first_name CHARACTER VARYING(128)  NOT NULL,
    last_name CHARACTER VARYING(128)  NOT NULL,
    created_time TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp  NOT NULL,
    created_by_user_id INTEGER,
    last_modified_time TIMESTAMP WITH TIME ZONE,
    last_modified_by_user_id INTEGER,
    org_id INTEGER  NOT NULL,
    CONSTRAINT PK_org_person PRIMARY KEY (org_person_id)
);

Note my primary key and default values. Now here's my jOOQ code:

// orgPerson represents a POJO filled with my values to be inserted and null for everything else
// Note that orgPerson.orgPersonId is null
OrgPersonRecord orgPersonRecord = create.newRecord( ORG_PERSON, orgPerson );
Integer orgPersonId = create.executeInsert( orgPersonRecord );

But when I run this, I get the error null value in column "org_person_id" violates not-null constraint.

I noticed the jOOQ docs say that calling newRecord automatically sets all the internal "changed" flags to true on the UpdatableRecord. So then I tried this:

// orgPerson represents a POJO filled with my values to be inserted and null for everything else
// Note that orgPerson.orgPersonId is null
OrgPersonRecord orgPersonRecord = create.newRecord( ORG_PERSON, orgPerson );
orgPersonRecord.changed( ORG_PERSON.ORG_PERSON_ID, false );
orgPersonRecord.changed( ORG_PERSON.CREATED_TIME, false );
orgPersonRecord.insert()
Integer orgPersonId = orgPersonRecord.getOrgPersonId();

But that gives me the error ERROR: duplicate key value violates unique constraint "pk_org_person". And when I do this repeatedly, the values seem to keep increasing by 1. This doesn't really make sense to me, but my greater question is: Is there a good way I can do an INSERT based on my object values, or better yet, simply include only the non-null columns?

I saw JOOQ ignoring database columns with default values, but that doesn't seem to resolve this. Any recommendations on the most concise way to handle this?

By the way, jOOQ has been fantastic to work with so far. Lukas, thank you for this awesome tool!

UPDATE #1:

The "not null issue" is addressed by Lukas's answer below, and that's an easy fix.

For the duplicate primary keys, I am definitely not confusing INSERT with UPDATE. When I run the above code (slight update since original post), jOOQ seems to arbitrarily pick a "starting" primary key value for OrgPersonId. For example, when I first load up my environment, jOOQ might start with "11" for OrgPersonId.

Then, when I do an INSERT, jOOQ will attempt to supply a value of "11" for OrgPersonId, I'll get the ERROR: duplicate key value and the INSERT will fail. If I then repeat the INSERT, jOOQ uses "12", then "13". It succeeds or fails based on whether that ID is available, but it's not "starting" with the right ID.

The manual (http://www.jooq.org/doc/3.4/manual/sql-execution/crud-with-updatablerecords/identity-values/) says that If you're using jOOQ's code generator, the above table will generate a org.jooq.UpdatableRecord with an IDENTITY column. This information is used by jOOQ internally, to update IDs after calling store().

UPDATE #2:

Ok, I just tried the generated query directly in Postgres and it fails there, too, with the same issue. So, clearly this is a Postgres issue and not a jOOQ issue. I'll post the final resolution on that when I find it in case anyone else runs into this.

UPDATE #3:

Issue has been resolved. We use FlywayDB (another awesome tool) to automate our database schema migration, and we had a bunch of INSERT statements in our Flyway scripts that manually INSERTED the id number. This was convenient because we wanted to create a bunch of dummy data and wanted to guarantee the right foreign key relationships.

But manually specifying the primary key increment does not advance the Postgres sequence! Hence, we had to cycle through the Postgres sequence before (correctly operating) jOOQ would get the right sequence value.

Solution is to remove all our manual inserts of the primary keys in our demo data migration scripts.

Upvotes: 3

Views: 2380

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 221135

violates not-null constraint

The first part that you're describing is a flaw (#3582), which is related to a previous issue (#2700), which enforced storing null values loaded from POJOs into jOOQ Records for database columns that are NOT NULL. The fix will be in jOOQ 3.5.0, 3.4.3, 3.3.4, and 3.2.7

duplicate key value violates unique constraint "pk_org_person"

The second part probably is caused by the fact that you are really loading an existing record and then calling executeInsert() on it (observe the INSERT, which will always execute an INSERT statement). You might want to call executeUpdate(), instead

Upvotes: 2

Related Questions