Jooq: Add support for PostgreSQL's `EXCLUDED` special table.

[testark]

PostgreSQL introduced a "special" EXCLUDED table to be used on INSERT INTO ... ON CONFLICT (...) DO UPDATE ... statements. It would be great if jOOQ could provide a "placeholder" for this special table.

---

See also:

• http://www.postgresql.org/docs/9.5/static/sql-insert.html

[lukaseder]

Interesting feature, thanks for the hint. Reminds me of the INSERTED, UPDATED, DELETED "magic" tables in Oracle triggers or in SQL Server OUTPUT clauses.

The easiest way to get this already today might be by using simple aliasing, where you alias the table inserted into as TABLE.as("excluded")

[sharatvisweswara]

Hi Lukas, thanks for all your great work on Jooq. Just a note for anyone else who ends up here that the TABLE.as("excluded") hack does not work unless you also use Settings.withRenderNameStyle(RenderNameStyle.AS_IS) when creating the DSLContext. By default, quoting the EXCLUDED keyword makes PostgresQL look for a corresponding FROM clause and fail the insert.

[lukaseder]

Thanks for your comment, @sharatvisweswara. Are you sure, though? The following code works:

CREATE TABLE x (i INT PRIMARY KEY, j INT);

INSERT INTO x VALUES (1, 1)
ON CONFLICT (i)
DO UPDATE SET j = "excluded".j
RETURNING *;

The pseudo-table is really called "excluded" (in lower case letters), as I've described in my example. It is not a keyword, and in order to make my suggestion work, you indeed need wo write the alias in lower case or disable quoting as you suggested.

Note also: jOOQ 3.10 will introduce new ways to create a org.jooq.Name through DSL.unquotedName(), which will always be unquoted, regardless of Settings.renderNameStyle

[sharatvisweswara]

I was able to consistently reproduce using this @lukaseder (and you are right that the keyword needs to be lower case, because that change fixes it):

context.insertInto(TEST_EXCLUDED)
    .columns(TEST_EXCLUDED.I, TEST_EXCLUDED.J)
    .values(1, 2)
    .onConflict(TEST_EXCLUDED.getPrimaryKey().getFields())
    .doUpdate()
    .set(TEST_EXCLUDED.J, TEST_EXCLUDED.as("EXCLUDED").J)
    .execute();

Throws:

org.jooq.exception.DataAccessException: SQL [insert into "ads"."test_excluded" ("i", "j") values (?, ?) on conflict ("i") do update set "j" = "EXCLUDED"."j"]; ERROR: missing FROM-clause entry for table "EXCLUDED"
  Position: 100

    at org.jooq_3.9.1.POSTGRES.debug(Unknown Source)
    at org.jooq.impl.Tools.translate(Tools.java:1983)
    at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:676)
    at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:363)
    at org.jooq.impl.AbstractDelegatingQuery.execute(AbstractDelegatingQuery.java:135)
    at com.evocalize.rickshaw.db.support.OnConflictUpdateTest.test(OnConflictUpdateTest.java:31)
        ... 22 more
Caused by: org.postgresql.util.PSQLException: ERROR: missing FROM-clause entry for table "EXCLUDED"
  Position: 100
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2476)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2189)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:300)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:428)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:354)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:169)
    at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:158)
    at org.jooq.tools.jdbc.DefaultPreparedStatement.execute(DefaultPreparedStatement.java:194)
    at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:431)
    at org.jooq.impl.AbstractDMLQuery.execute(AbstractDMLQuery.java:335)
    at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:349)
    ... 24 more

[xathien]

Since this is still floating around Long-term goals and ideas... would it be possible to make .set(someRecord).onDuplicateKeyUpdate().execute() automatically use the excluded table? Right now it generates syntactically valid SQL, but it uses the default table name, which causes the query to update nothing. 🙂

[lukaseder]

@xathien Would you mind creating a new issue for that with some details / examples? I think your issue isn't strictly related to this feature request.

[xathien]

You're right, it's not strictly related--I'll file a new issue.

[knutwannheden]

This is also relevant for SQLite which now also supports the INSERT ON CONFLICT clause of Postgres.

[lukaseder]

Good point, @knutwannheden. I've been working on an emulation of RETURNING via SQL Server's OUTPUT clause, which has syntactically similar pseudo tables called DELETED and INSERTED (#4498). To support such syntactic constructs, a new internal (or public?) API will be needed that takes a set of SelectFieldOrAsterisk and re-qualifies them under a new table name.

So, this new feature can be useful in this EXCLUDED case as well.

[abonander]

Shouldn't very basic support for Postgres simply consist of generating an extra EXCLUDED field on table classes that's just initialized with new MyTable(DSL.name("EXCLUDED"), null)?

[lukaseder]

@abonander Thanks for your suggestion. I have some reservations here.

1. I would personally find that confusing, as you'd be writing MY_TABLE.EXCLUDED.COLUMN_NAME when using jOOQ, and that looks like a nested record.
2. (less important), there might be conflicts with actual columns called EXCLUDED, which we can handle, but they would be annoying.
3. The feature is relatively arcane, I don't think it deserves such a big presence on every generated table. You could obviously do this in your own version of the code generator, by implementing JavaGenerator::generateTableClassFooter, though.

[lukaseder]

Notice, we have an internal utility called Tools::qualify, which does exactly what's needed here, and it would work for all RDBMS, including also SQL Server, which has similar tables, or future trigger support, when we want to dereference columns from a trigger pseudo table like :NEW or :OLD. This utility will also work with non-generated code, so it will be a more thorough solution.

By the way, there's already a generated rename(String) method on all generated tables, that produces the wanted table reference...

[dsvensson]

It would be nice if rename(String) was part of some public interface rather than declared on TableImpl (Table?) so that generic code could perform the tbl.rename("EXCLUDED") part.

[lukaseder]

@dsvensson Yes indeed, that would be nice: https://github.com/jOOQ/jOOQ/issues/5242