How to fetch int2vector fields in PostgreSQL (from Python)

[michalc]

I'm looking to query int2vector fields in PostgreSQL (and specifically those in pg_catalog, and specifically indoption from pg_index https://www.postgresql.org/docs/current/catalog-pg-index.html)

However, if I run:

import adbc_driver_postgresql.dbapi

with adbc_driver_postgresql.dbapi.connect("postgresql://postgres:[email protected]:5432/") as conn:
    with conn.cursor() as cursor:
        cursor.execute("SELECT indoption FROM pg_index LIMIT 1")
        print(cursor.fetchall())

Then I get something that I'm not quite sure how to use:

[(b'\x00\x00\x00\x01\x00\x00\x00\x00\x00\x00\x00\x15\x00\x00\x00\x02\x00\x00\x00\x00\x00\x00\x00\x02\x00\x00\x00\x00\x00\x02\x00\x00',)]

Running the same query using psycopg results in

[('0 0',)]

And using psql:

 indoption
-----------
 0 0

How can I get a non binary results from int2vector fields, similar to psql or psycopg? (Including maybe, how to adjust the query?)

Context - I'm making a SQLAlchemy dialect for ADBC, and especially would like to support its reflection capabilities, so querying the catalogue, including its int2vector fields, is needed for this

[michalc]

(Including maybe, how to adjust the query?)

I have found that you can manipulate the result in SQL, and get an int[] out of it:

SELECT string_to_array(indoption::text, ' ')::int[] FROM pg_index LIMIT 1

Which then seems to behave much like psycopg

[lidavidm]

I think we just need to provide a parser in the driver for this type

[lidavidm]

Converted this to an issue: #2903