Getting error when inserting a jsonb to a table using trigger functions

Question

I'm currently inserting this data {"curr": "testcurr","nm": "testnm"} to a view.

A trigger function is then run to insert into the original tables instead.

I'm getting an error column "curr" does not exist from the trigger function.

Here is the part of my code:

IF (tg_op = 'INSERT') THEN
                NEW."dat"= jsonb_build_object("curr",NEW."curr","symb",NEW."symb");
                NEW."ct"=now();
                INSERT INTO data.tbl
                    (
                    "nm",
                    "dat",
                    "ct",
                    )
                    VALUES
                    (
                    NEW."nm",
                    NEW."dat"
                    NEW."ct"
                    )returning "id" into NEW.id;
END

As you can see, the original table does not contain the 'curr' column, but my post data shouldn't have any problem right?

Vao Tsun · Accepted Answer · 2017-08-16 08:04:22Z

1

change:

jsonb_build_object("curr",NEW."curr","symb",NEW."symb")

to:

jsonb_build_object('curr',NEW."curr",'symb',NEW."symb")

https://www.postgresql.org/docs/current/static/functions-json.html look at the example - to get key "foo" you pass string 'foo'

double quotes are for identifiers - column, relation etc names - check https://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

answered Aug 16, 2017 at 8:04

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Joshua Rajandiran Over a year ago

thanks, I got another error now: cannot insert into column "curr" of view "tblview"

Vao Tsun Over a year ago

apparently you are trying to insert a row into a view, which can't be done by the view origin. that would be the different question and will require different code sample

Collectives™ on Stack Overflow

Getting error when inserting a jsonb to a table using trigger functions

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related