You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@nifi.apache.org by "Daniel Cheung (Jira)" <ji...@apache.org> on 2020/11/24 19:47:00 UTC
[jira] [Updated] (NIFI-8043) PutDatabaseRecord Postgres Upsert On
Conflict keys not quoted
[ https://issues.apache.org/jira/browse/NIFI-8043?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Daniel Cheung updated NIFI-8043:
--------------------------------
Description:
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, 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"}}
{{)}}
was:
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, 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"}}
{{)}}
> 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
> Priority: Major
>
> 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, 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"}}
> {{)}}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)