API Reference (Autodoc) ¶

Notes

The method is wrapped in log_operation("database.health") so monitoring probes appear in the structured log stream (D-09). The error string is the raw psycopg exception message — pycopg never appends the DSN, password, or credentials (T-45-07).

Examples

>>> status = db.health()
>>> status["connected"]
True

execute(sql: str, params: Sequence[Any] | Mapping[str, Any] | None = None, autocommit: bool = False) → list[dict[str, Any]][source]¶

Execute SQL and return results as list of dicts.

Parameters:

sql (str) – SQL query to execute.
params (Params, optional) – Query parameters — either a positional sequence (%s placeholders) or a named mapping (%(name)s placeholders). psycopg binds both forms natively without any translation.
autocommit (bool, optional) – Enable autocommit mode, by default False.

Returns:

List of result rows as dicts.

Return type:

list of dict

execute_many(sql: str, params_seq: Sequence[Sequence[Any]]) → int[source]¶

Execute SQL for multiple parameter sets.

Uses psycopg’s executemany() for better performance than sequential execute() calls.

Parameters:

sql (str) – SQL query with placeholders.
params_seq (Sequence of Sequence) – Sequence of parameter sequences.

Returns:

Total number of affected rows.

Return type:

insert_many(table: str, rows: list[dict[str, Any]], *, schema: str = 'public', on_conflict: str | None = None) → int[source]¶

Insert multiple rows efficiently.

Parameters:

table (str) – Table name.
rows (list of dict) – List of row dicts.
schema (str, optional) – Schema name, by default “public”.
on_conflict (str, optional) – ON CONFLICT clause (e.g., “DO NOTHING”, “DO UPDATE SET …”).

Returns:

Number of rows inserted.

Return type:

upsert_many(table: str, rows: list[dict[str, Any]], conflict_columns: list[str], *, update_columns: list[str] | None = None, schema: str = 'public') → int[source]¶

Upsert (insert or update) multiple rows.

Parameters:

table (str) – Table name.
rows (list of dict) – List of row dicts.
conflict_columns (list of str) – Columns that define uniqueness.
update_columns (list of str, optional) – Columns to update on conflict (None = all except conflict).
schema (str, optional) – Schema name, by default “public”.

Returns:

Number of rows affected.

Return type:

upsert(table: str, row: dict[str, Any], conflict_columns: list[str], *, update_columns: list[str] | None = None, schema: str = 'public') → dict[str, Any] | None[source]¶

Upsert a single row and return the affected row.

Parameters:

table (str) – Table name.
row (dict) – Row data as column-name to value mapping.
conflict_columns (list of str) – Columns that define uniqueness for the ON CONFLICT target.
update_columns (list of str, optional) – Columns to update on conflict. Defaults to all non-conflict columns.
schema (str, optional) – Schema name, by default “public”.

Returns:

The affected row as a dict (via RETURNING *). Under DO UPDATE the return is structurally always a dict; None is a defensive guard for a future no-row path and is not reachable under the current SQL.

Return type:

dict or None

Raises:

ValueError – If update_columns resolves to an empty list — i.e. all columns in row are also listed in conflict_columns and no explicit update_columns override is given.

insert(table: str, row: dict[str, Any], *, schema: str = 'public', on_conflict: str | None = None) → dict[str, Any] | None[source]¶

Insert a single row and return the inserted row.

The singular twin of insert_many() / upsert() — for inserting exactly one row without forcing a 1-element list through insert_many(). Unlike upsert(), on_conflict is a raw passthrough string (mirroring insert_many()’s shape, D-06), not computed from a conflict/update column list.

Parameters:

table (str) – Table name.
row (dict) – Row data as column-name to value mapping.
schema (str, optional) – Schema name, by default “public”.
on_conflict (str, optional) – ON CONFLICT clause (e.g., “DO NOTHING”, “DO UPDATE SET …”), threaded verbatim into the INSERT statement.

Returns:

The inserted row as a dict (via RETURNING *). Under on_conflict="DO NOTHING", if a pre-existing row conflicts, no row is returned by RETURNING * and this method returns None (D-07).

Return type:

dict or None

Raises:

ValueError – If row is empty (WR-01) — an empty dict would otherwise emit invalid SQL (INSERT INTO t () VALUES ()).

delete_where(table: str, where: dict[str, Any] | None = None, *, where_sql: str | None = None, where_params: Sequence[Any] | None = None, schema: str = 'public') → int[source]¶

Delete rows matching the given equality conditions.

Parameters:

table (str) – Table name.
where (dict, optional) – Equality conditions as column-name to value mapping. Mutually exclusive with where_sql (D-02). Exactly one of where / where_sql must resolve to a non-empty predicate — use db.schema.truncate_table to affect all rows.
where_sql (str, optional) – Raw SQL WHERE fragment (without the WHERE keyword), for predicates the where dict cannot express (e.g. "created_at < now() - %s::interval"). This is a documented escape hatch: the fragment is passed through verbatim and is not run through identifier validation — the caller owns the SQL text. Only where_params are treated as untrusted values and bound as %s.
where_params (Sequence, optional) – Positional %s parameters bound against where_sql. Requires where_sql to be present (D-04).
schema (str, optional) – Schema name, by default “public”.

Returns:

Number of rows deleted.

Return type:

Raises:

ValueError – If neither where nor where_sql resolves to a non-empty predicate (destructive guard); if both where and where_sql are supplied (D-02); if where_params is supplied without where_sql (D-04); or if where_sql is empty/whitespace-only (D-03).

update_where(table: str, values: dict[str, Any], where: dict[str, Any] | None = None, *, where_sql: str | None = None, where_params: Sequence[Any] | None = None, schema: str = 'public') → int[source]¶

Update rows matching the given equality conditions.

Parameters:

table (str) – Table name.
values (dict) – Column-name to new-value mapping for the SET clause. Must be non-empty.
where (dict, optional) – Equality conditions as column-name to value mapping. Mutually exclusive with where_sql (D-02). Exactly one of where / where_sql must resolve to a non-empty predicate — use execute with an explicit UPDATE statement to affect all rows.
where_sql (str, optional) – Raw SQL WHERE fragment (without the WHERE keyword), for predicates the where dict cannot express. This is a documented escape hatch: the fragment is passed through verbatim and is not run through identifier validation — the caller owns the SQL text. Only where_params are treated as untrusted values and bound as %s.
where_params (Sequence, optional) – Positional %s parameters bound against where_sql. Requires where_sql to be present (D-04).
schema (str, optional) – Schema name, by default “public”.

Returns:

Number of rows updated.

Return type:

Raises:

ValueError – If values is empty; if neither where nor where_sql resolves to a non-empty predicate (destructive guard); if both where and where_sql are supplied (D-02); if where_params is supplied without where_sql (D-04); or if where_sql is empty/whitespace-only (D-03).

exists(table: str, where: dict[str, Any] | None = None, *, where_sql: str | None = None, where_params: Sequence[Any] | None = None, schema: str = 'public') → bool[source]¶

Check whether any row matching the given equality conditions exists.

Parameters:

table (str) – Table name.
where (dict, optional) – Equality conditions as column-name to value mapping. Mutually exclusive with where_sql (D-02). Exactly one of where / where_sql must resolve to a non-empty predicate — an existence check with no predicate is meaningless.
where_sql (str, optional) – Raw SQL WHERE fragment (without the WHERE keyword), for predicates the where dict cannot express. This is a documented escape hatch: the fragment is passed through verbatim and is not run through identifier validation — the caller owns the SQL text. Only where_params are treated as untrusted values and bound as %s.
where_params (Sequence, optional) – Positional %s parameters bound against where_sql. Requires where_sql to be present (D-04).
schema (str, optional) – Schema name, by default “public”.

Returns:

True if at least one matching row exists, False otherwise.

Return type:

Raises:

ValueError – If neither where nor where_sql resolves to a non-empty predicate (guard fires before any SQL or cursor is opened); if both where and where_sql are supplied (D-02); if where_params is supplied without where_sql (D-04); or if where_sql is empty/whitespace-only (D-03).
InvalidIdentifierError – If any column name or the table/schema identifier is invalid.

count(table: str, *, where: dict[str, Any] | None = None, where_sql: str | None = None, where_params: Sequence[Any] | None = None, schema: str = 'public') → int[source]¶

Count rows in a table, optionally filtered by equality conditions.

Parameters:

table (str) – Table name.
where (dict, optional) – Equality conditions as column-name to value mapping. When neither where nor where_sql is given, all rows are counted without a WHERE clause. Mutually exclusive with where_sql (D-02).
where_sql (str, optional) – Raw SQL WHERE fragment (without the WHERE keyword), for predicates the where dict cannot express (e.g. "age > %s AND status = ANY(%s)"). This is a documented escape hatch: the fragment is passed through verbatim and is not run through identifier validation — the caller owns the SQL text. Only where_params are treated as untrusted values and bound as %s.
where_params (Sequence, optional) – Positional %s parameters bound against where_sql. Requires where_sql to be present (D-04).
schema (str, optional) – Schema name, by default “public”.

Returns:

Number of matching (or total) rows.

Return type:

Raises:

ValueError – If both where and where_sql are supplied (D-02); if where_params is supplied without where_sql (D-04); or if where_sql is empty/whitespace-only (D-03).
InvalidIdentifierError – If any column name or the table/schema identifier is invalid.

Select rows matching the given equality conditions.

The predicate read sibling of delete_where() / update_where() / exists() / count(). Unlike those, an empty predicate is legal here: with no where/where_sql, all rows are returned (still honoring order_by/limit/offset) — reads are non-destructive, so there is no empty-predicate guard (D-03).

Parameters:

table (str) – Table name.
where (dict, optional) – Equality conditions as column-name to value mapping. When neither where nor where_sql is given, all rows are returned without a WHERE clause (D-03). Mutually exclusive with where_sql (D-02).
where_sql (str, optional) – Raw SQL WHERE fragment (without the WHERE keyword), for predicates the where dict cannot express (e.g. "age > %s AND status = ANY(%s)"). This is a documented escape hatch: the fragment is passed through verbatim and is not run through identifier validation — the caller owns the SQL text. Only where_params are treated as untrusted values and bound as %s.
where_params (Sequence, optional) – Positional %s parameters bound against where_sql. Requires where_sql to be present (D-04).
columns (list of str, optional) – Column names to project, by default None (SELECT *).
order_by (str, optional) – ORDER BY clause. Like where_sql, this is a documented, caller-owned raw-SQL escape hatch — it is appended verbatim and is not run through identifier validation, so multi-column syntax such as "col1 DESC, col2 ASC" is accepted by design.
limit (int, optional) – Maximum number of rows to return.
offset (int, optional) – Number of rows to skip.
schema (str, optional) – Schema name, by default “public”.

Returns:

Matching rows (or all rows if no predicate), as dicts.

Return type:

list of dict

Raises:

ValueError – If both where and where_sql are supplied (D-02); if where_params is supplied without where_sql (D-04); if where_sql is empty/whitespace-only (D-03); or if limit or offset is negative (WR-02).
InvalidIdentifierError – If any column name or the table/schema identifier is invalid.

Select rows matching the given equality conditions (alias of select_where()).

A real, concrete, delegating twin of select_where() (D-02) — not a deprecated alias and not a module-level alias — provided as a shorter, familiar name for the same read. See select_where() for the full parameter and behavior documentation.

Parameters:

table (str) – Table name.
where (dict, optional) – Equality conditions as column-name to value mapping.
where_sql (str, optional) – Raw SQL WHERE fragment escape hatch (see select_where()).
where_params (Sequence, optional) – Positional %s parameters bound against where_sql.
columns (list of str, optional) – Column names to project, by default None (SELECT *).
order_by (str, optional) – ORDER BY clause (caller-owned raw-SQL escape hatch).
limit (int, optional) – Maximum number of rows to return.
offset (int, optional) – Number of rows to skip.
schema (str, optional) – Schema name, by default “public”.

Returns:

Matching rows (or all rows if no predicate), as dicts.

Return type:

list of dict

Select the first row matching the given equality conditions.

The single-row predicate read sibling of select_where(). Forces a server-side LIMIT 1 — never truncates in Python. On more than one match, the first row (per order_by if given, otherwise implementation-defined) is returned with no exception raised (D-04); uniqueness is the caller’s responsibility (a UNIQUE constraint, or exists()/count()). Does not expose limit/offset — they would be contradictory with the forced LIMIT 1 (D-05).

Parameters:

table (str) – Table name.
where (dict, optional) – Equality conditions as column-name to value mapping. When neither where nor where_sql is given, any one row (or None if the table is empty) is returned. Mutually exclusive with where_sql (D-02).
where_sql (str, optional) – Raw SQL WHERE fragment (without the WHERE keyword), for predicates the where dict cannot express. This is a documented escape hatch: the fragment is passed through verbatim and is not run through identifier validation — the caller owns the SQL text. Only where_params are treated as untrusted values and bound as %s.
where_params (Sequence, optional) – Positional %s parameters bound against where_sql. Requires where_sql to be present (D-04).
columns (list of str, optional) – Column names to project, by default None (SELECT *).
order_by (str, optional) – ORDER BY clause, making the “first” match deterministic. Like where_sql, this is a documented, caller-owned raw-SQL escape hatch — not run through identifier validation.
schema (str, optional) – Schema name, by default “public”.

Returns:

The first matching row as a dict, or None if no row matches.

Return type:

dict or None

Raises:

ValueError – If both where and where_sql are supplied (D-02); if where_params is supplied without where_sql (D-04); or if where_sql is empty/whitespace-only (D-03).
InvalidIdentifierError – If any column name or the table/schema identifier is invalid.

Select the first row matching the given equality conditions (alias of get()).

A real, concrete, delegating twin of get() (D-02) — not a deprecated alias and not a module-level alias — provided as a shorter, familiar name for the same single-row read. See get() for the full parameter and behavior documentation.

Parameters:

table (str) – Table name.
where (dict, optional) – Equality conditions as column-name to value mapping.
where_sql (str, optional) – Raw SQL WHERE fragment escape hatch (see get()).
where_params (Sequence, optional) – Positional %s parameters bound against where_sql.
columns (list of str, optional) – Column names to project, by default None (SELECT *).
order_by (str, optional) – ORDER BY clause, making the “first” match deterministic (caller-owned raw-SQL escape hatch).
schema (str, optional) – Schema name, by default “public”.

Returns:

The first matching row as a dict, or None if no row matches.

Return type:

dict or None

stream(sql: str, params: Sequence[Any] | None = None, *, batch_size: int = 1000) → Iterator[dict[str, Any]][source]¶

Stream query results in batches.

Memory-efficient way to process large result sets.

Parameters:

sql (str) – SQL query.
params (Sequence, optional) – Query parameters.
batch_size (int, optional) – Rows to fetch per batch, by default 1000.

Yields:

dict – Row dicts.

notify(channel: str, *, payload: str = '') → None[source]¶

Send notification on a channel.

Parameters:

channel (str) – Channel name.
payload (str, optional) – Notification payload (max 8000 bytes), by default “”.

insert_batch(table: str, rows: list[dict[str, Any]], *, schema: str = 'public', on_conflict: str | None = None, batch_size: int | None = None) → int[source]¶

Insert multiple rows efficiently using batch VALUES.

This method builds a single INSERT with multiple VALUES tuples, which is significantly faster than individual INSERT statements. For very large datasets (>10000 rows), consider using copy_insert().

Parameters:

table (str) – Table name.
rows (list of dict) – List of row dicts (all must have same keys).
schema (str, optional) – Schema name, by default “public”.
on_conflict (str, optional) – ON CONFLICT clause (e.g., “DO NOTHING”, “(id) DO UPDATE SET name = EXCLUDED.name”).
batch_size (int, optional) – Max rows per INSERT statement, by default from config.

Returns:

Total number of rows inserted.

Return type:

copy_insert(table: str, rows: list[dict[str, Any]], *, schema: str = 'public', columns: list[str] | None = None) → int[source]¶

Insert rows using PostgreSQL COPY protocol.

This is the fastest method for bulk inserts (10-100x faster than regular INSERT for large datasets). Best for >10000 rows.

Note: COPY doesn’t support ON CONFLICT. For upserts, use insert_batch().

Parameters:

table (str) – Table name.
rows (list of dict) – List of row dicts.
schema (str, optional) – Schema name, by default “public”.
columns (list of str, optional) – Column names. If not provided, uses keys from first row.

Returns:

Number of rows inserted.

Return type:

fetch_one(sql: str, params: Sequence[Any] | Mapping[str, Any] | None = None) → dict[str, Any] | None[source]¶

Execute SQL and return single row.

Parameters:

sql (str) – SQL query.
params (Params, optional) – Query parameters — either a positional sequence (%s placeholders) or a named mapping (%(name)s placeholders). psycopg binds both forms natively without any translation.

Returns:

Single row as dict, or None.

Return type:

dict or None

fetch_val(sql: str, params: Sequence[Any] | Mapping[str, Any] | None = None) → Any[source]¶

Execute SQL and return single value.

Parameters:

sql (str) – SQL query returning single column.
params (Params, optional) – Query parameters — either a positional sequence (%s placeholders) or a named mapping (%(name)s placeholders). psycopg binds both forms natively without any translation.

Returns:

Single value, or None.

Return type:

Any

fetch_all(sql: str, params: Sequence[Any] | Mapping[str, Any] | None = None) → list[dict[str, Any]][source]¶

Execute SQL and return all rows as a list of dicts.

Thin list[dict] complement to fetch_one(). The core uses psycopg’s dict_row row factory by default, so every row is already a plain dict — no extra conversion needed. Use fetch_one() when you expect exactly one row; use fetch_all for arbitrary result sets where you need the full list at once.

Parameters:

sql (str) – SQL query. The caller owns the SQL string and must parameterize dynamic values via params (same trust model as execute() / fetch_one()).
params (Params, optional) – Query parameters — either a positional sequence (%s placeholders) or a named mapping (%(name)s placeholders). psycopg binds both forms natively without any translation.

Returns:

All result rows as dicts (keyed by column name). Returns [] for queries that produce no rows or no description.

Return type:

list of dict

Notes

The underlying connection uses dict_row as its row factory, so all fetch methods (fetch_one, fetch_all, execute) return dict rows by default — no into= toggle or tuples path is provided.

paginate(table: str, limit: int, *, offset: int = 0, order_by: str | list[str] | None = None, where: dict[str, Any] | None = None, where_sql: str | None = None, where_params: Sequence[Any] | None = None, descending: bool = False, schema: str = 'public') → list[dict[str, Any]][source]¶

Return a page of rows from a table.

Parameters:

table (str) – Table name.
limit (int) – Maximum number of rows to return. Cast to int before use.
offset (int, optional) – Number of rows to skip, by default 0. Cast to int before use.
order_by (str or list of str, optional) – Column name(s) to sort by. Each name is validated via validate_identifiers before interpolation. Whole-clause direction is controlled by descending.
where (dict, optional) – Equality conditions as column-name to value mapping. When neither where nor where_sql is given, all rows are returned without a WHERE clause. Mutually exclusive with where_sql (D-02).
where_sql (str, optional) – Raw SQL WHERE fragment (without the WHERE keyword), for predicates the where dict cannot express. This is a documented escape hatch: the fragment is passed through verbatim and is not run through identifier validation — the caller owns the SQL text. Only where_params are treated as untrusted values and bound as %s.
where_params (Sequence, optional) – Positional %s parameters bound against where_sql. Requires where_sql to be present (D-04).
descending (bool, optional) – When True, append DESC to the ORDER BY clause (applies to all listed columns). Per-column direction is deferred.
schema (str, optional) – Schema name, by default “public”.

Returns:

Requested page as a list of row dicts. Returns [] when the query produces no rows.

Return type:

list of dict

Raises:

ValueError – If both where and where_sql are supplied (D-02); if where_params is supplied without where_sql (D-04); or if where_sql is empty/whitespace-only (D-03).
InvalidIdentifierError – If any column name in order_by or the table/schema identifier is invalid.

paginate_page(table: str, limit: int, *, offset: int = 0, order_by: str | list[str] | None = None, where: dict[str, Any] | None = None, where_sql: str | None = None, where_params: Sequence[Any] | None = None, with_total: bool = False, descending: bool = False, schema: str = 'public') → Page[source]¶

Return an offset page wrapped in a Page envelope (D-09).

Sibling of paginate() returning a typed Page instead of a plain list[dict]. paginate() itself is left untouched (frozen list[dict], C-03). has_next is always computed cheaply via a limit + 1 fetch-and-trim; total runs a separate COUNT(*) only when with_total=True (D-10).

Parameters:

table (str) – Table name.
limit (int) – Maximum number of rows to return in the page. Cast to int before use.
offset (int, optional) – Number of rows to skip, by default 0. Cast to int before use.
order_by (str or list of str, optional) – Column name(s) to sort by. Each name is validated via validate_identifiers before interpolation. Whole-clause direction is controlled by descending.
where (dict, optional) – Equality conditions as column-name to value mapping. Mutually exclusive with where_sql (D-02).
where_sql (str, optional) – Raw SQL WHERE fragment (without the WHERE keyword), for predicates the where dict cannot express. Documented escape hatch: passed through verbatim, never run through identifier validation — the caller owns the SQL text. Only where_params are treated as untrusted values and bound as %s.
where_params (Sequence, optional) – Positional %s parameters bound against where_sql. Requires where_sql to be present (D-04).
with_total (bool, optional) – When True, run a separate COUNT(*) (same predicate) and populate Page.total. By default False — avoids a surprise COUNT on deep pages (D-10).
descending (bool, optional) – When True, append DESC to the ORDER BY clause.
schema (str, optional) – Schema name, by default “public”.

Returns:

rows (trimmed to limit), total (int when with_total=True else None), has_next, and next_cursor (always None — offset pagination has no cursor concept).

Return type:

Raises:

ValueError – If both where and where_sql are supplied (D-02); if where_params is supplied without where_sql (D-04); if where_sql is empty/whitespace-only (D-03); if order_by is an empty list (WR-01); or if limit is not a positive integer (IN-02).
InvalidIdentifierError – If any column name in order_by or the table/schema identifier is invalid.

paginate_keyset(table: str, limit: int, *, order_by: str | list[str] | None = None, after: Sequence[Any] | None = None, where: dict[str, Any] | None = None, where_sql: str | None = None, where_params: Sequence[Any] | None = None, with_total: bool = False, descending: bool = False, schema: str = 'public') → Page[source]¶

Return a keyset (seek) page wrapped in a Page envelope (D-05).

Stable, index-friendly deep pagination via a PostgreSQL row-value comparison (c1, c2) > (%s, %s) (D-07, pycopg.base.QueryMixin._build_keyset_where()) instead of OFFSET. order_by is mandatory — the caller owns a unique/total order (append a unique column such as the primary key as a tiebreaker); no hidden PK-introspection round-trip is performed. Forward-only for this release — before=/backward paging is deferred. Caveat (IN-01): order_by columns should be NOT NULL (or the caller must guarantee no NULL values) — the row-value comparison follows SQL NULL semantics, so rows with a NULL key are silently skipped by the seek predicate.

Parameters:

table (str) – Table name.
limit (int) – Maximum number of rows to return in the page. Cast to int before use.
order_by (str or list of str) – Column name(s) defining the seek order. Mandatory (D-07) — raises ValueError if omitted. Each name is validated via validate_identifiers before interpolation. Columns should be NOT NULL (IN-01) — see caveat above.
after (Sequence, optional) – Cursor values, positionally keyed to order_by (D-06). Omit (or pass None) to fetch the first page — the keyset fragment is skipped entirely (ORDER BY + LIMIT only). Must have exactly len(order_by) values.
where (dict, optional) – Equality conditions as column-name to value mapping. Mutually exclusive with where_sql (D-02). ANDed with the keyset fragment when both are present.
where_sql (str, optional) – Raw SQL WHERE fragment (without the WHERE keyword), for predicates the where dict cannot express. Documented escape hatch: passed through verbatim, never run through identifier validation — the caller owns the SQL text. Only where_params are treated as untrusted values and bound as %s.
where_params (Sequence, optional) – Positional %s parameters bound against where_sql. Requires where_sql to be present (D-04).
with_total (bool, optional) – When True, run a separate COUNT(*) using only the base predicate (where/where_sql, not the keyset after fragment — the total is the full filtered set, not the remaining-after-cursor set) and populate Page.total. By default False (D-10).
descending (bool, optional) – When True, flips the row-value comparison operator to < and appends DESC to ORDER BY — both must agree (Pitfall 3). By default False.
schema (str, optional) – Schema name, by default “public”.

Returns:

rows (trimmed to limit), total (int when with_total=True else None), has_next, and next_cursor — the last row’s order_by-keyed value tuple (D-06), keyed to order_by columns (Pitfall 4), or None when the page is empty.

Return type:

Raises:

ValueError – If order_by is omitted or an empty list (D-07/WR-01); if after is supplied and len(after) != len(order_by); if both where and where_sql are supplied (D-02); if where_params is supplied without where_sql (D-04); if where_sql is empty/whitespace-only (D-03); or if limit is not a positive integer (IN-02).
InvalidIdentifierError – If any column name in order_by or the table/schema identifier is invalid.

from_dataframe(df: pd.DataFrame, table: str, *, schema: str = 'public', if_exists: Literal['fail', 'replace', 'append'] = 'fail', primary_key: str | list[str] | None = None, index: bool = False, dtype: dict[str, Any] | None = None) → None[source]¶

Create or append to table from pandas DataFrame.

Uses a two-phase Hybrid DDL+COPY strategy (D-01/D-04):

DDL phase — df_ddl.head(0).to_sql(con=engine, ...) creates or replaces the empty typed table via SQLAlchemy/pandas. This commit happens on the engine connection and preserves if_exists, dtype, and index semantics.
COPY phase — row data is streamed via psycopg COPY FROM STDIN on a separate self.connect() connection (D-03), which is committed after the COPY block closes.

D-04 two-phase contract for ``if_exists=’replace’``: The DDL commit (table dropped and recreated empty) happens before the COPY load. If the COPY step fails, the table will exist but be empty. This matches the pre-existing replace semantics (“drop and rebuild”) and has always been the observable behaviour of from_dataframe; it is now made explicit.

Parameters:

df (pd.DataFrame) – pandas DataFrame.
table (str) – Table name.
schema (str, optional) – Schema name, by default “public”.
if_exists ({'fail', 'replace', 'append'}, optional) – What to do if table exists, by default “fail”.
primary_key (str or list of str, optional) – Column(s) to set as primary key after creation.
index (bool, optional) – Write DataFrame index as a column, by default False. The index column(s) are promoted via reset_index() so they appear identically in both the DDL schema and the COPY stream (D-01a).
dtype (dict, optional) – Dict of column name to SQLAlchemy types.

to_dataframe(table: str | None = None, *, schema: str = 'public', sql: str | None = None, params: Params = None) → pd.DataFrame[source]¶

Read table or query into pandas DataFrame.

Parameters:

table (str, optional) – Table name (mutually exclusive with sql).
schema (str, optional) – Schema name, by default “public”.
sql (str, optional) – SQL query (mutually exclusive with table).
params (Params, optional) – Query parameters for sql. This method routes through SQLAlchemy text() / pd.read_sql, which uses the :name (colon-prefix) named-parameter style — not the %(name)s style used by the psycopg-backed execute / fetch_* methods. When passing a mapping, use :name placeholders in sql, e.g. "SELECT :x AS v" with params={"x": 9}.

Returns:

pandas DataFrame.

Return type:

pd.DataFrame

from_geodataframe(gdf: gpd.GeoDataFrame, table: str, *, schema: str = 'public', if_exists: Literal['fail', 'replace', 'append'] = 'fail', primary_key: str | list[str] | None = None, spatial_index: bool = True, geometry_column: str = 'geometry', srid: int | None = None) → None[source]¶

Create or append to table from GeoDataFrame.

Requires PostGIS extension.

Parameters:

gdf (gpd.GeoDataFrame) – geopandas GeoDataFrame.
table (str) – Table name.
schema (str, optional) – Schema name, by default “public”.
if_exists ({'fail', 'replace', 'append'}, optional) – What to do if table exists, by default “fail”.
primary_key (str or list of str, optional) – Column(s) for primary key.
spatial_index (bool, optional) – Create GIST spatial index on geometry, by default True.
geometry_column (str, optional) – Name of geometry column, by default “geometry”.
srid (int, optional) – Override SRID (extracted from CRS if not specified).

Raises:

ExtensionNotAvailableError – If PostGIS extension is not installed.

to_geodataframe(table: str | None = None, *, schema: str = 'public', sql: str | None = None, geometry_column: str = 'geometry', params: dict[str, Any] | None = None) → gpd.GeoDataFrame[source]¶

Read table or query into GeoDataFrame.

Parameters:

table (str, optional) – Table name (mutually exclusive with sql).
schema (str, optional) – Schema name, by default “public”.
sql (str, optional) – SQL query (mutually exclusive with table).
geometry_column (str, optional) – Name of geometry column, by default “geometry”.
params (dict, optional) – Query parameters.

Returns:

geopandas GeoDataFrame.

Return type:

gpd.GeoDataFrame

close() → None[source]¶: Close database connections.

Async Database class for pycopg.

Provides async/await interface for PostgreSQL operations using psycopg’s async support.

class pycopg.async_database.AsyncDatabase(config: Config)[source]¶

Bases: DatabaseBase, QueryMixin

Async PostgreSQL interface.

Provides async/await versions of Database methods using psycopg’s AsyncConnection.

config¶

Database connection configuration.

Type:: Config

__init__(config: Config)[source]¶

Initialize async database connection.

Parameters:: config (Config) – Database configuration.

property async_engine: AsyncEngine¶: Get or create async SQLAlchemy engine (lazy initialization).

property spatial: AsyncSpatialAccessor¶

Get or create the async spatial accessor (lazy initialization).

The PostGIS guard is deferred to the first helper call (an async check cannot run inside a property).

Returns:: Async spatial helper namespace bound to this database.
Return type:: AsyncSpatialAccessor

property etl: AsyncETLAccessor¶

Get or create the async ETL run-tracking accessor (lazy initialization).

The accessor is created on first access and cached for subsequent calls. No PostGIS or extension guard is applied — ETL run-tracking is core functionality, not an extension (D-08).

Returns:: Async ETL helper namespace bound to this database.
Return type:: AsyncETLAccessor

property timescale: AsyncTimescaleAccessor¶

Get or create the async TimescaleDB accessor (lazy initialization).

Provides async access to TimescaleDB operations such as hypertable management, compression, and retention policies. The accessor is created on first access and cached for subsequent calls.

Returns:: Async TimescaleDB helper namespace bound to this database.
Return type:: AsyncTimescaleAccessor

property admin: AsyncAdminAccessor¶

Get or create the async admin accessor (lazy initialization).

Provides async access to role and permission management operations. The accessor is created on first access and cached for subsequent calls.

Returns:: Async admin helper namespace bound to this database.
Return type:: AsyncAdminAccessor

property maint: AsyncMaintAccessor¶

Get or create the async maintenance accessor (lazy initialization).

Provides async access to size, vacuum, analyze, and explain operations. The accessor is created on first access and cached for subsequent calls.

Returns:: Async maintenance helper namespace bound to this database.
Return type:: AsyncMaintAccessor

property backup: AsyncBackupAccessor¶

Get or create the async backup accessor (lazy initialization).

Provides async access to pg_dump, pg_restore, and CSV copy operations. The accessor is created on first access and cached for subsequent calls.

Returns:: Async backup helper namespace bound to this database.
Return type:: AsyncBackupAccessor

property schema: AsyncSchemaAccessor¶

Get or create the async schema accessor (lazy initialization).

Provides async access to DDL and introspection operations such as database, extension, schema, table, column, constraint, and index management. The accessor is created on first access and cached for subsequent calls.

Returns:: Async schema helper namespace bound to this database.
Return type:: AsyncSchemaAccessor

async classmethod create(name: str, host: str = 'localhost', port: int = 5432, user: str = 'postgres', password: str = '', owner: str | None = None, template: str = 'template1', if_not_exists: bool = True) → AsyncDatabase[source]¶

Create a new database and return a connection to it.

This is a convenience method that: 1. Connects to the ‘postgres’ database 2. Creates the new database 3. Returns an AsyncDatabase instance connected to the new database

Parameters:

name (str) – Name of the database to create.
host (str, optional) – Database host, by default “localhost”.
port (int, optional) – Database port, by default 5432.
user (str, optional) – Database user, by default “postgres”.
password (str, optional) – Database password.
owner (str, optional) – Owner role for the new database.
template (str, optional) – Template database, by default “template1”.
if_not_exists (bool, optional) – If True, don’t error if database already exists, by default True.

Returns:

Instance connected to the newly created database.

Return type:

AsyncDatabase

Raises:

DatabaseExistsError – If the database already exists and if_not_exists is False.

async classmethod create_from_env(name: str, owner: str | None = None, template: str = 'template1', if_not_exists: bool = True, dotenv_path: str | Path | None = None) → AsyncDatabase[source]¶

Create a new database using connection params from environment.

Uses PGHOST, PGPORT, PGUSER, PGPASSWORD from environment or .env file, then creates the database and returns a connection to it.

Parameters:

name (str) – Name of the database to create.
owner (str, optional) – Owner role for the new database.
template (str, optional) – Template database, by default “template1”.
if_not_exists (bool, optional) – If True, don’t error if database already exists, by default True.
dotenv_path (str or Path, optional) – Path to .env file.

Returns:

Instance connected to the newly created database.

Return type:

AsyncDatabase

connect(autocommit: bool = False) → AsyncIterator[AsyncConnection][source]¶

Async context manager for connection.

Parameters:: autocommit (bool, optional) – Enable autocommit mode (required for CREATE DATABASE, etc.), by default False.
Yields:: AsyncConnection – psycopg AsyncConnection object.

cursor(autocommit: bool = False) → AsyncIterator[AsyncCursor[dict[str, Any]]][source]¶

Async context manager for cursor with dict rows.

Parameters:: autocommit (bool, optional) – Enable autocommit mode, by default False.
Yields:: AsyncCursor – psycopg AsyncCursor with dict_row factory.

session(autocommit: bool = False, *, isolation_level: IsolationLevel | None = None) → AsyncIterator[AsyncDatabase][source]¶

Async context manager for session mode with connection reuse.

In session mode, all operations reuse the same connection, significantly reducing overhead for multiple sequential operations.

Parameters:

autocommit (bool, optional) – Enable autocommit mode for the session, by default False.
isolation_level (IsolationLevel or None, optional) – Transaction isolation level set on the session connection immediately after connecting and before any transaction opens. Keyword-only. When None (default), the server default is used.

Yields:

AsyncDatabase – Self (AsyncDatabase instance with active session).

Raises:

RuntimeError – If already inside an active session.

property in_session: bool¶

Check if currently in session mode.

Returns:: True if in session mode, False otherwise.
Return type:: bool

savepoint(*, name: str | None = None) → AsyncIterator[None][source]¶

Async nested savepoint within an active session.

Parameters:: name (str or None, optional) – Savepoint name. When None (default), psycopg auto-generates a name. Keyword-only. The name reaches psycopg’s parameterized savepoint API and is never string-interpolated into SQL.
Yields:: None
Raises:: RuntimeError – If called outside an active db.session().

async health() → dict[str, Any][source]¶

Return a health-check dict for this database connection.

Opens a fresh async connection, executes SELECT 1, and measures the round-trip latency. The method never raises — on any failure it returns a dict with connected=False and the exception message in the error key, so monitoring loops can call it unconditionally.

Returns:

On success:

{
    "connected": True,
    "latency_ms": <float>,       # round-trip in milliseconds
    "server_version": "<str>",   # e.g. "16.1"
}

On failure:

{
    "connected": False,
    "latency_ms": None,
    "server_version": None,
    "error": "<exception message>",
}

Return type:

Notes

Examples

>>> status = await db.health()
>>> status["connected"]
True

transaction(*, isolation_level: IsolationLevel | None = None, savepoint_name: str | None = None) → AsyncIterator[AsyncConnection][source]¶

Async context manager for transactions.

Automatically commits on success, rolls back on exception. If a session is active, reuses the session connection.

Parameters:

isolation_level (IsolationLevel or None, optional) – Transaction isolation level (e.g. IsolationLevel.SERIALIZABLE). Keyword-only. When None (default), the server default is used. Raises RuntimeError if called inside an active db.session() (cannot change isolation mid-session — use db.session(isolation_level=...) instead).
savepoint_name (str or None, optional) – Named savepoint to open via psycopg’s native conn.transaction(savepoint_name=...) API. Keyword-only. When None (default), no savepoint is created. Inside a session, this creates a nested savepoint without unwinding the outer transaction.

Yields:

AsyncConnection – psycopg AsyncConnection in a transaction.

Raises:

RuntimeError – If isolation_level is set while inside an active db.session().

