[NIFI-8043] PutDatabaseRecord Postgres Upsert On Conflict keys not quoted

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.13.0
• Component/s: Extensions
• Assignee: Matt Burgess
• Reporter: Daniel Cheung
• Created: 24/Nov/20 19:42
• Updated: 16/May/23 09:07
• Resolved: 21/Jan/21 07:18

Description

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"
)

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"
)

 

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

Issue Links

• is related to: NIFI-6934 Support Postgres 9.5+ Upsert in the standard PutDatabaseRecord processor
• links to: GitHub Pull Request #4772