INSERT JSON into two tables and return serial ID

Question

I am trying to INSERT a new record into a Postgres 9.6 database, and take the ID of that newly inserted record and pass it to a different table for a foreign key reference. The data is in a JSON format, and I want to store the JSON blob in the row field and not parse it out. However, I am getting an error regarding a NULL value violating the NOT-NULL constraint of the PRIMARY KEY.

I am under the assumption that my code should create a new record in the my_data table (with the data_id auto generated and the data_fields filled in with my JSON object) but it appears that my assumption is incorrect.

What am I missing or not fully grasping? Below is the code to replicate my setup.

Tables:

CREATE TABLE my_data(
  data_id SERIAL PRIMARY KEY,
  data_fields JSONB
);

CREATE TABLE request_data(
    request_id SERIAL PRIMARY KEY,
    request_timestamp TIMESTAMP WITH TIME ZONE,
    data_id INTEGER REFERENCES my_data(data_id),
    record_count INTEGER,
    completedStatus boolean
);

Function:

CREATE OR REPLACE FUNCTION updateData (
dataFields JSONB,
requestTimestamp TIMESTAMP WITH TIME ZONE,
recordCount INTEGER,
completedStatus boolean,
OUT new_record_id INTEGER
)
RETURNS integer AS $$
BEGIN
INSERT INTO my_data SELECT * FROM jsonb_populate_recordset(NULL::my_data, $1::jsonb) RETURNING data_id INTO new_record_id;
INSERT INTO request_data (request_timestamp, data_id, record_count, completed_status) VALUES($2, new_record_id, $3, $4);
END;
$$ LANGUAGE plpgsql;

Call:

SELECT updateData (
'[{"identity": "personA", "timestamp": "2017-09-15T20: 03: 12+00: 00"},
{"identity": "personB", "timestamp": "2017-09-15T20: 03: 12+00: 00"},
{"identity": "personC", "timestamp": "2017-09-15T20: 03: 12+00: 00"}]'
 '2017-09-29', 
 3, 
 true)

Error:

ERROR: null value in column "data_id" violates not-null constraint
SQL state: 23502
Detail: Failing row contains (null, null).

Expected outcome for my_data table:

data_id     | data_fields
------------+------------------------------
    1       | [{"identity": "personA", "timestamp": "2017-09-15T20: 03: 12+00: 00"},{"identity": "personB", "timestamp": "2017-09-15T20: 03: 12+00: 00"},{"identity": "personC", "timestamp": "2017-09-15T20: 03: 12+00: 00"}]

Expected outcome for request_data table:

request_id  |    request_timestamp     | data_id | record_count | completed_status
------------+--------------------------+---------+--------------+-------------------
    1       |   2017-09-29             |  1      |  3           |   true

Why are you using this jsonb_populate_recordset ? Try this only insert into my_data values ($1::jsonb) as you are using serial for column data_id. — Fahad Anjum, Oct 05 '17 at 17:12

score 0 · Answer 1 · answered Oct 05 '17 at 18:15

0

@Fahad Anjum pointed out the better way to insert the json. I changed my INSERT statement to INSERT INTO my_data (data_fields) values($1) RETURNING data_id INTO new_record_id; and it gives me the results I need.

answered Oct 05 '17 at 18:15

unseen_damage

1,346
1
14
32

score 0 · Accepted Answer · answered Oct 09 '17 at 17:43

@Fahad pointed out the primary logic error.

But you do not need the cast in ~~($1::jsonb)~~ since the input parameter is typed jsonb already.

And I would recommend a single query with a data-modifying CTE combining both inserts in a simple SQL function - instead of two INSERT queries with an assignment in between in a PL/pgSQL function. Shorter, less prone to errors, faster:

CREATE OR REPLACE FUNCTION update_data(data_fields jsonb
                                     , request_timestamp timestamptz
                                     , recordcount integer
                                     , completed_status boolean)
  RETURNS integer AS
$func$
   WITH ins1 AS (
      INSERT INTO my_data(data_fields)
      VALUES ($1)  -- no need to cast
      RETURNING data_id
      )
   INSERT INTO request_data(request_timestamp, data_id, record_count, completed_status)
   SELECT $2, i.data_id, $3, $4
   FROM   ins1 i
   RETURNING data_id
$func$  LANGUAGE sql;

I also unified to lower case spelling. You had a mix of naming conventions resulting in a mismatch between completedStatus and completed_status. My standing advice is to avoid mixed case spelling for identifiers in Postgres.

INSERT JSON into two tables and return serial ID

2 Answers2