Обсуждение: Postgres stored procedure errs while parsing JSONB object

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

...
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
    

Hello David,

You are my hero. I added quotes in two places as:
- AS event_times(id INTEGER, "startDate" DATE, "endDate" DATE, times JSONB)
- (eid, showing.times, showing."startDate", showing."endDate")

It worked. I no longer get an error message.

Thank you,

Igor Shmukler

On Wed, May 20, 2020 at 11:04 AM David G. Johnston
<david.g.johnston@gmail.com> wrote:
>
> On Wednesday, May 20, 2020, Igor Shmukler <igor.shmukler@gmail.com> wrote:
>>
>>
>> ...
>> 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
>>
>
>
> Try showing.”startDate” and showing.”endDate” ... (i.e., you need double quotes around the case-sensitive identifier)
>
> David J.
>