async execute(sql: str, params: Sequence[Any] | Mapping[str, Any] | None = None, autocommit: bool = False) → list[dict[str, Any]][source]¶

Execute SQL and return results as list of dicts.

Parameters:

sql (str) – SQL query to execute.
params (Params, optional) – Query parameters — either a positional sequence (%s placeholders) or a named mapping (%(name)s placeholders). psycopg binds both forms natively without any translation.
autocommit (bool, optional) – Enable autocommit mode, by default False.

Returns:

List of result rows as dicts.

Return type:

list of dict

async execute_many(sql: str, params_seq: Sequence[Sequence[Any]]) → int[source]¶

Execute SQL for multiple parameter sets.

Uses psycopg’s executemany() for better performance than sequential execute() calls.

Parameters:

sql (str) – SQL query with placeholders.
params_seq (Sequence of Sequence) – Sequence of parameter sequences.

Returns:

Total number of affected rows.

Return type:

async insert_batch(table: str, rows: list[dict[str, Any]], *, schema: str = 'public', on_conflict: str | None = None, batch_size: int | None = None) → int[source]¶

Insert multiple rows efficiently using batch VALUES.

This method builds a single INSERT with multiple VALUES tuples, which is significantly faster than individual INSERT statements. For very large datasets (>10000 rows), consider using copy_insert().

Parameters:

table (str) – Table name.
rows (list of dict) – List of row dicts (all must have same keys).
schema (str, optional) – Schema name, by default “public”.
on_conflict (str, optional) – ON CONFLICT clause (e.g., “DO NOTHING”, “(id) DO UPDATE SET name = EXCLUDED.name”).
batch_size (int, optional) – Max rows per INSERT statement, by default from config.

Returns:

Total number of rows inserted.

Return type:

async copy_insert(table: str, rows: list[dict[str, Any]], *, schema: str = 'public', columns: list[str] | None = None) → int[source]¶

Insert rows using PostgreSQL COPY protocol.

This is the fastest method for bulk inserts (10-100x faster than regular INSERT for large datasets). Best for >10000 rows.

Note: COPY doesn’t support ON CONFLICT. For upserts, use insert_batch().

Parameters:

table (str) – Table name.
rows (list of dict) – List of row dicts.
schema (str, optional) – Schema name, by default “public”.
columns (list of str, optional) – Column names. If not provided, uses keys from first row.

Returns:

Number of rows inserted.

Return type:

async fetch_one(sql: str, params: Sequence[Any] | Mapping[str, Any] | None = None) → dict[str, Any] | None[source]¶

Execute SQL and return single row.

Parameters:

sql (str) – SQL query.
params (Params, optional) – Query parameters — either a positional sequence (%s placeholders) or a named mapping (%(name)s placeholders). psycopg binds both forms natively without any translation.

Returns:

Single row as dict, or None.

Return type:

dict or None

async fetch_val(sql: str, params: Sequence[Any] | Mapping[str, Any] | None = None) → Any[source]¶

Execute SQL and return single value.

Parameters:

sql (str) – SQL query returning single column.
params (Params, optional) – Query parameters — either a positional sequence (%s placeholders) or a named mapping (%(name)s placeholders). psycopg binds both forms natively without any translation.

Returns:

Single value, or None.

Return type:

Any

async fetch_all(sql: str, params: Sequence[Any] | Mapping[str, Any] | None = None) → list[dict[str, Any]][source]¶

Execute SQL and return all rows as a list of dicts.

Parameters:

sql (str) – SQL query. The caller owns the SQL string and must parameterize dynamic values via params (same trust model as execute() / fetch_one()).
params (Params, optional) – Query parameters — either a positional sequence (%s placeholders) or a named mapping (%(name)s placeholders). psycopg binds both forms natively without any translation.

Returns:

All result rows as dicts (keyed by column name). Returns [] for queries that produce no rows or no description.

Return type:

list of dict

Notes

async exists(table: str, where: dict[str, Any] | None = None, *, where_sql: str | None = None, where_params: Sequence[Any] | None = None, schema: str = 'public') → bool[source]¶

Check whether any row matching the given equality conditions exists.

Parameters:

table (str) – Table name.
where (dict, optional) – Equality conditions as column-name to value mapping. Mutually exclusive with where_sql (D-02). Exactly one of where / where_sql must resolve to a non-empty predicate — an existence check with no predicate is meaningless.
where_sql (str, optional) – Raw SQL WHERE fragment (without the WHERE keyword), for predicates the where dict cannot express. This is a documented escape hatch: the fragment is passed through verbatim and is not run through identifier validation — the caller owns the SQL text. Only where_params are treated as untrusted values and bound as %s.
where_params (Sequence, optional) – Positional %s parameters bound against where_sql. Requires where_sql to be present (D-04).
schema (str, optional) – Schema name, by default “public”.

Returns:

True if at least one matching row exists, False otherwise.

Return type:

Raises:

ValueError – If neither where nor where_sql resolves to a non-empty predicate (guard fires before any SQL or cursor is opened); if both where and where_sql are supplied (D-02); if where_params is supplied without where_sql (D-04); or if where_sql is empty/whitespace-only (D-03).
InvalidIdentifierError – If any column name or the table/schema identifier is invalid.

async count(table: str, *, where: dict[str, Any] | None = None, where_sql: str | None = None, where_params: Sequence[Any] | None = None, schema: str = 'public') → int[source]¶

Count rows in a table, optionally filtered by equality conditions.

Parameters:

table (str) – Table name.
where (dict, optional) – Equality conditions as column-name to value mapping. When neither where nor where_sql is given, all rows are counted without a WHERE clause. Mutually exclusive with where_sql (D-02).
where_sql (str, optional) – Raw SQL WHERE fragment (without the WHERE keyword), for predicates the where dict cannot express (e.g. "age > %s AND status = ANY(%s)"). This is a documented escape hatch: the fragment is passed through verbatim and is not run through identifier validation — the caller owns the SQL text. Only where_params are treated as untrusted values and bound as %s.
where_params (Sequence, optional) – Positional %s parameters bound against where_sql. Requires where_sql to be present (D-04).
schema (str, optional) – Schema name, by default “public”.

Returns:

Number of matching (or total) rows.

Return type:

Raises:

ValueError – If both where and where_sql are supplied (D-02); if where_params is supplied without where_sql (D-04); or if where_sql is empty/whitespace-only (D-03).
InvalidIdentifierError – If any column name or the table/schema identifier is invalid.

Select rows matching the given equality conditions.

Parameters:

table (str) – Table name.
where (dict, optional) – Equality conditions as column-name to value mapping. When neither where nor where_sql is given, all rows are returned without a WHERE clause (D-03). Mutually exclusive with where_sql (D-02).
where_sql (str, optional) – Raw SQL WHERE fragment (without the WHERE keyword), for predicates the where dict cannot express (e.g. "age > %s AND status = ANY(%s)"). This is a documented escape hatch: the fragment is passed through verbatim and is not run through identifier validation — the caller owns the SQL text. Only where_params are treated as untrusted values and bound as %s.
where_params (Sequence, optional) – Positional %s parameters bound against where_sql. Requires where_sql to be present (D-04).
columns (list of str, optional) – Column names to project, by default None (SELECT *).
order_by (str, optional) – ORDER BY clause. Like where_sql, this is a documented, caller-owned raw-SQL escape hatch — it is appended verbatim and is not run through identifier validation, so multi-column syntax such as "col1 DESC, col2 ASC" is accepted by design.
limit (int, optional) – Maximum number of rows to return.
offset (int, optional) – Number of rows to skip.
schema (str, optional) – Schema name, by default “public”.

Returns:

Matching rows (or all rows if no predicate), as dicts.

Return type:

list of dict

Raises:

ValueError – If both where and where_sql are supplied (D-02); if where_params is supplied without where_sql (D-04); if where_sql is empty/whitespace-only (D-03); or if limit or offset is negative (WR-02).
InvalidIdentifierError – If any column name or the table/schema identifier is invalid.

Select rows matching the given equality conditions (alias of select_where()).

Parameters:

table (str) – Table name.
where (dict, optional) – Equality conditions as column-name to value mapping.
where_sql (str, optional) – Raw SQL WHERE fragment escape hatch (see select_where()).
where_params (Sequence, optional) – Positional %s parameters bound against where_sql.
columns (list of str, optional) – Column names to project, by default None (SELECT *).
order_by (str, optional) – ORDER BY clause (caller-owned raw-SQL escape hatch).
limit (int, optional) – Maximum number of rows to return.
offset (int, optional) – Number of rows to skip.
schema (str, optional) – Schema name, by default “public”.

Returns:

Matching rows (or all rows if no predicate), as dicts.

Return type:

list of dict

Select the first row matching the given equality conditions.

Parameters:

table (str) – Table name.
where (dict, optional) – Equality conditions as column-name to value mapping. When neither where nor where_sql is given, any one row (or None if the table is empty) is returned. Mutually exclusive with where_sql (D-02).
where_sql (str, optional) – Raw SQL WHERE fragment (without the WHERE keyword), for predicates the where dict cannot express. This is a documented escape hatch: the fragment is passed through verbatim and is not run through identifier validation — the caller owns the SQL text. Only where_params are treated as untrusted values and bound as %s.
where_params (Sequence, optional) – Positional %s parameters bound against where_sql. Requires where_sql to be present (D-04).
columns (list of str, optional) – Column names to project, by default None (SELECT *).
order_by (str, optional) – ORDER BY clause, making the “first” match deterministic. Like where_sql, this is a documented, caller-owned raw-SQL escape hatch — not run through identifier validation.
schema (str, optional) – Schema name, by default “public”.

Returns:

The first matching row as a dict, or None if no row matches.

Return type:

dict or None

Raises:

ValueError – If both where and where_sql are supplied (D-02); if where_params is supplied without where_sql (D-04); or if where_sql is empty/whitespace-only (D-03).
InvalidIdentifierError – If any column name or the table/schema identifier is invalid.

Select the first row matching the given equality conditions (alias of get()).

Parameters:

table (str) – Table name.
where (dict, optional) – Equality conditions as column-name to value mapping.
where_sql (str, optional) – Raw SQL WHERE fragment escape hatch (see get()).
where_params (Sequence, optional) – Positional %s parameters bound against where_sql.
columns (list of str, optional) – Column names to project, by default None (SELECT *).
order_by (str, optional) – ORDER BY clause, making the “first” match deterministic (caller-owned raw-SQL escape hatch).
schema (str, optional) – Schema name, by default “public”.

Returns:

The first matching row as a dict, or None if no row matches.

Return type:

dict or None

async paginate(table: str, limit: int, *, offset: int = 0, order_by: str | list[str] | None = None, where: dict[str, Any] | None = None, where_sql: str | None = None, where_params: Sequence[Any] | None = None, descending: bool = False, schema: str = 'public') → list[dict[str, Any]][source]¶

Return a page of rows from a table.

Parameters:

table (str) – Table name.
limit (int) – Maximum number of rows to return. Cast to int before use.
offset (int, optional) – Number of rows to skip, by default 0. Cast to int before use.
order_by (str or list of str, optional) – Column name(s) to sort by. Each name is validated via validate_identifiers before interpolation. Whole-clause direction is controlled by descending.
where (dict, optional) – Equality conditions as column-name to value mapping. When neither where nor where_sql is given, all rows are returned without a WHERE clause. Mutually exclusive with where_sql (D-02).
where_sql (str, optional) – Raw SQL WHERE fragment (without the WHERE keyword), for predicates the where dict cannot express. This is a documented escape hatch: the fragment is passed through verbatim and is not run through identifier validation — the caller owns the SQL text. Only where_params are treated as untrusted values and bound as %s.
where_params (Sequence, optional) – Positional %s parameters bound against where_sql. Requires where_sql to be present (D-04).
descending (bool, optional) – When True, append DESC to the ORDER BY clause (applies to all listed columns). Per-column direction is deferred.
schema (str, optional) – Schema name, by default “public”.

Returns:

Requested page as a list of row dicts. Returns [] when the query produces no rows.

Return type:

list of dict

Raises:

ValueError – If both where and where_sql are supplied (D-02); if where_params is supplied without where_sql (D-04); or if where_sql is empty/whitespace-only (D-03).
InvalidIdentifierError – If any column name in order_by or the table/schema identifier is invalid.

async paginate_page(table: str, limit: int, *, offset: int = 0, order_by: str | list[str] | None = None, where: dict[str, Any] | None = None, where_sql: str | None = None, where_params: Sequence[Any] | None = None, with_total: bool = False, descending: bool = False, schema: str = 'public') → Page[source]¶

Return an offset page wrapped in a Page envelope (D-09).

Parameters:

table (str) – Table name.
limit (int) – Maximum number of rows to return in the page. Cast to int before use.
offset (int, optional) – Number of rows to skip, by default 0. Cast to int before use.
order_by (str or list of str, optional) – Column name(s) to sort by. Each name is validated via validate_identifiers before interpolation. Whole-clause direction is controlled by descending.
where (dict, optional) – Equality conditions as column-name to value mapping. Mutually exclusive with where_sql (D-02).
where_sql (str, optional) – Raw SQL WHERE fragment (without the WHERE keyword), for predicates the where dict cannot express. Documented escape hatch: passed through verbatim, never run through identifier validation — the caller owns the SQL text. Only where_params are treated as untrusted values and bound as %s.
where_params (Sequence, optional) – Positional %s parameters bound against where_sql. Requires where_sql to be present (D-04).
with_total (bool, optional) – When True, run a separate COUNT(*) (same predicate) and populate Page.total. By default False — avoids a surprise COUNT on deep pages (D-10).
descending (bool, optional) – When True, append DESC to the ORDER BY clause.
schema (str, optional) – Schema name, by default “public”.

Returns:

rows (trimmed to limit), total (int when with_total=True else None), has_next, and next_cursor (always None — offset pagination has no cursor concept).

Return type:

Raises:

ValueError – If both where and where_sql are supplied (D-02); if where_params is supplied without where_sql (D-04); if where_sql is empty/whitespace-only (D-03); if order_by is an empty list (WR-01); or if limit is not a positive integer (IN-02).
InvalidIdentifierError – If any column name in order_by or the table/schema identifier is invalid.

async paginate_keyset(table: str, limit: int, *, order_by: str | list[str] | None = None, after: Sequence[Any] | None = None, where: dict[str, Any] | None = None, where_sql: str | None = None, where_params: Sequence[Any] | None = None, with_total: bool = False, descending: bool = False, schema: str = 'public') → Page[source]¶

Return a keyset (seek) page wrapped in a Page envelope (D-05).

Parameters:

table (str) – Table name.
limit (int) – Maximum number of rows to return in the page. Cast to int before use.
order_by (str or list of str) – Column name(s) defining the seek order. Mandatory (D-07) — raises ValueError if omitted. Each name is validated via validate_identifiers before interpolation. Columns should be NOT NULL (IN-01) — see caveat above.
after (Sequence, optional) – Cursor values, positionally keyed to order_by (D-06). Omit (or pass None) to fetch the first page — the keyset fragment is skipped entirely (ORDER BY + LIMIT only). Must have exactly len(order_by) values.
where (dict, optional) – Equality conditions as column-name to value mapping. Mutually exclusive with where_sql (D-02). ANDed with the keyset fragment when both are present.
where_sql (str, optional) – Raw SQL WHERE fragment (without the WHERE keyword), for predicates the where dict cannot express. Documented escape hatch: passed through verbatim, never run through identifier validation — the caller owns the SQL text. Only where_params are treated as untrusted values and bound as %s.
where_params (Sequence, optional) – Positional %s parameters bound against where_sql. Requires where_sql to be present (D-04).
with_total (bool, optional) – When True, run a separate COUNT(*) using only the base predicate (where/where_sql, not the keyset after fragment — the total is the full filtered set, not the remaining-after-cursor set) and populate Page.total. By default False (D-10).
descending (bool, optional) – When True, flips the row-value comparison operator to < and appends DESC to ORDER BY — both must agree (Pitfall 3). By default False.
schema (str, optional) – Schema name, by default “public”.

Returns:

Return type:

Raises:

ValueError – If order_by is omitted or an empty list (D-07/WR-01); if after is supplied and len(after) != len(order_by); if both where and where_sql are supplied (D-02); if where_params is supplied without where_sql (D-04); if where_sql is empty/whitespace-only (D-03); or if limit is not a positive integer (IN-02).
InvalidIdentifierError – If any column name in order_by or the table/schema identifier is invalid.

async to_dataframe(table: str | None = None, *, schema: str = 'public', sql: str | None = None, params: Params = None) → pd.DataFrame[source]¶

Read table or query into pandas DataFrame.

Parameters:

table (str, optional) – Table name (mutually exclusive with sql).
schema (str, optional) – Schema name, by default “public”.
sql (str, optional) – SQL query (mutually exclusive with table).
params (Params, optional) – Query parameters for sql. This method routes through SQLAlchemy text() / pd.read_sql, which uses the :name (colon-prefix) named-parameter style — not the %(name)s style used by the psycopg-backed execute / fetch_* methods. When passing a mapping, use :name placeholders in sql, e.g. "SELECT :x AS v" with params={"x": 9}.

Returns:

pandas DataFrame.

Return type:

pd.DataFrame

async from_dataframe(df: pd.DataFrame, table: str, *, schema: str = 'public', if_exists: Literal['fail', 'replace', 'append'] = 'fail', primary_key: str | list[str] | None = None, index: bool = False, dtype: dict[str, Any] | None = None) → None[source]¶

Create or append to table from pandas DataFrame.

Uses a two-phase Hybrid DDL+COPY strategy (D-01/D-04):

DDL phase — df_ddl.head(0).to_sql(con=sync_conn, ...) creates or replaces the empty typed table via the async engine’s run_sync bridge (preserving if_exists, dtype, and index semantics without re-implementing them).
COPY phase — row data is streamed via psycopg AsyncCopy FROM STDIN on a separate async with self.connect() connection (D-03), which is committed after the COPY block closes.

Parameters:

df (pd.DataFrame) – pandas DataFrame.
table (str) – Table name.
schema (str, optional) – Schema name, by default “public”.
if_exists ({'fail', 'replace', 'append'}, optional) – What to do if table exists, by default “fail”.
primary_key (str or list of str, optional) – Column(s) to set as primary key after creation.
index (bool, optional) – Write DataFrame index as a column, by default False. The index column(s) are promoted via reset_index() so they appear identically in both the DDL schema and the COPY stream (D-01a).
dtype (dict, optional) – Dict of column name to SQLAlchemy types.

async to_geodataframe(table: str | None = None, *, schema: str = 'public', sql: str | None = None, geometry_column: str = 'geometry', params: dict[str, Any] | None = None) → gpd.GeoDataFrame[source]¶

Read table or query into GeoDataFrame.

Parameters:

table (str, optional) – Table name (mutually exclusive with sql).
schema (str, optional) – Schema name, by default “public”.
sql (str, optional) – SQL query (mutually exclusive with table).
geometry_column (str, optional) – Name of geometry column, by default “geometry”.
params (dict, optional) – Query parameters.

Returns:

geopandas GeoDataFrame.

Return type:

gpd.GeoDataFrame

async from_geodataframe(gdf: gpd.GeoDataFrame, table: str, *, schema: str = 'public', if_exists: Literal['fail', 'replace', 'append'] = 'fail', primary_key: str | list[str] | None = None, spatial_index: bool = True, geometry_column: str = 'geometry', srid: int | None = None) → None[source]¶

Create or append to table from GeoDataFrame.

Requires PostGIS extension.

Parameters:

gdf (gpd.GeoDataFrame) – geopandas GeoDataFrame.
table (str) – Table name.
schema (str, optional) – Schema name, by default “public”.
if_exists ({'fail', 'replace', 'append'}, optional) – What to do if table exists, by default “fail”.
primary_key (str or list of str, optional) – Column(s) for primary key.
spatial_index (bool, optional) – Create GIST spatial index on geometry, by default True.
geometry_column (str, optional) – Name of geometry column, by default “geometry”.
srid (int, optional) – Override SRID (extracted from CRS if not specified).

Raises:

ExtensionNotAvailableError – If PostGIS extension is not installed.

async insert_many(table: str, rows: list[dict[str, Any]], *, schema: str = 'public', on_conflict: str | None = None) → int[source]¶

Insert multiple rows efficiently.

Parameters:

table (str) – Table name.
rows (list of dict) – List of row dicts.
schema (str, optional) – Schema name, by default “public”.
on_conflict (str, optional) – ON CONFLICT clause (e.g., “DO NOTHING”, “DO UPDATE SET …”).

Returns:

Number of rows inserted.

Return type:

async upsert_many(table: str, rows: list[dict[str, Any]], conflict_columns: list[str], *, update_columns: list[str] | None = None, schema: str = 'public') → int[source]¶

Upsert (insert or update) multiple rows.

Parameters:

table (str) – Table name.
rows (list of dict) – List of row dicts.
conflict_columns (list of str) – Columns that define uniqueness.
update_columns (list of str, optional) – Columns to update on conflict (None = all except conflict).
schema (str, optional) – Schema name, by default “public”.

Returns:

Number of rows affected.

Return type:

async upsert(table: str, row: dict[str, Any], conflict_columns: list[str], *, update_columns: list[str] | None = None, schema: str = 'public') → dict[str, Any] | None[source]¶

Upsert a single row and return the affected row.

Parameters:

table (str) – Table name.
row (dict) – Row data as column-name to value mapping.
conflict_columns (list of str) – Columns that define uniqueness for the ON CONFLICT target.
update_columns (list of str, optional) – Columns to update on conflict. Defaults to all non-conflict columns.
schema (str, optional) – Schema name, by default “public”.

Returns:

Return type:

dict or None

Raises:

ValueError – If update_columns resolves to an empty list — i.e. all columns in row are also listed in conflict_columns and no explicit update_columns override is given.

async insert(table: str, row: dict[str, Any], *, schema: str = 'public', on_conflict: str | None = None) → dict[str, Any] | None[source]¶

Insert a single row and return the inserted row.

Parameters:

table (str) – Table name.
row (dict) – Row data as column-name to value mapping.
schema (str, optional) – Schema name, by default “public”.
on_conflict (str, optional) – ON CONFLICT clause (e.g., “DO NOTHING”, “DO UPDATE SET …”), threaded verbatim into the INSERT statement.

Returns:

The inserted row as a dict (via RETURNING *). Under on_conflict="DO NOTHING", if a pre-existing row conflicts, no row is returned by RETURNING * and this method returns None (D-07).

Return type:

dict or None

Raises:

ValueError – If row is empty (WR-01) — an empty dict would otherwise emit invalid SQL (INSERT INTO t () VALUES ()).

async delete_where(table: str, where: dict[str, Any] | None = None, *, where_sql: str | None = None, where_params: Sequence[Any] | None = None, schema: str = 'public') → int[source]¶

Delete rows matching the given equality conditions.

Parameters:

table (str) – Table name.
where (dict, optional) – Equality conditions as column-name to value mapping. Mutually exclusive with where_sql (D-02). Exactly one of where / where_sql must resolve to a non-empty predicate — use db.schema.truncate_table to affect all rows.
where_sql (str, optional) – Raw SQL WHERE fragment (without the WHERE keyword), for predicates the where dict cannot express (e.g. "created_at < now() - %s::interval"). This is a documented escape hatch: the fragment is passed through verbatim and is not run through identifier validation — the caller owns the SQL text. Only where_params are treated as untrusted values and bound as %s.
where_params (Sequence, optional) – Positional %s parameters bound against where_sql. Requires where_sql to be present (D-04).
schema (str, optional) – Schema name, by default “public”.

Returns:

Number of rows deleted.

Return type:

Raises:

async update_where(table: str, values: dict[str, Any], where: dict[str, Any] | None = None, *, where_sql: str | None = None, where_params: Sequence[Any] | None = None, schema: str = 'public') → int[source]¶

Update rows matching the given equality conditions.

Parameters:

table (str) – Table name.
values (dict) – Column-name to new-value mapping for the SET clause. Must be non-empty.
where (dict, optional) – Equality conditions as column-name to value mapping. Mutually exclusive with where_sql (D-02). Exactly one of where / where_sql must resolve to a non-empty predicate — use execute with an explicit UPDATE statement to affect all rows.
where_sql (str, optional) – Raw SQL WHERE fragment (without the WHERE keyword), for predicates the where dict cannot express. This is a documented escape hatch: the fragment is passed through verbatim and is not run through identifier validation — the caller owns the SQL text. Only where_params are treated as untrusted values and bound as %s.
where_params (Sequence, optional) – Positional %s parameters bound against where_sql. Requires where_sql to be present (D-04).
schema (str, optional) – Schema name, by default “public”.

Returns:

Number of rows updated.

Return type:

Raises:

async stream(sql: str, params: Sequence[Any] | None = None, *, batch_size: int = 1000) → AsyncIterator[dict[str, Any]][source]¶

Stream query results in batches.

Memory-efficient way to process large result sets.

Parameters:

sql (str) – SQL query.
params (Sequence, optional) – Query parameters.
batch_size (int, optional) – Rows to fetch per batch, by default 1000.

Yields:

dict – Row dicts.

async listen(channel: str) → AsyncIterator[str][source]¶

Listen for notifications on a channel.

Parameters:: channel (str) – Channel name.
Yields:: str – Notification payloads.

async notify(channel: str, *, payload: str = '') → None[source]¶

Send notification on a channel.

Parameters:

channel (str) – Channel name.
payload (str, optional) – Notification payload (max 8000 bytes), by default “”.

async close() → None[source]¶

Close database connections.

Disposes the async SQLAlchemy engine (releasing pooled connections) if one was created. Per-operation connections are opened and closed on demand, so only the lazily-created engine needs disposal here. Idempotent: safe to call when no engine exists or repeatedly.

PostGIS spatial helpers: pure SQL builders and geometry resolution.

This module provides the pure foundation of the db.spatial accessor namespace: one module-level SQL builder per spatial helper, plus the single internal geometry resolver shared by all of them. Builders are stateless functions returning (sql, params) tuples — no self, no I/O, no DB — so they are shared byte-identical between the sync and async accessors and are fully unit-testable without a database.

Security invariants (Phase 10 / hotfix v0.3.1):

Every identifier (table, schema, geometry column, ref table/column, columns= entries) passes pycopg.utils.validate_identifiers() before any string interpolation.
Every user value (coordinates, WKT, GeoJSON, distances, k, to_srid) is emitted as a %s placeholder appended to the params list — never f-string interpolated.
SRID is the only directly interpolated value, and only through an int(srid) coercion (integer cast is injection-safe).

The filter= parameter is a raw SQL fragment following the existing _build_select_sql convention — values inside it are the caller’s responsibility (T-14-04 accepted limitation).

