Apache NiFi put value to serial column

user_13070305 0
Viewed 141

I have a database table with following structure:

CREATE TABLE fact.cabinet_account (
    id serial NOT NULL,
    account_name text NULL,
    cabinet_id int4 NULL,
    CONSTRAINT cabinet_account_account_name_key UNIQUE (account_name),
    CONSTRAINT cabinet_account_pkey PRIMARY KEY (id),
    CONSTRAINT cabinet_account_cabinet_id_fkey FOREIGN KEY (cabinet_id) REFERENCES fact.cabinet(id)
);

And I have a JSON from InvokeHttp which I want to put to database:

{
  "login" : "some_maild@gmail.com",
  "priority_level" : 5,
  "is_archive" : false
}

I'm using QueryRecord with this script:

SELECT
19 AS cabinet_id, 
login AS account_name
FROM FLOWFILE

I'm trying to UPSERT in PutDatabaseRecord processor and got the error:

ERROR: value NULL at column "id"

How to put value for serial column with Apache NiFi?

UPDATE

My JSON looks like (before PutDatabase):

[ {
  "account_name" : "email1@maximagroup.ru",
  "priority_level" : 1000,
  "cabinet_id" : 19
}, {
  "account_name" : "email2@gmail.com",
  "priority_level" : 1,
  "cabinet_id" : 19
}, {
  "account_name" : "email3@umww.com",
  "priority_level" : 1000,
  "cabinet_id" : 19
} ]

PutDatabaseRecord looks like: enter image description here

apache-nifi
1 Answers

Try making the operation INSERT rather than UPSERT

An UPSERT needs to check whether the given id exists, to know if it should insert or update, which it can't do as no id is provided.

user_39559720
Related