| From: | Igor Shmukler <igor(dot)shmukler(at)gmail(dot)com> | 
|---|---|
| To: | pgsql-sql(at)postgresql(dot)org | 
| Subject: | Postgres stored procedure errs while parsing JSONB object | 
| Date: | 2020-05-20 14:39:34 | 
| Message-ID: | CAA1SNA2LA0-t--jqxSCRFH5odwMtRGrBNMXsujyzp73snroBHg@mail.gmail.com | 
| Views: | Whole Thread | Raw Message | Download mbox | Resend email | 
| Thread: | |
| Lists: | pgsql-sql | 
I am working on a Postgres stored procedure (function), which includes
the below block:
...
FOR showing IN SELECT * FROM json_to_recordset(to_json(event_times))
AS show(id INTEGER,
    times JSONB, startDate DATE, endDate DATE) LOOP
  IF showing.id > 0 THEN
    UPDATE
      event_shows
    SET
      start_date = showing.startDate, end_date = showing.endDate,
times = showing.times
    WHERE
      event_id = eid AND
      id = showing.id;
  ELSE
    INSERT INTO
      event_shows (event_id, start_date, end_date, times)
    VALUES
      (eid, showing.startDate, showing.endDate, showing.times);
  END IF;
END LOOP;
...
The event_times object is passed to the stored procedure as JSONB.
The event_times value (for testing) is:
[{"times":[{"end":"13:00","start":"12:00"}],"endDate":"2020-05-19T19:45:47.121Z","startDate":"2020-05-19T19:45:47.121Z"},{"startDate":"2020-05-20T19:55:15.000Z","endDate":"2020-05-20T19:55:15.000Z","times":[{"start":"12:00","end":"13:00"}]}]
When I run the code, it errs at:
"SQL statement \"INSERT INTO\n          event_shows (event_id,
start_date, end_date, times)\n        VALUES\n          (eid,
showing.startDate, showing.endDate, showing.times)\"
The message is: null value in column \"start_date\" violates not-null
constraint. Seems like my JSONB object is not being parsed correctly.
If/when I replace showing.startDate and showing.endDate for constants,
the INSERT works fine. From Postgres log, I see what the database is
trying to insert. It is the below:
Failing row contains (29, 34, null, null, [{\"end\": \"13:00\",
\"start\": \"12:00\"}], 2020-05-20 14:22:40.08743, 2020-05-20
14:22:40.08743)
I am expecting [or rather hoping for] `(29, 34, 2020-05-20
14:22:40.08743,  2020-05-20 14:22:40.08743,  [{\"end\": \"13:00\",
\"start\": \"12:00\"}])`.
Why I unable to parse the JSONB event_times correctly? What should I
change in my code?
Thank you
| From | Date | Subject | |
|---|---|---|---|
| Next Message | David G. Johnston | 2020-05-20 15:04:47 | Re: Postgres stored procedure errs while parsing JSONB object | 
| Previous Message | David G. Johnston | 2020-05-11 23:51:06 | Re: Exporting modifications to a table as XML/JSON |