Cannot convert between String and JSONB in PostgreSQL

Given the following json object below,

["ABC","DEF","GHI"]

["ABC","DEF","GHI"]

I would like to update a record in my postgresql database of type jsonb using a postgresql script such as below:

UPDATE my_table SET 
  my_jsonb_column = 
    CASE
      WHEN $33::JSONB IS NULL OR $33::JSONB = '[]'::JSONB THEN COALESCE(my_jsonb_column, '[]'::JSONB)
      ELSE COALESCE(my_jsonb_column, '[]'::JSONB) || $33::JSONB
    END,
--- ... more code here
WHERE my_id = $1::INT RETURNING *

UPDATE my_table SET 
  my_jsonb_column = 
    CASE
      WHEN $33::JSONB IS NULL OR $33::JSONB = '[]'::JSONB THEN COALESCE(my_jsonb_column, '[]'::JSONB)
      ELSE COALESCE(my_jsonb_column, '[]'::JSONB) || $33::JSONB
    END,
--- ... more code here
WHERE my_id = $1::INT RETURNING *

I am unfortunately unable to get this to work as Windmill interprets my inputs as either an actual JSON object or a string if wrapped in quotes. i.e. If my input is "['ABC','DEF','GHI']", I get the following error message below

{
    "error": {
        "name": "ExecutionErr",
        "message": "execution error:\nError: error serializing parameter 32: cannot convert between the Rust type `alloc::string::String` and the Postgres type `jsonb`: cannot convert between the Rust type `alloc::string::String` and the Postgres type `jsonb` @pg_executor.rs:129:24"
    }
}

{
    "error": {
        "name": "ExecutionErr",
        "message": "execution error:\nError: error serializing parameter 32: cannot convert between the Rust type `alloc::string::String` and the Postgres type `jsonb`: cannot convert between the Rust type `alloc::string::String` and the Postgres type `jsonb` @pg_executor.rs:129:24"
    }
}

Otherwise, I get the following error:

{
    "error": {
        "name": "ExecutionErr",
        "message": "execution error:\nUnsupported type in query: Array [String(\"ABC\"), String(\"DEF\"), String(\"GHI\")] and signature \"jsonb\""
    }
}

{
    "error": {
        "name": "ExecutionErr",
        "message": "execution error:\nUnsupported type in query: Array [String(\"ABC\"), String(\"DEF\"), String(\"GHI\")] and signature \"jsonb\""
    }
}

0 Replies

No replies yetBe the first to reply to this messageJoin

Cannot convert between String and JSONB in PostgreSQL

Did you find this page helpful?