{"id":3387,"library":"adbc-driver-postgresql","title":"ADBC PostgreSQL Driver","description":"The `adbc-driver-postgresql` package provides Python bindings for a libpq-based ADBC (Arrow Database Connectivity) driver for PostgreSQL. It offers a DBAPI 2.0-compatible interface, enabling high-performance data access to PostgreSQL databases by leveraging Apache Arrow for columnar data interchange. The library is currently active, with version 1.11.0, and typically releases in conjunction with the broader Apache Arrow ADBC project.","status":"active","version":"1.11.0","language":"en","source_language":"en","source_url":"https://github.com/apache/arrow-adbc","tags":["database","postgresql","adbc","apache arrow","dbapi","sql","columnar"],"install":[{"cmd":"pip install adbc-driver-postgresql","lang":"bash","label":"PyPI"},{"cmd":"mamba install adbc-driver-postgresql","lang":"bash","label":"Conda-forge"},{"cmd":"# Note: This Python package requires an underlying C/C++ ADBC PostgreSQL driver (libadbc_driver_postgresql.so/.dll/.dylib) to be available in your system's library path or specified via ADBC_POSTGRESQL_LIBRARY environment variable. Standard pip/conda installations often handle this for common platforms.\nexport ADBC_POSTGRESQL_LIBRARY=/path/to/libadbc_driver_postgresql.so","lang":"bash","label":"C Driver Dependency (Advanced)"}],"dependencies":[{"reason":"Required for the Python bindings to interact with the underlying ADBC driver. It provides the core ADBC functionality.","package":"adbc-driver-manager"},{"reason":"Optional, but highly recommended for interacting with Arrow-native data structures (e.g., `fetch_arrow_table`) and achieving optimal performance with columnar data.","package":"pyarrow","optional":true}],"imports":[{"symbol":"connect","correct":"import adbc_driver_postgresql.dbapi\nconn = adbc_driver_postgresql.dbapi.connect(...)"}],"quickstart":{"code":"import os\nimport adbc_driver_postgresql.dbapi\n\n# Configure your PostgreSQL connection URI.\n# For example, using a local Dockerized PostgreSQL with default credentials:\n# docker run -it --rm -e POSTGRES_PASSWORD=password -e POSTGRES_DB=testdb -p 5432:5432 postgres:latest\n# export ADBC_POSTGRESQL_TEST_URI=\"postgresql://postgres:password@localhost:5432/testdb\"\nuri = os.environ.get(\"ADBC_POSTGRESQL_TEST_URI\", \"postgresql://user:password@localhost:5432/mydb\")\n\ntry:\n    with adbc_driver_postgresql.dbapi.connect(uri) as conn:\n        with conn.cursor() as cur:\n            # DDL operations\n            cur.execute(\"DROP TABLE IF EXISTS example_adbc;\")\n            cur.execute(\"CREATE TABLE example_adbc (id INTEGER, name VARCHAR(50));\")\n\n            # DML operations with bind parameters\n            cur.executemany(\"INSERT INTO example_adbc VALUES (?, ?);\", [(1, 'Alice'), (2, 'Bob')])\n            conn.commit() # Commit changes for DML\n\n            # Query data and fetch as an Arrow Table (requires pyarrow)\n            cur.execute(\"SELECT id, name FROM example_adbc WHERE id > 0;\")\n            result_table = cur.fetch_arrow_table()\n            print(\"Fetched Arrow Table:\")\n            print(result_table)\n\n            # Query data and fetch a single row\n            cur.execute(\"SELECT id, name FROM example_adbc WHERE id = 1;\")\n            first_row = cur.fetchone()\n            print(f\"\\nFetched one row: {first_row}\")\n\nexcept Exception as e:\n    print(f\"An error occurred: {e}\")\n    print(\"Ensure your PostgreSQL database is running and ADBC_POSTGRESQL_TEST_URI is set correctly.\")","lang":"python","description":"This quickstart demonstrates how to connect to a PostgreSQL database using `adbc-driver-postgresql.dbapi`, execute DDL and DML statements, and fetch results as both an Apache Arrow Table and a single row. It assumes a PostgreSQL instance is accessible via the provided URI, preferably set via an environment variable for security."},"warnings":[{"fix":"Migrate to using the `Opaque` extension type metadata for differentiating binary column intent.","message":"The `ADBC:postgresql:typname` metadata key, previously attached to schema fields for unknown columns, has been deprecated in favor of the `Opaque` canonical extension type. Users should not rely on this key's continued existence.","severity":"deprecated","affected_versions":"Prior to 1.11.0, deprecated with introduction of Opaque type."},{"fix":"For queries incompatible with `COPY`, disable the optimization by setting the statement option `adbc.postgresql.use_copy` to `False` (or `0`).","message":"The PostgreSQL driver uses `COPY` for optimal performance in query execution by default. However, this optimization is not supported for all queries (e.g., `SHOW` queries). Such queries may fail or produce unexpected results.","severity":"gotcha","affected_versions":"All versions"},{"fix":"Avoid using prepared statements with parameters for `SELECT` queries that are expected to return results. Construct queries with string formatting (carefully, to avoid SQL injection) or use alternative fetching mechanisms for such cases.","message":"The PostgreSQL ADBC driver's support for prepared statements with parameters is currently limited to queries that *do not* return result sets (e.g., `INSERT`, `UPDATE`). This is due to the driver's reliance on the `COPY` protocol for performance, which is not compatible with parameterized `SELECT` statements.","severity":"gotcha","affected_versions":"All versions"},{"fix":"Be aware of type conversions and handle them explicitly in your application code. For `NUMERIC`, parse the string to the desired numeric type. For timestamps, ensure time zone handling is managed before binding or after fetching, if critical.","message":"There are known limitations and specific behaviors in type mapping between PostgreSQL and Arrow/Python types. Notably, PostgreSQL `NUMERIC` types are read as their string representation, and time zone information in `timestamp` values is ignored during binding.","severity":"gotcha","affected_versions":"All versions"},{"fix":"Always use `with` statements for `connect()` and `cursor()` calls to ensure proper resource management and automatic closure. For manual connections, ensure `conn.close()` is called in a `finally` block.","message":"Failure to explicitly close connections and cursors can lead to resource leaks (e.g., database connections remaining open indefinitely). While the Python driver manager attempts to close unclosed cursors when a connection is closed, explicit management is best practice.","severity":"gotcha","affected_versions":"All versions"}],"env_vars":null,"last_verified":"2026-04-11T00:00:00.000Z","next_check":"2026-07-10T00:00:00.000Z"}