pycopg.spatial.build_contains_sql(table: str, geom: str = 'geometry', schema: str = 'public', *, point: tuple[float, float] | None = None, wkt: str | None = None, geojson: dict[str, Any] | None = None, ref: tuple[str, str] | None = None, srid: int = 4326, columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → tuple[str, list[Any]][source]¶

Build SQL selecting rows whose geometry contains the input geometry.

Generates ST_Contains(t.{geom}, <geom_in>). The ref= form uses an EXISTS subquery (D-08).

Parameters:

table (str) – Table to query (aliased t).
geom (str, optional) – Geometry column name, by default “geometry” (D-06).
schema (str, optional) – Schema name, by default “public”.
point (tuple of float, optional) – (x, y) input geometry (one of the four D-05 forms).
wkt (str, optional) – WKT input geometry.
geojson (dict, optional) – GeoJSON input geometry.
ref (tuple of str, optional) – (ref_table, ref_col) input geometry (EXISTS semantics).
srid (int, optional) – SRID of the input geometry, by default 4326 (D-07).
columns (list of str, optional) – Columns to select, by default all (D-03).
filter (str, optional) – Additional raw SQL filter combined as AND (...) (D-11).
order_by (str, optional) – ORDER BY clause body (D-12).
limit (int, optional) – LIMIT value (D-12).

Returns:

SQL string and parameter list.

Return type:

tuple of (str, list)

Raises:

ValueError – If the geometry input forms are not mutually exclusive.
InvalidIdentifierError – If any identifier is invalid.

pycopg.spatial.build_intersects_sql(table: str, geom: str = 'geometry', schema: str = 'public', *, point: tuple[float, float] | None = None, wkt: str | None = None, geojson: dict[str, Any] | None = None, ref: tuple[str, str] | None = None, srid: int = 4326, columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → tuple[str, list[Any]][source]¶

Build SQL selecting rows whose geometry intersects the input geometry.

Generates ST_Intersects(t.{geom}, <geom_in>). The ref= form uses an EXISTS subquery (D-08).

Parameters:

table (str) – Table to query (aliased t).
geom (str, optional) – Geometry column name, by default “geometry” (D-06).
schema (str, optional) – Schema name, by default “public”.
point (tuple of float, optional) – (x, y) input geometry (one of the four D-05 forms).
wkt (str, optional) – WKT input geometry.
geojson (dict, optional) – GeoJSON input geometry.
ref (tuple of str, optional) – (ref_table, ref_col) input geometry (EXISTS semantics).
srid (int, optional) – SRID of the input geometry, by default 4326 (D-07).
columns (list of str, optional) – Columns to select, by default all (D-03).
filter (str, optional) – Additional raw SQL filter combined as AND (...) (D-11).
order_by (str, optional) – ORDER BY clause body (D-12).
limit (int, optional) – LIMIT value (D-12).

Returns:

SQL string and parameter list.

Return type:

tuple of (str, list)

Raises:

ValueError – If the geometry input forms are not mutually exclusive.
InvalidIdentifierError – If any identifier is invalid.

pycopg.spatial.build_overlaps_sql(table: str, geom: str = 'geometry', schema: str = 'public', *, point: tuple[float, float] | None = None, wkt: str | None = None, geojson: dict[str, Any] | None = None, ref: tuple[str, str] | None = None, srid: int = 4326, columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → tuple[str, list[Any]][source]¶

Build SQL selecting rows whose geometry overlaps the input geometry.

Generates ST_Overlaps(t.{geom}, <geom_in>) (DE-9IM predicate, SPAT-F06/D-06). The ref= form uses an EXISTS subquery (D-08). Mechanically mirrors build_intersects_sql() — only the ST_* function name differs.

Parameters:

table (str) – Table to query (aliased t).
geom (str, optional) – Geometry column name, by default “geometry” (D-06).
schema (str, optional) – Schema name, by default “public”.
point (tuple of float, optional) – (x, y) input geometry (one of the four D-05 forms).
wkt (str, optional) – WKT input geometry.
geojson (dict, optional) – GeoJSON input geometry.
ref (tuple of str, optional) – (ref_table, ref_col) input geometry (EXISTS semantics).
srid (int, optional) – SRID of the input geometry, by default 4326 (D-07).
columns (list of str, optional) – Columns to select, by default all (D-03).
filter (str, optional) – Additional raw SQL filter combined as AND (...) (D-11).
order_by (str, optional) – ORDER BY clause body (D-12).
limit (int, optional) – LIMIT value (D-12).

Returns:

SQL string and parameter list.

Return type:

tuple of (str, list)

Raises:

ValueError – If the geometry input forms are not mutually exclusive.
InvalidIdentifierError – If any identifier is invalid.

pycopg.spatial.build_touches_sql(table: str, geom: str = 'geometry', schema: str = 'public', *, point: tuple[float, float] | None = None, wkt: str | None = None, geojson: dict[str, Any] | None = None, ref: tuple[str, str] | None = None, srid: int = 4326, columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → tuple[str, list[Any]][source]¶

Build SQL selecting rows whose geometry touches the input geometry.

Generates ST_Touches(t.{geom}, <geom_in>) (DE-9IM predicate, SPAT-F06/D-06). The ref= form uses an EXISTS subquery (D-08). Mechanically mirrors build_intersects_sql() — only the ST_* function name differs.

Parameters:

table (str) – Table to query (aliased t).
geom (str, optional) – Geometry column name, by default “geometry” (D-06).
schema (str, optional) – Schema name, by default “public”.
point (tuple of float, optional) – (x, y) input geometry (one of the four D-05 forms).
wkt (str, optional) – WKT input geometry.
geojson (dict, optional) – GeoJSON input geometry.
ref (tuple of str, optional) – (ref_table, ref_col) input geometry (EXISTS semantics).
srid (int, optional) – SRID of the input geometry, by default 4326 (D-07).
columns (list of str, optional) – Columns to select, by default all (D-03).
filter (str, optional) – Additional raw SQL filter combined as AND (...) (D-11).
order_by (str, optional) – ORDER BY clause body (D-12).
limit (int, optional) – LIMIT value (D-12).

Returns:

SQL string and parameter list.

Return type:

tuple of (str, list)

Raises:

ValueError – If the geometry input forms are not mutually exclusive.
InvalidIdentifierError – If any identifier is invalid.

pycopg.spatial.build_crosses_sql(table: str, geom: str = 'geometry', schema: str = 'public', *, point: tuple[float, float] | None = None, wkt: str | None = None, geojson: dict[str, Any] | None = None, ref: tuple[str, str] | None = None, srid: int = 4326, columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → tuple[str, list[Any]][source]¶

Build SQL selecting rows whose geometry crosses the input geometry.

Generates ST_Crosses(t.{geom}, <geom_in>) (DE-9IM predicate, SPAT-F06/D-06). The ref= form uses an EXISTS subquery (D-08). Mechanically mirrors build_intersects_sql() — only the ST_* function name differs.

Parameters:

table (str) – Table to query (aliased t).
geom (str, optional) – Geometry column name, by default “geometry” (D-06).
schema (str, optional) – Schema name, by default “public”.
point (tuple of float, optional) – (x, y) input geometry (one of the four D-05 forms).
wkt (str, optional) – WKT input geometry.
geojson (dict, optional) – GeoJSON input geometry.
ref (tuple of str, optional) – (ref_table, ref_col) input geometry (EXISTS semantics).
srid (int, optional) – SRID of the input geometry, by default 4326 (D-07).
columns (list of str, optional) – Columns to select, by default all (D-03).
filter (str, optional) – Additional raw SQL filter combined as AND (...) (D-11).
order_by (str, optional) – ORDER BY clause body (D-12).
limit (int, optional) – LIMIT value (D-12).

Returns:

SQL string and parameter list.

Return type:

tuple of (str, list)

Raises:

ValueError – If the geometry input forms are not mutually exclusive.
InvalidIdentifierError – If any identifier is invalid.

pycopg.spatial.build_disjoint_sql(table: str, geom: str = 'geometry', schema: str = 'public', *, point: tuple[float, float] | None = None, wkt: str | None = None, geojson: dict[str, Any] | None = None, ref: tuple[str, str] | None = None, srid: int = 4326, columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → tuple[str, list[Any]][source]¶

Build SQL selecting rows whose geometry is disjoint from the input.

Generates ST_Disjoint(t.{geom}, <geom_in>) (DE-9IM predicate, SPAT-F06/D-07). The ref= form uses the SAME uniform EXISTS subquery pattern as every other predicate in this family — it is NOT special-cased into a NOT EXISTS set-complement form (D-07). Mechanically mirrors build_intersects_sql() — only the ST_* function name differs.

Warning

The ref= form means “EXISTS a ref row disjoint from t” (true as soon as ANY row in the reference table is disjoint from the current row — near-always true for a sparse reference table), NOT disjoint from all reference rows (D-07). ST_Disjoint does not use indexes [postgis.net/docs/ST_Disjoint.html]; a negated NOT ST_Intersects predicate (via filter=) is the indexable, performant alternative for large tables (D-08).

Parameters:

table (str) – Table to query (aliased t).
geom (str, optional) – Geometry column name, by default “geometry” (D-06).
schema (str, optional) – Schema name, by default “public”.
point (tuple of float, optional) – (x, y) input geometry (one of the four D-05 forms).
wkt (str, optional) – WKT input geometry.
geojson (dict, optional) – GeoJSON input geometry.
ref (tuple of str, optional) – (ref_table, ref_col) input geometry (EXISTS semantics — see the ref= footgun warning above, D-07).
srid (int, optional) – SRID of the input geometry, by default 4326 (D-07).
columns (list of str, optional) – Columns to select, by default all (D-03).
filter (str, optional) – Additional raw SQL filter combined as AND (...) (D-11).
order_by (str, optional) – ORDER BY clause body (D-12).
limit (int, optional) – LIMIT value (D-12).

Returns:

SQL string and parameter list.

Return type:

tuple of (str, list)

Raises:

ValueError – If the geometry input forms are not mutually exclusive.
InvalidIdentifierError – If any identifier is invalid.

pycopg.spatial.build_within_sql(left_table: str, left_geom: str, right_table: str, right_geom: str, schema: str = 'public', columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → tuple[str, list[Any]][source]¶

Build SQL joining two tables on a within relationship.

Generates the two-table JOIN form ST_Within(a.{left_geom}, b.{right_geom}) — this helper keeps its dedicated join signature per 08-DESIGN §3 and does not take the D-05 geometry input forms.

Parameters:

left_table (str) – Table whose rows are returned (aliased a).
left_geom (str) – Geometry column of the left table.
right_table (str) – Containing table (aliased b).
right_geom (str) – Geometry column of the right table.
schema (str, optional) – Schema name for both tables, by default “public”.
columns (list of str, optional) – Columns to select, by default all (D-03).
filter (str, optional) – Raw SQL filter fragment (D-11).
order_by (str, optional) – ORDER BY clause body (D-12).
limit (int, optional) – LIMIT value (D-12).

Returns:

SQL string and parameter list (always empty for this builder).

Return type:

tuple of (str, list)

Raises:

InvalidIdentifierError – If any identifier is invalid.

pycopg.spatial.build_dwithin_sql(table: str, geom: str = 'geometry', schema: str = 'public', *, point: tuple[float, float] | None = None, wkt: str | None = None, geojson: dict[str, Any] | None = None, ref: tuple[str, str] | None = None, srid: int = 4326, distance: float, unit: str = 'm', columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → tuple[str, list[Any]][source]¶

Build SQL selecting rows within a distance of the input geometry.

With unit="m" (default), both sides are cast to ::geography so the distance is in meters (D-09); unit="srid" uses native SRID units. The distance value is always a %s parameter.

Parameters:

table (str) – Table to query (aliased t).
geom (str, optional) – Geometry column name, by default “geometry” (D-06).
schema (str, optional) – Schema name, by default “public”.
point (tuple of float, optional) – (x, y) input geometry (one of the four D-05 forms).
wkt (str, optional) – WKT input geometry.
geojson (dict, optional) – GeoJSON input geometry.
ref (tuple of str, optional) – (ref_table, ref_col) input geometry (EXISTS semantics).
srid (int, optional) – SRID of the input geometry, by default 4326 (D-07).
distance (float) – Search distance, in meters (unit="m") or SRID units.
unit (str, optional) – "m" or "srid", by default “m” (D-09).
columns (list of str, optional) – Columns to select, by default all (D-03).
filter (str, optional) – Additional raw SQL filter combined as AND (...) (D-11).
order_by (str, optional) – ORDER BY clause body (D-12).
limit (int, optional) – LIMIT value (D-12).

Returns:

SQL string and parameter list.

Return type:

tuple of (str, list)

Raises:

ValueError – If unit is invalid or geometry forms are not exclusive.
InvalidIdentifierError – If any identifier is invalid.

pycopg.spatial.build_distance_sql(table: str, geom: str = 'geometry', schema: str = 'public', *, point: tuple[float, float] | None = None, wkt: str | None = None, geojson: dict[str, Any] | None = None, srid: int = 4326, unit: str = 'm', columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → tuple[str, list[Any]][source]¶

Build SQL selecting rows with their distance to the input geometry.

Adds a scalar ST_Distance(...) AS distance column. With unit="m" (default) both sides are cast to ::geography so the distance is in meters (D-09). Callers may pass order_by="distance" to sort by proximity. The ref= input form is not supported here — EXISTS semantics (D-08) do not define a scalar distance.

Parameters:

table (str) – Table to query (aliased t).
geom (str, optional) – Geometry column name, by default “geometry” (D-06).
schema (str, optional) – Schema name, by default “public”.
point (tuple of float, optional) – (x, y) input geometry.
wkt (str, optional) – WKT input geometry.
geojson (dict, optional) – GeoJSON input geometry.
srid (int, optional) – SRID of the input geometry, by default 4326 (D-07).
unit (str, optional) – "m" or "srid", by default “m” (D-09).
columns (list of str, optional) – Columns to select alongside the distance, by default all (D-03).
filter (str, optional) – Raw SQL filter fragment (D-11).
order_by (str, optional) – ORDER BY clause body, e.g. "distance" (D-12).
limit (int, optional) – LIMIT value (D-12).

Returns:

SQL string and parameter list.

Return type:

tuple of (str, list)

Raises:

ValueError – If unit is invalid or geometry forms are not exclusive.
InvalidIdentifierError – If any identifier is invalid.

pycopg.spatial.build_nearest_sql(table: str, geom: str = 'geometry', schema: str = 'public', *, point: tuple[float, float] | None = None, wkt: str | None = None, geojson: dict[str, Any] | None = None, srid: int = 4326, k: int = 5, columns: list[str] | None = None, filter: str | None = None) → tuple[str, list[Any]][source]¶

Build SQL selecting the k rows nearest to the input geometry.

Uses KNN ordering t.{geom}::geography <-> <geom_in>::geography for metric (meter-based) proximity, consistent with the D-09 meter default. A GiST index on the geometry column accelerates this ordering. k is emitted as a %s LIMIT parameter. No unit= parameter (D-10), and the ref= input form is not supported — EXISTS semantics (D-08) do not define a KNN ordering target.

Parameters:

table (str) – Table to query (aliased t).
geom (str, optional) – Geometry column name, by default “geometry” (D-06).
schema (str, optional) – Schema name, by default “public”.
point (tuple of float, optional) – (x, y) input geometry.
wkt (str, optional) – WKT input geometry.
geojson (dict, optional) – GeoJSON input geometry.
srid (int, optional) – SRID of the input geometry, by default 4326 (D-07).
k (int, optional) – Number of nearest rows to return, by default 5.
columns (list of str, optional) – Columns to select, by default all (D-03).
filter (str, optional) – Raw SQL filter fragment (D-11).

Returns:

SQL string and parameter list (k is the last parameter).

Return type:

tuple of (str, list)

Raises:

ValueError – If the geometry input forms are not mutually exclusive.
InvalidIdentifierError – If any identifier is invalid.

pycopg.spatial.build_area_sql(table: str, geom: str = 'geometry', schema: str = 'public', *, unit: str = 'm', columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → tuple[str, list[Any]][source]¶

Build SQL selecting rows with the area of their geometry.

Adds a scalar ST_Area(...) AS area column computed on the table’s own geometry column (no geometry input). With unit="m" (default) the geometry is cast to ::geography so the area is in square meters (D-09).

Parameters:

table (str) – Table to query (aliased t).
geom (str, optional) – Geometry column name, by default “geometry” (D-06).
schema (str, optional) – Schema name, by default “public”.
unit (str, optional) – "m" or "srid", by default “m” (D-09).
columns (list of str, optional) – Columns to select alongside the area, by default all (D-03).
filter (str, optional) – Raw SQL filter fragment (D-11).
order_by (str, optional) – ORDER BY clause body (D-12).
limit (int, optional) – LIMIT value (D-12).

Returns:

SQL string and parameter list (always empty for this builder).

Return type:

tuple of (str, list)

Raises:

ValueError – If unit is invalid.
InvalidIdentifierError – If any identifier is invalid.

pycopg.spatial.build_perimeter_sql(table: str, geom: str = 'geometry', schema: str = 'public', *, unit: str = 'm', columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → tuple[str, list[Any]][source]¶

Build SQL selecting rows with the perimeter of their geometry.

Adds a scalar ST_Perimeter(...) AS perimeter column computed on the table’s own geometry column. With unit="m" (default) the geometry is cast to ::geography so the perimeter is in meters (D-09).

Parameters:

table (str) – Table to query (aliased t).
geom (str, optional) – Geometry column name, by default “geometry” (D-06).
schema (str, optional) – Schema name, by default “public”.
unit (str, optional) – "m" or "srid", by default “m” (D-09).
columns (list of str, optional) – Columns to select alongside the perimeter, by default all (D-03).
filter (str, optional) – Raw SQL filter fragment (D-11).
order_by (str, optional) – ORDER BY clause body (D-12).
limit (int, optional) – LIMIT value (D-12).

Returns:

SQL string and parameter list (always empty for this builder).

Return type:

tuple of (str, list)

Raises:

ValueError – If unit is invalid.
InvalidIdentifierError – If any identifier is invalid.

pycopg.spatial.build_length_sql(table: str, geom: str = 'geometry', schema: str = 'public', *, unit: str = 'm', columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → tuple[str, list[Any]][source]¶

Build SQL selecting rows with the length of their geometry.

Adds a scalar ST_Length(...) AS length column computed on the table’s own geometry column. With unit="m" (default) the geometry is cast to ::geography so the length is in meters (D-09/D-10). ST_Length returns 0 for non-linear (areal/point) geometries (D-10) — this is a PostGIS pass-through, not a pycopg guard.

Parameters:

table (str) – Table to query (aliased t).
geom (str, optional) – Geometry column name, by default “geometry” (D-06).
schema (str, optional) – Schema name, by default “public”.
unit (str, optional) – "m" or "srid", by default “m” (D-09/D-10).
columns (list of str, optional) – Columns to select alongside the length, by default all (D-03).
filter (str, optional) – Raw SQL filter fragment (D-11).
order_by (str, optional) – ORDER BY clause body (D-12).
limit (int, optional) – LIMIT value (D-12).

Returns:

SQL string and parameter list (always empty for this builder).

Return type:

tuple of (str, list)

Raises:

ValueError – If unit is invalid.
InvalidIdentifierError – If any identifier is invalid.

pycopg.spatial.build_is_valid_sql(table: str, geom: str = 'geometry', schema: str = 'public', *, columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → tuple[str, list[Any]][source]¶

Build SQL selecting rows with the OGC validity of their geometry.

Adds a scalar ST_IsValid(...) AS is_valid boolean column computed on the table’s own geometry column (D-09/D-12).

Parameters:

table (str) – Table to query (aliased t).
geom (str, optional) – Geometry column name, by default “geometry” (D-06).
schema (str, optional) – Schema name, by default “public”.
columns (list of str, optional) – Columns to select alongside the validity flag, by default all (D-03).
filter (str, optional) – Raw SQL filter fragment (D-11).
order_by (str, optional) – ORDER BY clause body (D-12).
limit (int, optional) – LIMIT value (D-12).

Returns:

SQL string and parameter list (always empty for this builder).

Return type:

tuple of (str, list)

Raises:

InvalidIdentifierError – If any identifier is invalid.

pycopg.spatial.build_is_valid_reason_sql(table: str, geom: str = 'geometry', schema: str = 'public', *, columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → tuple[str, list[Any]][source]¶

Build SQL selecting rows with the OGC validity reason of their geometry.

Adds a scalar ST_IsValidReason(...) AS is_valid_reason text column computed on the table’s own geometry column (D-09/D-12) — describes why a geometry is invalid, or "Valid Geometry" when it is valid.

Parameters:

table (str) – Table to query (aliased t).
geom (str, optional) – Geometry column name, by default “geometry” (D-06).
schema (str, optional) – Schema name, by default “public”.
columns (list of str, optional) – Columns to select alongside the validity reason, by default all (D-03).
filter (str, optional) – Raw SQL filter fragment (D-11).
order_by (str, optional) – ORDER BY clause body (D-12).
limit (int, optional) – LIMIT value (D-12).

Returns:

SQL string and parameter list (always empty for this builder).

Return type:

tuple of (str, list)

Raises:

InvalidIdentifierError – If any identifier is invalid.

pycopg.spatial.build_geojson_sql(table: str, geom: str = 'geometry', schema: str = 'public', *, max_decimal_digits: int = 6, include_bbox: bool = False, include_crs: bool = False, columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → tuple[str, list[Any]][source]¶

Build SQL selecting rows with their geometry as a GeoJSON string.

Adds a scalar ST_AsGeoJSON(...) AS geojson column containing the geometry serialized as a GeoJSON text string. The result key is the frozen alias geojson; callers use json.loads(row["geojson"]) when a dict is needed (D-06).

The include_bbox and include_crs flags compose the ST_AsGeoJSON options bitmask (1 = bounding box, 2 = short CRS name) passed as a %s parameter alongside max_decimal_digits (D-06).

Parameters:

table (str) – Table to query (aliased t).
geom (str, optional) – Geometry column name, by default “geometry”.
schema (str, optional) – Schema name, by default “public”.
max_decimal_digits (int, optional) – Maximum decimal digits in coordinates, by default 6 (D-06).
include_bbox (bool, optional) – Include bounding box in GeoJSON output (options bit 1), by default False (D-06).
include_crs (bool, optional) – Include short CRS name in GeoJSON output (options bit 2), by default False (D-06).
columns (list of str, optional) – Columns to select alongside the GeoJSON text, by default all (D-08).
filter (str, optional) – Raw SQL filter fragment (D-11).
order_by (str, optional) – ORDER BY clause body (D-12).
limit (int, optional) – LIMIT value (D-12).

Returns:

SQL string and parameter list [max_decimal_digits, options].

Return type:

tuple of (str, list)

Raises:

InvalidIdentifierError – If any identifier (table, schema, geom, or columns entries) is invalid.

pycopg.spatial.build_text_sql(table: str, geom: str = 'geometry', schema: str = 'public', *, max_decimal_digits: int | None = None, columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → tuple[str, list[Any]][source]¶

Build SQL selecting rows with their geometry as a WKT string.

Adds a scalar ST_AsText(...) AS wkt column. The result key is the frozen alias wkt. When max_decimal_digits is None (default) the one-arg ST_AsText(t.geom) form is used, preserving full precision — WKT is intended for exact interchange and round-trip use, so silent truncation is undesirable (D-07). When set, the two-arg ST_AsText(t.geom, %s) form is used, which requires PostGIS >= 3.1 (the one-arg full-precision form works on all PostGIS 3.0+ servers).

Parameters:

table (str) – Table to query (aliased t).
geom (str, optional) – Geometry column name, by default “geometry”.
schema (str, optional) – Schema name, by default “public”.
max_decimal_digits (int or None, optional) – Maximum decimal digits in coordinates, by default None (full precision — one-arg form). When set, two-arg form is emitted (D-07).
columns (list of str, optional) – Columns to select alongside the WKT text, by default all (D-08).
filter (str, optional) – Raw SQL filter fragment (D-11).
order_by (str, optional) – ORDER BY clause body (D-12).
limit (int, optional) – LIMIT value (D-12).

Returns:

SQL string and parameter list ([] when max_decimal_digits is None; [max_decimal_digits] when set).

Return type:

tuple of (str, list)

Raises:

InvalidIdentifierError – If any identifier (table, schema, geom, or columns entries) is invalid.

pycopg.spatial.build_mvt_sql(table: str, geom: str = 'geometry', schema: str = 'public', *, tile_z: int, tile_x: int, tile_y: int, layer_name: str = 'default', extent: int = 4096, columns: list[str] | None = None, feature_id_column: str | None = None, srid: int | None = None, filter: str | None = None) → tuple[str, list[Any]][source]¶

Build SQL that generates a Mapbox Vector Tile (MVT) via ST_AsMVT.

Always emits the enforced ST_Transform → ST_AsMVTGeom → ST_AsMVT pipeline (D-03). The pipeline transforms input geometry to Web Mercator (EPSG:3857), clips it to the tile envelope, and encodes it as a protobuf MVT blob. An outer WHERE q.geom_mvt IS NOT NULL filter discards rows that clip entirely outside the tile.

Tile coordinates (tile_z, tile_x, tile_y) and the extent are coerced via int() and interpolated directly into the SQL — not as %s bind parameters. The only %s placeholders are layer_name and extent, making the params list exactly [layer_name, extent] (D-03).

SRID-0 / untagged columns¶

If the source geometry column has SRID 0 (untagged), PostGIS cannot determine the source CRS and ST_Transform silently produces wrong results. Use srid=4326 (or the correct source SRID) to inject ST_SetSRID before the transform (D-04):

# SRID-0 column rescue:
build_mvt_sql("points", srid=4326, tile_z=z, tile_x=x, tile_y=y)
# → ST_Transform(ST_SetSRID(t.geometry, 4326), 3857)

`feature_id_column`¶

When set, the column name is embedded as a literal SQL identifier in the ST_AsMVT call. It must reference an integer-type column in the source table — PostGIS enforces this at query time (MVT spec requires 64-bit integer feature IDs). Pass feature_id_column=None (the default) to omit the feature ID.

PostGIS requirement¶

Requires PostGIS ≥ 3.0 for ST_TileEnvelope and the five-arg ST_AsMVT form (documented requirement, not a runtime gate).

param table:: Table to query (aliased t).
type table:: str
param geom:: Geometry column name, by default "geometry".
type geom:: str, optional
param schema:: Schema name, by default "public".
type schema:: str, optional
param tile_z:: Tile zoom level. Required keyword-only (D-02).
type tile_z:: int
param tile_x:: Tile column index. Required keyword-only (D-02).
type tile_x:: int
param tile_y:: Tile row index. Required keyword-only (D-02).
type tile_y:: int
param layer_name:: MVT layer name, by default "default". Passed as a %s bind parameter.
type layer_name:: str, optional
param extent:: MVT tile extent in pixel units, by default 4096. Interpolated as an integer literal (injection-safe) and also passed as a %s bind parameter to ST_AsMVT.
type extent:: int, optional
param columns:: Extra columns to select alongside the geometry in the inner subquery (available as MVT feature properties).
type columns:: list of str, optional
param feature_id_column:: Integer-typed source column to use as the MVT feature ID. Validated as an identifier and interpolated directly — not a %s param. Must be an integer column (PostGIS requirement). By default None (no feature ID in the MVT).
type feature_id_column:: str or None, optional
param srid:: Source SRID to inject via ST_SetSRID before the transform (D-04). Use this when the geometry column has SRID 0 or an incorrect SRID tag. None (default) — trusts the stored SRID.
type srid:: int or None, optional
param filter:: Raw SQL fragment appended as WHERE {filter} inside the inner subquery (before clipping). This is a documented escape hatch; callers own the fragment (T-44-02c).
type filter:: str, optional
returns:: SQL string with %s placeholders and the parameter list [layer_name, extent] (exactly two elements).
rtype:: tuple of (str, list)
raises InvalidIdentifierError:: If table, schema, geom, any entry in columns, or feature_id_column is an invalid SQL identifier (T-44-02).
raises ValueError:: If tile_z, tile_x, tile_y, extent, or srid cannot be coerced to int (T-44-02b).

Examples

Basic tile for a roads table:

sql, params = build_mvt_sql("roads", tile_z=12, tile_x=2048, tile_y=1361)
# SELECT ST_AsMVT(q.*, %s, %s, 'geom_mvt') AS mvt
# FROM (SELECT ST_AsMVTGeom(ST_Transform(t.geometry, 3857),
#              ST_TileEnvelope(12, 2048, 1361), 4096) AS geom_mvt
#       FROM public.roads AS t) AS q
# WHERE q.geom_mvt IS NOT NULL
# params == ['default', 4096]

With SRID-0 rescue and a feature ID column:

sql, params = build_mvt_sql(
    "places", tile_z=10, tile_x=512, tile_y=400,
    srid=4326, feature_id_column="gid",
)

pycopg.spatial.build_centroid_sql(table: str, geom: str = 'geometry', schema: str = 'public', *, columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → tuple[str, list[Any]][source]¶

Build SQL selecting rows with their geometry centroid coordinates.

Adds scalar ST_X(ST_Centroid(...)) AS centroid_x and ST_Y(ST_Centroid(...)) AS centroid_y columns. Scalar result — no geometry column is returned, so into="gdf" is forbidden at the accessor level (D-02). No unit= parameter (D-10).

Parameters:

table (str) – Table to query (aliased t).
geom (str, optional) – Geometry column name, by default “geometry” (D-06).
schema (str, optional) – Schema name, by default “public”.
columns (list of str, optional) – Columns to select alongside the centroid, by default all (D-03).
filter (str, optional) – Raw SQL filter fragment (D-11).
order_by (str, optional) – ORDER BY clause body (D-12).
limit (int, optional) – LIMIT value (D-12).

Returns:

SQL string and parameter list (always empty for this builder).

Return type:

tuple of (str, list)

Raises:

InvalidIdentifierError – If any identifier is invalid.

pycopg.spatial.build_buffer_sql(table: str, geom: str = 'geometry', schema: str = 'public', *, distance: float, unit: str = 'm', columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → tuple[str, list[Any]][source]¶

Build SQL selecting rows with a buffer around their geometry.

Adds a buffer geometry column. With unit="m" (default) the geometry is cast to ::geography for a meter-based buffer, and the result is cast back to ::geometry (08-DESIGN §3) so the output is valid for into="gdf". The distance is always a %s parameter.

Parameters:

table (str) – Table to query (aliased t).
geom (str, optional) – Geometry column name, by default “geometry” (D-06).
schema (str, optional) – Schema name, by default “public”.
distance (float) – Buffer distance, in meters (unit="m") or SRID units.
unit (str, optional) – "m" or "srid", by default “m” (D-09).
columns (list of str, optional) – Columns to select alongside the buffer, by default all (D-03).
filter (str, optional) – Raw SQL filter fragment (D-11).
order_by (str, optional) – ORDER BY clause body (D-12).
limit (int, optional) – LIMIT value (D-12).

Returns:

SQL string and parameter list ([distance]).

Return type:

tuple of (str, list)

Raises:

ValueError – If unit is invalid.
InvalidIdentifierError – If any identifier is invalid.

pycopg.spatial.build_transform_sql(table: str, geom: str = 'geometry', schema: str = 'public', *, to_srid: int, columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → tuple[str, list[Any]][source]¶

Build SQL selecting rows with their geometry transformed to a SRID.

Adds a geometry_transformed geometry column via ST_Transform(t.{geom}, %s) with to_srid as a parameter. The output is a geometry, valid for into="gdf". No unit= parameter (D-10).

Parameters:

table (str) – Table to query (aliased t).
geom (str, optional) – Geometry column name, by default “geometry” (D-06).
schema (str, optional) – Schema name, by default “public”.
to_srid (int) – Target spatial reference identifier.
columns (list of str, optional) – Columns to select alongside the transform, by default all (D-03).
filter (str, optional) – Raw SQL filter fragment (D-11).
order_by (str, optional) – ORDER BY clause body (D-12).
limit (int, optional) – LIMIT value (D-12).

Returns:

SQL string and parameter list ([to_srid]).

Return type:

tuple of (str, list)

Raises:

InvalidIdentifierError – If any identifier is invalid.

pycopg.spatial.build_union_sql(table: str, geom: str = 'geometry', schema: str = 'public', *, other_geom: str, grid_size: float | None = None, columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → tuple[str, list[Any]][source]¶

Build SQL selecting the pairwise union of two geometry columns.

Adds a union_geom geometry column via ST_Union(t.{geom}, t.{other_geom}). No %s parameters are emitted — both geometry columns are identifier-validated and interpolated directly into the SQL. The result is a geometry, valid for into="gdf".

Note: inputs with mismatched SRIDs will raise a native PostGIS error at execution time (D-06). No pre-check is performed.

Parameters:

table (str) – Table to query (aliased t).
geom (str, optional) – First geometry column name, by default “geometry”.
schema (str, optional) – Schema name, by default “public”.
other_geom (str) – Second geometry column name (required, keyword-only).
grid_size (float, optional) – Fixed-precision (snap-rounding) overlay grid size, by default None. When set, emits the 3-arg ST_Union(a, b, %s) form bound to this value (pure passthrough, no range guard, D-02). Requires PostGIS >= 3.1 / GEOS >= 3.9 — older servers raise a native PostGIS error (D-04, no version pre-check performed). When None, SQL and params are byte-identical to the pre-grid_size form (D-03).
columns (list of str, optional) – Columns to select alongside the result, by default all.
filter (str, optional) – Raw SQL filter fragment.
order_by (str, optional) – ORDER BY clause body.
limit (int, optional) – LIMIT value.

Returns:

SQL string and parameter list ([] when grid_size is None, [grid_size] otherwise).

Return type:

tuple of (str, list)

Raises:

InvalidIdentifierError – If any identifier (table, schema, geom, other_geom, or any element of columns) is invalid.

pycopg.spatial.build_difference_sql(table: str, geom: str = 'geometry', schema: str = 'public', *, other_geom: str, grid_size: float | None = None, columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → tuple[str, list[Any]][source]¶

Build SQL selecting the pairwise difference of two geometry columns.

Adds a difference geometry column via ST_Difference(t.{geom}, t.{other_geom}) (A minus B). No %s parameters are emitted — both geometry columns are identifier-validated and interpolated directly into the SQL. The result is a geometry, valid for into="gdf".

Note: inputs with mismatched SRIDs will raise a native PostGIS error at execution time (D-06). No pre-check is performed.

Parameters:

table (str) – Table to query (aliased t).
geom (str, optional) – First geometry column name (minuend), by default “geometry”.
schema (str, optional) – Schema name, by default “public”.
other_geom (str) – Second geometry column name (subtrahend, required, keyword-only).
grid_size (float, optional) – Fixed-precision (snap-rounding) overlay grid size, by default None. When set, emits the 3-arg ST_Difference(a, b, %s) form bound to this value (pure passthrough, no range guard, D-02). Requires PostGIS >= 3.1 / GEOS >= 3.9 — older servers raise a native PostGIS error (D-04, no version pre-check performed). When None, SQL and params are byte-identical to the pre-grid_size form (D-03).
columns (list of str, optional) – Columns to select alongside the result, by default all.
filter (str, optional) – Raw SQL filter fragment.
order_by (str, optional) – ORDER BY clause body.
limit (int, optional) – LIMIT value.

Returns:

SQL string and parameter list ([] when grid_size is None, [grid_size] otherwise).

Return type:

tuple of (str, list)

Raises:

InvalidIdentifierError – If any identifier (table, schema, geom, other_geom, or any element of columns) is invalid.

pycopg.spatial.build_intersection_sql(table: str, geom: str = 'geometry', schema: str = 'public', *, other_geom: str, grid_size: float | None = None, columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → tuple[str, list[Any]][source]¶

Build SQL selecting the pairwise intersection of two geometry columns.

Adds an intersection geometry column via ST_Intersection(t.{geom}, t.{other_geom}) (shared portion of A and B). No %s parameters are emitted — both geometry columns are identifier-validated and interpolated directly into the SQL. The result is a geometry, valid for into="gdf".

Note: inputs with mismatched SRIDs will raise a native PostGIS error at execution time (D-06). No pre-check is performed.

Parameters:

table (str) – Table to query (aliased t).
geom (str, optional) – First geometry column name, by default “geometry”.
schema (str, optional) – Schema name, by default “public”.
other_geom (str) – Second geometry column name (required, keyword-only).
grid_size (float, optional) – Fixed-precision (snap-rounding) overlay grid size, by default None. When set, emits the 3-arg ST_Intersection(a, b, %s) form bound to this value (pure passthrough, no range guard, D-02). Requires PostGIS >= 3.1 / GEOS >= 3.9 — older servers raise a native PostGIS error (D-04, no version pre-check performed). When None, SQL and params are byte-identical to the pre-grid_size form (D-03).
columns (list of str, optional) – Columns to select alongside the result, by default all.
filter (str, optional) – Raw SQL filter fragment.
order_by (str, optional) – ORDER BY clause body.
limit (int, optional) – LIMIT value.

Returns:

SQL string and parameter list ([] when grid_size is None, [grid_size] otherwise).

Return type:

tuple of (str, list)

Raises:

InvalidIdentifierError – If any identifier (table, schema, geom, other_geom, or any element of columns) is invalid.

pycopg.spatial.build_simplify_sql(table: str, geom: str = 'geometry', schema: str = 'public', *, tolerance: float, preserve_topology: bool = True, preserve_collapsed: bool = False, columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → tuple[str, list[Any]][source]¶

Build SQL selecting rows with their geometry simplified.

By default uses ST_SimplifyPreserveTopology, which guarantees a non-NULL result even when all points collapse within tolerance (topology is preserved). Pass preserve_topology=False to use ST_Simplify instead — that variant may return NULL when the geometry collapses below tolerance (D-07).

When preserve_topology=False, the preserve_collapsed flag is forwarded to ST_Simplify as its third bound parameter.

The simplified geometry is aliased as simplified (D-01).

Parameters:

table (str) – Table to query (aliased t).
geom (str, optional) – Geometry column name, by default “geometry”.
schema (str, optional) – Schema name, by default “public”.
tolerance (float) – Simplification tolerance (required keyword-only, D-05). Units depend on the geometry’s SRID (degrees for 4326).
preserve_topology (bool, optional) – If True (default) use ST_SimplifyPreserveTopology (no NULL collapse). If False use ST_Simplify (faster, but can return NULL).
preserve_collapsed (bool, optional) – Passed to ST_Simplify only when preserve_topology=False. Setting preserve_collapsed=True while preserve_topology=True raises ValueError (D-05).
columns (list of str, optional) – Columns to select alongside the result, by default all.
filter (str, optional) – Raw SQL filter fragment.
order_by (str, optional) – ORDER BY clause body.
limit (int, optional) – LIMIT value.

Returns:

SQL string and parameter list.

Return type:

tuple of (str, list)

Raises:

ValueError – If preserve_collapsed=True and preserve_topology=True (contradictory, D-05).
InvalidIdentifierError – If any identifier is invalid.

pycopg.spatial.build_convex_hull_sql(table: str, geom: str = 'geometry', schema: str = 'public', *, columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → tuple[str, list[Any]][source]¶

Build SQL selecting rows with the convex hull of each geometry.

Adds a convex_hull geometry column via ST_ConvexHull(t.{geom}) (D-01). Returns NULL for empty geometries — pass-through (D-07). Valid for into="gdf".

Parameters:

table (str) – Table to query (aliased t).
geom (str, optional) – Geometry column name, by default “geometry”.
schema (str, optional) – Schema name, by default “public”.
columns (list of str, optional) – Columns to select alongside the result, by default all.
filter (str, optional) – Raw SQL filter fragment.
order_by (str, optional) – ORDER BY clause body.
limit (int, optional) – LIMIT value.

Returns:

SQL string and empty parameter list ([]).

Return type:

tuple of (str, list)

Raises:

InvalidIdentifierError – If any identifier is invalid.

pycopg.spatial.build_envelope_sql(table: str, geom: str = 'geometry', schema: str = 'public', *, columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → tuple[str, list[Any]][source]¶

Build SQL selecting rows with the bounding-box geometry of each row.

Adds an envelope geometry column via ST_Envelope(t.{geom}) (D-11) — the per-row minimum bounding rectangle, preserving SRID. Valid for into="gdf".

Parameters:

table (str) – Table to query (aliased t).
geom (str, optional) – Geometry column name, by default “geometry”.
schema (str, optional) – Schema name, by default “public”.
columns (list of str, optional) – Columns to select alongside the result, by default all.
filter (str, optional) – Raw SQL filter fragment.
order_by (str, optional) – ORDER BY clause body.
limit (int, optional) – LIMIT value.

Returns:

SQL string and empty parameter list ([]).

Return type:

tuple of (str, list)

Raises:

InvalidIdentifierError – If any identifier is invalid.

pycopg.spatial.build_point_on_surface_sql(table: str, geom: str = 'geometry', schema: str = 'public', *, columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → tuple[str, list[Any]][source]¶

Build SQL selecting rows with a guaranteed-inside representative point.

Adds a point_on_surface geometry column via ST_PointOnSurface(t.{geom}) (D-05/D-11). Unlike build_centroid_sql()’s scalar x/y shape, this is a real POINT geometry that is guaranteed to lie on the input geometry (useful for labels/markers/mapping) and preserves SRID. Valid for into="gdf".

Parameters:

table (str) – Table to query (aliased t).
geom (str, optional) – Geometry column name, by default “geometry”.
schema (str, optional) – Schema name, by default “public”.
columns (list of str, optional) – Columns to select alongside the result, by default all.
filter (str, optional) – Raw SQL filter fragment.
order_by (str, optional) – ORDER BY clause body.
limit (int, optional) – LIMIT value.

Returns:

SQL string and empty parameter list ([]).

Return type:

tuple of (str, list)

Raises:

InvalidIdentifierError – If any identifier is invalid.

pycopg.spatial.build_make_valid_sql(table: str, geom: str = 'geometry', schema: str = 'public', *, method: str | None = None, keep_collapsed: bool | None = None, columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → tuple[str, list[Any]][source]¶

Build SQL selecting rows with their geometry repaired via ST_MakeValid.

Adds a made_valid geometry column (D-01). Geometries that are already valid are returned unchanged. Invalid geometries (e.g. bowtie self-intersections) are repaired — the output type may change (POLYGON → MULTIPOLYGON or GEOMETRYCOLLECTION, D-07).

method selects the repair algorithm:

None (default) — PostGIS default algorithm (ST_MakeValid(geom) form; available since PostGIS 3.0).
"linework" — re-node the linework and assemble valid polygons; bound as the params string "method=linework" via %s.
"structure" — divide into structure components and rebuild; requires PostGIS ≥ 3.2 / GEOS ≥ 3.10 (D-04). On older servers, PostGIS raises a native error — no server-version pre-check is performed (D-04 passthrough). Bound as "method=structure" (plus optional keepcollapsed key) via %s.

The params string is assembled from programmer-controlled literals and bound through %s — it is never f-string-interpolated (D-02, Pitfall 4).

Parameters:

table (str) – Table to query (aliased t).
geom (str, optional) – Geometry column name, by default “geometry”.
schema (str, optional) – Schema name, by default “public”.
method ({None, "linework", "structure"}, optional) – Repair algorithm, by default None.
keep_collapsed (bool or None, optional) – Only valid when method="structure". Controls whether degenerate geometries that collapse to lower-dimensional forms are kept as such (True/False) or dropped by PostGIS’s default. Setting keep_collapsed when method != "structure" raises ValueError (D-03).
columns (list of str, optional) – Columns to select alongside the result, by default all.
filter (str, optional) – Raw SQL filter fragment.
order_by (str, optional) – ORDER BY clause body.
limit (int, optional) – LIMIT value.

Returns:

SQL string and parameter list (empty when method=None).

Return type:

tuple of (str, list)

Raises:

ValueError – If method is not in {None, "linework", "structure"} (D-02), or if keep_collapsed is set when method != "structure" (D-03).
InvalidIdentifierError – If any identifier is invalid.

pycopg.spatial.build_collect_geometries_sql(table: str, geom: str = 'geometry', schema: str = 'public', *, group_by: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → tuple[str, list[Any]][source]¶

Build SQL that collects geometries into a collection via ST_Collect (SPAT2-07).

Produces SELECT [group_cols,] ST_Collect(t.{geom}) AS collected FROM {schema}.{table} AS t [WHERE ...] [GROUP BY ...] [ORDER BY ...] [LIMIT ...]. No %s parameters are emitted — all identifiers are validated and interpolated.

NULL geometry inputs are skipped by the PostGIS aggregate; a group (or the whole table) with all-NULL inputs yields one row with a NULL collected value. No HAVING filter is applied (D-07).

Parameters:

table (str) – Table to query (aliased t).
geom (str, optional) – Geometry column name, by default "geometry".
schema (str, optional) – Schema name, by default "public".
group_by (list of str or None, optional) – Columns to group by. None (default) = whole-table aggregate (exactly one row). Pass the pre-normalized list from the accessor (str inputs are normalized to [str] at the accessor level).
filter (str, optional) – Raw SQL filter fragment (pre-aggregate WHERE clause).
order_by (str, optional) – ORDER BY clause body.
limit (int, optional) – LIMIT value.

Returns:

SQL string and empty parameter list ([]).

Return type:

tuple of (str, list)

Raises:

InvalidIdentifierError – If table, schema, geom, or any element of group_by is invalid.

Examples

Whole-table aggregate:

sql, params = build_collect_geometries_sql("zones")
# SELECT ST_Collect(t.geometry) AS collected FROM public.zones AS t

Grouped by region:

sql, params = build_collect_geometries_sql("zones", group_by=["region"])
# SELECT t.region, ST_Collect(t.geometry) AS collected
# FROM public.zones AS t GROUP BY t.region

pycopg.spatial.build_union_aggregate_sql(table: str, geom: str = 'geometry', schema: str = 'public', *, group_by: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → tuple[str, list[Any]][source]¶

Build SQL that dissolves geometries into a merged boundary via ST_Union (SPAT2-08).

Produces SELECT [group_cols,] ST_Union(t.{geom}) AS dissolved FROM {schema}.{table} AS t [WHERE ...] [GROUP BY ...] [ORDER BY ...] [LIMIT ...]. The result column is dissolved (not union_geom) to distinguish this aggregate form from the Phase 42 pairwise build_union_sql (D-06).

NULL geometry inputs are skipped by the PostGIS aggregate; a group (or the whole table) with all-NULL inputs yields one row with a NULL dissolved value. No HAVING filter is applied (D-07).

Parameters:

table (str) – Table to query (aliased t).
geom (str, optional) – Geometry column name, by default "geometry".
schema (str, optional) – Schema name, by default "public".
group_by (list of str or None, optional) – Columns to group by. None (default) = whole-table aggregate (exactly one row).
filter (str, optional) – Raw SQL filter fragment (pre-aggregate WHERE clause).
order_by (str, optional) – ORDER BY clause body.
limit (int, optional) – LIMIT value.

Returns:

SQL string and empty parameter list ([]).

Return type:

tuple of (str, list)

Raises:

InvalidIdentifierError – If table, schema, geom, or any element of group_by is invalid.

Examples

Whole-table dissolve:

sql, params = build_union_aggregate_sql("zones")
# SELECT ST_Union(t.geometry) AS dissolved FROM public.zones AS t

Grouped by region:

sql, params = build_union_aggregate_sql("zones", group_by=["region"])
# SELECT t.region, ST_Union(t.geometry) AS dissolved
# FROM public.zones AS t GROUP BY t.region

pycopg.spatial.build_extent_sql(table: str, geom: str = 'geometry', schema: str = 'public', *, group_by: list[str] | None = None, srid: int = 4326, into: str = 'rows', filter: str | None = None, order_by: str | None = None, limit: int | None = None) → tuple[str, list[Any]][source]¶

Build SQL that computes the bounding extent of a geometry column via ST_Extent (SPAT2-09).

ST_Extent returns a PostgreSQL box2d type, not a PostGIS geometry. This builder branches on into (D-04 exception — the only aggregate builder that sees into):

into="rows" (default): casts to text — ST_Extent(t.{geom})::text AS extent — returning the PostgreSQL BOX(xmin ymin, xmax ymax) string.
into="gdf": wraps in ST_SetSRID(ST_Extent(t.{geom})::geometry, {int(srid)}) to produce a real SRID-tagged polygon geometry that GeoPandas can load.

extent is not in _SCALAR_HELPERS — into="gdf" is valid via the geometry cast (D-04, Pitfall 3).

The srid parameter is consulted only on the gdf branch via int(srid) (injection-safe integer coercion; D-05). It is ignored for into="rows".

NULL geometry inputs are skipped by the PostGIS aggregate; a group (or the whole table) with all-NULL inputs yields one row with a NULL extent value. No HAVING filter is applied (D-07).

Parameters:

table (str) – Table to query (aliased t).
geom (str, optional) – Geometry column name, by default "geometry".
schema (str, optional) – Schema name, by default "public".
group_by (list of str or None, optional) – Columns to group by. None (default) = whole-table aggregate (exactly one row). Each column is validated via validate_identifiers before any f-string interpolation.
srid (int, optional) – Spatial Reference Identifier for the into="gdf" cast, by default 4326. Interpolated via int(srid) — a non-integer raises ValueError/TypeError before SQL assembly (T-43-03, D-05).
into (str, optional) – "rows" (default) or "gdf" — controls the SQL branch. The accessor calls _check_into before delegating here; this parameter is received only to select the SQL fragment.
filter (str, optional) – Raw SQL filter fragment (pre-aggregate WHERE clause).
order_by (str, optional) – ORDER BY clause body.
limit (int, optional) – LIMIT value.

Returns:

SQL string and empty parameter list ([]).

Return type:

tuple of (str, list)

Raises:

InvalidIdentifierError – If table, schema, geom, or any element of group_by is invalid (T-43-01).
ValueError – If into is not "rows" or "gdf" (validated by the accessor before this builder is called).

Examples

Whole-table bounding box as text:

sql, params = build_extent_sql("zones")
# SELECT ST_Extent(t.geometry)::text AS extent FROM public.zones AS t

Grouped bounding box as geometry for GeoPandas:

sql, params = build_extent_sql("zones", group_by=["region"], into="gdf", srid=4326)
# SELECT t.region, ST_SetSRID(ST_Extent(t.geometry)::geometry, 4326) AS extent
# FROM public.zones AS t GROUP BY t.region

class pycopg.spatial.SpatialAccessor(db: Database)[source]¶

Bases: object

Sync spatial helper namespace exposed as db.spatial.

Each method delegates SQL assembly to the module-level pure builders and routes the result per into= (D-01): "rows" returns list[dict[str, Any]] via Database.execute; "gdf" returns a GeoDataFrame via Database.to_geodataframe. PostGIS availability is guarded at construction (SPA-04).

__init__(db: Database) → None[source]¶

Initialize the accessor and verify PostGIS availability.

Parameters:: db (Database) – Parent database instance.
Raises:: ExtensionNotAvailableError – If the PostGIS extension is not installed.

contains(table: str, *, geom: str = 'geometry', schema: str = 'public', point: tuple[float, float] | None = None, wkt: str | None = None, geojson: dict[str, Any] | None = None, ref: tuple[str, str] | None = None, srid: int = 4326, into: Literal['rows'] = 'rows', columns: list[str] | None = None, filter: str | None = None, where: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]][source]¶

contains(table: str, *, geom: str = 'geometry', schema: str = 'public', point: tuple[float, float] | None = None, wkt: str | None = None, geojson: dict[str, Any] | None = None, ref: tuple[str, str] | None = None, srid: int = 4326, into: Literal['gdf'], columns: list[str] | None = None, filter: str | None = None, where: str | None = None, order_by: str | None = None, limit: int | None = None) → gpd.GeoDataFrame

Select rows whose geometry contains the input geometry.

Parameters:

table (str) – Table to query.
geom (str, optional) – Geometry column name, by default “geometry” (D-06).
schema (str, optional) – Schema name, by default “public”.
point (tuple of float, optional) – (x, y) input geometry (one of the four D-05 forms).
wkt (str, optional) – WKT input geometry.
geojson (dict, optional) – GeoJSON input geometry.
ref (tuple of str, optional) – (ref_table, ref_col) input geometry (EXISTS, D-08).
srid (int, optional) – SRID of the input geometry, by default 4326 (D-07).
into (str, optional) – "rows" (list of dict) or "gdf" (GeoDataFrame), by default “rows” (D-01).
columns (list of str, optional) – Columns to select, by default all (D-03).
filter (str, optional) – Additional raw SQL filter (D-11).
where (str, optional) – Deprecated. Use filter= instead.
order_by (str, optional) – ORDER BY clause body (D-12).
limit (int, optional) – LIMIT value (D-12).

Returns:

Matching rows in the requested form.

Return type:

list of dict or gpd.GeoDataFrame

Raises:

ValueError – If into is invalid or geometry forms are not exclusive.
InvalidIdentifierError – If any identifier is invalid.

within(left_table: str, left_geom: str, right_table: str, right_geom: str, *, schema: str = 'public', into: Literal['rows'] = 'rows', columns: list[str] | None = None, filter: str | None = None, where: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]][source]¶

within(left_table: str, left_geom: str, right_table: str, right_geom: str, *, schema: str = 'public', into: Literal['gdf'], columns: list[str] | None = None, filter: str | None = None, where: str | None = None, order_by: str | None = None, limit: int | None = None) → gpd.GeoDataFrame

within(left_table: str, left_geom: str, right_table: str, right_geom: str, *, schema: str = 'public', into: str, columns: list[str] | None = None, filter: str | None = None, where: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]] | gpd.GeoDataFrame

Select left-table rows whose geometry is within a right-table row.

Two-table JOIN form per 08-DESIGN §3 (dedicated signature, D-08).

Parameters:

left_table (str) – Table whose rows are returned.
left_geom (str) – Geometry column of the left table.
right_table (str) – Containing table.
right_geom (str) – Geometry column of the right table.
schema (str, optional) – Schema name for both tables, by default “public”.
into (str, optional) – "rows" or "gdf", by default “rows” (D-01).
columns (list of str, optional) – Columns to select, by default all (D-03).
filter (str, optional) – Raw SQL filter fragment (D-11).
where (str, optional) – Deprecated. Use filter= instead.
order_by (str, optional) – ORDER BY clause body (D-12).
limit (int, optional) – LIMIT value (D-12).

Returns:

Matching rows in the requested form.

Return type:

list of dict or gpd.GeoDataFrame

Raises:

ValueError – If into is invalid.
InvalidIdentifierError – If any identifier is invalid.

intersects(table: str, *, geom: str = 'geometry', schema: str = 'public', point: tuple[float, float] | None = None, wkt: str | None = None, geojson: dict[str, Any] | None = None, ref: tuple[str, str] | None = None, srid: int = 4326, into: Literal['rows'] = 'rows', columns: list[str] | None = None, filter: str | None = None, where: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]][source]¶

intersects(table: str, *, geom: str = 'geometry', schema: str = 'public', point: tuple[float, float] | None = None, wkt: str | None = None, geojson: dict[str, Any] | None = None, ref: tuple[str, str] | None = None, srid: int = 4326, into: Literal['gdf'], columns: list[str] | None = None, filter: str | None = None, where: str | None = None, order_by: str | None = None, limit: int | None = None) → gpd.GeoDataFrame

Select rows whose geometry intersects the input geometry.

Parameters:

table (str) – Table to query.
geom (str, optional) – Geometry column name, by default “geometry” (D-06).
schema (str, optional) – Schema name, by default “public”.
point (tuple of float, optional) – (x, y) input geometry (one of the four D-05 forms).
wkt (str, optional) – WKT input geometry.
geojson (dict, optional) – GeoJSON input geometry.
ref (tuple of str, optional) – (ref_table, ref_col) input geometry (EXISTS, D-08).
srid (int, optional) – SRID of the input geometry, by default 4326 (D-07).
into (str, optional) – "rows" or "gdf", by default “rows” (D-01).
columns (list of str, optional) – Columns to select, by default all (D-03).
filter (str, optional) – Additional raw SQL filter (D-11).
where (str, optional) – Deprecated. Use filter= instead.
order_by (str, optional) – ORDER BY clause body (D-12).
limit (int, optional) – LIMIT value (D-12).

Returns:

Matching rows in the requested form.

Return type:

list of dict or gpd.GeoDataFrame

Raises:

ValueError – If into is invalid or geometry forms are not exclusive.
InvalidIdentifierError – If any identifier is invalid.

overlaps(table: str, *, geom: str = 'geometry', schema: str = 'public', point: tuple[float, float] | None = None, wkt: str | None = None, geojson: dict[str, Any] | None = None, ref: tuple[str, str] | None = None, srid: int = 4326, into: Literal['rows'] = 'rows', columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]][source]¶

overlaps(table: str, *, geom: str = 'geometry', schema: str = 'public', point: tuple[float, float] | None = None, wkt: str | None = None, geojson: dict[str, Any] | None = None, ref: tuple[str, str] | None = None, srid: int = 4326, into: Literal['gdf'], columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → gpd.GeoDataFrame

Select rows whose geometry overlaps the input geometry.

Generates ST_Overlaps(t.{geom}, <geom_in>) (DE-9IM predicate, SPAT-F06/D-06). Mechanically mirrors intersects() — only the ST_* function name differs. No where= deprecated alias (born clean, D-06).

Parameters:

table (str) – Table to query.
geom (str, optional) – Geometry column name, by default “geometry” (D-06).
schema (str, optional) – Schema name, by default “public”.
point (tuple of float, optional) – (x, y) input geometry (one of the four D-05 forms).
wkt (str, optional) – WKT input geometry.
geojson (dict, optional) – GeoJSON input geometry.
ref (tuple of str, optional) – (ref_table, ref_col) input geometry (EXISTS, D-08).
srid (int, optional) – SRID of the input geometry, by default 4326 (D-07).
into (str, optional) – "rows" or "gdf", by default “rows” (D-01).
columns (list of str, optional) – Columns to select, by default all (D-03).
filter (str, optional) – Additional raw SQL filter (D-11).
order_by (str, optional) – ORDER BY clause body (D-12).
limit (int, optional) – LIMIT value (D-12).

Returns:

Matching rows in the requested form.

Return type:

list of dict or gpd.GeoDataFrame

Raises:

ValueError – If into is invalid or geometry forms are not exclusive.
InvalidIdentifierError – If any identifier is invalid.

touches(table: str, *, geom: str = 'geometry', schema: str = 'public', point: tuple[float, float] | None = None, wkt: str | None = None, geojson: dict[str, Any] | None = None, ref: tuple[str, str] | None = None, srid: int = 4326, into: Literal['rows'] = 'rows', columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]][source]¶

touches(table: str, *, geom: str = 'geometry', schema: str = 'public', point: tuple[float, float] | None = None, wkt: str | None = None, geojson: dict[str, Any] | None = None, ref: tuple[str, str] | None = None, srid: int = 4326, into: Literal['gdf'], columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → gpd.GeoDataFrame

Select rows whose geometry touches the input geometry.

Generates ST_Touches(t.{geom}, <geom_in>) (DE-9IM predicate, SPAT-F06/D-06). Mechanically mirrors intersects() — only the ST_* function name differs. No where= deprecated alias (born clean, D-06).

Parameters:

table (str) – Table to query.
geom (str, optional) – Geometry column name, by default “geometry” (D-06).
schema (str, optional) – Schema name, by default “public”.
point (tuple of float, optional) – (x, y) input geometry (one of the four D-05 forms).
wkt (str, optional) – WKT input geometry.
geojson (dict, optional) – GeoJSON input geometry.
ref (tuple of str, optional) – (ref_table, ref_col) input geometry (EXISTS, D-08).
srid (int, optional) – SRID of the input geometry, by default 4326 (D-07).
into (str, optional) – "rows" or "gdf", by default “rows” (D-01).
columns (list of str, optional) – Columns to select, by default all (D-03).
filter (str, optional) – Additional raw SQL filter (D-11).
order_by (str, optional) – ORDER BY clause body (D-12).
limit (int, optional) – LIMIT value (D-12).

Returns:

Matching rows in the requested form.

Return type:

list of dict or gpd.GeoDataFrame

Raises:

ValueError – If into is invalid or geometry forms are not exclusive.
InvalidIdentifierError – If any identifier is invalid.

crosses(table: str, *, geom: str = 'geometry', schema: str = 'public', point: tuple[float, float] | None = None, wkt: str | None = None, geojson: dict[str, Any] | None = None, ref: tuple[str, str] | None = None, srid: int = 4326, into: Literal['rows'] = 'rows', columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]][source]¶

crosses(table: str, *, geom: str = 'geometry', schema: str = 'public', point: tuple[float, float] | None = None, wkt: str | None = None, geojson: dict[str, Any] | None = None, ref: tuple[str, str] | None = None, srid: int = 4326, into: Literal['gdf'], columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → gpd.GeoDataFrame

Select rows whose geometry crosses the input geometry.

Generates ST_Crosses(t.{geom}, <geom_in>) (DE-9IM predicate, SPAT-F06/D-06). Mechanically mirrors intersects() — only the ST_* function name differs. No where= deprecated alias (born clean, D-06).

Parameters:

table (str) – Table to query.
geom (str, optional) – Geometry column name, by default “geometry” (D-06).
schema (str, optional) – Schema name, by default “public”.
point (tuple of float, optional) – (x, y) input geometry (one of the four D-05 forms).
wkt (str, optional) – WKT input geometry.
geojson (dict, optional) – GeoJSON input geometry.
ref (tuple of str, optional) – (ref_table, ref_col) input geometry (EXISTS, D-08).
srid (int, optional) – SRID of the input geometry, by default 4326 (D-07).
into (str, optional) – "rows" or "gdf", by default “rows” (D-01).
columns (list of str, optional) – Columns to select, by default all (D-03).
filter (str, optional) – Additional raw SQL filter (D-11).
order_by (str, optional) – ORDER BY clause body (D-12).
limit (int, optional) – LIMIT value (D-12).

Returns:

Matching rows in the requested form.

Return type:

list of dict or gpd.GeoDataFrame

Raises:

ValueError – If into is invalid or geometry forms are not exclusive.
InvalidIdentifierError – If any identifier is invalid.

disjoint(table: str, *, geom: str = 'geometry', schema: str = 'public', point: tuple[float, float] | None = None, wkt: str | None = None, geojson: dict[str, Any] | None = None, ref: tuple[str, str] | None = None, srid: int = 4326, into: Literal['rows'] = 'rows', columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]][source]¶

disjoint(table: str, *, geom: str = 'geometry', schema: str = 'public', point: tuple[float, float] | None = None, wkt: str | None = None, geojson: dict[str, Any] | None = None, ref: tuple[str, str] | None = None, srid: int = 4326, into: Literal['gdf'], columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → gpd.GeoDataFrame

Select rows whose geometry is disjoint from the input geometry.

Generates ST_Disjoint(t.{geom}, <geom_in>) (DE-9IM predicate, SPAT-F06/D-07). Mechanically mirrors intersects() — only the ST_* function name differs. No where= deprecated alias (born clean, D-06).

Warning

ST_Disjoint does not use indexes [postgis.net/docs/ST_Disjoint.html: “This function call does not use indexes”]. For large indexed tables, use a negated NOT ST_Intersects predicate instead — e.g. via the filter= escape hatch — for an indexable, more performant equivalent (D-08).

Parameters:

table (str) – Table to query.
geom (str, optional) – Geometry column name, by default “geometry” (D-06).
schema (str, optional) – Schema name, by default “public”.
point (tuple of float, optional) – (x, y) input geometry (one of the four D-05 forms).
wkt (str, optional) – WKT input geometry.
geojson (dict, optional) – GeoJSON input geometry.
ref (tuple of str, optional) – (ref_table, ref_col) input geometry — see the ref= EXISTS-quantifier warning above (D-07).
srid (int, optional) – SRID of the input geometry, by default 4326 (D-07).
into (str, optional) – "rows" or "gdf", by default “rows” (D-01).
columns (list of str, optional) – Columns to select, by default all (D-03).
filter (str, optional) – Additional raw SQL filter (D-11).
order_by (str, optional) – ORDER BY clause body (D-12).
limit (int, optional) – LIMIT value (D-12).

Returns:

Matching rows in the requested form.

Return type:

list of dict or gpd.GeoDataFrame

Raises:

ValueError – If into is invalid or geometry forms are not exclusive.
InvalidIdentifierError – If any identifier is invalid.

dwithin(table: str, *, geom: str = 'geometry', schema: str = 'public', point: tuple[float, float] | None = None, wkt: str | None = None, geojson: dict[str, Any] | None = None, ref: tuple[str, str] | None = None, srid: int = 4326, distance: float, unit: str = 'm', into: Literal['rows'] = 'rows', columns: list[str] | None = None, filter: str | None = None, where: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]][source]¶

dwithin(table: str, *, geom: str = 'geometry', schema: str = 'public', point: tuple[float, float] | None = None, wkt: str | None = None, geojson: dict[str, Any] | None = None, ref: tuple[str, str] | None = None, srid: int = 4326, distance: float, unit: str = 'm', into: Literal['gdf'], columns: list[str] | None = None, filter: str | None = None, where: str | None = None, order_by: str | None = None, limit: int | None = None) → gpd.GeoDataFrame

dwithin(table: str, *, geom: str = 'geometry', schema: str = 'public', point: tuple[float, float] | None = None, wkt: str | None = None, geojson: dict[str, Any] | None = None, ref: tuple[str, str] | None = None, srid: int = 4326, distance: float, unit: str = 'm', into: str, columns: list[str] | None = None, filter: str | None = None, where: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]] | gpd.GeoDataFrame

Select rows within a distance of the input geometry.

Parameters:

table (str) – Table to query.
geom (str, optional) – Geometry column name, by default “geometry” (D-06).
schema (str, optional) – Schema name, by default “public”.
point (tuple of float, optional) – (x, y) input geometry (one of the four D-05 forms).
wkt (str, optional) – WKT input geometry.
geojson (dict, optional) – GeoJSON input geometry.
ref (tuple of str, optional) – (ref_table, ref_col) input geometry (EXISTS, D-08).
srid (int, optional) – SRID of the input geometry, by default 4326 (D-07).
distance (float) – Search distance, meters by default (D-09).
unit (str, optional) – "m" or "srid", by default “m” (D-09).
into (str, optional) – "rows" or "gdf", by default “rows” (D-01).
columns (list of str, optional) – Columns to select, by default all (D-03).
filter (str, optional) – Additional raw SQL filter (D-11).
where (str, optional) – Deprecated. Use filter= instead.
order_by (str, optional) – ORDER BY clause body (D-12).
limit (int, optional) – LIMIT value (D-12).

Returns:

Matching rows in the requested form.

Return type:

list of dict or gpd.GeoDataFrame

Raises:

ValueError – If into/unit is invalid or geometry forms are not exclusive.
InvalidIdentifierError – If any identifier is invalid.

distance(table: str, *, geom: str = 'geometry', schema: str = 'public', point: tuple[float, float] | None = None, wkt: str | None = None, geojson: dict[str, Any] | None = None, srid: int = 4326, unit: str = 'm', into: str = 'rows', columns: list[str] | None = None, filter: str | None = None, where: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]][source]¶

Select rows with their distance to the input geometry.

Scalar result (distance column); into="gdf" raises ValueError per D-02.

Parameters:

table (str) – Table to query.
geom (str, optional) – Geometry column name, by default “geometry” (D-06).
schema (str, optional) – Schema name, by default “public”.
point (tuple of float, optional) – (x, y) input geometry.
wkt (str, optional) – WKT input geometry.
geojson (dict, optional) – GeoJSON input geometry.
srid (int, optional) – SRID of the input geometry, by default 4326 (D-07).
unit (str, optional) – "m" or "srid", by default “m” (D-09).
into (str, optional) – Only "rows" is valid for this helper (D-02).
columns (list of str, optional) – Columns to select alongside the distance (D-03).
filter (str, optional) – Raw SQL filter fragment (D-11).
where (str, optional) – Deprecated. Use filter= instead.
order_by (str, optional) – ORDER BY clause body, e.g. "distance" (D-12).
limit (int, optional) – LIMIT value (D-12).

Returns:

Rows including the computed distance column.

Return type:

list of dict

Raises:

ValueError – If into="gdf" (scalar helper) or inputs are invalid.
InvalidIdentifierError – If any identifier is invalid.

nearest(table: str, *, geom: str = 'geometry', schema: str = 'public', point: tuple[float, float] | None = None, wkt: str | None = None, geojson: dict[str, Any] | None = None, srid: int = 4326, k: int = 5, into: Literal['rows'] = 'rows', columns: list[str] | None = None, filter: str | None = None, where: str | None = None) → list[dict[str, Any]][source]¶

nearest(table: str, *, geom: str = 'geometry', schema: str = 'public', point: tuple[float, float] | None = None, wkt: str | None = None, geojson: dict[str, Any] | None = None, srid: int = 4326, k: int = 5, into: Literal['gdf'], columns: list[str] | None = None, filter: str | None = None, where: str | None = None) → gpd.GeoDataFrame

nearest(table: str, *, geom: str = 'geometry', schema: str = 'public', point: tuple[float, float] | None = None, wkt: str | None = None, geojson: dict[str, Any] | None = None, srid: int = 4326, k: int = 5, into: str, columns: list[str] | None = None, filter: str | None = None, where: str | None = None) → list[dict[str, Any]] | gpd.GeoDataFrame

Select the k rows nearest to the input geometry (KNN).

Metric ordering via ::geography casts; a GiST index on the geometry column accelerates this. No unit= parameter (D-10).

Parameters:

table (str) – Table to query.
geom (str, optional) – Geometry column name, by default “geometry” (D-06).
schema (str, optional) – Schema name, by default “public”.
point (tuple of float, optional) – (x, y) input geometry.
wkt (str, optional) – WKT input geometry.
geojson (dict, optional) – GeoJSON input geometry.
srid (int, optional) – SRID of the input geometry, by default 4326 (D-07).
k (int, optional) – Number of nearest rows to return, by default 5.
into (str, optional) – "rows" or "gdf", by default “rows” (D-01).
columns (list of str, optional) – Columns to select, by default all (D-03).
filter (str, optional) – Raw SQL filter fragment (D-11).
where (str, optional) – Deprecated. Use filter= instead.

Returns:

The k nearest rows in proximity order.

Return type:

list of dict or gpd.GeoDataFrame

Raises:

ValueError – If into is invalid or geometry forms are not exclusive.
InvalidIdentifierError – If any identifier is invalid.

area(table: str, *, geom: str = 'geometry', schema: str = 'public', unit: str = 'm', into: str = 'rows', columns: list[str] | None = None, filter: str | None = None, where: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]][source]¶

Select rows with the area of their geometry.

Scalar result (area column); into="gdf" raises ValueError per D-02.

Parameters:

table (str) – Table to query.
geom (str, optional) – Geometry column name, by default “geometry” (D-06).
schema (str, optional) – Schema name, by default “public”.
unit (str, optional) – "m" (square meters) or "srid", by default “m” (D-09).
into (str, optional) – Only "rows" is valid for this helper (D-02).
columns (list of str, optional) – Columns to select alongside the area (D-03).
filter (str, optional) – Raw SQL filter fragment (D-11).
where (str, optional) – Deprecated. Use filter= instead.
order_by (str, optional) – ORDER BY clause body (D-12).
limit (int, optional) – LIMIT value (D-12).

Returns:

Rows including the computed area column.

Return type:

list of dict

Raises:

ValueError – If into="gdf" (scalar helper) or unit is invalid.
InvalidIdentifierError – If any identifier is invalid.

perimeter(table: str, *, geom: str = 'geometry', schema: str = 'public', unit: str = 'm', into: str = 'rows', columns: list[str] | None = None, filter: str | None = None, where: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]][source]¶

Select rows with the perimeter of their geometry.

Scalar result (perimeter column); into="gdf" raises ValueError per D-02.

Parameters:

table (str) – Table to query.
geom (str, optional) – Geometry column name, by default “geometry” (D-06).
schema (str, optional) – Schema name, by default “public”.
unit (str, optional) – "m" (meters) or "srid", by default “m” (D-09).
into (str, optional) – Only "rows" is valid for this helper (D-02).
columns (list of str, optional) – Columns to select alongside the perimeter (D-03).
filter (str, optional) – Raw SQL filter fragment (D-11).
where (str, optional) – Deprecated. Use filter= instead.
order_by (str, optional) – ORDER BY clause body (D-12).
limit (int, optional) – LIMIT value (D-12).

Returns:

Rows including the computed perimeter column.

Return type:

list of dict

Raises:

ValueError – If into="gdf" (scalar helper) or unit is invalid.
InvalidIdentifierError – If any identifier is invalid.

length(table: str, *, geom: str = 'geometry', schema: str = 'public', unit: str = 'm', into: str = 'rows', columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]][source]¶

Select rows with the length of their geometry (ST_Length).

Scalar result (length column); into="gdf" raises ValueError (D-12). ST_Length returns 0 for non-linear (areal/point) geometries (D-10). No where= deprecated alias (born clean).

Parameters:

table (str) – Table to query.
geom (str, optional) – Geometry column name, by default “geometry”.
schema (str, optional) – Schema name, by default “public”.
unit (str, optional) – "m" (meters) or "srid", by default “m” (D-10).
into (str, optional) – Only "rows" is valid for this helper (D-12).
columns (list of str, optional) – Columns to select alongside the length.
filter (str, optional) – Raw SQL filter fragment.
order_by (str, optional) – ORDER BY clause body.
limit (int, optional) – LIMIT value.

Returns:

Rows including the computed length column.

Return type:

list of dict

Raises:

ValueError – If into="gdf" (scalar helper) or unit is invalid.
InvalidIdentifierError – If any identifier is invalid.

is_valid(table: str, *, geom: str = 'geometry', schema: str = 'public', into: str = 'rows', columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]][source]¶

Select rows with the OGC validity of their geometry (ST_IsValid).

Scalar result (boolean is_valid column); into="gdf" raises ValueError (D-12). No where= deprecated alias (born clean).

Parameters:

table (str) – Table to query.
geom (str, optional) – Geometry column name, by default “geometry”.
schema (str, optional) – Schema name, by default “public”.
into (str, optional) – Only "rows" is valid for this helper (D-12).
columns (list of str, optional) – Columns to select alongside the validity flag.
filter (str, optional) – Raw SQL filter fragment.
order_by (str, optional) – ORDER BY clause body.
limit (int, optional) – LIMIT value.

Returns:

Rows including the computed boolean is_valid column.

Return type:

list of dict

Raises:

ValueError – If into="gdf" (scalar helper).
InvalidIdentifierError – If any identifier is invalid.

is_valid_reason(table: str, *, geom: str = 'geometry', schema: str = 'public', into: str = 'rows', columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]][source]¶

Select rows with the OGC validity reason of their geometry.

Scalar result (text is_valid_reason column, ST_IsValidReason); into="gdf" raises ValueError (D-12). No where= deprecated alias (born clean).

Parameters:

table (str) – Table to query.
geom (str, optional) – Geometry column name, by default “geometry”.
schema (str, optional) – Schema name, by default “public”.
into (str, optional) – Only "rows" is valid for this helper (D-12).
columns (list of str, optional) – Columns to select alongside the validity reason.
filter (str, optional) – Raw SQL filter fragment.
order_by (str, optional) – ORDER BY clause body.
limit (int, optional) – LIMIT value.

Returns:

Rows including the computed text is_valid_reason column.

Return type:

list of dict

Raises:

ValueError – If into="gdf" (scalar helper).
InvalidIdentifierError – If any identifier is invalid.

centroid(table: str, *, geom: str = 'geometry', schema: str = 'public', into: str = 'rows', columns: list[str] | None = None, filter: str | None = None, where: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]][source]¶

Select rows with their geometry centroid coordinates.

Scalar result (centroid_x/centroid_y columns); into="gdf" raises ValueError per D-02. No unit= parameter (D-10).

Parameters:

table (str) – Table to query.
geom (str, optional) – Geometry column name, by default “geometry” (D-06).
schema (str, optional) – Schema name, by default “public”.
into (str, optional) – Only "rows" is valid for this helper (D-02).
columns (list of str, optional) – Columns to select alongside the centroid (D-03).
filter (str, optional) – Raw SQL filter fragment (D-11).
where (str, optional) – Deprecated. Use filter= instead.
order_by (str, optional) – ORDER BY clause body (D-12).
limit (int, optional) – LIMIT value (D-12).

Returns:

Rows including centroid_x and centroid_y columns.

Return type:

list of dict

Raises:

ValueError – If into="gdf" (scalar helper).
InvalidIdentifierError – If any identifier is invalid.

as_geojson(table: str, *, geom: str = 'geometry', schema: str = 'public', max_decimal_digits: int = 6, include_bbox: bool = False, include_crs: bool = False, into: str = 'rows', columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]][source]¶

Select rows with their geometry serialized as a GeoJSON string.

Scalar result (geojson column containing a JSON string); into="gdf" raises ValueError per D-08. Callers use json.loads(row["geojson"]) when a dict is needed (D-06).

Born-clean filter= only — no where= deprecated alias (D-09).

Parameters:

table (str) – Table to query.
geom (str, optional) – Geometry column name, by default “geometry”.
schema (str, optional) – Schema name, by default “public”.
max_decimal_digits (int, optional) – Maximum decimal digits in coordinates, by default 6 (D-06).
include_bbox (bool, optional) – Include bounding box in GeoJSON output (options bit 1), by default False (D-06).
include_crs (bool, optional) – Include short CRS name in GeoJSON output (options bit 2), by default False (D-06).
into (str, optional) – Only "rows" is valid for this helper (D-08).
columns (list of str, optional) – Columns to select alongside the GeoJSON text (D-08).
filter (str, optional) – Raw SQL filter fragment (D-11).
order_by (str, optional) – ORDER BY clause body (D-12).
limit (int, optional) – LIMIT value (D-12).

Returns:

Rows including the computed geojson column (JSON string).

Return type:

list of dict

Raises:

ValueError – If into="gdf" (scalar helper, D-08).
InvalidIdentifierError – If any identifier is invalid.

as_text(table: str, *, geom: str = 'geometry', schema: str = 'public', max_decimal_digits: int | None = None, into: str = 'rows', columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]][source]¶

Select rows with their geometry serialized as a WKT string.

Scalar result (wkt column); into="gdf" raises ValueError per D-08. Defaults to full precision (max_decimal_digits=None) — WKT is for exact interchange and round-trip use; silent truncation would be a data loss (D-07).

Born-clean filter= only — no where= deprecated alias (D-09).

Parameters:

table (str) – Table to query.
geom (str, optional) – Geometry column name, by default “geometry”.
schema (str, optional) – Schema name, by default “public”.
max_decimal_digits (int or None, optional) – Maximum decimal digits in coordinates, by default None (full precision, one-arg ST_AsText). When set, two-arg form is used (D-07); the two-arg form requires PostGIS >= 3.1.
into (str, optional) – Only "rows" is valid for this helper (D-08).
columns (list of str, optional) – Columns to select alongside the WKT text (D-08).
filter (str, optional) – Raw SQL filter fragment (D-11).
order_by (str, optional) – ORDER BY clause body (D-12).
limit (int, optional) – LIMIT value (D-12).

Returns:

Rows including the computed wkt column (WKT string).

Return type:

list of dict

Raises:

ValueError – If into="gdf" (scalar helper, D-08).
InvalidIdentifierError – If any identifier is invalid.

as_mvt(table: str, *, geom: str = 'geometry', schema: str = 'public', tile_z: int, tile_x: int, tile_y: int, layer_name: str = 'default', extent: int = 4096, columns: list[str] | None = None, feature_id_column: str | None = None, srid: int | None = None, filter: str | None = None) → bytes[source]¶

Generate a Mapbox Vector Tile (MVT) for a slippy-map tile coordinate.

Returns the MVT tile as bytes (protobuf-encoded). An empty tile (all geometry clips outside the tile envelope) returns b"" — never None or memoryview (D-05).

Born-clean filter= only — no deprecated where= alias (D-09). No into= parameter (this method returns bytes, not rows or a GeoDataFrame, and is NOT in _SCALAR_HELPERS).

The enforced pipeline (D-03):

ST_Transform(t.geom, 3857)          # → Web Mercator
→ ST_AsMVTGeom(…, ST_TileEnvelope(z, x, y), extent)  # clip+scale
→ ST_AsMVT(q.*, layer_name, extent, 'geom_mvt')       # encode

SRID-0 rescue (D-04): if the geometry column stores SRID 0 (untagged), pass srid=<source_srid> to inject ST_SetSRID before the transform:

db.spatial.as_mvt("points", tile_z=10, tile_x=512, tile_y=400, srid=4326)

feature_id_column must reference an integer-type column — PostGIS enforces this (MVT spec requires 64-bit integer feature IDs).

Requires PostGIS ≥ 3.0 (ST_TileEnvelope, five-arg ST_AsMVT).

Parameters:

table (str) – Table to query.
geom (str, optional) – Geometry column name, by default "geometry".
schema (str, optional) – Schema name, by default "public".
tile_z (int) – Tile zoom level. Required keyword-only (D-02).
tile_x (int) – Tile column index. Required keyword-only (D-02).
tile_y (int) – Tile row index. Required keyword-only (D-02).
layer_name (str, optional) – MVT layer name, by default "default".
extent (int, optional) – MVT tile extent in pixel units, by default 4096.
columns (list of str, optional) – Extra columns to include as MVT feature properties.
feature_id_column (str or None, optional) – Integer-typed column to use as MVT feature ID (PostGIS requirement). None (default) = no feature ID.
srid (int or None, optional) – Source SRID override for SRID-0 geometry columns (D-04).
filter (str, optional) – Raw SQL fragment appended as WHERE {filter} inside the inner subquery (T-44-02c documented escape hatch).

Returns:

MVT protobuf tile bytes, or b"" for an empty tile (D-05).

Return type:

bytes

Raises:

InvalidIdentifierError – If any identifier (table, schema, geom, columns, or feature_id_column) is invalid (T-44-02).

buffer(table: str, *, geom: str = 'geometry', schema: str = 'public', distance: float, unit: str = 'm', into: Literal['rows'] = 'rows', columns: list[str] | None = None, filter: str | None = None, where: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]][source]¶

buffer(table: str, *, geom: str = 'geometry', schema: str = 'public', distance: float, unit: str = 'm', into: Literal['gdf'], columns: list[str] | None = None, filter: str | None = None, where: str | None = None, order_by: str | None = None, limit: int | None = None) → gpd.GeoDataFrame

buffer(table: str, *, geom: str = 'geometry', schema: str = 'public', distance: float, unit: str = 'm', into: str, columns: list[str] | None = None, filter: str | None = None, where: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]] | gpd.GeoDataFrame

Select rows with a buffer around their geometry.

Returns a buffer geometry column — valid for into="gdf".

Parameters:

table (str) – Table to query.
geom (str, optional) – Geometry column name, by default “geometry” (D-06).
schema (str, optional) – Schema name, by default “public”.
distance (float) – Buffer distance, meters by default (D-09).
unit (str, optional) – "m" or "srid", by default “m” (D-09).
into (str, optional) – "rows" or "gdf", by default “rows” (D-01).
columns (list of str, optional) – Columns to select alongside the buffer (D-03).
filter (str, optional) – Raw SQL filter fragment (D-11).
where (str, optional) – Deprecated. Use filter= instead.
order_by (str, optional) – ORDER BY clause body (D-12).
limit (int, optional) – LIMIT value (D-12).

Returns:

Rows including the buffer geometry column.

Return type:

list of dict or gpd.GeoDataFrame

Raises:

ValueError – If into/unit is invalid.
InvalidIdentifierError – If any identifier is invalid.

transform(table: str, *, geom: str = 'geometry', schema: str = 'public', to_srid: int, into: Literal['rows'] = 'rows', columns: list[str] | None = None, filter: str | None = None, where: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]][source]¶

transform(table: str, *, geom: str = 'geometry', schema: str = 'public', to_srid: int, into: Literal['gdf'], columns: list[str] | None = None, filter: str | None = None, where: str | None = None, order_by: str | None = None, limit: int | None = None) → gpd.GeoDataFrame

transform(table: str, *, geom: str = 'geometry', schema: str = 'public', to_srid: int, into: str, columns: list[str] | None = None, filter: str | None = None, where: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]] | gpd.GeoDataFrame

Select rows with their geometry transformed to another SRID.

Returns a geometry_transformed geometry column — valid for into="gdf". No unit= parameter (D-10).

Parameters:

table (str) – Table to query.
geom (str, optional) – Geometry column name, by default “geometry” (D-06).
schema (str, optional) – Schema name, by default “public”.
to_srid (int) – Target spatial reference identifier.
into (str, optional) – "rows" or "gdf", by default “rows” (D-01).
columns (list of str, optional) – Columns to select alongside the transform (D-03).
filter (str, optional) – Raw SQL filter fragment (D-11).
where (str, optional) – Deprecated. Use filter= instead.
order_by (str, optional) – ORDER BY clause body (D-12).
limit (int, optional) – LIMIT value (D-12).

Returns:

Rows including the geometry_transformed column.

Return type:

list of dict or gpd.GeoDataFrame

Raises:

ValueError – If into is invalid.
InvalidIdentifierError – If any identifier is invalid.

union(table: str, *, geom: str = 'geometry', schema: str = 'public', other_geom: str, grid_size: float | None = None, into: Literal['rows'] = 'rows', columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]][source]¶

union(table: str, *, geom: str = 'geometry', schema: str = 'public', other_geom: str, grid_size: float | None = None, into: Literal['gdf'], columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → gpd.GeoDataFrame

union(table: str, *, geom: str = 'geometry', schema: str = 'public', other_geom: str, grid_size: float | None = None, into: str, columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]] | gpd.GeoDataFrame

Select rows with the pairwise union of two geometry columns.

Returns a union_geom geometry column via ST_Union(t.{geom}, t.{other_geom}) — valid for into="gdf". No where= deprecated alias (born clean for 1.0 freeze, D-10).

Note: inputs with mismatched SRIDs will raise a native PostGIS error at execution time (D-06). No pre-check is performed.

Parameters:

table (str) – Table to query.
geom (str, optional) – First geometry column name, by default “geometry”.
schema (str, optional) – Schema name, by default “public”.
other_geom (str) – Second geometry column name (required, keyword-only).
grid_size (float, optional) – Fixed-precision (snap-rounding) overlay grid size, by default None. When set, emits the 3-arg ST_Union(a, b, %s) form (pure passthrough, no range guard, D-02). Requires PostGIS >= 3.1 / GEOS >= 3.9 — older servers raise a native PostGIS error (D-04, no version pre-check performed).
into (str, optional) – "rows" or "gdf", by default “rows”.
columns (list of str, optional) – Columns to select alongside the result.
filter (str, optional) – Raw SQL filter fragment.
order_by (str, optional) – ORDER BY clause body.
limit (int, optional) – LIMIT value.

Returns:

Rows including the union_geom geometry column.

Return type:

list of dict or gpd.GeoDataFrame

Raises:

ValueError – If into is invalid.
InvalidIdentifierError – If any identifier is invalid.

difference(table: str, *, geom: str = 'geometry', schema: str = 'public', other_geom: str, grid_size: float | None = None, into: Literal['rows'] = 'rows', columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]][source]¶

difference(table: str, *, geom: str = 'geometry', schema: str = 'public', other_geom: str, grid_size: float | None = None, into: Literal['gdf'], columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → gpd.GeoDataFrame

difference(table: str, *, geom: str = 'geometry', schema: str = 'public', other_geom: str, grid_size: float | None = None, into: str, columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]] | gpd.GeoDataFrame

Select rows with the pairwise difference of two geometry columns.

Returns a difference geometry column via ST_Difference(t.{geom}, t.{other_geom}) (A minus B) — valid for into="gdf". No where= deprecated alias (born clean, D-10).

Note: inputs with mismatched SRIDs will raise a native PostGIS error at execution time (D-06). No pre-check is performed.

Parameters:

table (str) – Table to query.
geom (str, optional) – First geometry column name (minuend), by default “geometry”.
schema (str, optional) – Schema name, by default “public”.
other_geom (str) – Second geometry column name (subtrahend, required, keyword-only).
grid_size (float, optional) – Fixed-precision (snap-rounding) overlay grid size, by default None. When set, emits the 3-arg ST_Difference(a, b, %s) form (pure passthrough, no range guard, D-02). Requires PostGIS >= 3.1 / GEOS >= 3.9 — older servers raise a native PostGIS error (D-04, no version pre-check performed).
into (str, optional) – "rows" or "gdf", by default “rows”.
columns (list of str, optional) – Columns to select alongside the result.
filter (str, optional) – Raw SQL filter fragment.
order_by (str, optional) – ORDER BY clause body.
limit (int, optional) – LIMIT value.

Returns:

Rows including the difference geometry column.

Return type:

list of dict or gpd.GeoDataFrame

Raises:

ValueError – If into is invalid.
InvalidIdentifierError – If any identifier is invalid.

intersection(table: str, *, geom: str = 'geometry', schema: str = 'public', other_geom: str, grid_size: float | None = None, into: Literal['rows'] = 'rows', columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]][source]¶

intersection(table: str, *, geom: str = 'geometry', schema: str = 'public', other_geom: str, grid_size: float | None = None, into: Literal['gdf'], columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → gpd.GeoDataFrame

intersection(table: str, *, geom: str = 'geometry', schema: str = 'public', other_geom: str, grid_size: float | None = None, into: str, columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]] | gpd.GeoDataFrame

Select rows with the pairwise intersection of two geometry columns.

Returns an intersection geometry column via ST_Intersection(t.{geom}, t.{other_geom}) (shared portion) — valid for into="gdf". No where= deprecated alias (born clean, D-10).

Note: inputs with mismatched SRIDs will raise a native PostGIS error at execution time (D-06). No pre-check is performed.

Parameters:

table (str) – Table to query.
geom (str, optional) – First geometry column name, by default “geometry”.
schema (str, optional) – Schema name, by default “public”.
other_geom (str) – Second geometry column name (required, keyword-only).
grid_size (float, optional) – Fixed-precision (snap-rounding) overlay grid size, by default None. When set, emits the 3-arg ST_Intersection(a, b, %s) form (pure passthrough, no range guard, D-02). Requires PostGIS >= 3.1 / GEOS >= 3.9 — older servers raise a native PostGIS error (D-04, no version pre-check performed).
into (str, optional) – "rows" or "gdf", by default “rows”.
columns (list of str, optional) – Columns to select alongside the result.
filter (str, optional) – Raw SQL filter fragment.
order_by (str, optional) – ORDER BY clause body.
limit (int, optional) – LIMIT value.

Returns:

Rows including the intersection geometry column.

Return type:

list of dict or gpd.GeoDataFrame

Raises:

ValueError – If into is invalid.
InvalidIdentifierError – If any identifier is invalid.

simplify(table: str, *, geom: str = 'geometry', schema: str = 'public', tolerance: float, preserve_topology: bool = True, preserve_collapsed: bool = False, into: Literal['rows'] = 'rows', columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]][source]¶

simplify(table: str, *, geom: str = 'geometry', schema: str = 'public', tolerance: float, preserve_topology: bool = True, preserve_collapsed: bool = False, into: Literal['gdf'], columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → gpd.GeoDataFrame

simplify(table: str, *, geom: str = 'geometry', schema: str = 'public', tolerance: float, preserve_topology: bool = True, preserve_collapsed: bool = False, into: str, columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]] | gpd.GeoDataFrame

Select rows with their geometry simplified (ST_SimplifyPreserveTopology default).

Adds a simplified geometry column (D-01). The default uses ST_SimplifyPreserveTopology which guarantees a non-NULL result. Pass preserve_topology=False to use ST_Simplify — that variant may return NULL when the geometry collapses below tolerance (D-07). Valid for into="gdf". No where= deprecated alias (born clean, D-10).

Parameters:

table (str) – Table to query.
geom (str, optional) – Geometry column name, by default “geometry”.
schema (str, optional) – Schema name, by default “public”.
tolerance (float) – Simplification tolerance (required keyword-only, D-05).
preserve_topology (bool, optional) – If True (default) use ST_SimplifyPreserveTopology (no NULL collapse). If False use ST_Simplify (can return NULL).
preserve_collapsed (bool, optional) – Only consulted when preserve_topology=False. Setting preserve_collapsed=True while preserve_topology=True raises ValueError (D-05).
into (str, optional) – "rows" or "gdf", by default “rows”.
columns (list of str, optional) – Columns to select alongside the result.
filter (str, optional) – Raw SQL filter fragment.
order_by (str, optional) – ORDER BY clause body.
limit (int, optional) – LIMIT value.

Returns:

Rows including the simplified geometry column.

Return type:

list of dict or gpd.GeoDataFrame

Raises:

ValueError – If into is invalid, or if preserve_collapsed=True and preserve_topology=True (contradictory, D-05).
InvalidIdentifierError – If any identifier is invalid.

convex_hull(table: str, *, geom: str = 'geometry', schema: str = 'public', into: Literal['rows'] = 'rows', columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]][source]¶

convex_hull(table: str, *, geom: str = 'geometry', schema: str = 'public', into: Literal['gdf'], columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → gpd.GeoDataFrame

convex_hull(table: str, *, geom: str = 'geometry', schema: str = 'public', into: str, columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]] | gpd.GeoDataFrame

Select rows with the convex hull of each geometry (ST_ConvexHull).

Adds a convex_hull geometry column (D-01). Returns NULL for empty geometries (pass-through, D-07). Valid for into="gdf". No where= deprecated alias (born clean, D-10).

Parameters:

table (str) – Table to query.
geom (str, optional) – Geometry column name, by default “geometry”.
schema (str, optional) – Schema name, by default “public”.
into (str, optional) – "rows" or "gdf", by default “rows”.
columns (list of str, optional) – Columns to select alongside the result.
filter (str, optional) – Raw SQL filter fragment.
order_by (str, optional) – ORDER BY clause body.
limit (int, optional) – LIMIT value.

Returns:

Rows including the convex_hull geometry column.

Return type:

list of dict or gpd.GeoDataFrame

Raises:

ValueError – If into is invalid.
InvalidIdentifierError – If any identifier is invalid.

envelope(table: str, *, geom: str = 'geometry', schema: str = 'public', into: Literal['rows'] = 'rows', columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]][source]¶

envelope(table: str, *, geom: str = 'geometry', schema: str = 'public', into: Literal['gdf'], columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → gpd.GeoDataFrame

Select rows with the bounding-box geometry of each row (ST_Envelope).

Adds an envelope geometry column (D-11) — the per-row minimum bounding rectangle, preserving SRID. Valid for into="gdf". No where= deprecated alias (born clean, D-10).

Parameters:

table (str) – Table to query.
geom (str, optional) – Geometry column name, by default “geometry”.
schema (str, optional) – Schema name, by default “public”.
into (str, optional) – "rows" or "gdf", by default “rows”.
columns (list of str, optional) – Columns to select alongside the result.
filter (str, optional) – Raw SQL filter fragment.
order_by (str, optional) – ORDER BY clause body.
limit (int, optional) – LIMIT value.

Returns:

Rows including the envelope geometry column.

Return type:

list of dict or gpd.GeoDataFrame

Raises:

ValueError – If into is invalid.
InvalidIdentifierError – If any identifier is invalid.

point_on_surface(table: str, *, geom: str = 'geometry', schema: str = 'public', into: Literal['rows'] = 'rows', columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]][source]¶

point_on_surface(table: str, *, geom: str = 'geometry', schema: str = 'public', into: Literal['gdf'], columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → gpd.GeoDataFrame

point_on_surface(table: str, *, geom: str = 'geometry', schema: str = 'public', into: str, columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]] | gpd.GeoDataFrame

Select rows with a guaranteed-inside representative point (ST_PointOnSurface).

Adds a point_on_surface geometry column (D-05/D-11). Unlike centroid()’s scalar x/y shape, this is a real POINT geometry guaranteed to lie on the input geometry (useful for labels/markers/mapping), preserving SRID. Valid for into="gdf". No where= deprecated alias (born clean, D-10).

Parameters:

table (str) – Table to query.
geom (str, optional) – Geometry column name, by default “geometry”.
schema (str, optional) – Schema name, by default “public”.
into (str, optional) – "rows" or "gdf", by default “rows”.
columns (list of str, optional) – Columns to select alongside the result.
filter (str, optional) – Raw SQL filter fragment.
order_by (str, optional) – ORDER BY clause body.
limit (int, optional) – LIMIT value.

Returns:

Rows including the point_on_surface geometry column.

Return type:

list of dict or gpd.GeoDataFrame

Raises:

ValueError – If into is invalid.
InvalidIdentifierError – If any identifier is invalid.

make_valid(table: str, *, geom: str = 'geometry', schema: str = 'public', method: str | None = None, keep_collapsed: bool | None = None, into: Literal['rows'] = 'rows', columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]][source]¶

make_valid(table: str, *, geom: str = 'geometry', schema: str = 'public', method: str | None = None, keep_collapsed: bool | None = None, into: Literal['gdf'], columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → gpd.GeoDataFrame

Select rows with their geometry repaired via ST_MakeValid.

Adds a made_valid geometry column (D-01). Invalid geometries (e.g. bowtie self-intersections) are repaired — the output type may change (POLYGON → MULTIPOLYGON or GEOMETRYCOLLECTION, D-07). Already-valid geometries are returned unchanged. Valid for into="gdf". No where= deprecated alias (born clean, D-10).

Parameters:

table (str) – Table to query.
geom (str, optional) – Geometry column name, by default “geometry”.
schema (str, optional) – Schema name, by default “public”.
method ({None, "linework", "structure"}, optional) – Repair algorithm, by default None (PostGIS default). "structure" requires PostGIS ≥ 3.2 / GEOS ≥ 3.10 (D-04); on older servers, PostGIS raises a native error.
keep_collapsed (bool or None, optional) – Only valid when method="structure". Controls whether collapsed geometries are kept (True/False) or use the PostGIS default (None). Raises ValueError when set with any other method (D-03).
into (str, optional) – "rows" or "gdf", by default “rows”.
columns (list of str, optional) – Columns to select alongside the result.
filter (str, optional) – Raw SQL filter fragment.
order_by (str, optional) – ORDER BY clause body.
limit (int, optional) – LIMIT value.

Returns:

Rows including the made_valid geometry column.

Return type:

list of dict or gpd.GeoDataFrame

Raises:

ValueError – If into is invalid, method is not in the allowed set (D-02), or keep_collapsed is set with a non-structure method (D-03).
InvalidIdentifierError – If any identifier is invalid.

collect_geometries(table: str, *, geom: str = 'geometry', schema: str = 'public', group_by: str | list[str] | None = None, into: Literal['rows'] = 'rows', filter: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]][source]¶

collect_geometries(table: str, *, geom: str = 'geometry', schema: str = 'public', group_by: str | list[str] | None = None, into: Literal['gdf'], filter: str | None = None, order_by: str | None = None, limit: int | None = None) → gpd.GeoDataFrame

Collect geometries into a geometry collection per group via ST_Collect (SPAT2-07).

Adds a collected geometry column (D-06). The result is a GEOMETRYCOLLECTION (or the input type for single-row groups).

NULL geometry inputs are skipped by the PostGIS aggregate; a group (or the whole table) with all-NULL inputs yields one row with a NULL collected value. No HAVING filter is applied (D-07).

Valid for into="gdf". No where= deprecated alias (born clean, D-09). group_by is keyword-only (D-09).

Parameters:

table (str) – Table to query.
geom (str, optional) – Geometry column name, by default "geometry".
schema (str, optional) – Schema name, by default "public".
group_by (str or list of str or None, optional) – Column(s) to group by. A bare str is normalized to [str] (D-01). Pass None (default) for a whole-table aggregate. Raises ValueError for an empty list.
into (str, optional) – "rows" or "gdf", by default "rows".
filter (str, optional) – Raw SQL filter fragment (pre-aggregate WHERE clause).
order_by (str, optional) – ORDER BY clause body.
limit (int, optional) – LIMIT value.

Returns:

Rows or GeoDataFrame including the collected geometry column.

Return type:

list of dict or gpd.GeoDataFrame

Raises:

ValueError – If into is invalid or group_by is an empty list.
InvalidIdentifierError – If any identifier (table, schema, geom, or group_by columns) is invalid.

Examples

Whole-table collection:

rows = db.spatial.collect_geometries("zones")

Grouped by region:

rows = db.spatial.collect_geometries("zones", group_by="region")

union_aggregate(table: str, *, geom: str = 'geometry', schema: str = 'public', group_by: str | list[str] | None = None, into: Literal['rows'] = 'rows', filter: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]][source]¶

union_aggregate(table: str, *, geom: str = 'geometry', schema: str = 'public', group_by: str | list[str] | None = None, into: Literal['gdf'], filter: str | None = None, order_by: str | None = None, limit: int | None = None) → gpd.GeoDataFrame

Dissolve geometries into a merged boundary per group via ST_Union (SPAT2-08).

Adds a dissolved geometry column (D-06). Unlike the pairwise union method (Phase 42), this form aggregates all geometries in a group into a single merged result.

NULL geometry inputs are skipped by the PostGIS aggregate; a group (or the whole table) with all-NULL inputs yields one row with a NULL dissolved value. No HAVING filter is applied (D-07).

Valid for into="gdf". No where= deprecated alias (born clean, D-09). group_by is keyword-only (D-09).

Parameters:

table (str) – Table to query.
geom (str, optional) – Geometry column name, by default "geometry".
schema (str, optional) – Schema name, by default "public".
group_by (str or list of str or None, optional) – Column(s) to group by. A bare str is normalized to [str] (D-01). Pass None (default) for a whole-table aggregate. Raises ValueError for an empty list.
into (str, optional) – "rows" or "gdf", by default "rows".
filter (str, optional) – Raw SQL filter fragment (pre-aggregate WHERE clause).
order_by (str, optional) – ORDER BY clause body.
limit (int, optional) – LIMIT value.

Returns:

Rows or GeoDataFrame including the dissolved geometry column.

Return type:

list of dict or gpd.GeoDataFrame

Raises:

ValueError – If into is invalid or group_by is an empty list.
InvalidIdentifierError – If any identifier (table, schema, geom, or group_by columns) is invalid.

Examples

Whole-table dissolve:

rows = db.spatial.union_aggregate("zones")

Grouped by region:

gdf = db.spatial.union_aggregate("zones", group_by="region", into="gdf")

extent(table: str, *, geom: str = 'geometry', schema: str = 'public', group_by: str | list[str] | None = None, srid: int = 4326, into: Literal['rows'] = 'rows', filter: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]][source]¶

extent(table: str, *, geom: str = 'geometry', schema: str = 'public', group_by: str | list[str] | None = None, srid: int = 4326, into: Literal['gdf'], filter: str | None = None, order_by: str | None = None, limit: int | None = None) → gpd.GeoDataFrame

Compute the bounding extent of a geometry column via ST_Extent (SPAT2-09).

ST_Extent returns a PostgreSQL box2d type. This method branches on into (D-04 exception — the only aggregate method that passes into to the builder):

into="rows" (default): returns the BOX(xmin ymin, xmax ymax) string via ST_Extent(t.{geom})::text AS extent.
into="gdf": returns a real SRID-tagged polygon geometry via ST_SetSRID(ST_Extent(t.{geom})::geometry, {srid}), loadable by GeoPandas.

The srid parameter is consulted only on the gdf branch via int(srid) (injection-safe; D-05). It is ignored for into="rows".

extent is not in _SCALAR_HELPERS — into="gdf" is valid via the geometry cast (D-04, Pitfall 3).

NULL geometry inputs are skipped by the PostGIS aggregate; a group (or the whole table) with all-NULL inputs yields one row with a NULL extent value. No HAVING filter is applied (D-07).

Valid for into="gdf". No where= deprecated alias (born clean, D-09). group_by and srid are keyword-only (D-09).

Parameters:

table (str) – Table to query.
geom (str, optional) – Geometry column name, by default "geometry".
schema (str, optional) – Schema name, by default "public".
group_by (str or list of str or None, optional) – Column(s) to group by. A bare str is normalized to [str] (D-01). Pass None (default) for a whole-table aggregate. Raises ValueError for an empty list.
srid (int, optional) – SRID for the into="gdf" cast, by default 4326. Ignored for into="rows" (D-05).
into (str, optional) – "rows" or "gdf", by default "rows".
filter (str, optional) – Raw SQL filter fragment (pre-aggregate WHERE clause).
order_by (str, optional) – ORDER BY clause body.
limit (int, optional) – LIMIT value.

Returns:

For into="rows": list of dicts with an extent key containing the BOX(...) text string (or None for all-NULL groups). For into="gdf": GeoDataFrame with an extent geometry column.

Return type:

list of dict or gpd.GeoDataFrame

Raises:

ValueError – If into is invalid or group_by is an empty list.
InvalidIdentifierError – If any identifier (table, schema, geom, or group_by columns) is invalid.

Examples

Whole-table bounding box as text:

rows = db.spatial.extent("zones")
# [{"extent": "BOX(0 0, 10 10)"}]

Grouped bounding box as a GeoDataFrame:

gdf = db.spatial.extent("zones", group_by="region", into="gdf", srid=4326)

create_spatial_index(table: str, *, column: str = 'geometry', schema: str = 'public', name: str | None = None) → None[source]¶

Create a GIST spatial index on a geometry column.

Parameters:

table (str) – Table name.
column (str, optional) – Geometry column name, by default “geometry”.
schema (str, optional) – Schema name, by default “public”.
name (str, optional) – Index name (auto-generated if not provided).

list_geometry_columns(*, schema: str | None = None) → list[dict[str, Any]][source]¶

List geometry columns in the database.

Parameters:: schema (str, optional) – Schema filter.
Returns:: List of geometry column info.
Return type:: list of dict

class pycopg.spatial.AsyncSpatialAccessor(db: AsyncDatabase)[source]¶

Bases: object

Async spatial helper namespace exposed as async_db.spatial.

Mirrors SpatialAccessor exactly — same builders, same parameters, same into= routing — with awaited execution. The PostGIS guard is deferred to the first method call because __init__ cannot await (lazy _postgis_ok flag, SPA-04).

__init__(db: AsyncDatabase) → None[source]¶

Initialize the accessor (PostGIS guard deferred to first call).

Parameters:: db (AsyncDatabase) – Parent async database instance.

async contains(table: str, *, geom: str = 'geometry', schema: str = 'public', point: tuple[float, float] | None = None, wkt: str | None = None, geojson: dict[str, Any] | None = None, ref: tuple[str, str] | None = None, srid: int = 4326, into: Literal['rows'] = 'rows', columns: list[str] | None = None, filter: str | None = None, where: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]][source]¶

