ERROR: function to_jsonb(text, jsonb) does not exist - POSTGRESQL

Question

I have PostgreSQL v12 and this code works fine for me in this version. I have no idea if this is a version issue or not but I want to use POSTGRESQL v9.4

----------------------- Function Start v1.0 ------------------------------
drop function if exists aa_Dev.VDN();
CREATE OR REPLACE FUNCTION aa_dev.VDN()
  RETURNS VOID AS
$$
declare 

    tempp json;

begin

    DROP TABLE IF EXISTS aa_dev.sdg_vdn;

    CREATE TABLE IF NOT EXISTS aa_dev.sdg_vdn (
        VDN INT,
        skills INT[]
    );
    
    tempp := (select VDN_group from aa_dev.sdg_metadata);

    insert into aa_dev.sdg_vdn (vdn, skills)
    select (jsonb_populate_record(null::aa_dev.sdg_vdn, to_jsonb(t))).*
    from jsonb_each(tempp::jsonb) as t(vdn, skills);


end

$$
LANGUAGE plpgsql;

----------------- Function Ends ----------------------
select * from aa_dev.VDN();
select * from aa_dev.sdg_vdn;

So the error is

SQL Error [42883]: ERROR: function to_jsonb(text, jsonb) does not exist
  Hint: No function matches the given name and argument types. You might need to add explicit type casts.
  Where: PL/pgSQL function aa_dev.vdn() line 17 at SQL statement

How can I eliminate this error and run the function in postgresql 9.4. I have a restriction with this version.

Old postgresql did not support jsonb. There is nothing you can do except for upgrading your postgresql server — mvp
– mvp, Commented Feb 12, 2021 at 7:09
Also, Can you tell me in which version after 9.4 did PostgreSQL start supporting jsonb. @mvp — Chloe
– Chloe, Commented Feb 12, 2021 at 7:18
9.4 is no longer supported or maintained anyway. And if you upgrade, you should upgrade to the latest version. So just go for Postgres 13 — user330315
– user330315, Commented Feb 12, 2021 at 7:39

Bjarni Ragnarsson · Accepted Answer · 2021-02-12 07:40:14Z

2

There is some support to jsonb in version 9.4. https://www.postgresql.org/docs/9.4/functions-json.html

You can instead of to_jsonb(t) use to_json(t)::jsonb

answered Feb 12, 2021 at 7:40

Bjarni Ragnarsson

1,7811 gold badge8 silver badges8 bronze badges

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

Collectives™ on Stack Overflow

ERROR: function to_jsonb(text, jsonb) does not exist - POSTGRESQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related