[
https://issues.apache.org/jira/browse/NIFI-8043?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17268952#comment-17268952
]
Matt Burgess commented on NIFI-8043:
------------------------------------
The key column names don't get quoted in PutDatabaseRecord before passing them
to the database adapter to generate an UPSERT statement.
> PutDatabaseRecord Postgres Upsert On Conflict keys not quoted
> -------------------------------------------------------------
>
> Key: NIFI-8043
> URL: https://issues.apache.org/jira/browse/NIFI-8043
> Project: Apache NiFi
> Issue Type: Bug
> Components: Extensions
> Affects Versions: 1.12.0
> Reporter: Daniel Cheung
> Assignee: Matt Burgess
> Priority: Major
>
> h2. First attempt with camel case (fails when translate field name is true or
> false)
> Given that "Quote Column Identifiers" is enabled, one would expect the column
> names inside the conflict clause be quoted as well. However, they didn't seem
> to have been quoted, because my table's column names contain upper and
> lowercases and the flowfile is routed to the failure relationship of the
> PutDatabaseRecord processor with the DB error: {{ERROR: column "camelcase"
> does not exist}}.
> Whether setting "Update Keys" or not did not affect the outcome. If I
> understand, "Update Keys" would also affect the conflict clause, but it's
> also not quoted, and does not accept a string with manually quoted column
> names.
> SQL in question found in the DB error in the log, simplified from what I saw.
> {{INSERT INTO "public"."my_table"("camelCase", "txt")}}
> {{VALUES ("test", "test")}}
> {{ON CONFLICT (CAMELCASE)}}
> {{DO UPDATE SET ("camelCase", "txt") = (}}
> {{ EXCLUDED."camelCase",}}
> {{ EXCLUDED."txt"}}
> {{)}}
> h2. Second attempt with snake case (fails when translate field name is true)
> I changed my column names to {{_snake_case, txt}} and try upserting again and
> it still failed with this SQL in nifi-app.log:
> {{INSERT INTO "public"."my_table"("_snake_case", "txt")}}
> {{VALUES ("test", "test")}}
> {{ON CONFLICT (SNAKECASE)}}
> {{DO UPDATE SET ("}}{{_snake_case}}{{", "txt") = (}}
> {{ EXCLUDED."}}{{_snake_case}}{{",}}
> {{ EXCLUDED."txt"}}
> {{)}}
>
> h2. Current workaround
> I currently need to *disable translate field name* and set my table to *use
> snake case names as column names* to be able to use upsert
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