async contains(table: str, *, geom: str = 'geometry', schema: str = 'public', point: tuple[float, float] | None = None, wkt: str | None = None, geojson: dict[str, Any] | None = None, ref: tuple[str, str] | None = None, srid: int = 4326, into: Literal['gdf'], columns: list[str] | None = None, filter: str | None = None, where: str | None = None, order_by: str | None = None, limit: int | None = None) → gpd.GeoDataFrame

Select rows whose geometry contains the input geometry (async).

Parameters:

table (str) – Table to query.
geom (str, optional) – Geometry column name, by default “geometry” (D-06).
schema (str, optional) – Schema name, by default “public”.
point (tuple of float, optional) – (x, y) input geometry (one of the four D-05 forms).
wkt (str, optional) – WKT input geometry.
geojson (dict, optional) – GeoJSON input geometry.
ref (tuple of str, optional) – (ref_table, ref_col) input geometry (EXISTS, D-08).
srid (int, optional) – SRID of the input geometry, by default 4326 (D-07).
into (str, optional) – "rows" or "gdf", by default “rows” (D-01).
columns (list of str, optional) – Columns to select, by default all (D-03).
filter (str, optional) – Additional raw SQL filter (D-11).
where (str, optional) – Deprecated. Use filter= instead.
order_by (str, optional) – ORDER BY clause body (D-12).
limit (int, optional) – LIMIT value (D-12).

