How to use variable name in JSONB insert in postgres

Question

I am trying to write a simple plsql script to insert data in a table, which has jsonb as one of the columns

Example:

do $do$ declare
tel varchar:= '8000012000';
begin for i in 1..10 loop insert
    into
        t_tbl_sample(
            lob,
            prop_name,
            prop_value,
            prop_details
        )
    values(
        'TN_ADD' || i,'ABC','XYZ',
        '[{"specCode": {"name": "Telephone Number", "text": "TEL_NUM"}, "specValue": {"code": null, "text": tel}}]'
    );
end loop;
end $do$;

But executing this gives an error:

ERROR:  invalid input syntax for type json
LINE 11:   '[{"specCode": {"name": "Telephone Number", "text": "TEL_NUM...
           ^
DETAIL:  Token "tel" is invalid.

How can i write the pgSql to use a variable inside the JSONB element? Sounds like a simple requirement , but I could not find the syntax or reference anywhere

Jeremy · Accepted Answer · 2019-07-15 02:11:01Z

5

Probably the easiest way is to just use string concatenation:

( '[{"specCode": {"name": "Telephone Number", "text": "TEL_NUM"}, "specValue": {"code": null, "text":' || tel || '}}]')::json

That will work if tel is always an integer. Otherwise, you will need double-quotes:

( '[{"specCode": {"name": "Telephone Number", "text": "TEL_NUM"}, "specValue": {"code": null, "text":"' || tel || '"}}]')::json

Somewhat unrelated, but this really doesn't require pl/pgsql and definitely doesn't require a loop. You can use generate_series to grab each element of tel:

WITH data AS (
  SELECT i, substr('8000012000', i, 1) as tel
  FROM generate_series(1, length('8000012000')) as g(i)
)
INSERT INTO 
        t_tbl_sample(
            lob,
            prop_name,
            prop_value,
            prop_details
        )
SELECT  'TN_ADD' || i,
        'ABC',
        'XYZ',
        ('[{"specCode": {"name": "Telephone Number", "text": "TEL_NUM"}, "specValue": {"code": null, "text":"' ||  tel || '"}}]')::json
FROM data
returning *;

answered Jul 15, 2019 at 2:11

Jeremy

6,77322 silver badges21 bronze badges

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

1 Comment

user1933888 Over a year ago

Thanks for your answer, it gave me the right direction!

Collectives™ on Stack Overflow

How to use variable name in JSONB insert in postgres

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related