Psycopg

PostgreSQL adapter using psycopg 3 with both sync and async support. Features native pipeline mode for multi-statement batching.

Cloud Connectivity

PsycopgSyncConfig can create sync pools through the Google AlloyDB in-process connector when both psycopg and google-cloud-alloydb-connector are installed:

from sqlspec.adapters.psycopg import PsycopgSyncConfig

config = PsycopgSyncConfig(
    connection_config={"user": "app", "password": "secret", "dbname": "orders"},
    driver_features={
        "enable_alloydb": True,
        "alloydb_instance_uri": "projects/project/locations/us-central1/clusters/cluster/instances/instance",
    },
)

This connector path is sync-only. PsycopgAsyncConfig continues to use regular psycopg async connection configuration.

Sync Configuration

Async Configuration

Connection Parameters

Pool Parameters

Driver Features

Sync Driver

Async Driver

Extension Dialects

Psycopg supports the pgvector, pg_textsearch, and ParadeDB dialects for vector similarity search and full-text search operators. See the Dialects reference for operator details.

Data Dictionary

class sqlspec.adapters.psycopg.data_dictionary.PsycopgSyncDataDictionary

Bases: SyncDataDictionaryBase

PostgreSQL-specific sync data dictionary.

dialect: ClassVar[str] = 'postgres'

Dialect identifier. Must be defined by subclasses as a class attribute.

__init__()

get_metadata_capabilities(driver, domains=None)

Get PostgreSQL data-dictionary capability profile.

Return type:

MetadataCapabilityProfile

get_system_metadata_capabilities(driver, domains=None)

Get PostgreSQL opt-in system metadata capability disclosures.

Return type:

tuple[SystemMetadataCapability, ...]

get_schemas(driver)

Get schema metadata.

Return type:

MetadataResult

get_objects(driver, schema=None)

Get database object metadata.

Return type:

MetadataResult

get_table_details(driver, table, schema=None)

Get rich table metadata.

Return type:

MetadataResult

get_constraints(driver, table=None, schema=None)

Get constraint metadata.

Return type:

MetadataResult

get_views(driver, schema=None)

Get view metadata.

Return type:

MetadataResult

get_routines(driver, schema=None)

Get routine metadata.

Return type:

MetadataResult

get_privileges(driver, object_name=None, schema=None)

Get privilege metadata.

Return type:

MetadataResult

get_dependencies(driver, object_name=None, schema=None)

Get dependency metadata.

Return type:

MetadataResult

get_ddl(driver, object_name, schema=None, *, object_type='table', include_dependencies=True, prefer_native=True, redact=True)

Get object DDL where PostgreSQL exposes native definition helpers.

Return type:

DDLResult

get_system_metadata(driver, request=None, **kwargs)

Get opt-in PostgreSQL system metadata.

Return type:

SystemMetadataResult

get_version(driver)

Get PostgreSQL database version information.

Uses an inline cache check to avoid cross-module method calls that can trigger MyPyC boundary issues.

Parameters:

driver (PsycopgSyncDriver) -- Sync database driver instance.

Return type:

VersionInfo | None

Returns:

PostgreSQL version information or None if detection fails.

get_feature_flag(driver, feature)

Check if PostgreSQL database supports a specific feature.

Parameters:

• driver (PsycopgSyncDriver) -- Sync database driver instance.

• feature (str) -- Feature name to check.

Return type:

bool

Returns:

True if feature is supported, False otherwise.

get_optimal_type(driver, type_category)

Get optimal PostgreSQL type for a category.

Parameters:

• driver (PsycopgSyncDriver) -- Sync database driver instance.

• type_category (str) -- Type category.

Return type:

str

Returns:

PostgreSQL-specific type name.

get_tables(driver, schema=None)

Get tables sorted by topological dependency order using Recursive CTE.

Return type:

list[TableMetadata]

get_columns(driver, table=None, schema=None)

Get column information for a table or schema.

Return type:

list[ColumnMetadata]