Returns:

Matching rows in the requested form.

Return type:

list of dict or gpd.GeoDataFrame

Raises:

ValueError – If into is invalid or geometry forms are not exclusive.
InvalidIdentifierError – If any identifier is invalid.
ExtensionNotAvailableError – If PostGIS is not installed (first-call guard).

async within(left_table: str, left_geom: str, right_table: str, right_geom: str, *, schema: str = 'public', into: Literal['rows'] = 'rows', columns: list[str] | None = None, filter: str | None = None, where: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]][source]¶

async within(left_table: str, left_geom: str, right_table: str, right_geom: str, *, schema: str = 'public', into: Literal['gdf'], columns: list[str] | None = None, filter: str | None = None, where: str | None = None, order_by: str | None = None, limit: int | None = None) → gpd.GeoDataFrame

async within(left_table: str, left_geom: str, right_table: str, right_geom: str, *, schema: str = 'public', into: str, columns: list[str] | None = None, filter: str | None = None, where: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]] | gpd.GeoDataFrame

Select left rows whose geometry is within a right row (async).

Two-table JOIN form per 08-DESIGN §3 (dedicated signature, D-08).

Parameters:

left_table (str) – Table whose rows are returned.
left_geom (str) – Geometry column of the left table.
right_table (str) – Containing table.
right_geom (str) – Geometry column of the right table.
schema (str, optional) – Schema name for both tables, by default “public”.
into (str, optional) – "rows" or "gdf", by default “rows” (D-01).
columns (list of str, optional) – Columns to select, by default all (D-03).
filter (str, optional) – Raw SQL filter fragment (D-11).
where (str, optional) – Deprecated. Use filter= instead.
order_by (str, optional) – ORDER BY clause body (D-12).
limit (int, optional) – LIMIT value (D-12).

Returns:

Matching rows in the requested form.

Return type:

list of dict or gpd.GeoDataFrame

Raises:

ValueError – If into is invalid.
InvalidIdentifierError – If any identifier is invalid.
ExtensionNotAvailableError – If PostGIS is not installed (first-call guard).

async intersects(table: str, *, geom: str = 'geometry', schema: str = 'public', point: tuple[float, float] | None = None, wkt: str | None = None, geojson: dict[str, Any] | None = None, ref: tuple[str, str] | None = None, srid: int = 4326, into: Literal['rows'] = 'rows', columns: list[str] | None = None, filter: str | None = None, where: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]][source]¶

async intersects(table: str, *, geom: str = 'geometry', schema: str = 'public', point: tuple[float, float] | None = None, wkt: str | None = None, geojson: dict[str, Any] | None = None, ref: tuple[str, str] | None = None, srid: int = 4326, into: Literal['gdf'], columns: list[str] | None = None, filter: str | None = None, where: str | None = None, order_by: str | None = None, limit: int | None = None) → gpd.GeoDataFrame

Select rows whose geometry intersects the input geometry (async).

Parameters:

table (str) – Table to query.
geom (str, optional) – Geometry column name, by default “geometry” (D-06).
schema (str, optional) – Schema name, by default “public”.
point (tuple of float, optional) – (x, y) input geometry (one of the four D-05 forms).
wkt (str, optional) – WKT input geometry.
geojson (dict, optional) – GeoJSON input geometry.
ref (tuple of str, optional) – (ref_table, ref_col) input geometry (EXISTS, D-08).
srid (int, optional) – SRID of the input geometry, by default 4326 (D-07).
into (str, optional) – "rows" or "gdf", by default “rows” (D-01).
columns (list of str, optional) – Columns to select, by default all (D-03).
filter (str, optional) – Additional raw SQL filter (D-11).
where (str, optional) – Deprecated. Use filter= instead.
order_by (str, optional) – ORDER BY clause body (D-12).
limit (int, optional) – LIMIT value (D-12).

Returns:

Matching rows in the requested form.

Return type:

list of dict or gpd.GeoDataFrame

Raises:

ValueError – If into is invalid or geometry forms are not exclusive.
InvalidIdentifierError – If any identifier is invalid.
ExtensionNotAvailableError – If PostGIS is not installed (first-call guard).

async overlaps(table: str, *, geom: str = 'geometry', schema: str = 'public', point: tuple[float, float] | None = None, wkt: str | None = None, geojson: dict[str, Any] | None = None, ref: tuple[str, str] | None = None, srid: int = 4326, into: Literal['rows'] = 'rows', columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]][source]¶

async overlaps(table: str, *, geom: str = 'geometry', schema: str = 'public', point: tuple[float, float] | None = None, wkt: str | None = None, geojson: dict[str, Any] | None = None, ref: tuple[str, str] | None = None, srid: int = 4326, into: Literal['gdf'], columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → gpd.GeoDataFrame

Select rows whose geometry overlaps the input geometry (async).

Async mirror of SpatialAccessor.overlaps(). Generates ST_Overlaps(t.{geom}, <geom_in>) (DE-9IM predicate, SPAT-F06/D-06). No where= deprecated alias (born clean, D-06).

Parameters:

table (str) – Table to query.
geom (str, optional) – Geometry column name, by default “geometry” (D-06).
schema (str, optional) – Schema name, by default “public”.
point (tuple of float, optional) – (x, y) input geometry (one of the four D-05 forms).
wkt (str, optional) – WKT input geometry.
geojson (dict, optional) – GeoJSON input geometry.
ref (tuple of str, optional) – (ref_table, ref_col) input geometry (EXISTS, D-08).
srid (int, optional) – SRID of the input geometry, by default 4326 (D-07).
into (str, optional) – "rows" or "gdf", by default “rows” (D-01).
columns (list of str, optional) – Columns to select, by default all (D-03).
filter (str, optional) – Additional raw SQL filter (D-11).
order_by (str, optional) – ORDER BY clause body (D-12).
limit (int, optional) – LIMIT value (D-12).

Returns:

Matching rows in the requested form.

Return type:

list of dict or gpd.GeoDataFrame

Raises:

ValueError – If into is invalid or geometry forms are not exclusive.
InvalidIdentifierError – If any identifier is invalid.
ExtensionNotAvailableError – If PostGIS is not installed (first-call guard).

async touches(table: str, *, geom: str = 'geometry', schema: str = 'public', point: tuple[float, float] | None = None, wkt: str | None = None, geojson: dict[str, Any] | None = None, ref: tuple[str, str] | None = None, srid: int = 4326, into: Literal['rows'] = 'rows', columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]][source]¶

async touches(table: str, *, geom: str = 'geometry', schema: str = 'public', point: tuple[float, float] | None = None, wkt: str | None = None, geojson: dict[str, Any] | None = None, ref: tuple[str, str] | None = None, srid: int = 4326, into: Literal['gdf'], columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → gpd.GeoDataFrame

Select rows whose geometry touches the input geometry (async).

Async mirror of SpatialAccessor.touches(). Generates ST_Touches(t.{geom}, <geom_in>) (DE-9IM predicate, SPAT-F06/D-06). No where= deprecated alias (born clean, D-06).

Parameters:

table (str) – Table to query.
geom (str, optional) – Geometry column name, by default “geometry” (D-06).
schema (str, optional) – Schema name, by default “public”.
point (tuple of float, optional) – (x, y) input geometry (one of the four D-05 forms).
wkt (str, optional) – WKT input geometry.
geojson (dict, optional) – GeoJSON input geometry.
ref (tuple of str, optional) – (ref_table, ref_col) input geometry (EXISTS, D-08).
srid (int, optional) – SRID of the input geometry, by default 4326 (D-07).
into (str, optional) – "rows" or "gdf", by default “rows” (D-01).
columns (list of str, optional) – Columns to select, by default all (D-03).
filter (str, optional) – Additional raw SQL filter (D-11).
order_by (str, optional) – ORDER BY clause body (D-12).
limit (int, optional) – LIMIT value (D-12).

Returns:

Matching rows in the requested form.

Return type:

list of dict or gpd.GeoDataFrame

Raises:

ValueError – If into is invalid or geometry forms are not exclusive.
InvalidIdentifierError – If any identifier is invalid.
ExtensionNotAvailableError – If PostGIS is not installed (first-call guard).

async crosses(table: str, *, geom: str = 'geometry', schema: str = 'public', point: tuple[float, float] | None = None, wkt: str | None = None, geojson: dict[str, Any] | None = None, ref: tuple[str, str] | None = None, srid: int = 4326, into: Literal['rows'] = 'rows', columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]][source]¶

async crosses(table: str, *, geom: str = 'geometry', schema: str = 'public', point: tuple[float, float] | None = None, wkt: str | None = None, geojson: dict[str, Any] | None = None, ref: tuple[str, str] | None = None, srid: int = 4326, into: Literal['gdf'], columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → gpd.GeoDataFrame

Select rows whose geometry crosses the input geometry (async).

Async mirror of SpatialAccessor.crosses(). Generates ST_Crosses(t.{geom}, <geom_in>) (DE-9IM predicate, SPAT-F06/D-06). No where= deprecated alias (born clean, D-06).

Parameters:

table (str) – Table to query.
geom (str, optional) – Geometry column name, by default “geometry” (D-06).
schema (str, optional) – Schema name, by default “public”.
point (tuple of float, optional) – (x, y) input geometry (one of the four D-05 forms).
wkt (str, optional) – WKT input geometry.
geojson (dict, optional) – GeoJSON input geometry.
ref (tuple of str, optional) – (ref_table, ref_col) input geometry (EXISTS, D-08).
srid (int, optional) – SRID of the input geometry, by default 4326 (D-07).
into (str, optional) – "rows" or "gdf", by default “rows” (D-01).
columns (list of str, optional) – Columns to select, by default all (D-03).
filter (str, optional) – Additional raw SQL filter (D-11).
order_by (str, optional) – ORDER BY clause body (D-12).
limit (int, optional) – LIMIT value (D-12).

Returns:

Matching rows in the requested form.

Return type:

list of dict or gpd.GeoDataFrame

Raises:

ValueError – If into is invalid or geometry forms are not exclusive.
InvalidIdentifierError – If any identifier is invalid.
ExtensionNotAvailableError – If PostGIS is not installed (first-call guard).

async disjoint(table: str, *, geom: str = 'geometry', schema: str = 'public', point: tuple[float, float] | None = None, wkt: str | None = None, geojson: dict[str, Any] | None = None, ref: tuple[str, str] | None = None, srid: int = 4326, into: Literal['rows'] = 'rows', columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]][source]¶

async disjoint(table: str, *, geom: str = 'geometry', schema: str = 'public', point: tuple[float, float] | None = None, wkt: str | None = None, geojson: dict[str, Any] | None = None, ref: tuple[str, str] | None = None, srid: int = 4326, into: Literal['gdf'], columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → gpd.GeoDataFrame

Select rows whose geometry is disjoint from the input (async).

Async mirror of SpatialAccessor.disjoint(). Generates ST_Disjoint(t.{geom}, <geom_in>) (DE-9IM predicate, SPAT-F06/D-07). No where= deprecated alias (born clean, D-06).

Warning

Parameters:

table (str) – Table to query.
geom (str, optional) – Geometry column name, by default “geometry” (D-06).
schema (str, optional) – Schema name, by default “public”.
point (tuple of float, optional) – (x, y) input geometry (one of the four D-05 forms).
wkt (str, optional) – WKT input geometry.
geojson (dict, optional) – GeoJSON input geometry.
ref (tuple of str, optional) – (ref_table, ref_col) input geometry — see the ref= EXISTS-quantifier warning above (D-07).
srid (int, optional) – SRID of the input geometry, by default 4326 (D-07).
into (str, optional) – "rows" or "gdf", by default “rows” (D-01).
columns (list of str, optional) – Columns to select, by default all (D-03).
filter (str, optional) – Additional raw SQL filter (D-11).
order_by (str, optional) – ORDER BY clause body (D-12).
limit (int, optional) – LIMIT value (D-12).

Returns:

Matching rows in the requested form.

Return type:

list of dict or gpd.GeoDataFrame

Raises:

ValueError – If into is invalid or geometry forms are not exclusive.
InvalidIdentifierError – If any identifier is invalid.
ExtensionNotAvailableError – If PostGIS is not installed (first-call guard).

async dwithin(table: str, *, geom: str = 'geometry', schema: str = 'public', point: tuple[float, float] | None = None, wkt: str | None = None, geojson: dict[str, Any] | None = None, ref: tuple[str, str] | None = None, srid: int = 4326, distance: float, unit: str = 'm', into: Literal['rows'] = 'rows', columns: list[str] | None = None, filter: str | None = None, where: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]][source]¶

