c/driver/postgresql: consider VECTOR type support

[lidavidm]

Discussed in #2900

^{Originally posted by michalc May 31, 2025}
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?

[paleolimbot]

The read side is probably not bad (#2072 is a good template for the pieces that need to be updated to implement read support). I think our COPY writing wasn't built with many-to-many conversions in mind (which is what we need here) but it may work out of the box if Postgres implements implicit casting from an array to a vector.