get_indexes(driver, table=None, schema=None)

Get index metadata for a table or schema.

Return type:

list[IndexMetadata]

get_foreign_keys(driver, table=None, schema=None)

Get foreign key metadata.

Return type:

list[ForeignKeyMetadata]

class sqlspec.adapters.psycopg.data_dictionary.PsycopgAsyncDataDictionary

Bases: AsyncDataDictionaryBase

PostgreSQL-specific async data dictionary.

dialect: ClassVar[str] = 'postgres'

Dialect identifier. Must be defined by subclasses as a class attribute.

__init__()

async get_metadata_capabilities(driver, domains=None)

Get PostgreSQL data-dictionary capability profile.

Return type:

MetadataCapabilityProfile

async get_system_metadata_capabilities(driver, domains=None)

Get PostgreSQL opt-in system metadata capability disclosures.

Return type:

tuple[SystemMetadataCapability, ...]

async get_schemas(driver)

Get schema metadata.

Return type:

MetadataResult

async get_objects(driver, schema=None)

Get database object metadata.

Return type:

MetadataResult

async get_table_details(driver, table, schema=None)

Get rich table metadata.

Return type:

MetadataResult

async get_constraints(driver, table=None, schema=None)

Get constraint metadata.

Return type:

MetadataResult

async get_views(driver, schema=None)

Get view metadata.

Return type:

MetadataResult

async get_routines(driver, schema=None)

Get routine metadata.

Return type:

MetadataResult

async get_privileges(driver, object_name=None, schema=None)

Get privilege metadata.

Return type:

MetadataResult

async get_dependencies(driver, object_name=None, schema=None)

Get dependency metadata.

Return type:

MetadataResult

async get_ddl(driver, object_name, schema=None, *, object_type='table', include_dependencies=True, prefer_native=True, redact=True)

Get object DDL where PostgreSQL exposes native definition helpers.

Return type:

DDLResult

async get_system_metadata(driver, request=None, **kwargs)

Get opt-in PostgreSQL system metadata.

Return type:

SystemMetadataResult

async get_version(driver)

Get PostgreSQL database version information.

Uses an inline cache check to avoid cross-module method calls that can trigger MyPyC boundary issues.

Parameters:

driver (PsycopgAsyncDriver) -- Async database driver instance.

Return type:

VersionInfo | None

Returns:

PostgreSQL version information or None if detection fails.

async get_feature_flag(driver, feature)

Check if PostgreSQL database supports a specific feature.

Parameters:

• driver (PsycopgAsyncDriver) -- Async database driver instance.

• feature (str) -- Feature name to check.

Return type:

bool

Returns:

True if feature is supported, False otherwise.

async get_optimal_type(driver, type_category)

Get optimal PostgreSQL type for a category.

Parameters:

• driver (PsycopgAsyncDriver) -- Async database driver instance.

• type_category (str) -- Type category.

Return type:

str

Returns:

PostgreSQL-specific type name.

async get_tables(driver, schema=None)

Get tables sorted by topological dependency order using Recursive CTE.

Return type:

list[TableMetadata]

async get_columns(driver, table=None, schema=None)

Get column information for a table or schema.

Return type:

list[ColumnMetadata]

async get_indexes(driver, table=None, schema=None)

Get index metadata for a table or schema.

Return type:

list[IndexMetadata]

async get_foreign_keys(driver, table=None, schema=None)

Get foreign key metadata.

Return type:

list[ForeignKeyMetadata]

Extension Settings

Use the configuration types below in their corresponding extension_config namespace: "litestar", "events", or "adk" as supported by this adapter.

Native null pools and JSON codecs

Set null_pool=True in connection_config or driver_features to select psycopg's native null pool. Returned connections close instead of remaining idle. max_size and max_waiting still control concurrency and waiting clients; min_size is omitted because null pools do not maintain idle connections. The same option works for sync and async configurations.

json_serializer and json_deserializer driver features also configure psycopg's native JSON adaptation for each new connection. Codec setup errors propagate to the caller.