[
https://issues.apache.org/jira/browse/NIFI-8043?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Daniel Cheung updated NIFI-8043:
--------------------------------
Summary: PutDatabaseRecord Postgres Upsert On Conflict keys not quoted with
underscores missing if translate field name is true (was: PutDatabaseRecord
Postgres Upsert On Conflict keys not quoted with underscores missing)
> PutDatabaseRecord Postgres Upsert On Conflict keys not quoted with
> underscores missing if translate field name is true
> ----------------------------------------------------------------------------------------------------------------------
>
> 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
> Priority: Major
>
> h2. First Attempt with camel case
> 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
> 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"}}
> {{)}}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
