ERROR: cannot cast type integer to json in Postgresql

Question

When I am trying to insert the record in tableA, i am getting the below error:

QUERY:  insert into schemaname.tableB(col1, col2) values(....)
CONTEXT:  PL/pgSQL function schemaname.funcA() line 5 at SQL statement
********** Error **********
ERROR: cannot cast type integer to json
SQL state: 42846
Context: PL/pgSQL function schemaname.funcA() line 5 at SQL statement

I have created following trigger which function when record gets committed:

CREATE CONSTRAINT TRIGGER trg_name
  AFTER INSERT
  ON schemaname.tableA
  DEFERRABLE INITIALLY DEFERRED
  FOR EACH ROW
  EXECUTE PROCEDURE schemaname.funcA();

In the below trigger function col1 is of integer type and col2 is of json type.

CREATE OR REPLACE FUNCTION schemaname.funcA()
  RETURNS trigger AS
$BODY$

BEGIN

insert into schemaname.tableB(col1, col2) values(NEW.val1, json_build_object("abc", NEW.col2val1::json, "def", NEW.col2val2::json, "ghi", NEW.col2val3::json));

RETURN NEW;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Could you please help me what could be the cause of this error?

Laurenz Albe · Accepted Answer · 2018-01-18 07:35:10Z

1

There is no cast from integer to json.

But since json_build_object accepts arguments of any type, you can solve the problem by removing the ::json casts in your trigger function.

By the way, using "abc" as a string constant is wrong – probably an error introduced while trying to obfuscate your code. You have to write '"abc"'.

edited Jan 18, 2018 at 7:35

answered Jan 17, 2018 at 14:22

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

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

10 Comments

Nik Over a year ago

i removed ::json and then its showing the error near "abc" as ERROR: column "abc" does not exist. I want to insert the data as key value, so i have used "abc" as a key.

Laurenz Albe Over a year ago

Right, like I told you. You must put a string in single quotes.

Nik Over a year ago

I converted it into single quote. Now the error is ERROR: function json_build_object(....) does not exist HINT: No function matches the given name and argument types. You might need to add explicit type casts.

Laurenz Albe Over a year ago

How does the function call look now?

user330315 Over a year ago

@Nik: which Postgres version (select version()) are you using?

|

Nik · Accepted Answer · 2018-03-27 13:12:14Z

0

CREATE OR REPLACE FUNCTION schemaname.funcA()   RETURNS trigger AS $BODY$

    BEGIN

    insert into schemaname.tableB(col1, col2) values(NEW.col1, CAST('{"nameA" : ' ||'"'||NEW.nameA||'", ' || '"nameB" : '||'"'||NEW.nameB||'", ' ||'"nameC" : '||'"'||NEW.nameC||'", ' || '"nameD" : '||'"'||NEW.nameD||'"}' as json));

    RETURN NEW;

    END; $BODY$   LANGUAGE plpgsql VOLATILE   COST 100;

answered Mar 27, 2018 at 13:12

Nik

4611 gold badge9 silver badges23 bronze badges

Collectives™ on Stack Overflow

ERROR: cannot cast type integer to json in Postgresql

2 Answers 2

10 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related