How to insert data into a VECTOR PostgreSQL column (from Python)?

[michalc]

I'm looking to insert data into a VECTOR column by using adbc_get_table_schema to determine the schema of a table, but I'm having some problems getting beyond creating a RecordBatch (which I then would use in a RecordBatchReader and adbc_ingest)

Specifically, running:

import adbc_driver_postgresql.dbapi
import pyarrow as pa

with adbc_driver_postgresql.dbapi.connect("postgresql://postgres:password@127.0.0.1:5432/") as conn:
    with conn.cursor() as cursor:
        cursor.execute('CREATE TABLE my_table (my_vector VECTOR)')
        schema = conn.adbc_get_table_schema("my_table")
        pa.RecordBatch.from_pylist([
            {
                'my_vector': [1, 2]
            }
        ], schema=schema)

Results in an error:

Traceback (most recent call last):
  File "[...]", line 8, in <module>
    pa.RecordBatch.from_pylist([
  File "pyarrow/table.pxi", line 2046, in pyarrow.lib._Tabular.from_pylist
  File "pyarrow/table.pxi", line 6438, in pyarrow.lib._from_pylist
  File "pyarrow/table.pxi", line 3546, in pyarrow.lib.RecordBatch.from_arrays
  File "pyarrow/table.pxi", line 1622, in pyarrow.lib._sanitize_arrays
  File "pyarrow/array.pxi", line 405, in pyarrow.lib.asarray
  File "pyarrow/array.pxi", line 375, in pyarrow.lib.array
  File "pyarrow/array.pxi", line 45, in pyarrow.lib._sequence_to_array
  File "pyarrow/error.pxi", line 155, in pyarrow.lib.pyarrow_internal_check_status
  File "pyarrow/error.pxi", line 92, in pyarrow.lib.check_status
pyarrow.lib.ArrowTypeError: Expected bytes, got a 'list' object

It seems to only want bytes as the vector data, but I'm not sure how to encode a list as bytes. How can I create the RecordBatch, or otherwise insert data into a VECTOR column?

[lidavidm]

Without trying it I'm guessing this is the same thing where we don't have a parser for VECTOR and so it's falling back to binary type (you could print the schema you get to confirm), in which case we should add a real parser and a real way to bind the type

[michalc]

Printing the schema gives...

my_vector: extension<arrow.opaque[storage_type=binary, type_name=vector, vendor_name=PostgreSQL]>
  -- field metadata --
  ADBC:postgresql:typname: 'vector'

So I guess yes - binary?

What would be involved in adding a real parser to this?

[lidavidm]

Basically we'd need to decide what the appropriate Arrow type is and add subclasses to https://github.com/apache/arrow-adbc/blob/main/c/driver/postgresql/copy/writer.h and https://github.com/apache/arrow-adbc/blob/main/c/driver/postgresql/copy/reader.h

[lidavidm]

I converted this into an issue here: #2902