Array value must start with "{" or dimension information when excute_values to jsonb column

My codes

from psycopg2.extras import Json
from psycopg2.extensions import register_adapter

register_adapter(dict, Json)

data = [{
    'end_of_epoch_data': ['GasCoin', [{'Input': 5}, {'Input': 6}, {'Input': 7}]],
}]

def get_upsert_sql(schema: str, table: str, columns: str, primary_keys: list | tuple | set):
    return f"""INSERT INTO {schema}.{table}
            ({', '.join(columns)}) VALUES %s
            ON CONFLICT ({','.join(primary_keys)}) DO UPDATE
            SET {', '.join([f"{col}=EXCLUDED.{col}" for col in columns if col not in primary_keys])}"""


def upsert(data: list, uri: str, schema: str, table: str, primary_keys: list | tuple | set):
    connection = psycopg2.connect(uri)
    cursor = connection.cursor()
    try:
        columns = data[0].keys()
        query = get_upsert_sql(schema, table, columns, primary_keys)
        values = [[d[col] for col in columns] for d in data]
        execute_values(cursor, query, values)
        connection.commit()
    except Exception as e:
        connection.rollback()
        raise e
    finally:
        cursor.close()
        connection.close()

but I got errors like

  File "/Users/tests/test_pg_write.py", line 47, in upsert
    execute_values(cursor, query, values)
  File "/Users/venv/lib/python3.9/site-packages/psycopg2/extras.py", line 1299, in execute_values
    cur.execute(b''.join(parts))
psycopg2.errors.InvalidTextRepresentation: malformed array literal: "GasCoin"
LINE 2:             (end_of_epoch_data) VALUES (ARRAY['GasCoin',ARRA...
                                                      ^
DETAIL:  Array value must start with "{" or dimension information.

end_of_epoch_data is jsonb column in postgres table

any idea? thanks

UPDATE

it seems that the error is because i tried to write python list to jsonb column in pg table. but it seems that i can write json.dumps(dataend_of_epoch_data) which is str of python list into jsonb column of postgres table... is this right solution?

Solution

Instead of turning data into a list of lists, write it as is:

#values = [[d[col] for col in columns] for d in data] #no
values = [json.dumps(d['end_of_epoch_data']) for d in data] #yes

If end_of_epoch_data is declared as a column type text, json or jsonb PostgreSQL will accept that as a valid literal. If you try writing list of lists like the one you have, PostgreSQL won't accept it no matter what type of column you're saving it to.

The reason you're getting the error is PostgreSQL array requirements:

Multidimensional arrays must have matching extents for each dimension.

This:

values = [[d[col] for col in columns] for d in data]
print(values[0])
#[['GasCoin', [{'Input': 5}, {'Input': 6}, {'Input': 7}]]]

Gets you Python arrays (lists) which are ok in Python:

my_python_list_of_lists = ['1dim',['2dim',['3dim']]]
print(type(my_python_list_of_lists),my_python_list_of_lists)
#<class 'list'> ['1dim', ['2dim', ['3dim']]]

but won't work in PostgreSQL, because of the requirement up top:

create table test as select array['1dim',array['2dim',array['3dim']]];

ERROR:  malformed array literal: "2dim"
LINE 1: create table test as select array['1dim',array['2dim',array[...
                                                       ^
DETAIL:  Array value must start with "{" or dimension information.

The first element is a flat, non-array, scalar text. The second element is an array (in your case, of objects/dictionaries, but PostgreSQL doesn't make far enough to mind). When you give that to PostgreSQL, it reads the first element, considers the option the whole incoming thing might be a simple text[], one-dimensional array of text. Then it proceeds to the second element which turns out to be explicitly ARRAY[]. To hold that, the value would have to be text[][], not just text[].

For that to work, the first value would also have to be ARRAY[] - or coercible thereto - in order to achieve the array of matching extents. Here's an example where that could've worked:

create table test 
as (values (array['{a,b,c}',--this is `text`, but it could also work as `text[]`
                  array['d','e','f']
                 ]
           )
   );

column1	array_dims
{{a,b,c},{d,e,f}}	[1:2][1:3]

The first text element turns out to be a valid array literal, so it's interpreted as such, arriving at a 2x3 array.

These also wouldn't work, because even though the elements are coercible, the nested arrays aren't equal:

create table test as (values (array['{a,b,c,d}',array['d','e','f']]));
select column1,array_dims(column1) from test;

ERROR:  multidimensional arrays must have array expressions with matching dimensions

create table test as (values (array['{a,b,c}',array['d','e','f','g']]));
select column1,array_dims(column1) from test;

ERROR:  multidimensional arrays must have array expressions with matching dimensions