Search code examples
postgresqljsonb

String Manipulation throws error while Inserting into PostgreSql Jsonb Column

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?

Solution

  • 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.