"Unsupported JSONB handling in psqlpy: Error with version 123"

[akanksha314e]

When attempting to insert or retrieve data into/from a PostgreSQL JSONB column, I encounter an unsupported JSONB version 123 error. It seems psqlpy is not handling JSONB serialization or deserialization correctly.
Python version: 3.11.9
PostgreSQL version: 16.3
psqlpy version: 0.8.7

[chandr-andr]

Hello, @akanksha314e!

Could you please send me a snippet of the code so I can reproduce the problem?

I've checked JSONB now and It works.

[chandr-andr]

Please send Python with PSQLPy code and SQL code to create a table with a JSONB column

[akanksha314e]

Hi, @chandr-andr
CREATE TABLE entitydefinition (
id uuid DEFAULT uuid_generate_v1mc() PRIMARY KEY,
entityname text,
field text,
issystem boolean,
displayname text NOT NULL,
sortorder integer,
definition jsonb,
CONSTRAINT entitydefinition_uniq UNIQUE (entityname, field)
);
query = """
INSERT INTO entitydefinition
(
entityname,
displayname,
field,
issystem,
sortorder,
definition
)
VALUES
(
$1, $2, $3, $4, $5, $6
)
RETURNING id;
"""

Parameters

parameters = [
'test', # entityname
'pokemon', # displayname
'string', # field
False, # issystem
0, # sortorder
'{"uifieldtype": "Multiple selection dropdown", "listofvaluestype": "pokemon", "required": false, "useforsearch": false, "description": "test", "defaultvalues": ["f42f52e4-95c8-11ef-b220-8b2904a42783"], "value": "test1"}' # definition
]

[chandr-andr]

Thank you very much!
There is a problem:

'{"uifieldtype": "Multiple selection dropdown", "listofvaluestype": "pokemon", "required": false, "useforsearch": false, "description": "test", "defaultvalues": ["f42f52e4-95c8-11ef-b220-8b2904a42783"], "value": "test1"}'

You insert JSONB but you specify string not dict.

Rust is strongly typed so PSQLPy doesn't understand why you insert string into JSONB column.

[chandr-andr]

@akanksha314e

Just to give you a more explanation, so you won't have more problems in the future.
I've tried to create normal documentation, you can look at all supported types and find do or don't you need an extra type - https://psqlpy-python.github.io/usage/types/supported_types.html#simple-type.

So, by default, PSQLPy tries to convert Python dict into JSONB as specified in the documentation (screenshot)

So, PSQLPy cannot understand that the target column is JSONB and converts string to JSONB compatible type. We could do it, but it will raise execution time.