WindmillWWindmill
Powered by
ShenanigansS
Windmill•9mo ago
Shenanigans

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
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']"
"['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\""
    }
}
WindmillJoin
3,362Members
Resources

Similar Threads

Was this page helpful?
Recent Announcements
Recent Announcements
henri-c

Weekly kenote to tell you about our latest updates https://discord.com/channels/930051556043276338/1278977038430240813 https://youtube.com/live/2dGd9TdT8xs?feature=share

henri-c · 4d ago

Pyra

### HTTP tracing (EE) Capture HTTP requests made by job scripts as observability spans Features: - View HTTP request traces (method, URL, status, timing) in the job details UI - Auto-instrumentation for Native TypeScript, MITM proxy for other languages - Integrates with external OpenTelemetry collectors changelog: https://www.windmill.dev/changelog/http-tracing docs: https://www.windmill.dev/docs/advanced/instance_settings#http-tracing Additionally jobs memory metrics are now fully OSS!

Pyra · 2w ago

henri-c

First keynote of the year here https://discord.com/channels/930051556043276338/1278977038430240813 🙂

henri-c · 4w ago

Similar Threads

Cannot navigate between Nodes in Decision Tree app component
ShenanigansSShenanigans / help
8mo ago
After upgrading windmill, we are now seeing this error "string too long to represent as jsonb string
RicoRRico / help
2y ago
Database Manager JSONB Error
fooosieeeFfooosieee / help
2w ago
How to insert JSONB parameter in Postgres Action
amgfredAamgfred / help
2y ago