[
https://issues.apache.org/jira/browse/NIFI-8043?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17269085#comment-17269085
]
ASF subversion and git services commented on NIFI-8043:
-------------------------------------------------------
Commit fb2a8b5820b816d7afddb5141cd064275267f796 in nifi's branch
refs/heads/main from Matt Burgess
[ https://gitbox.apache.org/repos/asf?p=nifi.git;h=fb2a8b5 ]
NIFI-8043: Quote update key column names in PutDatabaseRecord
Signed-off-by: Pierre Villard <[email protected]>
This closes #4772.
> 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
> Reporter: Daniel Cheung
> Assignee: Matt Burgess
> Priority: Major
> Time Spent: 10m
> Remaining Estimate: 0h
>
> 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)
