Question 168676 in PostgreSQL Question posted in
The official documentation can be found here.

Can a variable be concatenated with a string and inserted into a JSONB in PostgreSQL?

in my postgre SQL statement I need to concat an ID int with string and insert it as JSON value into JSONB column. My query is as follows:

DO
$$
  DECLARE  
    myID int = 555;
  BEGIN
    INSERT INTO mytable (status, url)
    VALUES(0,
           '{
             "alias": "XXX",
             "pageUrl": "https://example.com/?myID="' || myID || '}'::jsonb
          );
  END
$$;

But this query fails with error ERROR: syntax error at or near "INSERT". Any ideas how to fix it would be welcome. Thank you.

Tags:

2

Answers


  1. 0

    Here is a way to do it using json_build_object to prepare your json :

    DO
$$
  DECLARE  
    myID int = 555;
    myJson json;
  BEGIN
    myJson = json_build_object('alias', 'XXX', 'pageUrl', 'https://example.com/?myID=' || myID) ;
    
    INSERT INTO mytable (status, url)
    VALUES(0, myJson);
  END
$$;
    Login or Signup to reply.
  2. 0

    You can try this option

    Schema (PostgreSQL v15)

        CREATE TABLE mytable(
    Status INT,
    URL jsonb 
    );
    
    
    INSERT INTO mytable (status, url)
    SELECT 0,
           CONCAT('{
            "alias": "XXX",
            "pageUrl": "https://example.com/?myID=', 555 ,
           '"}')::jsonb

    Query #1

    SELECT * FROM mytable;
    status url
    0 {"alias":"XXX","pageUrl":"https://example.com/?myID=555"}

    View on DB Fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search