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

Inserting JSON data into Postgresql from Jenkins pipeline

I’m trying to insert JSON data into my database from Jenkins pipeline, unfortunately I have problem with proper format.
My query looks like that:

def insertQuery = """
                  INSERT INTO public.metrics(pr, info) VALUES ('KP-1111', '{"key": "value"}');
                  """

and later:

  sh """
     PGPASSWORD="${dbPassword}" psql -h ${dbHost} -p ${dbPort} -d ${dbName} -U ${dbUser} -c "${insertQuery}" -w
     """

Error I’m getting:

ERROR:  invalid input syntax for type json
LINE 2: ... INTO public.metrics(pr, info) VALUES ('KP-1111', '{key: val...
                                                             ^
DETAIL:  Token "key" is invalid.
CONTEXT:  JSON data, line 1: {key...

I tried to escape signs, move JSON into variable instead of hardcode in query. Is there any other option I can insert this data?

Tags:

2

Answers


  1. 0

    Your query’s JSON data is in the wrong format for PostgreSQL’s JSON type, which is why you’re seeing this error. JSON data must have keys that are double-quoted and enclosed in single quotes for PostgreSQL to accept it. Here’s how to change your query so that JSON data is successfully inserted:

    def jsonData = '{"key": "value"}' // Define your JSON data here

def insertQuery = """
                  INSERT INTO public.metrics(pr, info) VALUES ('KP-1111', '${jsonData}'::json);
                  """

sh """
   PGPASSWORD="${dbPassword}" psql -h ${dbHost} -p ${dbPort} -d ${dbName} -U ${dbUser} -c "${insertQuery}" -w

   """

    Hope it works

    Login or Signup to reply.
  2. 0

    You have quietly mismatched quotes. The query string has key and value surrounded in double quotes, then the sh again surrounds the entire query in double quotes, so your -c "${insertQuery}" ends up evaluated to:

    -c "INSERT INTO public.metrics(pr, info) VALUES ('KP-1111', '{"key": "value"}');"

    which terminates the string at the double quote before key, concatenates it with key, reopens quoted string and continues up until value and does the same, cutting it up like this:

    "INSERT INTO public.metrics(pr, info) VALUES ('KP-1111', '{"
key
": "
value
"}');"

    So you end up losing those double quotes entirely. Which is why you can see the error message without them around key and value:

    "INSERT INTO public.metrics(pr, info) VALUES ('KP-1111', '{key: value}');"

    You can escape the quotes with to avoid them terminating the string in your sh:

    def insertQuery = """
                  INSERT INTO public.metrics(pr, info) VALUES ('KP-1111', '{\"key\": \"value\"}');
                  """
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search