Member since
05-15-2023
1
Post
0
Kudos Received
0
Solutions
05-16-2023
12:52 AM
Hello, @mburgess I'm facing same issue with upsert to Postgresql. My software versions: NiFi version 1.19.1, Postgresql 13, JDBC driver: postgresql-42.5.1.jar When I started to make sample flow to represent this bug, I found another one similar 🙂 I created two tables with camelcase in table and colum names: MyTable and MyTable2 with similar columns list using queries: CREATE TABLE IF NOT EXISTS public."MyTable" ("Id" SERIAL PRIMARY KEY, "Col1" VARCHAR(1024), "createdAt" TIMESTAMP DEFAULT NOW()); CREATE TABLE IF NOT EXISTS public."MyTable2" ("Id" SERIAL PRIMARY KEY, "Col1" VARCHAR(1024), "createdAt" TIMESTAMP DEFAULT NOW()); First, I've generated JSON and tried to put some rows in table MyTable using ConvertJSONToSQL. Here is my ConvertJSONToSQL processor: And here I got a "new" bug. ConvertJSONToSQL quotes schema name along with table name. As result I get "public.MyTable" in constructed query, and Postgres rejects it. INSERT INTO "public.MyTable" ("Col1") VALUES (?) Okay, I've inserted some rows manually in MyTable and tried to clone data using ExecuteSQL processor connected directly to PutDatabaseRecord. ExecuteSql processor: PutDatabaseRecord processor: PutDatabaseRecord generates correct quoting on "public"."Mytable" but does not quote key column indentifier in ON CONFLINCT clause as topic starter pointed. INSERT INTO "public"."MyTable2"("Id", "Col1", "createdAt") VALUES (1, 'value1', '2023-05-16 08:10:56+03') ON CONFLICT (ID) DO UPDATE SET ("Id", "Col1", "createdAt") = (EXCLUDED."Id", EXCLUDED."Col1", EXCLUDED."createdAt") I hope this helps to figure out how to reproduce this bug. UPD: PutDatabaseRecord also changes key colum name to uppercase.
... View more