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