CODEWITHSUNDEEP
postgresqljooq
user1858796
user1858796

Reputation: 319

Using postgres currval() in jooq

I am working with postgres database and Java. I am using Jooq to query my database. I need to make an insert in my table and get the primary_key/sequence generated by that insert. I know in simple postgres i can do it like this:

This is what my table looks like:

CREATE TABLE "myTable" (

    "id" SERIAL NOT NULL,
    "some_text" TEXT NOT NULL,
    PRIMARY KEY ("id")
);

This is the insert query:

INSERT INTO public.myTable(some_text)

VALUES ('myValue');

and than to get the latest sequence,

SELECT currval('myTableName_myColumnName_seq')

FROM myTable;

1) How can I use currval in JOOQ? Right now I am attempting something like this:

config.dsl().insertInto(Tables.myTable)
                .set(Tables.myTable.myText, inputText)
                .execute();

config.dsl().select.currval('myTableName_myColumnName_seq')
                .from myTable;

but off-course the last statement gives error.

Upvotes: 2

Views: 554

Answers (2)

Lukas Eder
Lukas Eder

Reputation: 221145

You can get the current value of a sequence through Sequence.currval(), which returns an expression for that purpose. E.g.

dsl().select(MYTABLENAME_MYCOLUMNNAME_SEQ.currval()).from(...)

But since this sequence is auto-generated from a SERIAL which produces sequence values automatically on your insertions, I completely agree with icuken's answer, you should use INSERT .. RETURNING instead.

Upvotes: 3

icuken
icuken

Reputation: 1356

The problem with your solution is that while you inserting a record to your table, there might be another process that gets value from a sequence and you'll get wrong value with your second query (SELECT currval).

PostgreSQL allows you to get some data back in INSERT statement with RETURNING clause:

INSERT INTO public.myTable(some_text)
VALUES ('myValue')
RETURNING id;

As jOOQ manual states, you should use returning and fetch in this case. I'm not sure about proper usage (I'm not familiar with jOOQ), something like following:

config.dsl().insertInto(Tables.myTable)
            .set(Tables.myTable.myText, inputText)
            .returning(Tables.myTable.id)
            .fetch();

Upvotes: 3

Related Questions