async dwithin(table: str, *, geom: str = 'geometry', schema: str = 'public', point: tuple[float, float] | None = None, wkt: str | None = None, geojson: dict[str, Any] | None = None, ref: tuple[str, str] | None = None, srid: int = 4326, distance: float, unit: str = 'm', into: Literal['gdf'], columns: list[str] | None = None, filter: str | None = None, where: str | None = None, order_by: str | None = None, limit: int | None = None) → gpd.GeoDataFrame

async dwithin(table: str, *, geom: str = 'geometry', schema: str = 'public', point: tuple[float, float] | None = None, wkt: str | None = None, geojson: dict[str, Any] | None = None, ref: tuple[str, str] | None = None, srid: int = 4326, distance: float, unit: str = 'm', into: str, columns: list[str] | None = None, filter: str | None = None, where: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]] | gpd.GeoDataFrame

Select rows within a distance of the input geometry (async).

Parameters:

table (str) – Table to query.
geom (str, optional) – Geometry column name, by default “geometry” (D-06).
schema (str, optional) – Schema name, by default “public”.
point (tuple of float, optional) – (x, y) input geometry (one of the four D-05 forms).
wkt (str, optional) – WKT input geometry.
geojson (dict, optional) – GeoJSON input geometry.
ref (tuple of str, optional) – (ref_table, ref_col) input geometry (EXISTS, D-08).
srid (int, optional) – SRID of the input geometry, by default 4326 (D-07).
distance (float) – Search distance, meters by default (D-09).
unit (str, optional) – "m" or "srid", by default “m” (D-09).
into (str, optional) – "rows" or "gdf", by default “rows” (D-01).
columns (list of str, optional) – Columns to select, by default all (D-03).
filter (str, optional) – Additional raw SQL filter (D-11).
where (str, optional) – Deprecated. Use filter= instead.
order_by (str, optional) – ORDER BY clause body (D-12).
limit (int, optional) – LIMIT value (D-12).

Returns:

Matching rows in the requested form.

Return type:

list of dict or gpd.GeoDataFrame

Raises:

ValueError – If into/unit is invalid or geometry forms are not exclusive.
InvalidIdentifierError – If any identifier is invalid.
ExtensionNotAvailableError – If PostGIS is not installed (first-call guard).

async distance(table: str, *, geom: str = 'geometry', schema: str = 'public', point: tuple[float, float] | None = None, wkt: str | None = None, geojson: dict[str, Any] | None = None, srid: int = 4326, unit: str = 'm', into: str = 'rows', columns: list[str] | None = None, filter: str | None = None, where: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]][source]¶

Select rows with their distance to the input geometry (async).

Scalar result (distance column); into="gdf" raises ValueError per D-02.

Parameters:

table (str) – Table to query.
geom (str, optional) – Geometry column name, by default “geometry” (D-06).
schema (str, optional) – Schema name, by default “public”.
point (tuple of float, optional) – (x, y) input geometry.
wkt (str, optional) – WKT input geometry.
geojson (dict, optional) – GeoJSON input geometry.
srid (int, optional) – SRID of the input geometry, by default 4326 (D-07).
unit (str, optional) – "m" or "srid", by default “m” (D-09).
into (str, optional) – Only "rows" is valid for this helper (D-02).
columns (list of str, optional) – Columns to select alongside the distance (D-03).
filter (str, optional) – Raw SQL filter fragment (D-11).
where (str, optional) – Deprecated. Use filter= instead.
order_by (str, optional) – ORDER BY clause body, e.g. "distance" (D-12).
limit (int, optional) – LIMIT value (D-12).

Returns:

Rows including the computed distance column.

Return type:

list of dict

Raises:

ValueError – If into="gdf" (scalar helper) or inputs are invalid.
InvalidIdentifierError – If any identifier is invalid.
ExtensionNotAvailableError – If PostGIS is not installed (first-call guard).

async nearest(table: str, *, geom: str = 'geometry', schema: str = 'public', point: tuple[float, float] | None = None, wkt: str | None = None, geojson: dict[str, Any] | None = None, srid: int = 4326, k: int = 5, into: Literal['rows'] = 'rows', columns: list[str] | None = None, filter: str | None = None, where: str | None = None) → list[dict[str, Any]][source]¶

async nearest(table: str, *, geom: str = 'geometry', schema: str = 'public', point: tuple[float, float] | None = None, wkt: str | None = None, geojson: dict[str, Any] | None = None, srid: int = 4326, k: int = 5, into: Literal['gdf'], columns: list[str] | None = None, filter: str | None = None, where: str | None = None) → gpd.GeoDataFrame

async nearest(table: str, *, geom: str = 'geometry', schema: str = 'public', point: tuple[float, float] | None = None, wkt: str | None = None, geojson: dict[str, Any] | None = None, srid: int = 4326, k: int = 5, into: str, columns: list[str] | None = None, filter: str | None = None, where: str | None = None) → list[dict[str, Any]] | gpd.GeoDataFrame

Select the k rows nearest to the input geometry (async KNN).

Metric ordering via ::geography casts; a GiST index on the geometry column accelerates this. No unit= parameter (D-10).

Parameters:

table (str) – Table to query.
geom (str, optional) – Geometry column name, by default “geometry” (D-06).
schema (str, optional) – Schema name, by default “public”.
point (tuple of float, optional) – (x, y) input geometry.
wkt (str, optional) – WKT input geometry.
geojson (dict, optional) – GeoJSON input geometry.
srid (int, optional) – SRID of the input geometry, by default 4326 (D-07).
k (int, optional) – Number of nearest rows to return, by default 5.
into (str, optional) – "rows" or "gdf", by default “rows” (D-01).
columns (list of str, optional) – Columns to select, by default all (D-03).
filter (str, optional) – Raw SQL filter fragment (D-11).
where (str, optional) – Deprecated. Use filter= instead.

Returns:

The k nearest rows in proximity order.

Return type:

list of dict or gpd.GeoDataFrame

Raises:

ValueError – If into is invalid or geometry forms are not exclusive.
InvalidIdentifierError – If any identifier is invalid.
ExtensionNotAvailableError – If PostGIS is not installed (first-call guard).

async area(table: str, *, geom: str = 'geometry', schema: str = 'public', unit: str = 'm', into: str = 'rows', columns: list[str] | None = None, filter: str | None = None, where: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]][source]¶

Select rows with the area of their geometry (async).

Scalar result (area column); into="gdf" raises ValueError per D-02.

Parameters:

table (str) – Table to query.
geom (str, optional) – Geometry column name, by default “geometry” (D-06).
schema (str, optional) – Schema name, by default “public”.
unit (str, optional) – "m" (square meters) or "srid", by default “m” (D-09).
into (str, optional) – Only "rows" is valid for this helper (D-02).
columns (list of str, optional) – Columns to select alongside the area (D-03).
filter (str, optional) – Raw SQL filter fragment (D-11).
where (str, optional) – Deprecated. Use filter= instead.
order_by (str, optional) – ORDER BY clause body (D-12).
limit (int, optional) – LIMIT value (D-12).

Returns:

Rows including the computed area column.

Return type:

list of dict

Raises:

ValueError – If into="gdf" (scalar helper) or unit is invalid.
InvalidIdentifierError – If any identifier is invalid.
ExtensionNotAvailableError – If PostGIS is not installed (first-call guard).

async perimeter(table: str, *, geom: str = 'geometry', schema: str = 'public', unit: str = 'm', into: str = 'rows', columns: list[str] | None = None, filter: str | None = None, where: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]][source]¶

Select rows with the perimeter of their geometry (async).

Scalar result (perimeter column); into="gdf" raises ValueError per D-02.

Parameters:

table (str) – Table to query.
geom (str, optional) – Geometry column name, by default “geometry” (D-06).
schema (str, optional) – Schema name, by default “public”.
unit (str, optional) – "m" (meters) or "srid", by default “m” (D-09).
into (str, optional) – Only "rows" is valid for this helper (D-02).
columns (list of str, optional) – Columns to select alongside the perimeter (D-03).
filter (str, optional) – Raw SQL filter fragment (D-11).
where (str, optional) – Deprecated. Use filter= instead.
order_by (str, optional) – ORDER BY clause body (D-12).
limit (int, optional) – LIMIT value (D-12).

Returns:

Rows including the computed perimeter column.

Return type:

list of dict

Raises:

ValueError – If into="gdf" (scalar helper) or unit is invalid.
InvalidIdentifierError – If any identifier is invalid.
ExtensionNotAvailableError – If PostGIS is not installed (first-call guard).

async length(table: str, *, geom: str = 'geometry', schema: str = 'public', unit: str = 'm', into: str = 'rows', columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]][source]¶

Select rows with the length of their geometry (async, ST_Length).

Scalar result (length column); into="gdf" raises ValueError (D-12). ST_Length returns 0 for non-linear (areal/point) geometries (D-10). No where= deprecated alias (born clean).

Parameters:

table (str) – Table to query.
geom (str, optional) – Geometry column name, by default “geometry”.
schema (str, optional) – Schema name, by default “public”.
unit (str, optional) – "m" (meters) or "srid", by default “m” (D-10).
into (str, optional) – Only "rows" is valid for this helper (D-12).
columns (list of str, optional) – Columns to select alongside the length.
filter (str, optional) – Raw SQL filter fragment.
order_by (str, optional) – ORDER BY clause body.
limit (int, optional) – LIMIT value.

Returns:

Rows including the computed length column.

Return type:

list of dict

Raises:

ValueError – If into="gdf" (scalar helper) or unit is invalid.
InvalidIdentifierError – If any identifier is invalid.
ExtensionNotAvailableError – If PostGIS is not installed (first-call guard).

async is_valid(table: str, *, geom: str = 'geometry', schema: str = 'public', into: str = 'rows', columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]][source]¶

Select rows with the OGC validity of their geometry (async, ST_IsValid).

Scalar result (boolean is_valid column); into="gdf" raises ValueError (D-12). No where= deprecated alias (born clean).

Parameters:

table (str) – Table to query.
geom (str, optional) – Geometry column name, by default “geometry”.
schema (str, optional) – Schema name, by default “public”.
into (str, optional) – Only "rows" is valid for this helper (D-12).
columns (list of str, optional) – Columns to select alongside the validity flag.
filter (str, optional) – Raw SQL filter fragment.
order_by (str, optional) – ORDER BY clause body.
limit (int, optional) – LIMIT value.

Returns:

Rows including the computed boolean is_valid column.

Return type:

list of dict

Raises:

ValueError – If into="gdf" (scalar helper).
InvalidIdentifierError – If any identifier is invalid.
ExtensionNotAvailableError – If PostGIS is not installed (first-call guard).

async is_valid_reason(table: str, *, geom: str = 'geometry', schema: str = 'public', into: str = 'rows', columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]][source]¶

Select rows with the OGC validity reason of their geometry (async).

Scalar result (text is_valid_reason column, ST_IsValidReason); into="gdf" raises ValueError (D-12). No where= deprecated alias (born clean).

Parameters:

table (str) – Table to query.
geom (str, optional) – Geometry column name, by default “geometry”.
schema (str, optional) – Schema name, by default “public”.
into (str, optional) – Only "rows" is valid for this helper (D-12).
columns (list of str, optional) – Columns to select alongside the validity reason.
filter (str, optional) – Raw SQL filter fragment.
order_by (str, optional) – ORDER BY clause body.
limit (int, optional) – LIMIT value.

Returns:

Rows including the computed text is_valid_reason column.

Return type:

list of dict

Raises:

ValueError – If into="gdf" (scalar helper).
InvalidIdentifierError – If any identifier is invalid.
ExtensionNotAvailableError – If PostGIS is not installed (first-call guard).

async centroid(table: str, *, geom: str = 'geometry', schema: str = 'public', into: str = 'rows', columns: list[str] | None = None, filter: str | None = None, where: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]][source]¶

Select rows with their geometry centroid coordinates (async).

Scalar result (centroid_x/centroid_y columns); into="gdf" raises ValueError per D-02. No unit= parameter (D-10).

Parameters:

table (str) – Table to query.
geom (str, optional) – Geometry column name, by default “geometry” (D-06).
schema (str, optional) – Schema name, by default “public”.
into (str, optional) – Only "rows" is valid for this helper (D-02).
columns (list of str, optional) – Columns to select alongside the centroid (D-03).
filter (str, optional) – Raw SQL filter fragment (D-11).
where (str, optional) – Deprecated. Use filter= instead.
order_by (str, optional) – ORDER BY clause body (D-12).
limit (int, optional) – LIMIT value (D-12).

Returns:

Rows including centroid_x and centroid_y columns.

Return type:

list of dict

Raises:

ValueError – If into="gdf" (scalar helper).
InvalidIdentifierError – If any identifier is invalid.
ExtensionNotAvailableError – If PostGIS is not installed (first-call guard).

async as_geojson(table: str, *, geom: str = 'geometry', schema: str = 'public', max_decimal_digits: int = 6, include_bbox: bool = False, include_crs: bool = False, into: str = 'rows', columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]][source]¶

Select rows with their geometry serialized as a GeoJSON string (async).

Scalar result (geojson column containing a JSON string); into="gdf" raises ValueError per D-08. Callers use json.loads(row["geojson"]) when a dict is needed (D-06).

Born-clean filter= only — no where= deprecated alias (D-09).

Parameters:

table (str) – Table to query.
geom (str, optional) – Geometry column name, by default “geometry”.
schema (str, optional) – Schema name, by default “public”.
max_decimal_digits (int, optional) – Maximum decimal digits in coordinates, by default 6 (D-06).
include_bbox (bool, optional) – Include bounding box in GeoJSON output (options bit 1), by default False (D-06).
include_crs (bool, optional) – Include short CRS name in GeoJSON output (options bit 2), by default False (D-06).
into (str, optional) – Only "rows" is valid for this helper (D-08).
columns (list of str, optional) – Columns to select alongside the GeoJSON text (D-08).
filter (str, optional) – Raw SQL filter fragment (D-11).
order_by (str, optional) – ORDER BY clause body (D-12).
limit (int, optional) – LIMIT value (D-12).

Returns:

Rows including the computed geojson column (JSON string).

Return type:

list of dict

Raises:

ValueError – If into="gdf" (scalar helper, D-08).
InvalidIdentifierError – If any identifier is invalid.
ExtensionNotAvailableError – If PostGIS is not installed (first-call guard).

async as_text(table: str, *, geom: str = 'geometry', schema: str = 'public', max_decimal_digits: int | None = None, into: str = 'rows', columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]][source]¶

Select rows with their geometry serialized as a WKT string (async).

Born-clean filter= only — no where= deprecated alias (D-09).

Parameters:

table (str) – Table to query.
geom (str, optional) – Geometry column name, by default “geometry”.
schema (str, optional) – Schema name, by default “public”.
max_decimal_digits (int or None, optional) – Maximum decimal digits in coordinates, by default None (full precision, one-arg ST_AsText). When set, two-arg form is used (D-07); the two-arg form requires PostGIS >= 3.1.
into (str, optional) – Only "rows" is valid for this helper (D-08).
columns (list of str, optional) – Columns to select alongside the WKT text (D-08).
filter (str, optional) – Raw SQL filter fragment (D-11).
order_by (str, optional) – ORDER BY clause body (D-12).
limit (int, optional) – LIMIT value (D-12).

Returns:

Rows including the computed wkt column (WKT string).

Return type:

list of dict

Raises:

ValueError – If into="gdf" (scalar helper, D-08).
InvalidIdentifierError – If any identifier is invalid.
ExtensionNotAvailableError – If PostGIS is not installed (first-call guard).

async as_mvt(table: str, *, geom: str = 'geometry', schema: str = 'public', tile_z: int, tile_x: int, tile_y: int, layer_name: str = 'default', extent: int = 4096, columns: list[str] | None = None, feature_id_column: str | None = None, srid: int | None = None, filter: str | None = None) → bytes[source]¶

Generate a Mapbox Vector Tile (MVT) for a slippy-map tile coordinate (async).

Returns the MVT tile as bytes (protobuf-encoded). An empty tile (all geometry clips outside the tile envelope) returns b"" — never None or memoryview (D-05).

Born-clean filter= only — no deprecated where= alias (D-09). No into= parameter (this method returns bytes, not rows or a GeoDataFrame, and is NOT in _SCALAR_HELPERS).

The enforced pipeline (D-03):

ST_Transform(t.geom, 3857)
→ ST_AsMVTGeom(…, ST_TileEnvelope(z, x, y), extent)
→ ST_AsMVT(q.*, layer_name, extent, 'geom_mvt')

Parameters:

table (str) – Table to query.
geom (str, optional) – Geometry column name, by default "geometry".
schema (str, optional) – Schema name, by default "public".
tile_z (int) – Tile zoom level. Required keyword-only (D-02).
tile_x (int) – Tile column index. Required keyword-only (D-02).
tile_y (int) – Tile row index. Required keyword-only (D-02).
layer_name (str, optional) – MVT layer name, by default "default".
extent (int, optional) – MVT tile extent in pixel units, by default 4096.
columns (list of str, optional) – Extra columns to include as MVT feature properties.
feature_id_column (str or None, optional) – Integer-typed column to use as MVT feature ID. None (default) = no feature ID.
srid (int or None, optional) – Source SRID override for SRID-0 geometry columns (D-04).
filter (str, optional) – Raw SQL fragment appended as WHERE {filter} inside the inner subquery (T-44-02c documented escape hatch).

Returns:

MVT protobuf tile bytes, or b"" for an empty tile (D-05).

Return type:

bytes

Raises:

InvalidIdentifierError – If any identifier is invalid (T-44-02).
ExtensionNotAvailableError – If PostGIS is not installed (first-call guard).

async buffer(table: str, *, geom: str = 'geometry', schema: str = 'public', distance: float, unit: str = 'm', into: Literal['rows'] = 'rows', columns: list[str] | None = None, filter: str | None = None, where: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]][source]¶

async buffer(table: str, *, geom: str = 'geometry', schema: str = 'public', distance: float, unit: str = 'm', into: Literal['gdf'], columns: list[str] | None = None, filter: str | None = None, where: str | None = None, order_by: str | None = None, limit: int | None = None) → gpd.GeoDataFrame

async buffer(table: str, *, geom: str = 'geometry', schema: str = 'public', distance: float, unit: str = 'm', into: str, columns: list[str] | None = None, filter: str | None = None, where: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]] | gpd.GeoDataFrame

Select rows with a buffer around their geometry (async).

Returns a buffer geometry column — valid for into="gdf".

Parameters:

table (str) – Table to query.
geom (str, optional) – Geometry column name, by default “geometry” (D-06).
schema (str, optional) – Schema name, by default “public”.
distance (float) – Buffer distance, meters by default (D-09).
unit (str, optional) – "m" or "srid", by default “m” (D-09).
into (str, optional) – "rows" or "gdf", by default “rows” (D-01).
columns (list of str, optional) – Columns to select alongside the buffer (D-03).
filter (str, optional) – Raw SQL filter fragment (D-11).
where (str, optional) – Deprecated. Use filter= instead.
order_by (str, optional) – ORDER BY clause body (D-12).
limit (int, optional) – LIMIT value (D-12).

Returns:

Rows including the buffer geometry column.

Return type:

list of dict or gpd.GeoDataFrame

Raises:

ValueError – If into/unit is invalid.
InvalidIdentifierError – If any identifier is invalid.
ExtensionNotAvailableError – If PostGIS is not installed (first-call guard).

async transform(table: str, *, geom: str = 'geometry', schema: str = 'public', to_srid: int, into: Literal['rows'] = 'rows', columns: list[str] | None = None, filter: str | None = None, where: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]][source]¶

async transform(table: str, *, geom: str = 'geometry', schema: str = 'public', to_srid: int, into: Literal['gdf'], columns: list[str] | None = None, filter: str | None = None, where: str | None = None, order_by: str | None = None, limit: int | None = None) → gpd.GeoDataFrame

async transform(table: str, *, geom: str = 'geometry', schema: str = 'public', to_srid: int, into: str, columns: list[str] | None = None, filter: str | None = None, where: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]] | gpd.GeoDataFrame

Select rows with their geometry transformed to a SRID (async).

Returns a geometry_transformed geometry column — valid for into="gdf". No unit= parameter (D-10).

Parameters:

table (str) – Table to query.
geom (str, optional) – Geometry column name, by default “geometry” (D-06).
schema (str, optional) – Schema name, by default “public”.
to_srid (int) – Target spatial reference identifier.
into (str, optional) – "rows" or "gdf", by default “rows” (D-01).
columns (list of str, optional) – Columns to select alongside the transform (D-03).
filter (str, optional) – Raw SQL filter fragment (D-11).
where (str, optional) – Deprecated. Use filter= instead.
order_by (str, optional) – ORDER BY clause body (D-12).
limit (int, optional) – LIMIT value (D-12).

Returns:

Rows including the geometry_transformed column.

Return type:

list of dict or gpd.GeoDataFrame

Raises:

ValueError – If into is invalid.
InvalidIdentifierError – If any identifier is invalid.
ExtensionNotAvailableError – If PostGIS is not installed (first-call guard).

async union(table: str, *, geom: str = 'geometry', schema: str = 'public', other_geom: str, grid_size: float | None = None, into: Literal['rows'] = 'rows', columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]][source]¶

async union(table: str, *, geom: str = 'geometry', schema: str = 'public', other_geom: str, grid_size: float | None = None, into: Literal['gdf'], columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → gpd.GeoDataFrame

async union(table: str, *, geom: str = 'geometry', schema: str = 'public', other_geom: str, grid_size: float | None = None, into: str, columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]] | gpd.GeoDataFrame

Select rows with the pairwise union of two geometry columns (async).

Returns a union_geom geometry column via ST_Union(t.{geom}, t.{other_geom}) — valid for into="gdf". No where= deprecated alias (born clean for 1.0 freeze, D-10).

Note: inputs with mismatched SRIDs will raise a native PostGIS error at execution time (D-06). No pre-check is performed.

Parameters:

table (str) – Table to query.
geom (str, optional) – First geometry column name, by default “geometry”.
schema (str, optional) – Schema name, by default “public”.
other_geom (str) – Second geometry column name (required, keyword-only).
grid_size (float, optional) – Fixed-precision (snap-rounding) overlay grid size, by default None. When set, emits the 3-arg ST_Union(a, b, %s) form (pure passthrough, no range guard, D-02). Requires PostGIS >= 3.1 / GEOS >= 3.9 — older servers raise a native PostGIS error (D-04, no version pre-check performed).
into (str, optional) – "rows" or "gdf", by default “rows”.
columns (list of str, optional) – Columns to select alongside the result.
filter (str, optional) – Raw SQL filter fragment.
order_by (str, optional) – ORDER BY clause body.
limit (int, optional) – LIMIT value.

Returns:

Rows including the union_geom geometry column.

Return type:

list of dict or gpd.GeoDataFrame

Raises:

ValueError – If into is invalid.
InvalidIdentifierError – If any identifier is invalid.
ExtensionNotAvailableError – If PostGIS is not installed (first-call guard).

async difference(table: str, *, geom: str = 'geometry', schema: str = 'public', other_geom: str, grid_size: float | None = None, into: Literal['rows'] = 'rows', columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]][source]¶

async difference(table: str, *, geom: str = 'geometry', schema: str = 'public', other_geom: str, grid_size: float | None = None, into: Literal['gdf'], columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → gpd.GeoDataFrame

async difference(table: str, *, geom: str = 'geometry', schema: str = 'public', other_geom: str, grid_size: float | None = None, into: str, columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]] | gpd.GeoDataFrame

Select rows with the pairwise difference of two geometry columns (async).

Returns a difference geometry column via ST_Difference(t.{geom}, t.{other_geom}) (A minus B) — valid for into="gdf". No where= deprecated alias (born clean, D-10).

Note: inputs with mismatched SRIDs will raise a native PostGIS error at execution time (D-06). No pre-check is performed.

Parameters:

table (str) – Table to query.
geom (str, optional) – First geometry column name (minuend), by default “geometry”.
schema (str, optional) – Schema name, by default “public”.
other_geom (str) – Second geometry column name (subtrahend, required, keyword-only).
grid_size (float, optional) – Fixed-precision (snap-rounding) overlay grid size, by default None. When set, emits the 3-arg ST_Difference(a, b, %s) form (pure passthrough, no range guard, D-02). Requires PostGIS >= 3.1 / GEOS >= 3.9 — older servers raise a native PostGIS error (D-04, no version pre-check performed).
into (str, optional) – "rows" or "gdf", by default “rows”.
columns (list of str, optional) – Columns to select alongside the result.
filter (str, optional) – Raw SQL filter fragment.
order_by (str, optional) – ORDER BY clause body.
limit (int, optional) – LIMIT value.

Returns:

Rows including the difference geometry column.

Return type:

list of dict or gpd.GeoDataFrame

Raises:

ValueError – If into is invalid.
InvalidIdentifierError – If any identifier is invalid.
ExtensionNotAvailableError – If PostGIS is not installed (first-call guard).

async intersection(table: str, *, geom: str = 'geometry', schema: str = 'public', other_geom: str, grid_size: float | None = None, into: Literal['rows'] = 'rows', columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]][source]¶

async intersection(table: str, *, geom: str = 'geometry', schema: str = 'public', other_geom: str, grid_size: float | None = None, into: Literal['gdf'], columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → gpd.GeoDataFrame

async intersection(table: str, *, geom: str = 'geometry', schema: str = 'public', other_geom: str, grid_size: float | None = None, into: str, columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]] | gpd.GeoDataFrame

Select rows with the pairwise intersection of two geometry columns (async).

Returns an intersection geometry column via ST_Intersection(t.{geom}, t.{other_geom}) (shared portion) — valid for into="gdf". No where= deprecated alias (born clean, D-10).

Note: inputs with mismatched SRIDs will raise a native PostGIS error at execution time (D-06). No pre-check is performed.

Parameters:

table (str) – Table to query.
geom (str, optional) – First geometry column name, by default “geometry”.
schema (str, optional) – Schema name, by default “public”.
other_geom (str) – Second geometry column name (required, keyword-only).
grid_size (float, optional) – Fixed-precision (snap-rounding) overlay grid size, by default None. When set, emits the 3-arg ST_Intersection(a, b, %s) form (pure passthrough, no range guard, D-02). Requires PostGIS >= 3.1 / GEOS >= 3.9 — older servers raise a native PostGIS error (D-04, no version pre-check performed).
into (str, optional) – "rows" or "gdf", by default “rows”.
columns (list of str, optional) – Columns to select alongside the result.
filter (str, optional) – Raw SQL filter fragment.
order_by (str, optional) – ORDER BY clause body.
limit (int, optional) – LIMIT value.

Returns:

Rows including the intersection geometry column.

Return type:

list of dict or gpd.GeoDataFrame

Raises:

ValueError – If into is invalid.
InvalidIdentifierError – If any identifier is invalid.
ExtensionNotAvailableError – If PostGIS is not installed (first-call guard).

async simplify(table: str, *, geom: str = 'geometry', schema: str = 'public', tolerance: float, preserve_topology: bool = True, preserve_collapsed: bool = False, into: Literal['rows'] = 'rows', columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]][source]¶

async simplify(table: str, *, geom: str = 'geometry', schema: str = 'public', tolerance: float, preserve_topology: bool = True, preserve_collapsed: bool = False, into: Literal['gdf'], columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → gpd.GeoDataFrame

async simplify(table: str, *, geom: str = 'geometry', schema: str = 'public', tolerance: float, preserve_topology: bool = True, preserve_collapsed: bool = False, into: str, columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]] | gpd.GeoDataFrame

Select rows with their geometry simplified — async (ST_SimplifyPreserveTopology default).

Async mirror of SpatialAccessor.simplify(). Adds a simplified geometry column (D-01). The default uses ST_SimplifyPreserveTopology which guarantees a non-NULL result. Pass preserve_topology=False to use ST_Simplify — that variant may return NULL when the geometry collapses (D-07). Valid for into="gdf". No where= deprecated alias (born clean, D-10).

Parameters:

table (str) – Table to query.
geom (str, optional) – Geometry column name, by default “geometry”.
schema (str, optional) – Schema name, by default “public”.
tolerance (float) – Simplification tolerance (required keyword-only, D-05).
preserve_topology (bool, optional) – If True (default) use ST_SimplifyPreserveTopology. If False use ST_Simplify (can return NULL).
preserve_collapsed (bool, optional) – Only consulted when preserve_topology=False (D-05).
into (str, optional) – "rows" or "gdf", by default “rows”.
columns (list of str, optional) – Columns to select alongside the result.
filter (str, optional) – Raw SQL filter fragment.
order_by (str, optional) – ORDER BY clause body.
limit (int, optional) – LIMIT value.

Returns:

Rows including the simplified geometry column.

Return type:

list of dict or gpd.GeoDataFrame

Raises:

ValueError – If into is invalid, or preserve_collapsed=True and preserve_topology=True (contradictory, D-05).
InvalidIdentifierError – If any identifier is invalid.
ExtensionNotAvailableError – If PostGIS is not installed (first-call guard).

async convex_hull(table: str, *, geom: str = 'geometry', schema: str = 'public', into: Literal['rows'] = 'rows', columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]][source]¶

async convex_hull(table: str, *, geom: str = 'geometry', schema: str = 'public', into: Literal['gdf'], columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → gpd.GeoDataFrame

async convex_hull(table: str, *, geom: str = 'geometry', schema: str = 'public', into: str, columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]] | gpd.GeoDataFrame

Select rows with the convex hull of each geometry — async (ST_ConvexHull).

Async mirror of SpatialAccessor.convex_hull(). Adds a convex_hull geometry column (D-01). Valid for into="gdf". No where= deprecated alias (born clean, D-10).

Parameters:

table (str) – Table to query.
geom (str, optional) – Geometry column name, by default “geometry”.
schema (str, optional) – Schema name, by default “public”.
into (str, optional) – "rows" or "gdf", by default “rows”.
columns (list of str, optional) – Columns to select alongside the result.
filter (str, optional) – Raw SQL filter fragment.
order_by (str, optional) – ORDER BY clause body.
limit (int, optional) – LIMIT value.

Returns:

Rows including the convex_hull geometry column.

Return type:

list of dict or gpd.GeoDataFrame

Raises:

ValueError – If into is invalid.
InvalidIdentifierError – If any identifier is invalid.
ExtensionNotAvailableError – If PostGIS is not installed (first-call guard).

async envelope(table: str, *, geom: str = 'geometry', schema: str = 'public', into: Literal['rows'] = 'rows', columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]][source]¶

async envelope(table: str, *, geom: str = 'geometry', schema: str = 'public', into: Literal['gdf'], columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → gpd.GeoDataFrame

async envelope(table: str, *, geom: str = 'geometry', schema: str = 'public', into: str, columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]] | gpd.GeoDataFrame

Select rows with the bounding-box geometry of each row — async (ST_Envelope).

Async mirror of SpatialAccessor.envelope(). Adds an envelope geometry column (D-11) — the per-row minimum bounding rectangle, preserving SRID. Valid for into="gdf". No where= deprecated alias (born clean, D-10).

Parameters:

table (str) – Table to query.
geom (str, optional) – Geometry column name, by default “geometry”.
schema (str, optional) – Schema name, by default “public”.
into (str, optional) – "rows" or "gdf", by default “rows”.
columns (list of str, optional) – Columns to select alongside the result.
filter (str, optional) – Raw SQL filter fragment.
order_by (str, optional) – ORDER BY clause body.
limit (int, optional) – LIMIT value.

Returns:

Rows including the envelope geometry column.

Return type:

list of dict or gpd.GeoDataFrame

Raises:

ValueError – If into is invalid.
InvalidIdentifierError – If any identifier is invalid.
ExtensionNotAvailableError – If PostGIS is not installed (first-call guard).

async point_on_surface(table: str, *, geom: str = 'geometry', schema: str = 'public', into: Literal['rows'] = 'rows', columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]][source]¶

async point_on_surface(table: str, *, geom: str = 'geometry', schema: str = 'public', into: Literal['gdf'], columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → gpd.GeoDataFrame

async point_on_surface(table: str, *, geom: str = 'geometry', schema: str = 'public', into: str, columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]] | gpd.GeoDataFrame

Select rows with a guaranteed-inside representative point — async (ST_PointOnSurface).

Async mirror of SpatialAccessor.point_on_surface(). Unlike centroid()’s scalar x/y shape, this is a real POINT geometry guaranteed to lie on the input geometry (useful for labels/markers/mapping), preserving SRID. Valid for into="gdf". No where= deprecated alias (born clean, D-10).

Parameters:

table (str) – Table to query.
geom (str, optional) – Geometry column name, by default “geometry”.
schema (str, optional) – Schema name, by default “public”.
into (str, optional) – "rows" or "gdf", by default “rows”.
columns (list of str, optional) – Columns to select alongside the result.
filter (str, optional) – Raw SQL filter fragment.
order_by (str, optional) – ORDER BY clause body.
limit (int, optional) – LIMIT value.

Returns:

Rows including the point_on_surface geometry column.

Return type:

list of dict or gpd.GeoDataFrame

Raises:

ValueError – If into is invalid.
InvalidIdentifierError – If any identifier is invalid.
ExtensionNotAvailableError – If PostGIS is not installed (first-call guard).

async make_valid(table: str, *, geom: str = 'geometry', schema: str = 'public', method: str | None = None, keep_collapsed: bool | None = None, into: Literal['rows'] = 'rows', columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]][source]¶

async make_valid(table: str, *, geom: str = 'geometry', schema: str = 'public', method: str | None = None, keep_collapsed: bool | None = None, into: Literal['gdf'], columns: list[str] | None = None, filter: str | None = None, order_by: str | None = None, limit: int | None = None) → gpd.GeoDataFrame

Select rows with their geometry repaired via ST_MakeValid — async.

Async mirror of SpatialAccessor.make_valid(). Adds a made_valid geometry column (D-01). The output type may change (D-07). Valid for into="gdf". No where= deprecated alias (born clean, D-10).

Parameters:

table (str) – Table to query.
geom (str, optional) – Geometry column name, by default “geometry”.
schema (str, optional) – Schema name, by default “public”.
method ({None, "linework", "structure"}, optional) – Repair algorithm, by default None. "structure" requires PostGIS ≥ 3.2 / GEOS ≥ 3.10 (D-04).
keep_collapsed (bool or None, optional) – Only valid when method="structure" (D-03).
into (str, optional) – "rows" or "gdf", by default “rows”.
columns (list of str, optional) – Columns to select alongside the result.
filter (str, optional) – Raw SQL filter fragment.
order_by (str, optional) – ORDER BY clause body.
limit (int, optional) – LIMIT value.

Returns:

Rows including the made_valid geometry column.

Return type:

list of dict or gpd.GeoDataFrame

Raises:

ValueError – If into is invalid, method not in allowed set (D-02), or keep_collapsed set with non-structure method (D-03).
InvalidIdentifierError – If any identifier is invalid.
ExtensionNotAvailableError – If PostGIS is not installed (first-call guard).

async collect_geometries(table: str, *, geom: str = 'geometry', schema: str = 'public', group_by: str | list[str] | None = None, into: Literal['rows'] = 'rows', filter: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]][source]¶

async collect_geometries(table: str, *, geom: str = 'geometry', schema: str = 'public', group_by: str | list[str] | None = None, into: Literal['gdf'], filter: str | None = None, order_by: str | None = None, limit: int | None = None) → gpd.GeoDataFrame

Collect geometries into a geometry collection per group — async (ST_Collect).

Async mirror of SpatialAccessor.collect_geometries(). Adds a collected geometry column (D-06). Valid for into="gdf". No where= deprecated alias (born clean, D-09).

Parameters:

table (str) – Table to query.
geom (str, optional) – Geometry column name, by default "geometry".
schema (str, optional) – Schema name, by default "public".
group_by (str or list of str or None, optional) – Column(s) to group by. A bare str is normalized to [str] (D-01). Pass None for a whole-table aggregate. Raises ValueError for an empty list.
into (str, optional) – "rows" or "gdf", by default "rows".
filter (str, optional) – Raw SQL filter fragment (pre-aggregate WHERE clause).
order_by (str, optional) – ORDER BY clause body.
limit (int, optional) – LIMIT value.

Returns:

Rows or GeoDataFrame including the collected geometry column.

Return type:

list of dict or gpd.GeoDataFrame

Raises:

ValueError – If into is invalid or group_by is an empty list.
InvalidIdentifierError – If any identifier is invalid.
ExtensionNotAvailableError – If PostGIS is not installed (first-call guard).

