String Manipulation throws error while Inserting into PostgreSql Jsonb Column

Question

I have the following code, to insert data into my table res(ID bigserial, Results jsonb not null). I want to insert data so that 'Display' column always has the 'i' appended to it, so that every row has a different value for 'Display' Column.

DO $$
declare cnt bigint;
BEGIN
FOR i IN 1..2 LOOP
    INSERT INTO res (Results)
        VALUES ('{"cid":"CID1","Display":"User One'|| i || '","FName":"Userfff","LName":"One"}'); 
    INSERT INTO res (Results)
        VALUES ('{"cid":"CID1","Display":"User One'|| i || '","FName":"Userfff","LName":"One"}');
    INSERT INTO res (Results)
        VALUES ('{"cid":"CID1","Display":"User One'|| i || '","FName":"Userfff","LName":"One"}');
END LOOP;
END;
$$
LANGUAGE plpgsql;

However, when I run this code, I get the following error:

ERROR:  column "results" is of type jsonb but expression is of type text
LINE 2:             VALUES ('{"cid":"CID1","Display":"User One'|| i ...
                        ^
HINT:  You will need to rewrite or cast the expression.

How should I modify my query so that the query runs successfully?

Your loop will insert 6 rows with 3 rows having the same value for Display is that really what you want? — user330315
– user330315, Commented Feb 2, 2017 at 7:36

user330315 · Accepted Answer · 2017-02-02 07:39:31Z

1

No need for a loop or even PL/pgSQL.

You can generate any number of rows using generate_series()

To put a value into a string I prefer to use format() as that makes dealing with strings a lot easier:

insert into res(results)
select format('{"cid":"CID1","Display":"User One %s","FName":"Userfff","LName":"One"}', i::text)::jsonb
from generate_series(1,5) g(i);

The second parameter to the format() function will be put where the %s in the first parameter.

The above inserts 5 rows each with a different value for display. This is what you stated in your question. Your sample code however inserts a total of 6 rows where 3 rows have the same value for display.

answered Feb 2, 2017 at 7:39

user330315

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

1 Comment

puneet Over a year ago

Thanks, it does the job

Collectives™ on Stack Overflow

String Manipulation throws error while Inserting into PostgreSql Jsonb Column

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