async union_aggregate(table: str, *, geom: str = 'geometry', schema: str = 'public', group_by: str | list[str] | None = None, into: Literal['rows'] = 'rows', filter: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]][source]¶

async union_aggregate(table: str, *, geom: str = 'geometry', schema: str = 'public', group_by: str | list[str] | None = None, into: Literal['gdf'], filter: str | None = None, order_by: str | None = None, limit: int | None = None) → gpd.GeoDataFrame

Dissolve geometries into a merged boundary per group — async (ST_Union).

Async mirror of SpatialAccessor.union_aggregate(). Adds a dissolved geometry column (D-06). Valid for into="gdf". No where= deprecated alias (born clean, D-09).

Parameters:

table (str) – Table to query.
geom (str, optional) – Geometry column name, by default "geometry".
schema (str, optional) – Schema name, by default "public".
group_by (str or list of str or None, optional) – Column(s) to group by. A bare str is normalized to [str] (D-01). Pass None for a whole-table aggregate. Raises ValueError for an empty list.
into (str, optional) – "rows" or "gdf", by default "rows".
filter (str, optional) – Raw SQL filter fragment (pre-aggregate WHERE clause).
order_by (str, optional) – ORDER BY clause body.
limit (int, optional) – LIMIT value.

Returns:

Rows or GeoDataFrame including the dissolved geometry column.

Return type:

list of dict or gpd.GeoDataFrame

Raises:

ValueError – If into is invalid or group_by is an empty list.
InvalidIdentifierError – If any identifier is invalid.
ExtensionNotAvailableError – If PostGIS is not installed (first-call guard).

async extent(table: str, *, geom: str = 'geometry', schema: str = 'public', group_by: str | list[str] | None = None, srid: int = 4326, into: Literal['rows'] = 'rows', filter: str | None = None, order_by: str | None = None, limit: int | None = None) → list[dict[str, Any]][source]¶

async extent(table: str, *, geom: str = 'geometry', schema: str = 'public', group_by: str | list[str] | None = None, srid: int = 4326, into: Literal['gdf'], filter: str | None = None, order_by: str | None = None, limit: int | None = None) → gpd.GeoDataFrame

Compute the bounding extent of a geometry column — async (ST_Extent).

Async mirror of SpatialAccessor.extent(). Adds an extent column — text for into="rows", geometry for into="gdf" (D-04). Valid for into="gdf" via the box2d::geometry cast. No where= deprecated alias (born clean, D-09).

Parameters:

table (str) – Table to query.
geom (str, optional) – Geometry column name, by default "geometry".
schema (str, optional) – Schema name, by default "public".
group_by (str or list of str or None, optional) – Column(s) to group by. A bare str is normalized to [str] (D-01). Pass None for a whole-table aggregate. Raises ValueError for an empty list.
srid (int, optional) – SRID for the into="gdf" cast, by default 4326. Ignored for into="rows" (D-05).
into (str, optional) – "rows" or "gdf", by default "rows".
filter (str, optional) – Raw SQL filter fragment (pre-aggregate WHERE clause).
order_by (str, optional) – ORDER BY clause body.
limit (int, optional) – LIMIT value.

Returns:

Rows or GeoDataFrame including the extent column.

Return type:

list of dict or gpd.GeoDataFrame

Raises:

ValueError – If into is invalid or group_by is an empty list.
InvalidIdentifierError – If any identifier is invalid.
ExtensionNotAvailableError – If PostGIS is not installed (first-call guard).

async create_spatial_index(table: str, *, column: str = 'geometry', schema: str = 'public', name: str | None = None) → None[source]¶

Create a GIST spatial index on a geometry column.

Parameters:

table (str) – Table name.
column (str, optional) – Geometry column name, by default “geometry”.
schema (str, optional) – Schema name, by default “public”.
name (str, optional) – Index name (auto-generated if not provided).

async list_geometry_columns(*, schema: str | None = None) → list[dict[str, Any]][source]¶

List geometry columns in the database.

Parameters:: schema (str, optional) – Schema filter.
Returns:: List of geometry column info.
Return type:: list of dict

TimescaleDB accessor classes for db.timescale.* / async_db.timescale.*.

This module provides TimescaleAccessor and AsyncTimescaleAccessor — the real implementation of the 15 TimescaleDB helper methods, moved verbatim from Database / AsyncDatabase as part of the v0.6.0 accessor reorganisation (D-06) and extended through v0.8.0.

Both classes are exposed on the parent database via a lazy-cached timescale property. The flat db.* timescale names were removed in v0.7.0.

class pycopg.timescale.TimescaleAccessor(db: Database)[source]¶

Bases: object

TimescaleDB helper namespace exposed as db.timescale.

Methods are moved verbatim from Database. The extension guard (has_extension("timescaledb")) is checked inside each method, not at construction, consistent with the ETL accessor pattern.

__init__(db: Database) → None[source]¶

Store the parent database reference.

Parameters:: db (Database) – Parent database instance. Stored as self._db; no extension check is performed at construction time.

create_hypertable(table: str, time_column: str, *, schema: str = 'public', chunk_time_interval: str = '1 day', if_not_exists: bool = True, migrate_data: bool = True) → None[source]¶

Convert a table to a TimescaleDB hypertable.

Requires TimescaleDB extension.

Parameters:

table (str) – Table name (must exist with time column).
time_column (str) – Name of the timestamp column.
schema (str, optional) – Schema name, by default “public”.
chunk_time_interval (str, optional) – Chunk time interval (e.g., ‘1 day’, ‘1 week’), by default “1 day”.
if_not_exists (bool, optional) – Don’t error if already a hypertable, by default True.
migrate_data (bool, optional) – Migrate existing data to chunks, by default True.

Raises:

ExtensionNotAvailableError – If TimescaleDB extension is not installed.

enable_compression(table: str, *, segment_by: str | list[str] | None = None, order_by: str | list[str] | None = None, schema: str = 'public') → None[source]¶

Enable compression on a hypertable.

Parameters:

table (str) – Hypertable name.
segment_by (str or list of str, optional) – Column(s) to segment compressed data by.
order_by (str or list of str, optional) – Column(s) to order compressed data by.
schema (str, optional) – Schema name, by default “public”.

Raises:

ExtensionNotAvailableError – If TimescaleDB extension is not installed.

add_compression_policy(table: str, *, compress_after: str = '7 days', schema: str = 'public') → None[source]¶

Add automatic compression policy to hypertable.

Parameters:

table (str) – Hypertable name.
compress_after (str, optional) – Compress chunks older than this interval, by default “7 days”.
schema (str, optional) – Schema name, by default “public”.

Raises:

ExtensionNotAvailableError – If TimescaleDB extension is not installed.

compress_chunk(chunk: str, *, if_not_compressed: bool = True) → None[source]¶

Compress a single named chunk (Community license feature).

chunk is the fully-qualified chunk name as returned by show_chunks() (e.g. "_timescaledb_internal._hyper_1_2_chunk"), bound as %s::regclass (TSDB-F03, RESEARCH D-10/D-11) — it is a value, not a SQL identifier, so validate_identifiers does not apply here; injection safety comes from parameter binding alone.

Parameters:

chunk (str) – Fully-qualified chunk name (as returned by show_chunks()).
if_not_compressed (bool, optional) – If True (default), silently skip if the chunk is already compressed instead of raising.

Return type:

None

Raises:

ExtensionNotAvailableError – If TimescaleDB extension is not installed.
psycopg.errors.FeatureNotSupported – If the local TimescaleDB runs under the Apache license (Community feature not available). Callers should catch this and tolerate it on non-Community builds (see D-12).

decompress_chunk(chunk: str, *, if_compressed: bool = True) → None[source]¶

Decompress a single named chunk (Community license feature).

chunk is the fully-qualified chunk name as returned by show_chunks(), bound as %s::regclass (TSDB-F03, RESEARCH D-10/D-11) — it is a value, not a SQL identifier, so validate_identifiers does not apply here; injection safety comes from parameter binding alone.

Parameters:

chunk (str) – Fully-qualified chunk name (as returned by show_chunks()).
if_compressed (bool, optional) – If True (default), silently skip if the chunk is already decompressed instead of raising.

Return type:

None

Raises:

ExtensionNotAvailableError – If TimescaleDB extension is not installed.
psycopg.errors.FeatureNotSupported – If the local TimescaleDB runs under the Apache license (Community feature not available). Callers should catch this and tolerate it on non-Community builds (see D-12).

add_retention_policy(table: str, drop_after: str, *, schema: str = 'public') → None[source]¶

Add automatic data retention policy to hypertable.

Parameters:

table (str) – Hypertable name.
drop_after (str) – Drop chunks older than this interval.
schema (str, optional) – Schema name, by default “public”.

Raises:

ExtensionNotAvailableError – If TimescaleDB extension is not installed.

list_hypertables() → list[dict[str, Any]][source]¶

List all hypertables.

Returns:: List of hypertable info dicts.
Return type:: list of dict
Raises:: ExtensionNotAvailableError – If TimescaleDB extension is not installed.

hypertable_info(table: str, *, schema: str = 'public') → dict[str, Any][source]¶

Get detailed info about a hypertable.

Parameters:

table (str) – Hypertable name.
schema (str, optional) – Schema name, by default “public”.

Returns:

Dict with hypertable details including size info.

Return type:

Raises:

ExtensionNotAvailableError – If TimescaleDB extension is not installed.

show_chunks(table: str, *, older_than: str | datetime | None = None, newer_than: str | datetime | None = None, schema: str = 'public') → list[str][source]¶

List chunks for a hypertable, sorted oldest-first by range start.

Parameters:

table (str) – Hypertable name.
older_than (str or datetime or None, optional) – Return only chunks whose time range ends before this bound. A str is treated as a PostgreSQL interval literal (e.g. "30 days"); a datetime as an absolute timestamptz cutoff. None (default) imposes no upper-age filter.
newer_than (str or datetime or None, optional) – Return only chunks whose time range starts after this bound. Same type rules as older_than. None (default) imposes no lower-age filter.
schema (str, optional) – Schema name, by default "public".

Returns:

Fully-qualified chunk names (e.g. _timescaledb_internal._hyper_1_2_chunk), sorted oldest-first by range_start. Never sorted lexicographically — use the DB-supplied order so _hyper_N_10 sorts after _hyper_N_9.

Return type:

list of str

Raises:

ExtensionNotAvailableError – If TimescaleDB extension is not installed.

drop_chunks(table: str, *, older_than: str | datetime | None = None, newer_than: str | datetime | None = None, schema: str = 'public', dry_run: bool = False) → list[str][source]¶

Drop chunks from a hypertable matching the given bounds.

Uses a capture-before-drop pattern: the matching chunk list is retrieved first (while chunks still exist), then the drop is issued. The returned list is always in oldest-first order, identical in shape to show_chunks().

Parameters:

table (str) – Hypertable name.
older_than (str or datetime or None, optional) – Drop only chunks whose time range ends before this bound. A str is treated as a PostgreSQL interval literal (e.g. "30 days"); a datetime as an absolute timestamptz cutoff.
newer_than (str or datetime or None, optional) – Drop only chunks whose time range starts after this bound. Same type rules as older_than.
schema (str, optional) – Schema name, by default "public".
dry_run (bool, optional) – If True, return the would-be-dropped chunk list without actually dropping anything. By default False.

Returns:

Fully-qualified chunk names that were (or would be) dropped, sorted oldest-first by range_start.

Return type:

list of str

Raises:

ValueError – If both older_than and newer_than are None — this guard fires before any DB round-trip to prevent an accidental full-table wipe.
ExtensionNotAvailableError – If TimescaleDB extension is not installed.

Notes

DESTRUCTIVE / IRREVERSIBLE. Dropped chunks cannot be recovered unless you have a backup. Always call with dry_run=True first to inspect which chunks will be removed.

add_dimension(table: str, column: str, *, partition_type: str = 'hash', number_partitions: int | None = None, chunk_interval: str | None = None, schema: str = 'public', if_not_exists: bool = True) → None[source]¶

Add a partitioning dimension to a hypertable.

Uses the modern TimescaleDB 2.13+ by_hash / by_range builder form (TSDB 2.28 verified). Validates mutual exclusivity of number_partitions and chunk_interval at construction time — before any DB round-trip (D-07).

Parameters:

table (str) – Hypertable name.
column (str) – Column to partition by.
partition_type (str, optional) – "hash" (space-partitioning) or "range" (time/value partitioning), by default "hash".
number_partitions (int or None, optional) – Number of hash partitions. Required when partition_type="hash"; forbidden for "range".
chunk_interval (str or None, optional) – Chunk interval for the new dimension (e.g. "7 days"). Required when partition_type="range"; forbidden for "hash".
schema (str, optional) – Schema name, by default "public".
if_not_exists (bool, optional) – If True (default), a duplicate dimension is silently ignored (TSDB emits a NOTICE). If False, a duplicate dimension raises TimescaleError.

Raises:

ValueError – If partition_type is neither "hash" nor "range", or if the number_partitions / chunk_interval mutual exclusivity constraint is violated — raised before any DB round-trip.
ExtensionNotAvailableError – If TimescaleDB extension is not installed.
TimescaleError – Wraps any database-level failure of add_dimension — most notably the duplicate-dimension error (SQLSTATE TS160) raised when if_not_exists=False and the column is already a dimension, but also other DB errors such as the table not being a hypertable. Non-database errors (e.g. ValueError) propagate unchanged.

add_reorder_policy(table: str, index_name: str, *, schema: str = 'public', if_not_exists: bool = True) → None[source]¶

Add an automatic reorder policy to a hypertable.

Registers a background job that periodically reorders chunks by the given index (Community license feature). On Apache-licensed builds, psycopg raises FeatureNotSupported — the caller must tolerate it.

Parameters:

table (str) – Hypertable name.
index_name (str) – Name of the index to reorder by.
schema (str, optional) – Schema name, by default "public".
if_not_exists (bool, optional) – If True (default), silently skip if a reorder policy already exists for this hypertable.

Raises:

ExtensionNotAvailableError – If TimescaleDB extension is not installed.
psycopg.errors.FeatureNotSupported – If the local TimescaleDB runs under the Apache license (Community feature not available). Callers should catch this and tolerate it on non-Community builds (see D-12).

create_continuous_aggregate(view_name: str, select_sql: str, *, schema: str = 'public', materialized_only: bool = True, with_no_data: bool = False) → None[source]¶

Create a TimescaleDB continuous aggregate materialized view.

Runs on a dedicated connect(autocommit=True) connection (D-02) because TimescaleDB’s internal multi-transaction materialization cannot execute inside a transaction block. The license error (FeatureNotSupported / 0A000) propagates to the caller — no swallow (D-09).

Note

select_sql is structural SQL authored by the caller and is not safe for untrusted user input (the same contract as the aggregates argument in the Phase-32 query helpers).

Parameters:

view_name (str) – Name of the continuous-aggregate view to create.
select_sql (str) – The SELECT statement for the continuous aggregate. Must contain a time_bucket(...) grouping — a ValueError is raised before any DB round-trip if this substring is absent (D-04).
schema (str, optional) – Schema for the view, by default "public".
materialized_only (bool, optional) – If True (default), sets timescaledb.materialized_only=true, returning only materialised data on queries. Set to False to also include real-time data beyond the materialisation horizon.
with_no_data (bool, optional) – If True, creates the view with WITH NO DATA (skips initial materialisation). Default False (materialises on creation).

Raises:

ValueError – If select_sql does not contain time_bucket( — a cagg select without a time_bucket grouping is almost always a user error.
ExtensionNotAvailableError – If the TimescaleDB extension is not installed.
psycopg.errors.FeatureNotSupported – If the local TimescaleDB runs under the Apache license (Community feature not available). Callers should catch and tolerate it on non-Community builds (see D-09).

refresh_continuous_aggregate(view_name: str, *, window_start: datetime | None = None, window_end: datetime | None = None, schema: str = 'public') → None[source]¶

Refresh a TimescaleDB continuous aggregate over an optional time window.

Runs on a dedicated connect(autocommit=True) connection (D-02) because TimescaleDB’s refresh procedure issues multiple internal transactions (one per batch on TSDB 2.28+) and cannot execute inside a transaction block. The license error (FeatureNotSupported / 0A000) propagates to the caller — no swallow (D-09).

Window bounds are absolute timestamps (datetime or None). Both None → full refresh across the entire cagg range (D-06). One side None → open-ended on that side. Relative interval strings ("7 days" etc.) are deliberately rejected — unlike TimescaleAccessor.drop_chunks(), a refresh window is an absolute materialisation range (D-05).

Parameters:

view_name (str) – Name of the continuous-aggregate view to refresh.
window_start (datetime or None, optional) – Start of the materialisation window (inclusive). None means no lower bound (full refresh from the beginning).
window_end (datetime or None, optional) – End of the materialisation window (exclusive). None means no upper bound (full refresh to the end).
schema (str, optional) – Schema for the view, by default "public".

Raises:

ValueError – If window_start or window_end is not a datetime or None. Relative interval strings are not accepted as refresh window bounds.
ExtensionNotAvailableError – If the TimescaleDB extension is not installed.
psycopg.errors.FeatureNotSupported – If the local TimescaleDB runs under the Apache license (Community feature not available). Callers should catch and tolerate it on non-Community builds (see D-09).

add_continuous_aggregate_policy(view_name: str, start_offset: str | None, end_offset: str | None, *, schedule_interval: str = '1 hour', schema: str = 'public', if_not_exists: bool = True) → None[source]¶

Schedules a background job that periodically calls refresh_continuous_aggregate on the given view. Uses a plain self._db.execute call (D-01 — transaction-safe, like add_reorder_policy / add_compression_policy / add_retention_policy; NOT the autocommit seam).

On Apache-licensed TimescaleDB builds, the underlying add_continuous_aggregate_policy function raises FeatureNotSupported (SQLSTATE 0A000) — the license error propagates to the caller without swallowing (D-09).

Parameters:

view_name (str) – Name of the continuous-aggregate view to register the policy on.
start_offset (str or None) – Start offset for the policy refresh window (older boundary), e.g. "7 days". None means open-ended (no lower bound).
end_offset (str or None) – End offset for the policy refresh window (newer boundary), e.g. "1 hour". None means open-ended (no upper bound).
schedule_interval (str, optional) – How often the policy job runs, by default "1 hour".
schema (str, optional) – Schema for the view, by default "public".
if_not_exists (bool, optional) – If True (default), silently skip if a policy already exists for this view. If False, the DB raises an error on duplicate.

Raises:

ValueError – If start_offset and end_offset share the same fixed-duration unit (second, minute, hour, day, week) and start_offset does not cover a longer window than end_offset — raised before any DB round-trip (D-07).
ExtensionNotAvailableError – If TimescaleDB extension is not installed.
psycopg.errors.FeatureNotSupported – If the local TimescaleDB runs under the Apache license (Community feature not available). Callers should catch and tolerate it on non-Community builds (D-09).

drop_continuous_aggregate(view_name: str, *, cascade: bool = False, if_exists: bool = False, schema: str = 'public') → None[source]¶

Drop a TimescaleDB continuous aggregate materialized view.

Uses a plain self._db.execute call (TSDB-F01, RESEARCH D-05 — DROP MATERIALIZED VIEW is ordinary transaction-safe DDL, unlike create_continuous_aggregate() / refresh_continuous_aggregate() which require the autocommit seam). Dropping a continuous aggregate also removes its refresh policies.

Parameters:

view_name (str) – Name of the continuous-aggregate view to drop.
cascade (bool, optional) – If True, adds CASCADE to the drop statement, also dropping any dependent objects. By default False.
if_exists (bool, optional) – If True, adds IF EXISTS so the drop is a no-op when the view does not exist. By default False — a destructive operation should be explicit about its target.
schema (str, optional) – Schema for the view, by default "public".

Raises:

ExtensionNotAvailableError – If TimescaleDB extension is not installed.
psycopg.errors.FeatureNotSupported – If the local TimescaleDB runs under the Apache license (Community feature not available). Callers should catch this and tolerate it on non-Community builds (see D-12).

Notes

DESTRUCTIVE / IRREVERSIBLE. Dropped continuous aggregates and their materialized data cannot be recovered unless you have a backup.

remove_continuous_aggregate_policy(view_name: str, *, if_exists: bool = True, schema: str = 'public') → None[source]¶

Remove the auto-refresh policy from a continuous aggregate.

Symmetric counterpart to add_continuous_aggregate_policy() (TSDB-F01). Uses a plain self._db.execute call (D-01 — transaction-safe, like add_continuous_aggregate_policy; NOT the autocommit seam).

Parameters:

view_name (str) – Name of the continuous-aggregate view to remove the policy from.
if_exists (bool, optional) – If True (default), silently skip if no policy exists for this view. If False, the DB raises an error when absent.
schema (str, optional) – Schema for the view, by default "public".

Raises:

ExtensionNotAvailableError – If TimescaleDB extension is not installed.
psycopg.errors.FeatureNotSupported – If the local TimescaleDB runs under the Apache license (Community feature not available). Callers should catch and tolerate it on non-Community builds (see D-12).

time_bucket(table: str, time_column: str, bucket_width: str, aggregates: str, *, filter: str | None = None, where: str | None = None, schema: str = 'public', origin: datetime | None = None, offset: str | None = None, into: Literal['df'] = 'df') → pd.DataFrame[source]¶

Run a time_bucket bucketed aggregation (TS-ADV-06).

Buckets time_column into fixed bucket_width intervals and applies the caller-supplied aggregates. The first output column is deterministically named bucket (D-01).

Parameters:

table (str) – Hypertable name.
time_column (str) – Timestamp column to bucket on.
bucket_width (str) – Bucket interval (e.g. "1 hour"), bound as %s.
aggregates (str) – Caller-supplied structural SQL aggregate list (e.g. "avg(value), max(value)"). Documented as structural SQL — not untrusted end-user input — the same posture as the spatial accessor (D-04).
filter (str or None, optional) – Optional structural-SQL WHERE fragment (without the WHERE keyword). None (default) omits the clause.
where (str or None, optional) – Deprecated. Use filter= instead.
schema (str, optional) – Schema name, by default "public".
origin (datetime or None, optional) – Absolute alignment anchor for the bucket boundaries, bound as a bare typed %s. Mutually exclusive with offset (TSDB-F02).
offset (str or None, optional) – Relative interval shift for the bucket boundaries (e.g. "30 minutes"), bound as %s::interval. Mutually exclusive with origin (TSDB-F02).
into (str, optional) – Output form — "df" (default) returns a pandas.DataFrame; "rows" returns a list[dict]. Any other value (e.g. "gdf") raises ValueError before any DB round-trip (D-03).

Returns:

Bucketed aggregation results in the requested form.

Return type:

Raises:

ValueError – If into is not "df" or "rows" — raised before any DB call (D-03). Also raised if both origin and offset are non-None — mutually exclusive, raised before any DB round-trip (D-07, TSDB-F02).
ExtensionNotAvailableError – If the TimescaleDB extension is not installed.

time_bucket_gapfill(table: str, time_column: str, bucket_width: str, start: datetime, finish: datetime, aggregates: str, *, filter: str | None = None, where: str | None = None, schema: str = 'public', into: Literal['df'] = 'df') → pd.DataFrame[source]¶

Run a gap-filled time_bucket_gapfill aggregation (TS-ADV-07).

Like time_bucket() but NULL-pads missing buckets across the explicit [start, finish) range, enabling locf() / interpolate() inside aggregates. start and finish are required absolute bounds — gapfill cannot infer them from a WHERE predicate (TS-ADV-07) — and are bound twice (gapfill args + WHERE range, D-10). The first output column is named bucket.

No Python start < finish guard is applied; the DB is the authority (D-09). On Apache-licensed builds time_bucket_gapfill raises psycopg.errors.FeatureNotSupported (a license gate, not a syntax error).

Parameters:

table (str) – Hypertable name.
time_column (str) – Timestamp column to bucket on.
bucket_width (str) – Bucket interval (e.g. "1 hour"), bound as %s.
start (datetime) – Lower (inclusive) bound of the gap-filled range, bound as %s.
finish (datetime) – Upper (exclusive) bound of the gap-filled range, bound as %s.
aggregates (str) – Caller-supplied structural SQL aggregate list, optionally including locf(...) / interpolate(...) (D-04).
filter (str or None, optional) – Optional structural-SQL WHERE fragment ANDed onto the time range. None (default) omits it.
where (str or None, optional) – Deprecated. Use filter= instead.
schema (str, optional) – Schema name, by default "public".
into (str, optional) – Output form — "df" (default) or "rows"; any other value (e.g. "gdf") raises ValueError before any DB call (D-03).

Returns:

Gap-filled bucketed results in the requested form.

Return type:

Raises:

ValueError – If into is not "df" or "rows" — raised before any DB call (D-03).
ExtensionNotAvailableError – If the TimescaleDB extension is not installed.
psycopg.errors.FeatureNotSupported – If the local TimescaleDB runs under the Apache license (gapfill is a Community/TSL feature).

class pycopg.timescale.AsyncTimescaleAccessor(db: AsyncDatabase)[source]¶

Bases: object

Async TimescaleDB helper namespace exposed as async_db.timescale.

Mirrors TimescaleAccessor exactly with await calls.

__init__(db: AsyncDatabase) → None[source]¶

Store the parent async database reference.

Parameters:: db (AsyncDatabase) – Parent async database instance. Stored as self._db; no extension check is performed at construction time.

async create_hypertable(table: str, time_column: str, *, schema: str = 'public', chunk_time_interval: str = '1 day', if_not_exists: bool = True, migrate_data: bool = True) → None[source]¶

Convert a table to a TimescaleDB hypertable.

Requires TimescaleDB extension.

Parameters:

table (str) – Table name (must exist with time column).
time_column (str) – Name of the timestamp column.
schema (str, optional) – Schema name, by default “public”.
chunk_time_interval (str, optional) – Chunk time interval (e.g., ‘1 day’, ‘1 week’), by default “1 day”.
if_not_exists (bool, optional) – Don’t error if already a hypertable, by default True.
migrate_data (bool, optional) – Migrate existing data to chunks, by default True.

Raises:

ExtensionNotAvailableError – If TimescaleDB extension is not installed.

async enable_compression(table: str, *, segment_by: str | list[str] | None = None, order_by: str | list[str] | None = None, schema: str = 'public') → None[source]¶

Enable compression on a hypertable.

Parameters:

table (str) – Hypertable name.
segment_by (str or list of str, optional) – Column(s) to segment compressed data by.
order_by (str or list of str, optional) – Column(s) to order compressed data by.
schema (str, optional) – Schema name, by default “public”.

Raises:

ExtensionNotAvailableError – If TimescaleDB extension is not installed.

async add_compression_policy(table: str, *, compress_after: str = '7 days', schema: str = 'public') → None[source]¶

Add automatic compression policy to hypertable.

Parameters:

table (str) – Hypertable name.
compress_after (str, optional) – Compress chunks older than this interval, by default “7 days”.
schema (str, optional) – Schema name, by default “public”.

Raises:

ExtensionNotAvailableError – If TimescaleDB extension is not installed.

async compress_chunk(chunk: str, *, if_not_compressed: bool = True) → None[source]¶

Compress a single named chunk (Community license feature).

Parameters:

chunk (str) – Fully-qualified chunk name (as returned by show_chunks()).
if_not_compressed (bool, optional) – If True (default), silently skip if the chunk is already compressed instead of raising.

Return type:

None

Raises:

ExtensionNotAvailableError – If TimescaleDB extension is not installed.
psycopg.errors.FeatureNotSupported – If the local TimescaleDB runs under the Apache license (Community feature not available). Callers should catch this and tolerate it on non-Community builds (see D-12).

async decompress_chunk(chunk: str, *, if_compressed: bool = True) → None[source]¶

Decompress a single named chunk (Community license feature).

Parameters:

chunk (str) – Fully-qualified chunk name (as returned by show_chunks()).
if_compressed (bool, optional) – If True (default), silently skip if the chunk is already decompressed instead of raising.

Return type:

None

Raises:

ExtensionNotAvailableError – If TimescaleDB extension is not installed.
psycopg.errors.FeatureNotSupported – If the local TimescaleDB runs under the Apache license (Community feature not available). Callers should catch this and tolerate it on non-Community builds (see D-12).

async add_retention_policy(table: str, drop_after: str, *, schema: str = 'public') → None[source]¶

Add automatic data retention policy to hypertable.

Parameters:

table (str) – Hypertable name.
drop_after (str) – Drop chunks older than this interval.
schema (str, optional) – Schema name, by default “public”.

Raises:

ExtensionNotAvailableError – If TimescaleDB extension is not installed.

async list_hypertables() → list[dict[str, Any]][source]¶

List all hypertables.

Returns:: List of hypertable info dicts.
Return type:: list of dict
Raises:: ExtensionNotAvailableError – If TimescaleDB extension is not installed.

async hypertable_info(table: str, *, schema: str = 'public') → dict[str, Any][source]¶

Get detailed info about a hypertable.

Parameters:

table (str) – Hypertable name.
schema (str, optional) – Schema name, by default “public”.

Returns:

Dict with hypertable details including size info.

Return type:

Raises:

ExtensionNotAvailableError – If TimescaleDB extension is not installed.

async show_chunks(table: str, *, older_than: str | datetime | None = None, newer_than: str | datetime | None = None, schema: str = 'public') → list[str][source]¶

List chunks for a hypertable, sorted oldest-first by range start.

Parameters:

table (str) – Hypertable name.
older_than (str or datetime or None, optional) – Return only chunks whose time range ends before this bound. A str is treated as a PostgreSQL interval literal (e.g. "30 days"); a datetime as an absolute timestamptz cutoff. None (default) imposes no upper-age filter.
newer_than (str or datetime or None, optional) – Return only chunks whose time range starts after this bound. Same type rules as older_than. None (default) imposes no lower-age filter.
schema (str, optional) – Schema name, by default "public".

Returns:

Return type:

list of str

Raises:

ExtensionNotAvailableError – If TimescaleDB extension is not installed.

async drop_chunks(table: str, *, older_than: str | datetime | None = None, newer_than: str | datetime | None = None, schema: str = 'public', dry_run: bool = False) → list[str][source]¶

Drop chunks from a hypertable matching the given bounds.

Parameters:

table (str) – Hypertable name.
older_than (str or datetime or None, optional) – Drop only chunks whose time range ends before this bound. A str is treated as a PostgreSQL interval literal (e.g. "30 days"); a datetime as an absolute timestamptz cutoff.
newer_than (str or datetime or None, optional) – Drop only chunks whose time range starts after this bound. Same type rules as older_than.
schema (str, optional) – Schema name, by default "public".
dry_run (bool, optional) – If True, return the would-be-dropped chunk list without actually dropping anything. By default False.

Returns:

Fully-qualified chunk names that were (or would be) dropped, sorted oldest-first by range_start.

Return type:

list of str

Raises:

ValueError – If both older_than and newer_than are None — this guard fires before any DB round-trip to prevent an accidental full-table wipe.
ExtensionNotAvailableError – If TimescaleDB extension is not installed.

Notes

DESTRUCTIVE / IRREVERSIBLE. Dropped chunks cannot be recovered unless you have a backup. Always call with dry_run=True first to inspect which chunks will be removed.

async add_dimension(table: str, column: str, *, partition_type: str = 'hash', number_partitions: int | None = None, chunk_interval: str | None = None, schema: str = 'public', if_not_exists: bool = True) → None[source]¶

Add a partitioning dimension to a hypertable.

Async mirror of TimescaleAccessor.add_dimension().

Parameters:

table (str) – Hypertable name.
column (str) – Column to partition by.
partition_type (str, optional) – "hash" (space-partitioning) or "range" (time/value partitioning), by default "hash".
number_partitions (int or None, optional) – Number of hash partitions. Required when partition_type="hash"; forbidden for "range".
chunk_interval (str or None, optional) – Chunk interval for the new dimension (e.g. "7 days"). Required when partition_type="range"; forbidden for "hash".
schema (str, optional) – Schema name, by default "public".
if_not_exists (bool, optional) – If True (default), a duplicate dimension is silently ignored. If False, a duplicate dimension raises TimescaleError.

Raises:

ValueError – If partition_type is invalid or mutual-exclusivity is violated — raised before any DB round-trip (D-07).
ExtensionNotAvailableError – If TimescaleDB extension is not installed.
TimescaleError – Wraps any database-level failure of add_dimension — most notably the duplicate-dimension error (SQLSTATE TS160) raised when if_not_exists=False. Non-database errors (e.g. ValueError) propagate unchanged.

async add_reorder_policy(table: str, index_name: str, *, schema: str = 'public', if_not_exists: bool = True) → None[source]¶

Add an automatic reorder policy to a hypertable.

Async mirror of TimescaleAccessor.add_reorder_policy().

Parameters:

table (str) – Hypertable name.
index_name (str) – Name of the index to reorder by.
schema (str, optional) – Schema name, by default "public".
if_not_exists (bool, optional) – If True (default), silently skip if a reorder policy already exists for this hypertable.

Raises:

ExtensionNotAvailableError – If TimescaleDB extension is not installed.
psycopg.errors.FeatureNotSupported – If the local TimescaleDB runs under the Apache license (Community feature not available). Callers should catch and tolerate it on non-Community builds (D-12).

async create_continuous_aggregate(view_name: str, select_sql: str, *, schema: str = 'public', materialized_only: bool = True, with_no_data: bool = False) → None[source]¶

Create a TimescaleDB continuous aggregate materialized view.

Async mirror of TimescaleAccessor.create_continuous_aggregate(). Runs on a dedicated async with self._db.connect(autocommit=True) connection (D-02). The license error (FeatureNotSupported / 0A000) propagates to the caller — no swallow (D-09).

Note

select_sql is structural SQL authored by the caller and is not safe for untrusted user input.

Parameters:

view_name (str) – Name of the continuous-aggregate view to create.
select_sql (str) – The SELECT statement for the continuous aggregate. Must contain a time_bucket(...) grouping — a ValueError is raised before any DB round-trip if this substring is absent (D-04).
schema (str, optional) – Schema for the view, by default "public".
materialized_only (bool, optional) – If True (default), sets timescaledb.materialized_only=true. Set to False to also include real-time data beyond the materialisation horizon.
with_no_data (bool, optional) – If True, creates with WITH NO DATA. Default False (materialises on creation).

Raises:

ValueError – If select_sql does not contain time_bucket( — a cagg select without a time_bucket grouping is almost always a user error.
ExtensionNotAvailableError – If the TimescaleDB extension is not installed.
psycopg.errors.FeatureNotSupported – If the local TimescaleDB runs under the Apache license (Community feature not available). Callers should catch and tolerate it on non-Community builds (see D-09).

async refresh_continuous_aggregate(view_name: str, *, window_start: datetime | None = None, window_end: datetime | None = None, schema: str = 'public') → None[source]¶

Refresh a TimescaleDB continuous aggregate over an optional time window.

Async mirror of TimescaleAccessor.refresh_continuous_aggregate(). Runs on a dedicated async with self._db.connect(autocommit=True) connection (D-02). The license error (FeatureNotSupported / 0A000) propagates to the caller — no swallow (D-09).

Parameters:

view_name (str) – Name of the continuous-aggregate view to refresh.
window_start (datetime or None, optional) – Start of the materialisation window (inclusive). None means no lower bound (full refresh from the beginning).
window_end (datetime or None, optional) – End of the materialisation window (exclusive). None means no upper bound (full refresh to the end).
schema (str, optional) – Schema for the view, by default "public".

Raises:

ValueError – If window_start or window_end is not a datetime or None. Relative interval strings are not accepted as refresh window bounds.
ExtensionNotAvailableError – If the TimescaleDB extension is not installed.
psycopg.errors.FeatureNotSupported – If the local TimescaleDB runs under the Apache license (Community feature not available). Callers should catch and tolerate it on non-Community builds (see D-09).

async add_continuous_aggregate_policy(view_name: str, start_offset: str | None, end_offset: str | None, *, schedule_interval: str = '1 hour', schema: str = 'public', if_not_exists: bool = True) → None[source]¶

Async mirror of TimescaleAccessor.add_continuous_aggregate_policy(). Uses a plain await self._db.execute call (D-01 — transaction-safe; NOT the autocommit seam).

Parameters:

view_name (str) – Name of the continuous-aggregate view to register the policy on.
start_offset (str or None) – Start offset for the policy refresh window (older boundary), e.g. "7 days". None means open-ended (no lower bound).
end_offset (str or None) – End offset for the policy refresh window (newer boundary), e.g. "1 hour". None means open-ended (no upper bound).
schedule_interval (str, optional) – How often the policy job runs, by default "1 hour".
schema (str, optional) – Schema for the view, by default "public".
if_not_exists (bool, optional) – If True (default), silently skip if a policy already exists for this view. If False, the DB raises an error on duplicate.

Raises:

ValueError – If start_offset and end_offset share the same fixed-duration unit and start_offset does not cover a longer window than end_offset — raised before any DB round-trip (D-07).
ExtensionNotAvailableError – If TimescaleDB extension is not installed.
psycopg.errors.FeatureNotSupported – If the local TimescaleDB runs under the Apache license (Community feature not available). Callers should catch and tolerate it on non-Community builds (D-09).

async drop_continuous_aggregate(view_name: str, *, cascade: bool = False, if_exists: bool = False, schema: str = 'public') → None[source]¶

Drop a TimescaleDB continuous aggregate materialized view.

Async mirror of TimescaleAccessor.drop_continuous_aggregate(). Uses a plain await self._db.execute call (TSDB-F01, RESEARCH D-05 — DROP MATERIALIZED VIEW is ordinary transaction-safe DDL, unlike create_continuous_aggregate() / refresh_continuous_aggregate() which require the autocommit seam). Dropping a continuous aggregate also removes its refresh policies.

Parameters:

view_name (str) – Name of the continuous-aggregate view to drop.
cascade (bool, optional) – If True, adds CASCADE to the drop statement, also dropping any dependent objects. By default False.
if_exists (bool, optional) – If True, adds IF EXISTS so the drop is a no-op when the view does not exist. By default False — a destructive operation should be explicit about its target.
schema (str, optional) – Schema for the view, by default "public".

Raises:

ExtensionNotAvailableError – If TimescaleDB extension is not installed.
psycopg.errors.FeatureNotSupported – If the local TimescaleDB runs under the Apache license (Community feature not available). Callers should catch this and tolerate it on non-Community builds (see D-12).

Notes

DESTRUCTIVE / IRREVERSIBLE. Dropped continuous aggregates and their materialized data cannot be recovered unless you have a backup.

async remove_continuous_aggregate_policy(view_name: str, *, if_exists: bool = True, schema: str = 'public') → None[source]¶

Remove the auto-refresh policy from a continuous aggregate.

Async mirror of TimescaleAccessor.remove_continuous_aggregate_policy(). Symmetric counterpart to add_continuous_aggregate_policy() (TSDB-F01). Uses a plain await self._db.execute call (D-01 — transaction-safe; NOT the autocommit seam).

Parameters:

view_name (str) – Name of the continuous-aggregate view to remove the policy from.
if_exists (bool, optional) – If True (default), silently skip if no policy exists for this view. If False, the DB raises an error when absent.
schema (str, optional) – Schema for the view, by default "public".

Raises:

ExtensionNotAvailableError – If TimescaleDB extension is not installed.
psycopg.errors.FeatureNotSupported – If the local TimescaleDB runs under the Apache license (Community feature not available). Callers should catch and tolerate it on non-Community builds (see D-12).

async time_bucket(table: str, time_column: str, bucket_width: str, aggregates: str, *, filter: str | None = None, where: str | None = None, schema: str = 'public', origin: datetime | None = None, offset: str | None = None, into: Literal['df'] = 'df') → pd.DataFrame[source]¶

Run a time_bucket bucketed aggregation (TS-ADV-06, TS-ADV-10).

Async mirror of TimescaleAccessor.time_bucket() — buckets time_column into fixed bucket_width intervals and applies the caller-supplied aggregates. The first output column is deterministically named bucket (D-01).

Parameters:

table (str) – Hypertable name.
time_column (str) – Timestamp column to bucket on.
bucket_width (str) – Bucket interval (e.g. "1 hour"), bound as %s.
aggregates (str) – Caller-supplied structural SQL aggregate list (e.g. "avg(value), max(value)"). Documented as structural SQL — not untrusted end-user input (D-04).
filter (str or None, optional) – Optional structural-SQL WHERE fragment (without the WHERE keyword). None (default) omits the clause.
where (str or None, optional) – Deprecated. Use filter= instead.
schema (str, optional) – Schema name, by default "public".
origin (datetime or None, optional) – Absolute alignment anchor for the bucket boundaries, bound as a bare typed %s. Mutually exclusive with offset (TSDB-F02).
offset (str or None, optional) – Relative interval shift for the bucket boundaries (e.g. "30 minutes"), bound as %s::interval. Mutually exclusive with origin (TSDB-F02).
into (str, optional) – Output form — "df" (default) returns a pandas.DataFrame; "rows" returns a list[dict]. Any other value (e.g. "gdf") raises ValueError before any DB round-trip (D-03).

Returns:

Bucketed aggregation results in the requested form.

Return type:

Raises:

ValueError – If into is not "df" or "rows" — raised before any DB call (D-03). Also raised if both origin and offset are non-None — mutually exclusive, raised before any DB round-trip (D-07, TSDB-F02).
ExtensionNotAvailableError – If the TimescaleDB extension is not installed.

async time_bucket_gapfill(table: str, time_column: str, bucket_width: str, start: datetime, finish: datetime, aggregates: str, *, filter: str | None = None, where: str | None = None, schema: str = 'public', into: Literal['df'] = 'df') → pd.DataFrame[source]¶

Run a gap-filled time_bucket_gapfill aggregation (TS-ADV-07/10).

Async mirror of TimescaleAccessor.time_bucket_gapfill() — NULL-pads missing buckets across the explicit [start, finish) range. start and finish are required absolute bounds (TS-ADV-07) and are bound twice (gapfill args + WHERE range, D-10). No Python start < finish guard is applied (D-09). On Apache-licensed builds the underlying function raises psycopg.errors.FeatureNotSupported (a license gate, not a syntax error).

Parameters:

table (str) – Hypertable name.
time_column (str) – Timestamp column to bucket on.
bucket_width (str) – Bucket interval (e.g. "1 hour"), bound as %s.
start (datetime) – Lower (inclusive) bound of the gap-filled range, bound as %s.
finish (datetime) – Upper (exclusive) bound of the gap-filled range, bound as %s.
aggregates (str) – Caller-supplied structural SQL aggregate list, optionally including locf(...) / interpolate(...) (D-04).
filter (str or None, optional) – Optional structural-SQL WHERE fragment ANDed onto the time range. None (default) omits it.
where (str or None, optional) – Deprecated. Use filter= instead.
schema (str, optional) – Schema name, by default "public".
into (str, optional) – Output form — "df" (default) or "rows"; any other value (e.g. "gdf") raises ValueError before any DB call (D-03).

Returns:

Gap-filled bucketed results in the requested form.

Return type:

Raises:

ValueError – If into is not "df" or "rows" — raised before any DB call (D-03).
ExtensionNotAvailableError – If the TimescaleDB extension is not installed.
psycopg.errors.FeatureNotSupported – If the local TimescaleDB runs under the Apache license (gapfill is a Community/TSL feature).

Admin accessor classes for db.admin.* / async_db.admin.*.

This module provides AdminAccessor and AsyncAdminAccessor — the real implementation of the 11 admin (roles & permissions) helper methods, moved verbatim from Database / AsyncDatabase as part of the v0.6.0 accessor reorganisation (D-06).

Both classes are exposed on the parent database via a lazy-cached admin property. The flat db.* admin names were removed in v0.7.0.

class pycopg.admin.AdminAccessor(db: Database)[source]¶

Bases: object

Admin helper namespace exposed as db.admin.

Methods are moved verbatim from Database. Role and permission management operations are accessible via this accessor.

__init__(db: Database) → None[source]¶

Store the parent database reference.

Parameters:: db (Database) – Parent database instance. Stored as self._db; no connection check is performed at construction time.

create_role(name: str, *, password: str | None = None, login: bool = True, superuser: bool = False, createdb: bool = False, createrole: bool = False, inherit: bool = True, replication: bool = False, connection_limit: int = -1, valid_until: str | None = None, in_roles: list[str] | None = None, if_not_exists: bool = True) → None[source]¶

Create a database role/user.

Parameters:

name (str) – Role name.
password (str, optional) – Role password (for login roles).
login (bool, optional) – Can log in (True = user, False = group role), by default True.
superuser (bool, optional) – Is superuser, by default False.
createdb (bool, optional) – Can create databases, by default False.
createrole (bool, optional) – Can create other roles, by default False.
inherit (bool, optional) – Inherits privileges from member roles, by default True.
replication (bool, optional) – Can initiate streaming replication, by default False.
connection_limit (int, optional) – Max concurrent connections (-1 = unlimited), by default -1.
valid_until (str, optional) – Password expiration (e.g., ‘2025-12-31’).
in_roles (list of str, optional) – List of roles to be a member of.
if_not_exists (bool, optional) – Don’t error if role exists, by default True.

drop_role(name: str, *, if_exists: bool = True) → None[source]¶

Drop a role.

Parameters:

name (str) – Role name.
if_exists (bool, optional) – Don’t error if role doesn’t exist, by default True.

role_exists(name: str) → bool[source]¶

Check if a role exists.

Parameters:: name (str) – Role name.
Returns:: True if role exists.
Return type:: bool

list_roles(*, include_system: bool = False) → list[dict[str, Any]][source]¶

List all roles.

Parameters:: include_system (bool, optional) – Include system roles (pg_*), by default False.
Returns:: List of role info dicts.
Return type:: list of dict

Alter a role’s attributes.

Parameters:

name (str) – Role name.
password (str, optional) – New password.
login (bool, optional) – Enable/disable login.
superuser (bool, optional) – Enable/disable superuser.
createdb (bool, optional) – Enable/disable createdb.
createrole (bool, optional) – Enable/disable createrole.
connection_limit (int, optional) – New connection limit.
valid_until (str, optional) – New password expiration.
rename_to (str, optional) – Rename the role.

grant_role(role: str, member: str, *, with_admin: bool = False) → None[source]¶

Grant role membership to another role.

Parameters:

role (str) – Role to grant.
member (str) – Role receiving membership.
with_admin (bool, optional) – Allow member to grant role to others, by default False.

revoke_role(role: str, member: str) → None[source]¶

Revoke role membership from a role.

Parameters:

role (str) – Role to revoke.
member (str) – Role losing membership.

grant(privileges: str | list[str], on: str, to: str, *, object_type: str = 'TABLE', schema: str = 'public', with_grant_option: bool = False) → None[source]¶

Grant privileges on database objects.

Parameters:

privileges (str or list of str) – Privilege(s) to grant (SELECT, INSERT, UPDATE, DELETE, ALL, etc.).
on (str) – Object name or ALL TABLES/SEQUENCES/FUNCTIONS.
to (str) – Role receiving privileges.
object_type (str, optional) – Type of object (TABLE, SEQUENCE, FUNCTION, SCHEMA, DATABASE), by default “TABLE”.
schema (str, optional) – Schema name (for tables/sequences), by default “public”.
with_grant_option (bool, optional) – Allow grantee to grant to others, by default False.

revoke(privileges: str | list[str], on: str, from_role: str, *, object_type: str = 'TABLE', schema: str = 'public', cascade: bool = False) → None[source]¶

Revoke privileges on database objects.

Parameters:

privileges (str or list of str) – Privilege(s) to revoke.
on (str) – Object name or ALL TABLES/SEQUENCES/FUNCTIONS.
from_role (str) – Role losing privileges.
object_type (str, optional) – Type of object, by default “TABLE”.
schema (str, optional) – Schema name, by default “public”.
cascade (bool, optional) – Revoke from dependent privileges, by default False.

list_role_members(role: str) → list[str][source]¶

List members of a role.

Parameters:: role (str) – Role name.
Returns:: List of member role names.
Return type:: list of str

list_role_grants(role: str) → list[dict[str, Any]][source]¶

List privileges granted to a role.

Parameters:: role (str) – Role name.
Returns:: List of privilege info dicts.
Return type:: list of dict

class pycopg.admin.AsyncAdminAccessor(db: AsyncDatabase)[source]¶

Bases: object

Async admin helper namespace exposed as async_db.admin.

Mirrors AdminAccessor exactly with await calls.

__init__(db: AsyncDatabase) → None[source]¶

Store the parent async database reference.

Parameters:: db (AsyncDatabase) – Parent async database instance. Stored as self._db; no connection check is performed at construction time.

async create_role(name: str, *, password: str | None = None, login: bool = True, superuser: bool = False, createdb: bool = False, createrole: bool = False, inherit: bool = True, replication: bool = False, connection_limit: int = -1, valid_until: str | None = None, in_roles: list[str] | None = None, if_not_exists: bool = True) → None[source]¶

Create a database role/user.

Parameters:

name (str) – Role name.
password (str, optional) – Role password (for login roles).
login (bool, optional) – Can log in (True = user, False = group role), by default True.
superuser (bool, optional) – Is superuser, by default False.
createdb (bool, optional) – Can create databases, by default False.
createrole (bool, optional) – Can create other roles, by default False.
inherit (bool, optional) – Inherits privileges from member roles, by default True.
replication (bool, optional) – Can initiate streaming replication, by default False.
connection_limit (int, optional) – Max concurrent connections (-1 = unlimited), by default -1.
valid_until (str, optional) – Password expiration (e.g., ‘2025-12-31’).
in_roles (list of str, optional) – List of roles to be a member of.
if_not_exists (bool, optional) – Don’t error if role exists, by default True.

async drop_role(name: str, *, if_exists: bool = True) → None[source]¶

Drop a role.

Parameters:

name (str) – Role name.
if_exists (bool, optional) – Don’t error if role doesn’t exist, by default True.

async role_exists(name: str) → bool[source]¶

Check if a role exists.

Parameters:: name (str) – Role name.
Returns:: True if role exists.
Return type:: bool

async list_roles(*, include_system: bool = False) → list[dict[str, Any]][source]¶

List all roles.

Parameters:: include_system (bool, optional) – Include system roles (pg_*), by default False.
Returns:: List of role info dicts.
Return type:: list of dict

Alter a role’s attributes.

Parameters:

name (str) – Role name.
password (str, optional) – New password.
login (bool, optional) – Enable/disable login.
superuser (bool, optional) – Enable/disable superuser.
createdb (bool, optional) – Enable/disable createdb.
createrole (bool, optional) – Enable/disable createrole.
connection_limit (int, optional) – New connection limit.
valid_until (str, optional) – New password expiration.
rename_to (str, optional) – Rename the role.

async grant_role(role: str, member: str, *, with_admin: bool = False) → None[source]¶

Grant role membership to another role.

Parameters:

role (str) – Role to grant.
member (str) – Role receiving membership.
with_admin (bool, optional) – Allow member to grant role to others, by default False.

async revoke_role(role: str, member: str) → None[source]¶

Revoke role membership from a role.

Parameters:

role (str) – Role to revoke.
member (str) – Role losing membership.

async grant(privileges: str | list[str], on: str, to: str, *, object_type: str = 'TABLE', schema: str = 'public', with_grant_option: bool = False) → None[source]¶

Grant privileges on database objects.

Parameters:

privileges (str or list of str) – Privilege(s) to grant (SELECT, INSERT, UPDATE, DELETE, ALL, etc.).
on (str) – Object name or ALL TABLES/SEQUENCES/FUNCTIONS.
to (str) – Role receiving privileges.
object_type (str, optional) – Type of object (TABLE, SEQUENCE, FUNCTION, SCHEMA, DATABASE), by default “TABLE”.
schema (str, optional) – Schema name (for tables/sequences), by default “public”.
with_grant_option (bool, optional) – Allow grantee to grant to others, by default False.

async revoke(privileges: str | list[str], on: str, from_role: str, *, object_type: str = 'TABLE', schema: str = 'public', cascade: bool = False) → None[source]¶

Revoke privileges on database objects.

Parameters:

privileges (str or list of str) – Privilege(s) to revoke.
on (str) – Object name or ALL TABLES/SEQUENCES/FUNCTIONS.
from_role (str) – Role losing privileges.
object_type (str, optional) – Type of object, by default “TABLE”.
schema (str, optional) – Schema name, by default “public”.
cascade (bool, optional) – Revoke from dependent privileges, by default False.

async list_role_members(role: str) → list[str][source]¶

List members of a role.

Parameters:: role (str) – Role name.
Returns:: List of member role names.
Return type:: list of str

async list_role_grants(role: str) → list[dict[str, Any]][source]¶

List privileges granted to a role.

Parameters:: role (str) – Role name.
Returns:: List of privilege info dicts.
Return type:: list of dict

Maintenance & size accessor classes for db.maint.* / async_db.maint.*.

This module provides MaintAccessor and AsyncMaintAccessor — the real implementation of the 6 maintenance and size helper methods, moved verbatim from Database / AsyncDatabase as part of the v0.6.0 accessor reorganisation (D-06).

Both classes are exposed on the parent database via a lazy-cached maint property. The flat db.* maintenance names were removed in v0.7.0.

class pycopg.maint.MaintAccessor(db: Database)[source]¶

Bases: object

Maintenance helper namespace exposed as db.maint.

Methods are moved verbatim from Database. Size queries and maintenance operations (VACUUM, ANALYZE, EXPLAIN) are accessible via this accessor.

__init__(db: Database) → None[source]¶

Store the parent database reference.

Parameters:: db (Database) – Parent database instance. Stored as self._db; no connection check is performed at construction time.

size(*, pretty: bool = True) → str | int[source]¶

Get database size.

Parameters:: pretty (bool, optional) – Return human-readable size (e.g., ‘1.2 GB’), by default True.
Returns:: Database size.
Return type:: str or int

table_size(table: str, *, schema: str = 'public', pretty: bool = True) → str | int[source]¶

Get table size including indexes.

Parameters:

table (str) – Table name.
schema (str, optional) – Schema name, by default “public”.
pretty (bool, optional) – Return human-readable size, by default True.

Returns:

Table size.

Return type:

str or int

table_sizes(*, schema: str = 'public', limit: int = 20) → list[dict[str, Any]][source]¶

Get sizes of all tables in schema, sorted by size.

Parameters:

schema (str, optional) – Schema name, by default “public”.
limit (int, optional) – Max tables to return, by default 20.

Returns:

List of table size info.

Return type:

list of dict

vacuum(*, table: str | None = None, schema: str = 'public', analyze: bool = True, full: bool = False) → None[source]¶

Vacuum database or table.

Parameters:

table (str, optional) – Table name (None for whole database).
schema (str, optional) – Schema name, by default “public”.
analyze (bool, optional) – Update statistics, by default True.
full (bool, optional) – Full vacuum (reclaims more space but locks table), by default False.

analyze(*, table: str | None = None, schema: str = 'public') → None[source]¶

Update table statistics for query planner.

Parameters:

table (str, optional) – Table name (None for whole database).
schema (str, optional) – Schema name, by default “public”.

explain(sql: str, params: Sequence[Any] | Mapping[str, Any] | None = None, *, analyze: bool = False, format: str = 'text') → list[str][source]¶

Get query execution plan.

Parameters:

sql (str) – SQL query.
params (Sequence, optional) – Query parameters.
analyze (bool, optional) – Actually run the query for real stats, by default False.
format (str, optional) – Output format (text, json, xml, yaml), by default “text”.

Returns:

Query plan lines.

Return type:

list of str

class pycopg.maint.AsyncMaintAccessor(db: AsyncDatabase)[source]¶

Bases: object

Async maintenance helper namespace exposed as async_db.maint.

Mirrors MaintAccessor exactly with await calls.

__init__(db: AsyncDatabase) → None[source]¶

Store the parent async database reference.

Parameters:: db (AsyncDatabase) – Parent async database instance. Stored as self._db; no connection check is performed at construction time.

async size(*, pretty: bool = True) → str | int[source]¶

Get database size.

Parameters:: pretty (bool, optional) – Return human-readable size (e.g., ‘1.2 GB’), by default True.
Returns:: Database size.
Return type:: str or int

async table_size(table: str, *, schema: str = 'public', pretty: bool = True) → str | int[source]¶

Get table size including indexes.

Parameters:

table (str) – Table name.
schema (str, optional) – Schema name, by default “public”.
pretty (bool, optional) – Return human-readable size, by default True.

Returns:

Table size.

Return type:

str or int

async table_sizes(*, schema: str = 'public', limit: int = 20) → list[dict[str, Any]][source]¶

Get sizes of all tables in schema, sorted by size.

Parameters:

schema (str, optional) – Schema name, by default “public”.
limit (int, optional) – Max tables to return, by default 20.

Returns:

List of table size info.

Return type:

list of dict

async vacuum(*, table: str | None = None, schema: str = 'public', analyze: bool = True, full: bool = False) → None[source]¶

Vacuum database or table.

Parameters:

table (str, optional) – Table name (None for whole database).
schema (str, optional) – Schema name, by default “public”.
analyze (bool, optional) – Update statistics, by default True.
full (bool, optional) – Full vacuum (reclaims more space but locks table), by default False.

async analyze(*, table: str | None = None, schema: str = 'public') → None[source]¶

Update table statistics for query planner.

Parameters:

table (str, optional) – Table name (None for whole database).
schema (str, optional) – Schema name, by default “public”.

async explain(sql: str, params: Sequence[Any] | Mapping[str, Any] | None = None, *, analyze: bool = False, format: str = 'text') → list[str][source]¶

Get query execution plan.

Parameters:

sql (str) – SQL query.
params (Sequence, optional) – Query parameters.
analyze (bool, optional) – Actually run the query for real stats, by default False.
format (str, optional) – Output format (text, json, xml, yaml), by default “text”.

Returns:

Query plan lines.

Return type:

list of str

Backup & restore accessor classes for db.backup.* / async_db.backup.*.

This module provides BackupAccessor and AsyncBackupAccessor — the real implementation of the 4 public backup/restore/CSV methods (plus the private _psql_restore companion), moved verbatim from Database / AsyncDatabase as part of the v0.6.0 accessor reorganisation (D-06).

Both classes are exposed on the parent database via a lazy-cached backup property. The flat db.* backup names were removed in v0.7.0.

class pycopg.backup.BackupAccessor(db: Database)[source]¶

Bases: object

Backup helper namespace exposed as db.backup.

Methods are moved verbatim from Database. Database dump/restore and CSV import/export operations are accessible via this accessor.

__init__(db: Database) → None[source]¶

Store the parent database reference.

Parameters:: db (Database) – Parent database instance. Stored as self._db; no connection check is performed at construction time.

pg_dump(output_file: str | Path, *, format: Literal['plain', 'custom', 'directory', 'tar'] = 'custom', schema_only: bool = False, data_only: bool = False, tables: list[str] | None = None, exclude_tables: list[str] | None = None, schemas: list[str] | None = None, compress: int = 6, jobs: int = 1) → None[source]¶

Backup database using pg_dump.

Parameters:

output_file (str or Path) – Output file path.
format ({'plain', 'custom', 'directory', 'tar'}, optional) – Dump format (plain=SQL, custom=compressed, directory=parallel, tar), by default “custom”.
schema_only (bool, optional) – Dump only schema, no data, by default False.
data_only (bool, optional) – Dump only data, no schema, by default False.
tables (list of str, optional) – Only dump these tables.
exclude_tables (list of str, optional) – Exclude these tables.
schemas (list of str, optional) – Only dump these schemas.
compress (int, optional) – Compression level (0-9, for custom format), by default 6.
jobs (int, optional) – Parallel jobs (for directory format), by default 1.

pg_restore(input_file: str | Path, *, clean: bool = False, if_exists: bool = True, create: bool = False, data_only: bool = False, schema_only: bool = False, tables: list[str] | None = None, schemas: list[str] | None = None, jobs: int = 1, no_owner: bool = False, no_privileges: bool = False) → None[source]¶

Restore database from pg_dump backup.

Parameters:

input_file (str or Path) – Backup file path.
clean (bool, optional) – Drop objects before recreating, by default False.
if_exists (bool, optional) – Use IF EXISTS with clean (prevents errors), by default True.
create (bool, optional) – Create database before restoring, by default False.
data_only (bool, optional) – Restore only data, by default False.
schema_only (bool, optional) – Restore only schema, by default False.
tables (list of str, optional) – Only restore these tables.
schemas (list of str, optional) – Only restore these schemas.
jobs (int, optional) – Parallel jobs, by default 1.
no_owner (bool, optional) – Don’t restore ownership, by default False.
no_privileges (bool, optional) – Don’t restore privileges, by default False.

copy_to_csv(table: str, output_file: str | Path, *, schema: str = 'public', columns: list[str] | None = None, delimiter: str = ',', header: bool = True, null_string: str = '', encoding: str = 'UTF8') → int[source]¶

Export table to CSV file.

Parameters:

table (str) – Table name.
output_file (str or Path) – Output CSV file path.
schema (str, optional) – Schema name, by default “public”.
columns (list of str, optional) – Specific columns to export.
delimiter (str, optional) – Field delimiter, by default “,”.
header (bool, optional) – Include header row, by default True.
null_string (str, optional) – String for NULL values, by default “”.
encoding (str, optional) – File encoding, by default “UTF8”.

Returns:

Number of rows exported.

Return type:

copy_from_csv(table: str, input_file: str | Path, *, schema: str = 'public', columns: list[str] | None = None, delimiter: str = ',', header: bool = True, null_string: str = '', encoding: str = 'UTF8') → int[source]¶

Import CSV file into table.

Parameters:

table (str) – Table name.
input_file (str or Path) – Input CSV file path.
schema (str, optional) – Schema name, by default “public”.
columns (list of str, optional) – Specific columns to import.
delimiter (str, optional) – Field delimiter, by default “,”.
header (bool, optional) – First row is header, by default True.
null_string (str, optional) – String representing NULL, by default “”.
encoding (str, optional) – File encoding, by default “UTF8”.

Returns:

Number of rows imported.

Return type:

class pycopg.backup.AsyncBackupAccessor(db: AsyncDatabase)[source]¶

Bases: object

Async backup helper namespace exposed as async_db.backup.

Mirrors BackupAccessor exactly with await calls.

__init__(db: AsyncDatabase) → None[source]¶

Store the parent async database reference.

Parameters:: db (AsyncDatabase) – Parent async database instance. Stored as self._db; no connection check is performed at construction time.

async pg_dump(output_file: str | Path, *, format: Literal['plain', 'custom', 'directory', 'tar'] = 'custom', schema_only: bool = False, data_only: bool = False, tables: list[str] | None = None, exclude_tables: list[str] | None = None, schemas: list[str] | None = None, compress: int = 6, jobs: int = 1) → None[source]¶

Backup database using pg_dump.

Parameters:

output_file (str or Path) – Output file path.
format ({'plain', 'custom', 'directory', 'tar'}, optional) – Dump format (plain=SQL, custom=compressed, directory=parallel, tar), by default “custom”.
schema_only (bool, optional) – Dump only schema, no data, by default False.
data_only (bool, optional) – Dump only data, no schema, by default False.
tables (list of str, optional) – Only dump these tables.
exclude_tables (list of str, optional) – Exclude these tables.
schemas (list of str, optional) – Only dump these schemas.
compress (int, optional) – Compression level (0-9, for custom format), by default 6.
jobs (int, optional) – Parallel jobs (for directory format), by default 1.

async pg_restore(input_file: str | Path, *, clean: bool = False, if_exists: bool = True, create: bool = False, data_only: bool = False, schema_only: bool = False, tables: list[str] | None = None, schemas: list[str] | None = None, jobs: int = 1, no_owner: bool = False, no_privileges: bool = False) → None[source]¶

Restore database from pg_dump backup.

Parameters:

input_file (str or Path) – Backup file path.
clean (bool, optional) – Drop objects before recreating, by default False.
if_exists (bool, optional) – Use IF EXISTS with clean (prevents errors), by default True.
create (bool, optional) – Create database before restoring, by default False.
data_only (bool, optional) – Restore only data, by default False.
schema_only (bool, optional) – Restore only schema, by default False.
tables (list of str, optional) – Only restore these tables.
schemas (list of str, optional) – Only restore these schemas.
jobs (int, optional) – Parallel jobs, by default 1.
no_owner (bool, optional) – Don’t restore ownership, by default False.
no_privileges (bool, optional) – Don’t restore privileges, by default False.

async copy_to_csv(table: str, output_file: str | Path, *, schema: str = 'public', columns: list[str] | None = None, delimiter: str = ',', header: bool = True, null_string: str = '', encoding: str = 'UTF8') → int[source]¶

Export table to CSV file.

Parameters:

table (str) – Table name.
output_file (str or Path) – Output CSV file path.
schema (str, optional) – Schema name, by default “public”.
columns (list of str, optional) – Specific columns to export.
delimiter (str, optional) – Field delimiter, by default “,”.
header (bool, optional) – Include header row, by default True.
null_string (str, optional) – String for NULL values, by default “”.
encoding (str, optional) – File encoding, by default “UTF8”.

Returns:

Number of rows exported.

Return type:

async copy_from_csv(table: str, input_file: str | Path, *, schema: str = 'public', columns: list[str] | None = None, delimiter: str = ',', header: bool = True, null_string: str = '', encoding: str = 'UTF8') → int[source]¶

Import CSV file into table.

Parameters:

table (str) – Table name.
input_file (str or Path) – Input CSV file path.
schema (str, optional) – Schema name, by default “public”.
columns (list of str, optional) – Specific columns to import.
delimiter (str, optional) – Field delimiter, by default “,”.
header (bool, optional) – First row is header, by default True.
null_string (str, optional) – String representing NULL, by default “”.
encoding (str, optional) – File encoding, by default “UTF8”.

Returns:

Number of rows imported.

Return type:

Schema accessor classes for db.schema.* / async_db.schema.*.

This module provides SchemaAccessor and AsyncSchemaAccessor — the real implementation of the 32 DDL + introspection helper methods, moved verbatim from Database / AsyncDatabase as part of the v0.6.0 accessor reorganisation (D-06) and extended with 5 introspection helpers in v0.9.0 (primary_key, foreign_keys, sequences, views, describe).

Both classes are exposed on the parent database via a lazy-cached schema property. The flat db.* DDL names were removed in v0.7.0.

class pycopg.schema.TableDescription(columns: list[dict[str, Any]], primary_key: dict[str, Any] | None, foreign_keys: list[dict[str, Any]], indexes: list[dict[str, Any]])[source]¶

Bases: object

Typed wrapper around SchemaAccessor.describe()’s flat dict (D-12).

Returned when describe(into="dataclass") is requested. Fields mirror the four describe dict keys 1:1 — each sub-value is the exact, unreshaped output of its standalone helper (SchemaAccessor.table_info(), SchemaAccessor.primary_key(), SchemaAccessor.foreign_keys(), SchemaAccessor.list_indexes()), preserving the D-04 “shapes can never drift” invariant — this is a typed wrapper only, never a reshape.

Parameters:

columns (list of dict) – Exact output of SchemaAccessor.table_info().
primary_key (dict or None) – Exact output of SchemaAccessor.primary_key().
foreign_keys (list of dict) – Exact output of SchemaAccessor.foreign_keys().
indexes (list of dict) – Exact output of SchemaAccessor.list_indexes().

columns: list[dict[str, Any]]¶

primary_key: dict[str, Any] | None¶

foreign_keys: list[dict[str, Any]]¶

indexes: list[dict[str, Any]]¶

__init__(columns: list[dict[str, Any]], primary_key: dict[str, Any] | None, foreign_keys: list[dict[str, Any]], indexes: list[dict[str, Any]]) → None¶

class pycopg.schema.SchemaAccessor(db: Database)[source]¶

Bases: object

Schema helper namespace exposed as db.schema.

Methods are moved verbatim from Database. DDL and introspection operations (databases, extensions, schemas, tables, columns, constraints, indexes) are accessible via this accessor.

__init__(db: Database) → None[source]¶

Store the parent database reference.

Parameters:: db (Database) – Parent database instance. Stored as self._db; no connection check is performed at construction time.

create_database(name: str, *, owner: str | None = None, template: str = 'template1') → None[source]¶

Create a new database.

Parameters:

name (str) – Database name.
owner (str, optional) – Owner role.
template (str, optional) – Template database, by default “template1”.

drop_database(name: str, *, if_exists: bool = True) → None[source]¶

Drop a database.

Parameters:

name (str) – Database name.
if_exists (bool, optional) – Don’t error if database doesn’t exist, by default True.

database_exists(name: str) → bool[source]¶

Check if a database exists.

Parameters:: name (str) – Database name.
Returns:: True if database exists.
Return type:: bool

list_databases() → list[str][source]¶

List all databases.

Returns:: List of database names.
Return type:: list of str

create_extension(name: str, *, schema: str | None = None, if_not_exists: bool = True) → None[source]¶

Create a PostgreSQL extension.

Parameters:

name (str) – Extension name (e.g., ‘postgis’, ‘timescaledb’, ‘uuid-ossp’).
schema (str, optional) – Schema to install extension in.
if_not_exists (bool, optional) – Don’t error if extension exists, by default True.

drop_extension(name: str, *, if_exists: bool = True, cascade: bool = False) → None[source]¶

Drop a PostgreSQL extension.

Parameters:

name (str) – Extension name.
if_exists (bool, optional) – Don’t error if extension doesn’t exist, by default True.
cascade (bool, optional) – Drop dependent objects, by default False.

list_extensions() → list[dict[str, Any]][source]¶

List installed extensions.

Returns:: List of dicts with extname, extversion, nspname (schema).
Return type:: list of dict

has_extension(name: str) → bool[source]¶

Check if an extension is installed.

Parameters:: name (str) – Extension name.
Returns:: True if extension is installed.
Return type:: bool

create_schema(name: str, *, if_not_exists: bool = True, owner: str | None = None) → None[source]¶

Create a schema.

Parameters:

name (str) – Schema name.
if_not_exists (bool, optional) – Don’t error if schema exists, by default True.
owner (str, optional) – Owner role.

drop_schema(name: str, *, if_exists: bool = True, cascade: bool = False) → None[source]¶

Drop a schema.

Parameters:

name (str) – Schema name.
if_exists (bool, optional) – Don’t error if schema doesn’t exist, by default True.
cascade (bool, optional) – Drop all objects in schema, by default False.

list_schemas() → list[str][source]¶

List all schemas.

Returns:: List of schema names.
Return type:: list of str

schema_exists(name: str) → bool[source]¶

Check if a schema exists.

Parameters:: name (str) – Schema name.
Returns:: True if schema exists.
Return type:: bool

list_tables(*, schema: str = 'public') → list[str][source]¶

List tables in a schema.

Parameters:: schema (str, optional) – Schema name, by default “public”.
Returns:: List of table names.
Return type:: list of str

table_exists(name: str, *, schema: str = 'public') → bool[source]¶

Check if a table exists.

Parameters:

name (str) – Table name.
schema (str, optional) – Schema name, by default “public”.

Returns:

True if table exists.

Return type:

column_exists(table: str, column: str, *, schema: str = 'public') → bool[source]¶

Check if a column exists on a table.

Parameters:

table (str) – Table name.
column (str) – Column name.
schema (str, optional) – Schema name, by default “public”.

Returns:

True if the column exists on the table.

Return type:

list_columns(table: str, *, schema: str = 'public') → list[str][source]¶

Get list of column names for a table.

Parameters:

table (str) – Table name.
schema (str, optional) – Schema name, by default “public”.

Returns:

List of column names in ordinal order.

Return type:

list of str

columns_with_types(table: str, *, schema: str = 'public') → list[tuple[str, str]][source]¶

Get list of (column_name, data_type) tuples for a table.

Parameters:

table (str) – Table name.
schema (str, optional) – Schema name, by default “public”.

Returns:

List of (name, type) tuples in ordinal order.

Return type:

list of tuple of (str, str)

drop_table(name: str, *, schema: str = 'public', if_exists: bool = True, cascade: bool = False) → None[source]¶

Drop a table.

Parameters:

name (str) – Table name.
schema (str, optional) – Schema name, by default “public”.
if_exists (bool, optional) – Don’t error if table doesn’t exist, by default True.
cascade (bool, optional) – Drop dependent objects, by default False.

truncate_table(name: str, *, schema: str = 'public', cascade: bool = False) → None[source]¶

Truncate a table (delete all rows).

Parameters:

name (str) – Table name.
schema (str, optional) – Schema name, by default “public”.
cascade (bool, optional) – Truncate dependent tables, by default False.

Raises:

TableNotFoundError – If the table does not exist in the given schema.

table_info(name: str, *, schema: str = 'public') → list[dict[str, Any]][source]¶

Get column information for a table.

Parameters:

name (str) – Table name.
schema (str, optional) – Schema name, by default “public”.

Returns:

List of column info dicts with column_name, data_type, is_nullable, column_default, ordinal_position.

Return type:

list of dict

row_count(name: str, *, schema: str = 'public') → int[source]¶

Get approximate row count for a table.

Uses pg_stat for speed. For exact count, use execute(“SELECT COUNT(*)…”).

Parameters:

name (str) – Table name.
schema (str, optional) – Schema name, by default “public”.

Returns:

Approximate row count.

Return type:

table_size(name: str, *, schema: str = 'public') → int[source]¶

Get the total on-disk size of a table, in bytes.

Uses pg_total_relation_size, which includes the table’s indexes and TOAST data in addition to the base heap size. Returns the raw byte count with no pretty formatting.

Parameters:

name (str) – Table name.
schema (str, optional) – Schema name, by default “public”.

Returns:

Total table size in bytes (heap + indexes + TOAST).

Return type: