PostgreSQL

Support for the PostgreSQL database.

The following table summarizes current support levels for database release versions.

DBAPI Support

The following dialect/DBAPI options are available. Please refer to individual DBAPI sections for connect information.

• psycopg2

• psycopg (a.k.a. psycopg 3)

• pg8000

• asyncpg

• psycopg2cffi

Sequences/SERIAL/IDENTITY

PostgreSQL supports sequences, and SQLAlchemy uses these as the default means of creating new primary key values for integer-based primary key columns. When creating tables, SQLAlchemy will issue the SERIAL datatype for integer-based primary key columns, which generates a sequence and server side default corresponding to the column.

To specify a specific named sequence to be used for primary key generation, use the Sequence() construct:

Table(
    "sometable",
    metadata,
    Column(
        "id", Integer, Sequence("some_id_seq", start=1), primary_key=True
    )
)

When SQLAlchemy issues a single INSERT statement, to fulfill the contract of having the “last insert identifier” available, a RETURNING clause is added to the INSERT statement which specifies the primary key columns should be returned after the statement completes. The RETURNING functionality only takes place if PostgreSQL 8.2 or later is in use. As a fallback approach, the sequence, whether specified explicitly or implicitly via SERIAL, is executed independently beforehand, the returned value to be used in the subsequent insert. Note that when an insert() construct is executed using “executemany” semantics, the “last inserted identifier” functionality does not apply; no RETURNING clause is emitted nor is the sequence pre-executed in this case.

PostgreSQL 10 and above IDENTITY columns

PostgreSQL 10 and above have a new IDENTITY feature that supersedes the use of SERIAL. The Identity construct in a Column can be used to control its behavior:

from sqlalchemy import Table, Column, MetaData, Integer, Computed
metadata = MetaData()
data = Table(
    "data",
    metadata,
    Column(
        'id', Integer, Identity(start=42, cycle=True), primary_key=True
    ),
    Column('data', String)
)

The CREATE TABLE for the above Table object would be:

CREATE TABLE data (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 42 CYCLE),
    data VARCHAR,
    PRIMARY KEY (id)
)

Changed in version 1.4: Added Identity construct in a Column to specify the option of an autoincrementing column.

Note

Previous versions of SQLAlchemy did not have built-in support for rendering of IDENTITY, and could use the following compilation hook to replace occurrences of SERIAL with IDENTITY:

from sqlalchemy.schema import CreateColumn
from sqlalchemy.ext.compiler import compiles
@compiles(CreateColumn, 'postgresql')
def use_identity(element, compiler, **kw):
    text = compiler.visit_create_column(element, **kw)
    text = text.replace(
        "SERIAL", "INT GENERATED BY DEFAULT AS IDENTITY"
     )
    return text

Using the above, a table such as:

t = Table(
    't', m,
    Column('id', Integer, primary_key=True),
    Column('data', String)
)

Will generate on the backing database as:

CREATE TABLE t (
    id INT GENERATED BY DEFAULT AS IDENTITY,
    data VARCHAR,
    PRIMARY KEY (id)
)

Server Side Cursors

Server-side cursor support is available for the psycopg2, asyncpg dialects and may also be available in others.

Server side cursors are enabled on a per-statement basis by using the Connection.execution_options.stream_results connection execution option:

with engine.connect() as conn:
    result = conn.execution_options(stream_results=True).execute(text("select * from table"))

Note that some kinds of SQL statements may not be supported with server side cursors; generally, only SQL statements that return rows should be used with this option.

Deprecated since version 1.4: The dialect-level server_side_cursors flag is deprecated and will be removed in a future release. Please use the Connection.stream_results execution option for unbuffered cursor support.

See also

Using Server Side Cursors (a.k.a. stream results)

Transaction Isolation Level

Most SQLAlchemy dialects support setting of transaction isolation level using the create_engine.isolation_level parameter at the create_engine() level, and at the Connection level via the Connection.execution_options.isolation_level parameter.

For PostgreSQL dialects, this feature works either by making use of the DBAPI-specific features, such as psycopg2’s isolation level flags which will embed the isolation level setting inline with the "BEGIN" statement, or for DBAPIs with no direct support by emitting SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL <level> ahead of the "BEGIN" statement emitted by the DBAPI. For the special AUTOCOMMIT isolation level, DBAPI-specific techniques are used which is typically an .autocommit flag on the DBAPI connection object.

To set isolation level using create_engine():

engine = create_engine(
    "postgresql+pg8000://scott:tiger@localhost/test",
    isolation_level = "REPEATABLE READ"
)

To set using per-connection execution options:

with engine.connect() as conn:
    conn = conn.execution_options(
        isolation_level="REPEATABLE READ"
    )
    with conn.begin():
        # ... work with transaction

There are also more options for isolation level configurations, such as “sub-engine” objects linked to a main Engine which each apply different isolation level settings. See the discussion at Setting Transaction Isolation Levels including DBAPI Autocommit for background.

Valid values for isolation_level on most PostgreSQL dialects include:

• READ COMMITTED

• READ UNCOMMITTED

• REPEATABLE READ

• SERIALIZABLE

• AUTOCOMMIT

See also

Setting Transaction Isolation Levels including DBAPI Autocommit

Setting READ ONLY / DEFERRABLE

Psycopg2 Transaction Isolation Level

pg8000 Transaction Isolation Level

Setting READ ONLY / DEFERRABLE

Most PostgreSQL dialects support setting the “READ ONLY” and “DEFERRABLE” characteristics of the transaction, which is in addition to the isolation level setting. These two attributes can be established either in conjunction with or independently of the isolation level by passing the postgresql_readonly and postgresql_deferrable flags with Connection.execution_options(). The example below illustrates passing the "SERIALIZABLE" isolation level at the same time as setting “READ ONLY” and “DEFERRABLE”:

with engine.connect() as conn:
    conn = conn.execution_options(
        isolation_level="SERIALIZABLE",
        postgresql_readonly=True,
        postgresql_deferrable=True
    )
    with conn.begin():
        #  ... work with transaction

Note that some DBAPIs such as asyncpg only support “readonly” with SERIALIZABLE isolation.

New in version 1.4: added support for the postgresql_readonly and postgresql_deferrable execution options.

Temporary Table / Resource Reset for Connection Pooling

The QueuePool connection pool implementation used by the SQLAlchemy Engine object includes reset on return behavior that will invoke the DBAPI .rollback() method when connections are returned to the pool. While this rollback will clear out the immediate state used by the previous transaction, it does not cover a wider range of session-level state, including temporary tables as well as other server state such as prepared statement handles and statement caches. The PostgreSQL database includes a variety of commands which may be used to reset this state, including DISCARD, RESET, DEALLOCATE, and UNLISTEN.

To install one or more of these commands as the means of performing reset-on-return, the PoolEvents.reset() event hook may be used, as demonstrated in the example below. The implementation will end transactions in progress as well as discard temporary tables using the CLOSE, RESET and DISCARD commands; see the PostgreSQL documentation for background on what each of these statements do.

The create_engine.pool_reset_on_return parameter is set to None so that the custom scheme can replace the default behavior completely. The custom hook implementation calls .rollback() in any case, as it’s usually important that the DBAPI’s own tracking of commit/rollback will remain consistent with the state of the transaction:

from sqlalchemy import create_engine
from sqlalchemy import event
postgresql_engine = create_engine(
    "postgresql+pyscopg2://scott:tiger@hostname/dbname",
    # disable default reset-on-return scheme
    pool_reset_on_return=None,
)
@event.listens_for(postgresql_engine, "reset")
def _reset_postgresql(dbapi_connection, connection_record, reset_state):
    if not reset_state.terminate_only:
        dbapi_connection.execute("CLOSE ALL")
        dbapi_connection.execute("RESET ALL")
        dbapi_connection.execute("DISCARD TEMP")
    # so that the DBAPI itself knows that the connection has been
    # reset
    dbapi_connection.rollback()

Changed in version 2.0.0b3: Added additional state arguments to the PoolEvents.reset() event and additionally ensured the event is invoked for all “reset” occurrences, so that it’s appropriate as a place for custom “reset” handlers. Previous schemes which use the PoolEvents.checkin() handler remain usable as well.

See also

Reset On Return - in the Connection Pooling documentation

Setting Alternate Search Paths on Connect

The PostgreSQL search_path variable refers to the list of schema names that will be implicitly referred towards when a particular table or other object is referenced in a SQL statement. As detailed in the next section Remote-Schema Table Introspection and PostgreSQL search_path, SQLAlchemy is generally organized around the concept of keeping this variable at its default value of public, however, in order to have it set to any arbitrary name or names when connections are used automatically, the “SET SESSION search_path” command may be invoked for all connections in a pool using the following event handler, as discussed at Setting a Default Schema for New Connections:

from sqlalchemy import event
from sqlalchemy import create_engine
engine = create_engine("postgresql+psycopg2://scott:tiger@host/dbname")
@event.listens_for(engine, "connect", insert=True)
def set_search_path(dbapi_connection, connection_record):
    existing_autocommit = dbapi_connection.autocommit
    dbapi_connection.autocommit = True
    cursor = dbapi_connection.cursor()
    cursor.execute("SET SESSION search_path='%s'" % schema_name)
    cursor.close()
    dbapi_connection.autocommit = existing_autocommit

The reason the recipe is complicated by use of the .autocommit DBAPI attribute is so that when the SET SESSION search_path directive is invoked, it is invoked outside of the scope of any transaction and therefore will not be reverted when the DBAPI connection has a rollback.

See also

Setting a Default Schema for New Connections - in the Describing Databases with MetaData documentation

Remote-Schema Table Introspection and PostgreSQL search_path

Section Best Practices Summarized

keep the search_path variable set to its default of public, without any other schema names. For other schema names, name these explicitly within Table definitions. Alternatively, the postgresql_ignore_search_path option will cause all reflected Table objects to have a Table.schema attribute set up.

The PostgreSQL dialect can reflect tables from any schema, as outlined in Reflecting Tables from Other Schemas.

With regards to tables which these Table objects refer to via foreign key constraint, a decision must be made as to how the .schema is represented in those remote tables, in the case where that remote schema name is also a member of the current PostgreSQL search path.

By default, the PostgreSQL dialect mimics the behavior encouraged by PostgreSQL’s own pg_get_constraintdef() builtin procedure. This function returns a sample definition for a particular foreign key constraint, omitting the referenced schema name from that definition when the name is also in the PostgreSQL schema search path. The interaction below illustrates this behavior:

test=> CREATE TABLE test_schema.referred(id INTEGER PRIMARY KEY);
CREATE TABLE
test=> CREATE TABLE referring(
test(>         id INTEGER PRIMARY KEY,
test(>         referred_id INTEGER REFERENCES test_schema.referred(id));
CREATE TABLE
test=> SET search_path TO public, test_schema;
test=> SELECT pg_catalog.pg_get_constraintdef(r.oid, true) FROM
test-> pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n
test-> ON n.oid = c.relnamespace
test-> JOIN pg_catalog.pg_constraint r  ON c.oid = r.conrelid
test-> WHERE c.relname='referring' AND r.contype = 'f'
test-> ;
               pg_get_constraintdef
---------------------------------------------------
 FOREIGN KEY (referred_id) REFERENCES referred(id)
(1 row)

Above, we created a table referred as a member of the remote schema test_schema, however when we added test_schema to the PG search_path and then asked pg_get_constraintdef() for the FOREIGN KEY syntax, test_schema was not included in the output of the function.

On the other hand, if we set the search path back to the typical default of public:

test=> SET search_path TO public;
SET

The same query against pg_get_constraintdef() now returns the fully schema-qualified name for us:

test=> SELECT pg_catalog.pg_get_constraintdef(r.oid, true) FROM
test-> pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n
test-> ON n.oid = c.relnamespace
test-> JOIN pg_catalog.pg_constraint r  ON c.oid = r.conrelid
test-> WHERE c.relname='referring' AND r.contype = 'f';
                     pg_get_constraintdef
---------------------------------------------------------------
 FOREIGN KEY (referred_id) REFERENCES test_schema.referred(id)
(1 row)

SQLAlchemy will by default use the return value of pg_get_constraintdef() in order to determine the remote schema name. That is, if our search_path were set to include test_schema, and we invoked a table reflection process as follows:

>>> from sqlalchemy import Table, MetaData, create_engine, text
>>> engine = create_engine("postgresql+psycopg2://scott:tiger@localhost/test")
>>> with engine.connect() as conn:
...     conn.execute(text("SET search_path TO test_schema, public"))
...     metadata_obj = MetaData()
...     referring = Table('referring', metadata_obj,
...                       autoload_with=conn)
...
<sqlalchemy.engine.result.CursorResult object at 0x101612ed0>

The above process would deliver to the MetaData.tables collection referred table named without the schema:

>>> metadata_obj.tables['referred'].schema is None
True

To alter the behavior of reflection such that the referred schema is maintained regardless of the search_path setting, use the postgresql_ignore_search_path option, which can be specified as a dialect-specific argument to both Table as well as MetaData.reflect():

>>> with engine.connect() as conn:
...     conn.execute(text("SET search_path TO test_schema, public"))
...     metadata_obj = MetaData()
...     referring = Table('referring', metadata_obj,
...                       autoload_with=conn,
...                       postgresql_ignore_search_path=True)
...
<sqlalchemy.engine.result.CursorResult object at 0x1016126d0>

We will now have test_schema.referred stored as schema-qualified:

>>> metadata_obj.tables['test_schema.referred'].schema
'test_schema'

Best Practices for PostgreSQL Schema reflection

The description of PostgreSQL schema reflection behavior is complex, and is the product of many years of dealing with widely varied use cases and user preferences. But in fact, there’s no need to understand any of it if you just stick to the simplest use pattern: leave the search_path set to its default of public only, never refer to the name public as an explicit schema name otherwise, and refer to all other schema names explicitly when building up a Table object. The options described here are only for those users who can’t, or prefer not to, stay within these guidelines.

Note that in all cases, the “default” schema is always reflected as None. The “default” schema on PostgreSQL is that which is returned by the PostgreSQL current_schema() function. On a typical PostgreSQL installation, this is the name public. So a table that refers to another which is in the public (i.e. default) schema will always have the .schema attribute set to None.

See also

Interaction of Schema-qualified Reflection with the Default Schema - discussion of the issue from a backend-agnostic perspective

The Schema Search Path - on the PostgreSQL website.

INSERT/UPDATE…RETURNING

The dialect supports PG 8.2’s INSERT..RETURNING, UPDATE..RETURNING and DELETE..RETURNING syntaxes. INSERT..RETURNING is used by default for single-row INSERT statements in order to fetch newly generated primary key identifiers. To specify an explicit RETURNING clause, use the _UpdateBase.returning() method on a per-statement basis:

# INSERT..RETURNING
result = table.insert().returning(table.c.col1, table.c.col2).\
    values(name='foo')
print(result.fetchall())
# UPDATE..RETURNING
result = table.update().returning(table.c.col1, table.c.col2).\
    where(table.c.name=='foo').values(name='bar')
print(result.fetchall())
# DELETE..RETURNING
result = table.delete().returning(table.c.col1, table.c.col2).\
    where(table.c.name=='foo')
print(result.fetchall())

INSERT…ON CONFLICT (Upsert)

Starting with version 9.5, PostgreSQL allows “upserts” (update or insert) of rows into a table via the ON CONFLICT clause of the INSERT statement. A candidate row will only be inserted if that row does not violate any unique constraints. In the case of a unique constraint violation, a secondary action can occur which can be either “DO UPDATE”, indicating that the data in the target row should be updated, or “DO NOTHING”, which indicates to silently skip this row.

Conflicts are determined using existing unique constraints and indexes. These constraints may be identified either using their name as stated in DDL, or they may be inferred by stating the columns and conditions that comprise the indexes.

SQLAlchemy provides ON CONFLICT support via the PostgreSQL-specific insert() function, which provides the generative methods Insert.on_conflict_do_update() and Insert.on_conflict_do_nothing():

>>> from sqlalchemy.dialects.postgresql import insert
>>> insert_stmt = insert(my_table).values(
...     id='some_existing_id',
...     data='inserted value')
>>> do_nothing_stmt = insert_stmt.on_conflict_do_nothing(
...     index_elements=['id']
... )
>>> print(do_nothing_stmt)
INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s)
ON CONFLICT (id) DO NOTHING
>>> do_update_stmt = insert_stmt.on_conflict_do_update(
...     constraint='pk_my_table',
...     set_=dict(data='updated value')
... )
>>> print(do_update_stmt)
INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s)
ON CONFLICT ON CONSTRAINT pk_my_table DO UPDATE SET data = %(param_1)s

New in version 1.1.

See also

INSERT .. ON CONFLICT - in the PostgreSQL documentation.

Specifying the Target

Both methods supply the “target” of the conflict using either the named constraint or by column inference:

• The Insert.on_conflict_do_update.index_elements argument specifies a sequence containing string column names, Column objects, and/or SQL expression elements, which would identify a unique index:

  >>> do_update_stmt = insert_stmt.on_conflict_do_update(
  ...     index_elements=['id'],
  ...     set_=dict(data='updated value')
  ... )
  >>> print(do_update_stmt)
  INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s)
  ON CONFLICT (id) DO UPDATE SET data = %(param_1)s
  >>> do_update_stmt = insert_stmt.on_conflict_do_update(
  ...     index_elements=[my_table.c.id],
  ...     set_=dict(data='updated value')
  ... )
  >>> print(do_update_stmt)
  INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s)
  ON CONFLICT (id) DO UPDATE SET data = %(param_1)s

• When using Insert.on_conflict_do_update.index_elements to infer an index, a partial index can be inferred by also specifying the use the Insert.on_conflict_do_update.index_where parameter:

  >>> stmt = insert(my_table).values(user_email='a@b.com', data='inserted data')
  >>> stmt = stmt.on_conflict_do_update(
  ...     index_elements=[my_table.c.user_email],
  ...     index_where=my_table.c.user_email.like('%@gmail.com'),
  ...     set_=dict(data=stmt.excluded.data)
  ... )
  >>> print(stmt)
  INSERT INTO my_table (data, user_email)
  VALUES (%(data)s, %(user_email)s) ON CONFLICT (user_email)
  WHERE user_email LIKE %(user_email_1)s DO UPDATE SET data = excluded.data

• The Insert.on_conflict_do_update.constraint argument is used to specify an index directly rather than inferring it. This can be the name of a UNIQUE constraint, a PRIMARY KEY constraint, or an INDEX:

  >>> do_update_stmt = insert_stmt.on_conflict_do_update(
  ...     constraint='my_table_idx_1',
  ...     set_=dict(data='updated value')
  ... )
  >>> print(do_update_stmt)
  INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s)
  ON CONFLICT ON CONSTRAINT my_table_idx_1 DO UPDATE SET data = %(param_1)s
  >>> do_update_stmt = insert_stmt.on_conflict_do_update(
  ...     constraint='my_table_pk',
  ...     set_=dict(data='updated value')
  ... )
  >>> print(do_update_stmt)
  INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s)
  ON CONFLICT ON CONSTRAINT my_table_pk DO UPDATE SET data = %(param_1)s

• The Insert.on_conflict_do_update.constraint argument may also refer to a SQLAlchemy construct representing a constraint, e.g. UniqueConstraint, PrimaryKeyConstraint, Index, or ExcludeConstraint. In this use, if the constraint has a name, it is used directly. Otherwise, if the constraint is unnamed, then inference will be used, where the expressions and optional WHERE clause of the constraint will be spelled out in the construct. This use is especially convenient to refer to the named or unnamed primary key of a Table using the Table.primary_key attribute:

  >>> do_update_stmt = insert_stmt.on_conflict_do_update(
  ...     constraint=my_table.primary_key,
  ...     set_=dict(data='updated value')
  ... )
  >>> print(do_update_stmt)
  INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s)
  ON CONFLICT (id) DO UPDATE SET data = %(param_1)s

The SET Clause

ON CONFLICT...DO UPDATE is used to perform an update of the already existing row, using any combination of new values as well as values from the proposed insertion. These values are specified using the Insert.on_conflict_do_update.set_ parameter. This parameter accepts a dictionary which consists of direct values for UPDATE:

>>> stmt = insert(my_table).values(id='some_id', data='inserted value')
>>> do_update_stmt = stmt.on_conflict_do_update(
...     index_elements=['id'],
...     set_=dict(data='updated value')
... )
>>> print(do_update_stmt)
INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s)
ON CONFLICT (id) DO UPDATE SET data = %(param_1)s

Warning

The Insert.on_conflict_do_update() method does not take into account Python-side default UPDATE values or generation functions, e.g. those specified using Column.onupdate. These values will not be exercised for an ON CONFLICT style of UPDATE, unless they are manually specified in the Insert.on_conflict_do_update.set_ dictionary.

Updating using the Excluded INSERT Values

In order to refer to the proposed insertion row, the special alias Insert.excluded is available as an attribute on the Insert object; this object is a ColumnCollection which alias contains all columns of the target table:

>>> stmt = insert(my_table).values(
...     id='some_id',
...     data='inserted value',
...     author='jlh'
... )
>>> do_update_stmt = stmt.on_conflict_do_update(
...     index_elements=['id'],
...     set_=dict(data='updated value', author=stmt.excluded.author)
... )
>>> print(do_update_stmt)
INSERT INTO my_table (id, data, author)
VALUES (%(id)s, %(data)s, %(author)s)
ON CONFLICT (id) DO UPDATE SET data = %(param_1)s, author = excluded.author

Additional WHERE Criteria

The Insert.on_conflict_do_update() method also accepts a WHERE clause using the Insert.on_conflict_do_update.where parameter, which will limit those rows which receive an UPDATE:

>>> stmt = insert(my_table).values(
...     id='some_id',
...     data='inserted value',
...     author='jlh'
... )
>>> on_update_stmt = stmt.on_conflict_do_update(
...     index_elements=['id'],
...     set_=dict(data='updated value', author=stmt.excluded.author),
...     where=(my_table.c.status == 2)
... )
>>> print(on_update_stmt)
INSERT INTO my_table (id, data, author)
VALUES (%(id)s, %(data)s, %(author)s)
ON CONFLICT (id) DO UPDATE SET data = %(param_1)s, author = excluded.author
WHERE my_table.status = %(status_1)s

Skipping Rows with DO NOTHING

ON CONFLICT may be used to skip inserting a row entirely if any conflict with a unique or exclusion constraint occurs; below this is illustrated using the Insert.on_conflict_do_nothing() method:

>>> stmt = insert(my_table).values(id='some_id', data='inserted value')
>>> stmt = stmt.on_conflict_do_nothing(index_elements=['id'])
>>> print(stmt)
INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s)
ON CONFLICT (id) DO NOTHING

If DO NOTHING is used without specifying any columns or constraint, it has the effect of skipping the INSERT for any unique or exclusion constraint violation which occurs:

>>> stmt = insert(my_table).values(id='some_id', data='inserted value')
>>> stmt = stmt.on_conflict_do_nothing()
>>> print(stmt)
INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s)
ON CONFLICT DO NOTHING

Full Text Search

PostgreSQL’s full text search system is available through the use of the func namespace, combined with the use of custom operators via the Operators.bool_op() method. For simple cases with some degree of cross-backend compatibility, the Operators.match() operator may also be used.

Simple plain text matching with match()

The Operators.match() operator provides for cross-compatible simple text matching. For the PostgreSQL backend, it’s hardcoded to generate an expression using the @@ operator in conjunction with the plainto_tsquery() PostgreSQL function.

On the PostgreSQL dialect, an expression like the following:

select(sometable.c.text.match("search string"))

would emit to the database:

SELECT text @@ plainto_tsquery('search string') FROM table

Above, passing a plain string to Operators.match() will automatically make use of plainto_tsquery() to specify the type of tsquery. This establishes basic database cross-compatibility for Operators.match() with other backends.

Changed in version 2.0: The default tsquery generation function used by the PostgreSQL dialect with Operators.match() is plainto_tsquery().

To render exactly what was rendered in 1.4, use the following form:

from sqlalchemy import func
select(
    sometable.c.text.bool_op("@@")(func.to_tsquery("search string"))
)

Which would emit:

SELECT text @@ to_tsquery('search string') FROM table

Using PostgreSQL full text functions and operators directly

Text search operations beyond the simple use of Operators.match() may make use of the func namespace to generate PostgreSQL full-text functions, in combination with Operators.bool_op() to generate any boolean operator.

For example, the query:

select(
    func.to_tsquery('cat').bool_op("@>")(func.to_tsquery('cat & rat'))
)

would generate:

SELECT to_tsquery('cat') @> to_tsquery('cat & rat')

The TSVECTOR type can provide for explicit CAST:

from sqlalchemy.dialects.postgresql import TSVECTOR
from sqlalchemy import select, cast
select(cast("some text", TSVECTOR))

produces a statement equivalent to:

SELECT CAST('some text' AS TSVECTOR) AS anon_1

The func namespace is augmented by the PostgreSQL dialect to set up correct argument and return types for most full text search functions. These functions are used automatically by the sqlalchemy.sql.expression.func namespace assuming the sqlalchemy.dialects.postgresql package has been imported, or create_engine() has been invoked using a postgresql dialect. These functions are documented at:

• to_tsvector

• to_tsquery

• plainto_tsquery

• phraseto_tsquery

• websearch_to_tsquery

• ts_headline

Specifying the “regconfig” with match() or custom operators

PostgreSQL’s plainto_tsquery() function accepts an optional “regconfig” argument that is used to instruct PostgreSQL to use a particular pre-computed GIN or GiST index in order to perform the search. When using Operators.match(), this additional parameter may be specified using the postgresql_regconfig parameter, such as:

select(mytable.c.id).where(
    mytable.c.title.match('somestring', postgresql_regconfig='english')
)

Which would emit:

SELECT mytable.id FROM mytable
WHERE mytable.title @@ plainto_tsquery('english', 'somestring')

When using other PostgreSQL search functions with func, the “regconfig” parameter may be passed directly as the initial argument:

select(mytable.c.id).where(
    func.to_tsvector("english", mytable.c.title).bool_op("@@")(
        func.to_tsquery("english", "somestring")
    )
)

produces a statement equivalent to:

SELECT mytable.id FROM mytable
WHERE to_tsvector('english', mytable.title) @@
    to_tsquery('english', 'somestring')

It is recommended that you use the EXPLAIN ANALYZE... tool from PostgreSQL to ensure that you are generating queries with SQLAlchemy that take full advantage of any indexes you may have created for full text search.

See also

Full Text Search - in the PostgreSQL documentation

FROM ONLY …

The dialect supports PostgreSQL’s ONLY keyword for targeting only a particular table in an inheritance hierarchy. This can be used to produce the SELECT ... FROM ONLY, UPDATE ONLY ..., and DELETE FROM ONLY ... syntaxes. It uses SQLAlchemy’s hints mechanism:

# SELECT ... FROM ONLY ...
result = table.select().with_hint(table, 'ONLY', 'postgresql')
print(result.fetchall())
# UPDATE ONLY ...
table.update(values=dict(foo='bar')).with_hint('ONLY',
                                               dialect_name='postgresql')
# DELETE FROM ONLY ...
table.delete().with_hint('ONLY', dialect_name='postgresql')

PostgreSQL-Specific Index Options

Several extensions to the Index construct are available, specific to the PostgreSQL dialect.

Covering Indexes

The postgresql_include option renders INCLUDE(colname) for the given string names:

Index("my_index", table.c.x, postgresql_include=['y'])

would render the index as CREATE INDEX my_index ON table (x) INCLUDE (y)

Note that this feature requires PostgreSQL 11 or later.

New in version 1.4.

Partial Indexes

Partial indexes add criterion to the index definition so that the index is applied to a subset of rows. These can be specified on Index using the postgresql_where keyword argument:

Index('my_index', my_table.c.id, postgresql_where=my_table.c.value > 10)

Operator Classes

PostgreSQL allows the specification of an operator class for each column of an index (see https://www.postgresql.org/docs/current/interactive/indexes-opclass.html). The Index construct allows these to be specified via the postgresql_ops keyword argument:

Index(
    'my_index', my_table.c.id, my_table.c.data,
    postgresql_ops={
        'data': 'text_pattern_ops',
        'id': 'int4_ops'
    })

Note that the keys in the postgresql_ops dictionaries are the “key” name of the Column, i.e. the name used to access it from the .c collection of Table, which can be configured to be different than the actual name of the column as expressed in the database.

If postgresql_ops is to be used against a complex SQL expression such as a function call, then to apply to the column it must be given a label that is identified in the dictionary by name, e.g.:

Index(
    'my_index', my_table.c.id,
    func.lower(my_table.c.data).label('data_lower'),
    postgresql_ops={
        'data_lower': 'text_pattern_ops',
        'id': 'int4_ops'
    })

Operator classes are also supported by the ExcludeConstraint construct using the ExcludeConstraint.ops parameter. See that parameter for details.

New in version 1.3.21: added support for operator classes with ExcludeConstraint.

Index Types

PostgreSQL provides several index types: B-Tree, Hash, GiST, and GIN, as well as the ability for users to create their own (see https://www.postgresql.org/docs/current/static/indexes-types.html). These can be specified on Index using the postgresql_using keyword argument:

Index('my_index', my_table.c.data, postgresql_using='gin')

The value passed to the keyword argument will be simply passed through to the underlying CREATE INDEX command, so it must be a valid index type for your version of PostgreSQL.

Index Storage Parameters

PostgreSQL allows storage parameters to be set on indexes. The storage parameters available depend on the index method used by the index. Storage parameters can be specified on Index using the postgresql_with keyword argument:

Index('my_index', my_table.c.data, postgresql_with={"fillfactor": 50})

New in version 1.0.6.

PostgreSQL allows to define the tablespace in which to create the index. The tablespace can be specified on Index using the postgresql_tablespace keyword argument:

Index('my_index', my_table.c.data, postgresql_tablespace='my_tablespace')

New in version 1.1.

Note that the same option is available on Table as well.

Indexes with CONCURRENTLY

The PostgreSQL index option CONCURRENTLY is supported by passing the flag postgresql_concurrently to the Index construct:

tbl = Table('testtbl', m, Column('data', Integer))
idx1 = Index('test_idx1', tbl.c.data, postgresql_concurrently=True)

The above index construct will render DDL for CREATE INDEX, assuming PostgreSQL 8.2 or higher is detected or for a connection-less dialect, as:

CREATE INDEX CONCURRENTLY test_idx1 ON testtbl (data)

For DROP INDEX, assuming PostgreSQL 9.2 or higher is detected or for a connection-less dialect, it will emit:

DROP INDEX CONCURRENTLY test_idx1

New in version 1.1: support for CONCURRENTLY on DROP INDEX. The CONCURRENTLY keyword is now only emitted if a high enough version of PostgreSQL is detected on the connection (or for a connection-less dialect).

When using CONCURRENTLY, the PostgreSQL database requires that the statement be invoked outside of a transaction block. The Python DBAPI enforces that even for a single statement, a transaction is present, so to use this construct, the DBAPI’s “autocommit” mode must be used:

metadata = MetaData()
table = Table(
    "foo", metadata,
    Column("id", String))
index = Index(
    "foo_idx", table.c.id, postgresql_concurrently=True)
with engine.connect() as conn:
    with conn.execution_options(isolation_level='AUTOCOMMIT'):
        table.create(conn)

See also

Transaction Isolation Level

PostgreSQL Index Reflection

The PostgreSQL database creates a UNIQUE INDEX implicitly whenever the UNIQUE CONSTRAINT construct is used. When inspecting a table using Inspector, the Inspector.get_indexes() and the Inspector.get_unique_constraints() will report on these two constructs distinctly; in the case of the index, the key duplicates_constraint will be present in the index entry if it is detected as mirroring a constraint. When performing reflection using Table(..., autoload_with=engine), the UNIQUE INDEX is not returned in Table.indexes when it is detected as mirroring a UniqueConstraint in the Table.constraints collection .

Changed in version 1.0.0: - Table reflection now includes UniqueConstraint objects present in the Table.constraints collection; the PostgreSQL backend will no longer include a “mirrored” Index construct in Table.indexes if it is detected as corresponding to a unique constraint.

Special Reflection Options

The Inspector used for the PostgreSQL backend is an instance of PGInspector, which offers additional methods:

from sqlalchemy import create_engine, inspect
engine = create_engine("postgresql+psycopg2://localhost/test")
insp = inspect(engine)  # will be a PGInspector
print(insp.get_enums())

class sqlalchemy.dialects.postgresql.base.PGInspector

Members

get_domains(), get_enums(), get_foreign_table_names(), get_table_oid(), has_type()

Class signature

class sqlalchemy.dialects.postgresql.base.PGInspector (sqlalchemy.engine.reflection.Inspector)

• method sqlalchemy.dialects.postgresql.base.PGInspector.get_domains(schema: Optional[str] = None) → List[ReflectedDomain]

  Return a list of DOMAIN objects.

  Each member is a dictionary containing these fields:

  • name - name of the domain

  • schema - the schema name for the domain.

  • visible - boolean, whether or not this domain is visible in the default search path.

  • type - the type defined by this domain.

  • nullable - Indicates if this domain can be NULL.

  • default - The default value of the domain or None if the domain has no default.

  • constraints - A list of dict wit the constraint defined by this domain. Each element constaints two keys: name of the constraint and check with the constraint text.

  • Parameters:

    schema – schema name. If None, the default schema (typically ‘public’) is used. May also be set to '*' to indicate load domains for all schemas.

  New in version 2.0.

• method sqlalchemy.dialects.postgresql.base.PGInspector.get_enums(schema: Optional[str] = None) → List[ReflectedEnum]

  Return a list of ENUM objects.

  Each member is a dictionary containing these fields:

  • name - name of the enum

  • schema - the schema name for the enum.

  • visible - boolean, whether or not this enum is visible in the default search path.

  • labels - a list of string labels that apply to the enum.

  • Parameters:

    schema – schema name. If None, the default schema (typically ‘public’) is used. May also be set to '*' to indicate load enums for all schemas.

  New in version 1.0.0.

• method sqlalchemy.dialects.postgresql.base.PGInspector.get_foreign_table_names(schema: Optional[str] = None) → List[str]

  Return a list of FOREIGN TABLE names.

  Behavior is similar to that of Inspector.get_table_names(), except that the list is limited to those tables that report a relkind value of f.

  New in version 1.0.0.

• method sqlalchemy.dialects.postgresql.base.PGInspector.get_table_oid(table_name: str, schema: Optional[str] = None) → int

  Return the OID for the given table name.

  • Parameters:

    • table_name – string name of the table. For special quoting, use quoted_name.

    • schema – string schema name; if omitted, uses the default schema of the database connection. For special quoting, use quoted_name.

• method sqlalchemy.dialects.postgresql.base.PGInspector.has_type(type_name: str, schema: Optional[str] = None, **kw: Any) → bool

  Return if the database has the specified type in the provided schema.

  • Parameters:

    • type_name – the type to check.

    • schema – schema name. If None, the default schema (typically ‘public’) is used. May also be set to '*' to check in all schemas.

New in version 2.0.

PostgreSQL Table Options

Several options for CREATE TABLE are supported directly by the PostgreSQL dialect in conjunction with the Table construct:

• TABLESPACE:

  Table("some_table", metadata, ..., postgresql_tablespace='some_tablespace')

  The above option is also available on the Index construct.

• ON COMMIT:

  Table("some_table", metadata, ..., postgresql_on_commit='PRESERVE ROWS')

• WITH OIDS:

  Table("some_table", metadata, ..., postgresql_with_oids=True)

• WITHOUT OIDS:

  Table("some_table", metadata, ..., postgresql_with_oids=False)

• INHERITS:

  Table("some_table", metadata, ..., postgresql_inherits="some_supertable")
  Table("some_table", metadata, ..., postgresql_inherits=("t1", "t2", ...))
  .. versionadded:: 1.0.0

• PARTITION BY:

  Table("some_table", metadata, ...,
        postgresql_partition_by='LIST (part_column)')
  .. versionadded:: 1.2.6

See also

PostgreSQL CREATE TABLE options - in the PostgreSQL documentation.

PostgreSQL Constraint Options

The following option(s) are supported by the PostgreSQL dialect in conjunction with selected constraint constructs:

• NOT VALID: This option applies towards CHECK and FOREIGN KEY constraints when the constraint is being added to an existing table via ALTER TABLE, and has the effect that existing rows are not scanned during the ALTER operation against the constraint being added.

  When using a SQL migration tool such as Alembic that renders ALTER TABLE constructs, the postgresql_not_valid argument may be specified as an additional keyword argument within the operation that creates the constraint, as in the following Alembic example:

  def update():
      op.create_foreign_key(
          "fk_user_address",
          "address",
          "user",
          ["user_id"],
          ["id"],
          postgresql_not_valid=True
      )

  The keyword is ultimately accepted directly by the CheckConstraint, ForeignKeyConstraint and ForeignKey constructs; when using a tool like Alembic, dialect-specific keyword arguments are passed through to these constructs from the migration operation directives:

  CheckConstraint("some_field IS NOT NULL", postgresql_not_valid=True)
  ForeignKeyConstraint(["some_id"], ["some_table.some_id"], postgresql_not_valid=True)

  New in version 1.4.32.

  See also

  PostgreSQL ALTER TABLE options - in the PostgreSQL documentation.

Table values, Table and Column valued functions, Row and Tuple objects

PostgreSQL makes great use of modern SQL forms such as table-valued functions, tables and rows as values. These constructs are commonly used as part of PostgreSQL’s support for complex datatypes such as JSON, ARRAY, and other datatypes. SQLAlchemy’s SQL expression language has native support for most table-valued and row-valued forms.

Table-Valued Functions

Many PostgreSQL built-in functions are intended to be used in the FROM clause of a SELECT statement, and are capable of returning table rows or sets of table rows. A large portion of PostgreSQL’s JSON functions for example such as json_array_elements(), json_object_keys(), json_each_text(), json_each(), json_to_record(), json_populate_recordset() use such forms. These classes of SQL function calling forms in SQLAlchemy are available using the FunctionElement.table_valued() method in conjunction with Function objects generated from the func namespace.

Examples from PostgreSQL’s reference documentation follow below:

• json_each():

  >>> from sqlalchemy import select, func
  >>> stmt = select(func.json_each('{"a":"foo", "b":"bar"}').table_valued("key", "value"))
  >>> print(stmt)
  SELECT anon_1.key, anon_1.value
  FROM json_each(:json_each_1) AS anon_1

• json_populate_record():

  >>> from sqlalchemy import select, func, literal_column
  >>> stmt = select(
  ...     func.json_populate_record(
  ...         literal_column("null::myrowtype"),
  ...         '{"a":1,"b":2}'
  ...     ).table_valued("a", "b", name="x")
  ... )
  >>> print(stmt)
  SELECT x.a, x.b
  FROM json_populate_record(null::myrowtype, :json_populate_record_1) AS x

• json_to_record() - this form uses a PostgreSQL specific form of derived columns in the alias, where we may make use of column() elements with types to produce them. The FunctionElement.table_valued() method produces a TableValuedAlias construct, and the method TableValuedAlias.render_derived() method sets up the derived columns specification:

  >>> from sqlalchemy import select, func, column, Integer, Text
  >>> stmt = select(
  ...     func.json_to_record('{"a":1,"b":[1,2,3],"c":"bar"}').table_valued(
  ...         column("a", Integer), column("b", Text), column("d", Text),
  ...     ).render_derived(name="x", with_types=True)
  ... )
  >>> print(stmt)
  SELECT x.a, x.b, x.d
  FROM json_to_record(:json_to_record_1) AS x(a INTEGER, b TEXT, d TEXT)

• WITH ORDINALITY - part of the SQL standard, WITH ORDINALITY adds an ordinal counter to the output of a function and is accepted by a limited set of PostgreSQL functions including unnest() and generate_series(). The FunctionElement.table_valued() method accepts a keyword parameter with_ordinality for this purpose, which accepts the string name that will be applied to the “ordinality” column:

  >>> from sqlalchemy import select, func
  >>> stmt = select(
  ...     func.generate_series(4, 1, -1).
  ...     table_valued("value", with_ordinality="ordinality").
  ...     render_derived()
  ... )
  >>> print(stmt)
  SELECT anon_1.value, anon_1.ordinality
  FROM generate_series(:generate_series_1, :generate_series_2, :generate_series_3)
  WITH ORDINALITY AS anon_1(value, ordinality)

New in version 1.4.0b2.

See also

Table-Valued Functions - in the SQLAlchemy Unified Tutorial

Column Valued Functions

Similar to the table valued function, a column valued function is present in the FROM clause, but delivers itself to the columns clause as a single scalar value. PostgreSQL functions such as json_array_elements(), unnest() and generate_series() may use this form. Column valued functions are available using the FunctionElement.column_valued() method of FunctionElement:

• json_array_elements():

  >>> from sqlalchemy import select, func
  >>> stmt = select(func.json_array_elements('["one", "two"]').column_valued("x"))
  >>> print(stmt)
  SELECT x
  FROM json_array_elements(:json_array_elements_1) AS x

• unnest() - in order to generate a PostgreSQL ARRAY literal, the array() construct may be used:

  >>> from sqlalchemy.dialects.postgresql import array
  >>> from sqlalchemy import select, func
  >>> stmt = select(func.unnest(array([1, 2])).column_valued())
  >>> print(stmt)
  SELECT anon_1
  FROM unnest(ARRAY[%(param_1)s, %(param_2)s]) AS anon_1

  The function can of course be used against an existing table-bound column that’s of type ARRAY:

  >>> from sqlalchemy import table, column, ARRAY, Integer
  >>> from sqlalchemy import select, func
  >>> t = table("t", column('value', ARRAY(Integer)))
  >>> stmt = select(func.unnest(t.c.value).column_valued("unnested_value"))
  >>> print(stmt)
  SELECT unnested_value
  FROM unnest(t.value) AS unnested_value

See also

Column Valued Functions - Table Valued Function as a Scalar Column - in the SQLAlchemy Unified Tutorial

Row Types

Built-in support for rendering a ROW may be approximated using func.ROW with the sqlalchemy.func namespace, or by using the tuple_() construct:

>>> from sqlalchemy import table, column, func, tuple_
>>> t = table("t", column("id"), column("fk"))
>>> stmt = t.select().where(
...     tuple_(t.c.id, t.c.fk) > (1,2)
... ).where(
...     func.ROW(t.c.id, t.c.fk) < func.ROW(3, 7)
... )
>>> print(stmt)
SELECT t.id, t.fk
FROM t
WHERE (t.id, t.fk) > (:param_1, :param_2) AND ROW(t.id, t.fk) < ROW(:ROW_1, :ROW_2)

See also

PostgreSQL Row Constructors

PostgreSQL Row Constructor Comparison

Table Types passed to Functions

PostgreSQL supports passing a table as an argument to a function, which it refers towards as a “record” type. SQLAlchemy FromClause objects such as Table support this special form using the FromClause.table_valued() method, which is comparable to the FunctionElement.table_valued() method except that the collection of columns is already established by that of the FromClause itself:

>>> from sqlalchemy import table, column, func, select
>>> a = table( "a", column("id"), column("x"), column("y"))
>>> stmt = select(func.row_to_json(a.table_valued()))
>>> print(stmt)
SELECT row_to_json(a) AS row_to_json_1
FROM a

New in version 1.4.0b2.

ARRAY Types

The PostgreSQL dialect supports arrays, both as multidimensional column types as well as array literals:

• ARRAY - ARRAY datatype

• array - array literal

• array_agg() - ARRAY_AGG SQL function

• aggregate_order_by - helper for PG’s ORDER BY aggregate function syntax.

JSON Types

The PostgreSQL dialect supports both JSON and JSONB datatypes, including psycopg2’s native support and support for all of PostgreSQL’s special operators:

• JSON

• JSONB

• JSONPATH

HSTORE Type

The PostgreSQL HSTORE type as well as hstore literals are supported:

• HSTORE - HSTORE datatype

• hstore - hstore literal

ENUM Types

PostgreSQL has an independently creatable TYPE structure which is used to implement an enumerated type. This approach introduces significant complexity on the SQLAlchemy side in terms of when this type should be CREATED and DROPPED. The type object is also an independently reflectable entity. The following sections should be consulted:

• ENUM - DDL and typing support for ENUM.

• PGInspector.get_enums() - retrieve a listing of current ENUM types

• ENUM.create() , ENUM.drop() - individual CREATE and DROP commands for ENUM.

Using ENUM with ARRAY

The combination of ENUM and ARRAY is not directly supported by backend DBAPIs at this time. Prior to SQLAlchemy 1.3.17, a special workaround was needed in order to allow this combination to work, described below.

Changed in version 1.3.17: The combination of ENUM and ARRAY is now directly handled by SQLAlchemy’s implementation without any workarounds needed.

from sqlalchemy import TypeDecorator
from sqlalchemy.dialects.postgresql import ARRAY
class ArrayOfEnum(TypeDecorator):
    impl = ARRAY
    def bind_expression(self, bindvalue):
        return sa.cast(bindvalue, self)
    def result_processor(self, dialect, coltype):
        super_rp = super(ArrayOfEnum, self).result_processor(dialect, coltype)
        def handle_raw_string(value):
            inner = re.match(r"^{(.*)}$", value).group(1)
            return inner.split(",") if inner else []
        def process(value):
            if value is None:
                return None
            return super_rp(handle_raw_string(value))
        return process

E.g.:

Table(
    "mydata",
    metadata,
    Column("id", Integer, primary_key=True),
    Column("data", ArrayOfEnum(ENUM("a", "b", "c", name="myenum"))),
)

This type is not included as a built-in type as it would be incompatible with a DBAPI that suddenly decides to support ARRAY of ENUM directly in a new version.

Using JSON/JSONB with ARRAY

Similar to using ENUM, prior to SQLAlchemy 1.3.17, for an ARRAY of JSON/JSONB we need to render the appropriate CAST. Current psycopg2 drivers accommodate the result set correctly without any special steps.

Changed in version 1.3.17: The combination of JSON/JSONB and ARRAY is now directly handled by SQLAlchemy’s implementation without any workarounds needed.

class CastingArray(ARRAY):
    def bind_expression(self, bindvalue):
        return sa.cast(bindvalue, self)

E.g.:

Table(
    "mydata",
    metadata,
    Column("id", Integer, primary_key=True),
    Column("data", CastingArray(JSONB)),
)

Range and Multirange Types

PostgreSQL range and multirange types are supported for the psycopg2, psycopg, and asyncpg dialects.

Data values being passed to the database may be passed as string values or by using the Range data object.

New in version 2.0: Added the backend-agnostic Range object used to indicate ranges. The psycopg2-specific range classes are no longer exposed and are only used internally by that particular dialect.

E.g. an example of a fully typed model using the TSRANGE datatype:

from datetime import datetime
from sqlalchemy.dialects.postgresql import Range
from sqlalchemy.dialects.postgresql import TSRANGE
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column
class Base(DeclarativeBase):
    pass
class RoomBooking(Base):
    __tablename__ = "room_booking"
    id: Mapped[int] = mapped_column(primary_key=True)
    room: Mapped[str]
    during: Mapped[Range[datetime]] = mapped_column(TSRANGE)

To represent data for the during column above, the Range type is a simple dataclass that will represent the bounds of the range. Below illustrates an INSERT of a row into the above room_booking table:

from sqlalchemy import create_engine
from sqlalchemy.orm import Session
engine = create_engine("postgresql+psycopg://scott:tiger@pg14/dbname")
Base.metadata.create_all(engine)
with Session(engine) as session:
    booking = RoomBooking(
        room="101", during=Range(datetime(2013, 3, 23), datetime(2013, 3, 25))
    )
    session.add(booking)
    session.commit()

Selecting from any range column will also return Range objects as indicated:

from sqlalchemy import select
with Session(engine) as session:
    for row in session.execute(select(RoomBooking.during)):
        print(row)

The available range datatypes are as follows:

• INT4RANGE

• INT8RANGE

• NUMRANGE

• DATERANGE

• TSRANGE

• TSTZRANGE

class sqlalchemy.dialects.postgresql.Range

Represent a PostgreSQL range.

E.g.:

r = Range(10, 50, bounds="()")

The calling style is similar to that of psycopg and psycopg2, in part to allow easier migration from previous SQLAlchemy versions that used these objects directly.

• Parameters:

  • lower – Lower bound value, or None

  • upper – Upper bound value, or None

  • bounds – keyword-only, optional string value that is one of "()", "[)", "(]", "[]". Defaults to "[)".

  • empty – keyword-only, optional bool indicating this is an “empty” range

New in version 2.0.

Members

__eq__(), adjacent_to(), contained_by(), contains(), difference(), is_empty, isempty, lower, lower_inc, lower_inf, not_extend_left_of(), not_extend_right_of(), overlaps(), strictly_left_of(), strictly_right_of(), union(), upper, upper_inc, upper_inf

Class signature

class sqlalchemy.dialects.postgresql.Range (typing.Generic)

• method sqlalchemy.dialects.postgresql.Range.__eq__(other: Any) → bool

  Compare this range to the other taking into account bounds inclusivity, returning True if they are equal.

• method sqlalchemy.dialects.postgresql.Range.adjacent_to(other: Range[_T]) → bool

  Determine whether this range is adjacent to the other.

• method sqlalchemy.dialects.postgresql.Range.contained_by(other: Range[_T]) → bool

  Determine whether this range is a contained by other.

• method sqlalchemy.dialects.postgresql.Range.contains(value: Union[_T, Range[_T]]) → bool

  Determine whether this range contains value.

• method sqlalchemy.dialects.postgresql.Range.difference(other: Range[_T]) → Range[_T]

  Compute the difference between this range and the other.

  This raises a ValueError exception if the two ranges are “disjunct”, that is neither adjacent nor overlapping.

• attribute sqlalchemy.dialects.postgresql.Range.is_empty

  A synonym for the ‘empty’ attribute.

• attribute sqlalchemy.dialects.postgresql.Range.isempty

  A synonym for the ‘empty’ attribute.

• attribute sqlalchemy.dialects.postgresql.Range.lower: Optional[_T]

  the lower bound

• attribute sqlalchemy.dialects.postgresql.Range.lower_inc

  Return True if the lower bound is inclusive.

• attribute sqlalchemy.dialects.postgresql.Range.lower_inf

  Return True if this range is non-empty and lower bound is infinite.

• method sqlalchemy.dialects.postgresql.Range.not_extend_left_of(other: Range[_T]) → bool

  Determine whether this does not extend to the left of other.

• method sqlalchemy.dialects.postgresql.Range.not_extend_right_of(other: Range[_T]) → bool

  Determine whether this does not extend to the right of other.

• method sqlalchemy.dialects.postgresql.Range.overlaps(other: Range[_T]) → bool

  Determine whether this range overlaps with other.

• method sqlalchemy.dialects.postgresql.Range.strictly_left_of(other: Range[_T]) → bool

  Determine whether this range is completely to the left of other.

• method sqlalchemy.dialects.postgresql.Range.strictly_right_of(other: Range[_T]) → bool

  Determine whether this range is completely to the right of other.

• method sqlalchemy.dialects.postgresql.Range.union(other: Range[_T]) → Range[_T]

  Compute the union of this range with the other.

  This raises a ValueError exception if the two ranges are “disjunct”, that is neither adjacent nor overlapping.

• attribute sqlalchemy.dialects.postgresql.Range.upper: Optional[_T]

  the upper bound

• attribute sqlalchemy.dialects.postgresql.Range.upper_inc

  Return True if the upper bound is inclusive.

• attribute sqlalchemy.dialects.postgresql.Range.upper_inf

  Return True if this range is non-empty and the upper bound is infinite.

Multiranges

Multiranges are supported by PostgreSQL 14 and above. SQLAlchemy’s multirange datatypes deal in lists of Range types.

New in version 2.0: Added support for MULTIRANGE datatypes. In contrast to the psycopg multirange feature, SQLAlchemy’s adaptation represents a multirange datatype as a list of Range objects.

The example below illustrates use of the TSMULTIRANGE datatype:

from datetime import datetime
from typing import List
from sqlalchemy.dialects.postgresql import Range
from sqlalchemy.dialects.postgresql import TSMULTIRANGE
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column
class Base(DeclarativeBase):
    pass
class EventCalendar(Base):
    __tablename__ = "event_calendar"
    id: Mapped[int] = mapped_column(primary_key=True)
    event_name: Mapped[str]
    in_session_periods: Mapped[List[Range[datetime]]] = mapped_column(TSMULTIRANGE)

Illustrating insertion and selecting of a record:

from sqlalchemy import create_engine
from sqlalchemy import select
from sqlalchemy.orm import Session
engine = create_engine("postgresql+psycopg://scott:tiger@pg14/test")
Base.metadata.create_all(engine)
with Session(engine) as session:
    calendar = EventCalendar(
        event_name="SQLAlchemy Tutorial Sessions",
        in_session_periods=[
            Range(datetime(2013, 3, 23), datetime(2013, 3, 25)),
            Range(datetime(2013, 4, 12), datetime(2013, 4, 15)),
            Range(datetime(2013, 5, 9), datetime(2013, 5, 12)),
        ],
    )
    session.add(calendar)
    session.commit()
    for multirange in session.scalars(select(EventCalendar.in_session_periods)):
        for range_ in multirange:
            print(f"Start: {range_.lower}  End: {range_.upper}")

Note

In the above example, the list of Range types as handled by the ORM will not automatically detect in-place changes to a particular list value; to update list values with the ORM, either re-assign a new list to the attribute, or use the MutableList type modifier. See the section Mutation Tracking for background.

The available multirange datatypes are as follows:

• INT4MULTIRANGE

• INT8MULTIRANGE

• NUMMULTIRANGE

• DATEMULTIRANGE

• TSMULTIRANGE

• TSTZMULTIRANGE

PostgreSQL Data Types

As with all SQLAlchemy dialects, all UPPERCASE types that are known to be valid with PostgreSQL are importable from the top level dialect, whether they originate from sqlalchemy.types or from the local dialect:

from sqlalchemy.dialects.postgresql import (
    ARRAY,
    BIGINT,
    BIT,
    BOOLEAN,
    BYTEA,
    CHAR,
    CIDR,
    DATE,
    DOUBLE_PRECISION,
    ENUM,
    FLOAT,
    HSTORE,
    INET,
    INTEGER,
    INTERVAL,
    JSON,
    JSONB,
    MACADDR,
    MACADDR8,
    MONEY,
    NUMERIC,
    OID,
    REAL,
    SMALLINT,
    TEXT,
    TIME,
    TIMESTAMP,
    UUID,
    VARCHAR,
    INT4RANGE,
    INT8RANGE,
    NUMRANGE,
    DATERANGE,
    TSRANGE,
    TSTZRANGE,
    REGCONFIG,
    REGCLASS,
    TSQUERY,
    TSVECTOR,
)

Types which are specific to PostgreSQL, or have PostgreSQL-specific construction arguments, are as follows:

class sqlalchemy.dialects.postgresql.AbstractRange

Base for PostgreSQL RANGE types.

See also

PostgreSQL range functions

Members

__ne__(), adjacent_to(), contained_by(), contains(), difference(), not_extend_left_of(), not_extend_right_of(), overlaps(), strictly_left_of(), strictly_right_of(), union()

Class signature

class sqlalchemy.dialects.postgresql.AbstractRange (sqlalchemy.types.TypeEngine)

• class comparator_factory

  Define comparison operations for range types.

  Class signature

  class sqlalchemy.dialects.postgresql.AbstractRange.comparator_factory (sqlalchemy.types.Comparator)

  • method sqlalchemy.dialects.postgresql.AbstractRange.comparator_factory.__ne__(other: Any) → ColumnElement[bool]

    Boolean expression. Returns true if two ranges are not equal

  • method sqlalchemy.dialects.postgresql.AbstractRange.comparator_factory.adjacent_to(other: Any) → ColumnElement[bool]

    Boolean expression. Returns true if the range in the column is adjacent to the range in the operand.

  • method sqlalchemy.dialects.postgresql.AbstractRange.comparator_factory.contained_by(other: Any) → ColumnElement[bool]

    Boolean expression. Returns true if the column is contained within the right hand operand.

  • method sqlalchemy.dialects.postgresql.AbstractRange.comparator_factory.contains(other: Any, **kw: Any) → ColumnElement[bool]

    Boolean expression. Returns true if the right hand operand, which can be an element or a range, is contained within the column.

    kwargs may be ignored by this operator but are required for API conformance.

  • method sqlalchemy.dialects.postgresql.AbstractRange.comparator_factory.difference(other: Any) → ColumnElement[bool]

    Range expression. Returns the union of the two ranges. Will raise an exception if the resulting range is not contiguous.

  • method sqlalchemy.dialects.postgresql.AbstractRange.comparator_factory.not_extend_left_of(other: Any) → ColumnElement[bool]

    Boolean expression. Returns true if the range in the column does not extend left of the range in the operand.

  • method sqlalchemy.dialects.postgresql.AbstractRange.comparator_factory.not_extend_right_of(other: Any) → ColumnElement[bool]

    Boolean expression. Returns true if the range in the column does not extend right of the range in the operand.

  • method sqlalchemy.dialects.postgresql.AbstractRange.comparator_factory.overlaps(other: Any) → ColumnElement[bool]

    Boolean expression. Returns true if the column overlaps (has points in common with) the right hand operand.

  • method sqlalchemy.dialects.postgresql.AbstractRange.comparator_factory.strictly_left_of(other: Any) → ColumnElement[bool]

    Boolean expression. Returns true if the column is strictly left of the right hand operand.

  • method sqlalchemy.dialects.postgresql.AbstractRange.comparator_factory.strictly_right_of(other: Any) → ColumnElement[bool]

    Boolean expression. Returns true if the column is strictly right of the right hand operand.

  • method sqlalchemy.dialects.postgresql.AbstractRange.comparator_factory.union(other: Any) → ColumnElement[bool]

    Range expression. Returns the union of the two ranges. Will raise an exception if the resulting range is not contiguous.

class sqlalchemy.dialects.postgresql.AbstractMultiRange

base for PostgreSQL MULTIRANGE types

Class signature

class sqlalchemy.dialects.postgresql.AbstractMultiRange (sqlalchemy.dialects.postgresql.ranges.AbstractRange)

class sqlalchemy.dialects.postgresql.ARRAY

PostgreSQL ARRAY type.

Changed in version 1.1: The ARRAY type is now a subclass of the core ARRAY type.

The ARRAY type is constructed in the same way as the core ARRAY type; a member type is required, and a number of dimensions is recommended if the type is to be used for more than one dimension:

from sqlalchemy.dialects import postgresql
mytable = Table("mytable", metadata,
        Column("data", postgresql.ARRAY(Integer, dimensions=2))
    )

The ARRAY type provides all operations defined on the core ARRAY type, including support for “dimensions”, indexed access, and simple matching such as Comparator.any() and Comparator.all(). ARRAY class also provides PostgreSQL-specific methods for containment operations, including Comparator.contains() Comparator.contained_by(), and Comparator.overlap(), e.g.:

mytable.c.data.contains([1, 2])

The ARRAY type may not be supported on all PostgreSQL DBAPIs; it is currently known to work on psycopg2 only.

Additionally, the ARRAY type does not work directly in conjunction with the ENUM type. For a workaround, see the special type at Using ENUM with ARRAY.

Detecting Changes in ARRAY columns when using the ORM

The ARRAY type, when used with the SQLAlchemy ORM, does not detect in-place mutations to the array. In order to detect these, the sqlalchemy.ext.mutable extension must be used, using the MutableList class:

from sqlalchemy.dialects.postgresql import ARRAY
from sqlalchemy.ext.mutable import MutableList
class SomeOrmClass(Base):
    # ...
    data = Column(MutableList.as_mutable(ARRAY(Integer)))

This extension will allow “in-place” changes such to the array such as .append() to produce events which will be detected by the unit of work. Note that changes to elements inside the array, including subarrays that are mutated in place, are not detected.

Alternatively, assigning a new array value to an ORM element that replaces the old one will always trigger a change event.

See also

ARRAY - base array type

array - produces a literal array value.

Members

contained_by(), contains(), overlap(), __init__()

Class signature

class sqlalchemy.dialects.postgresql.ARRAY (sqlalchemy.types.ARRAY)

• class Comparator

  Define comparison operations for ARRAY.

  Note that these operations are in addition to those provided by the base Comparator class, including Comparator.any() and Comparator.all().

  Class signature

  class sqlalchemy.dialects.postgresql.ARRAY.Comparator (sqlalchemy.types.Comparator)

  • method sqlalchemy.dialects.postgresql.ARRAY.Comparator.contained_by(other)

    Boolean expression. Test if elements are a proper subset of the elements of the argument array expression.

  • method sqlalchemy.dialects.postgresql.ARRAY.Comparator.contains(other, **kwargs)

    Boolean expression. Test if elements are a superset of the elements of the argument array expression.

    kwargs may be ignored by this operator but are required for API conformance.

  • method sqlalchemy.dialects.postgresql.ARRAY.Comparator.overlap(other)

    Boolean expression. Test if array has elements in common with an argument array expression.

• method sqlalchemy.dialects.postgresql.ARRAY.__init__(item_type: _TypeEngineArgument[Any], as_tuple: bool = False, dimensions: Optional[int] = None, zero_indexes: bool = False)

  Construct an ARRAY.

  E.g.:

  Column('myarray', ARRAY(Integer))

  Arguments are:

  • Parameters:

    • item_type – The data type of items of this array. Note that dimensionality is irrelevant here, so multi-dimensional arrays like INTEGER[][], are constructed as ARRAY(Integer), not as ARRAY(ARRAY(Integer)) or such.

    • as_tuple=False – Specify whether return results should be converted to tuples from lists. DBAPIs such as psycopg2 return lists by default. When tuples are returned, the results are hashable.

    • dimensions – if non-None, the ARRAY will assume a fixed number of dimensions. This will cause the DDL emitted for this ARRAY to include the exact number of bracket clauses [], and will also optimize the performance of the type overall. Note that PG arrays are always implicitly “non-dimensioned”, meaning they can store any number of dimensions no matter how they were declared.

    • zero_indexes=False –

      when True, index values will be converted between Python zero-based and PostgreSQL one-based indexes, e.g. a value of one will be added to all index values before passing to the database.

      New in version 0.9.5.

class sqlalchemy.dialects.postgresql.BIT

Class signature

class sqlalchemy.dialects.postgresql.BIT (sqlalchemy.types.TypeEngine)

class sqlalchemy.dialects.postgresql.BYTEA

Members

__init__()

Class signature

class sqlalchemy.dialects.postgresql.BYTEA (sqlalchemy.types.LargeBinary)

• method sqlalchemy.dialects.postgresql.BYTEA.__init__(length: Optional[int] = None)

  inherited from the sqlalchemy.types.LargeBinary.__init__ method of LargeBinary

  Construct a LargeBinary type.

  • Parameters:

    length – optional, a length for the column for use in DDL statements, for those binary types that accept a length, such as the MySQL BLOB type.

class sqlalchemy.dialects.postgresql.CIDR

Class signature

class sqlalchemy.dialects.postgresql.CIDR (sqlalchemy.types.TypeEngine)

class sqlalchemy.dialects.postgresql.DOMAIN

Represent the DOMAIN PostgreSQL type.

A domain is essentially a data type with optional constraints that restrict the allowed set of values. E.g.:

PositiveInt = Domain(
    "pos_int", Integer, check="VALUE > 0", not_null=True
)
UsPostalCode = Domain(
    "us_postal_code",
    Text,
    check="VALUE ~ '^\d{5}$' OR VALUE ~ '^\d{5}-\d{4}$'"
)

See the PostgreSQL documentation for additional details

New in version 2.0.

Members

__init__(), create(), drop()

Class signature

class sqlalchemy.dialects.postgresql.DOMAIN (sqlalchemy.dialects.postgresql.named_types.NamedType, sqlalchemy.types.SchemaType)

• method sqlalchemy.dialects.postgresql.DOMAIN.__init__(name: str, data_type: _TypeEngineArgument[Any], *, collation: Optional[str] = None, default: Optional[Union[str, elements.TextClause]] = None, constraint_name: Optional[str] = None, not_null: Optional[bool] = None, check: Optional[str] = None, create_type: bool = True, **kw: Any)

  Construct a DOMAIN.

  • Parameters:

    • name – the name of the domain

    • data_type – The underlying data type of the domain. This can include array specifiers.

    • collation – An optional collation for the domain. If no collation is specified, the underlying data type’s default collation is used. The underlying type must be collatable if collation is specified.

    • default – The DEFAULT clause specifies a default value for columns of the domain data type. The default should be a string or a text() value. If no default value is specified, then the default value is the null value.

    • constraint_name – An optional name for a constraint. If not specified, the backend generates a name.

    • not_null – Values of this domain are prevented from being null. By default domain are allowed to be null. If not specified no nullability clause will be emitted.

    • check – CHECK clause specify integrity constraint or test which values of the domain must satisfy. A constraint must be an expression producing a Boolean result that can use the key word VALUE to refer to the value being tested. Differently from PostgreSQL, only a single check clause is currently allowed in SQLAlchemy.

    • schema – optional schema name

    • metadata – optional MetaData object which this DOMAIN will be directly associated

    • create_type – Defaults to True. Indicates that CREATE TYPE should be emitted, after optionally checking for the presence of the type, when the parent table is being created; and additionally that DROP TYPE is called when the table is dropped.

• method sqlalchemy.dialects.postgresql.DOMAIN.create(bind, checkfirst=True, **kw)

  inherited from the NamedType.create() method of NamedType

  Emit CREATE DDL for this type.

  • Parameters:

    • bind – a connectable Engine, Connection, or similar object to emit SQL.

    • checkfirst – if True, a query against the PG catalog will be first performed to see if the type does not exist already before creating.

• method sqlalchemy.dialects.postgresql.DOMAIN.drop(bind, checkfirst=True, **kw)

  inherited from the NamedType.drop() method of NamedType

  Emit DROP DDL for this type.

  • Parameters:

    • bind – a connectable Engine, Connection, or similar object to emit SQL.

    • checkfirst – if True, a query against the PG catalog will be first performed to see if the type actually exists before dropping.

class sqlalchemy.dialects.postgresql.DOUBLE_PRECISION

The SQL DOUBLE PRECISION type.

New in version 2.0.

See also

Double - documentation for the base type.

Class signature

class sqlalchemy.dialects.postgresql.DOUBLE_PRECISION (sqlalchemy.types.Double)

• method sqlalchemy.dialects.postgresql.DOUBLE_PRECISION.__init__(precision: Optional[int] = None, asdecimal: bool = False, decimal_return_scale: Optional[int] = None)

  inherited from the sqlalchemy.types.Float.__init__ method of Float

  Construct a Float.

  • Parameters:

    • precision –

      the numeric precision for use in DDL CREATE TABLE. Backends should attempt to ensure this precision indicates a number of digits for the generic Float datatype.

      Note

      For the Oracle backend, the Float.precision parameter is not accepted when rendering DDL, as Oracle does not support float precision specified as a number of decimal places. Instead, use the Oracle-specific FLOAT datatype and specify the FLOAT.binary_precision parameter. This is new in version 2.0 of SQLAlchemy.

      To create a database agnostic Float that separately specifies binary precision for Oracle, use TypeEngine.with_variant() as follows:

      from sqlalchemy import Column
      from sqlalchemy import Float
      from sqlalchemy.dialects import oracle
      Column(
          "float_data",
          Float(5).with_variant(oracle.FLOAT(binary_precision=16), "oracle")
      )

    • asdecimal – the same flag as that of Numeric, but defaults to False. Note that setting this flag to True results in floating point conversion.

    • decimal_return_scale –

      Default scale to use when converting from floats to Python decimals. Floating point values will typically be much longer due to decimal inaccuracy, and most floating point database types don’t have a notion of “scale”, so by default the float type looks for the first ten decimal places when converting. Specifying this value will override that length. Note that the MySQL float types, which do include “scale”, will use “scale” as the default for decimal_return_scale, if not otherwise specified.

      New in version 0.9.0.

class sqlalchemy.dialects.postgresql.ENUM

PostgreSQL ENUM type.

This is a subclass of Enum which includes support for PG’s CREATE TYPE and DROP TYPE.

When the builtin type Enum is used and the Enum.native_enum flag is left at its default of True, the PostgreSQL backend will use a ENUM type as the implementation, so the special create/drop rules will be used.

The create/drop behavior of ENUM is necessarily intricate, due to the awkward relationship the ENUM type has in relationship to the parent table, in that it may be “owned” by just a single table, or may be shared among many tables.

When using Enum or ENUM in an “inline” fashion, the CREATE TYPE and DROP TYPE is emitted corresponding to when the Table.create() and Table.drop() methods are called:

table = Table('sometable', metadata,
    Column('some_enum', ENUM('a', 'b', 'c', name='myenum'))
)
table.create(engine)  # will emit CREATE ENUM and CREATE TABLE
table.drop(engine)  # will emit DROP TABLE and DROP ENUM

To use a common enumerated type between multiple tables, the best practice is to declare the Enum or ENUM independently, and associate it with the MetaData object itself:

my_enum = ENUM('a', 'b', 'c', name='myenum', metadata=metadata)
t1 = Table('sometable_one', metadata,
    Column('some_enum', myenum)
)
t2 = Table('sometable_two', metadata,
    Column('some_enum', myenum)
)

When this pattern is used, care must still be taken at the level of individual table creates. Emitting CREATE TABLE without also specifying checkfirst=True will still cause issues:

t1.create(engine) # will fail: no such type 'myenum'

If we specify checkfirst=True, the individual table-level create operation will check for the ENUM and create if not exists:

# will check if enum exists, and emit CREATE TYPE if not
t1.create(engine, checkfirst=True)

When using a metadata-level ENUM type, the type will always be created and dropped if either the metadata-wide create/drop is called:

metadata.create_all(engine)  # will emit CREATE TYPE
metadata.drop_all(engine)  # will emit DROP TYPE

The type can also be created and dropped directly:

my_enum.create(engine)
my_enum.drop(engine)

Changed in version 1.0.0: The PostgreSQL ENUM type now behaves more strictly with regards to CREATE/DROP. A metadata-level ENUM type will only be created and dropped at the metadata level, not the table level, with the exception of table.create(checkfirst=True). The table.drop() call will now emit a DROP TYPE for a table-level enumerated type.

Members

__init__(), create(), drop()

Class signature

class sqlalchemy.dialects.postgresql.ENUM (sqlalchemy.dialects.postgresql.named_types.NamedType, sqlalchemy.types.NativeForEmulated, sqlalchemy.types.Enum)

• method sqlalchemy.dialects.postgresql.ENUM.__init__(*enums, name: str, create_type: bool = True, **kw)

  Construct an ENUM.

  Arguments are the same as that of Enum, but also including the following parameters.

  • Parameters:

    create_type – Defaults to True. Indicates that CREATE TYPE should be emitted, after optionally checking for the presence of the type, when the parent table is being created; and additionally that DROP TYPE is called when the table is dropped. When False, no check will be performed and no CREATE TYPE or DROP TYPE is emitted, unless ENUM.create() or ENUM.drop() are called directly. Setting to False is helpful when invoking a creation scheme to a SQL file without access to the actual database - the ENUM.create() and ENUM.drop() methods can be used to emit SQL to a target bind.

• method sqlalchemy.dialects.postgresql.ENUM.create(bind=None, checkfirst=True)

  Emit CREATE TYPE for this ENUM.

  If the underlying dialect does not support PostgreSQL CREATE TYPE, no action is taken.

  • Parameters:

    • bind – a connectable Engine, Connection, or similar object to emit SQL.

    • checkfirst – if True, a query against the PG catalog will be first performed to see if the type does not exist already before creating.

• method sqlalchemy.dialects.postgresql.ENUM.drop(bind=None, checkfirst=True)

  Emit DROP TYPE for this ENUM.

  If the underlying dialect does not support PostgreSQL DROP TYPE, no action is taken.

  • Parameters:

    • bind – a connectable Engine, Connection, or similar object to emit SQL.

    • checkfirst – if True, a query against the PG catalog will be first performed to see if the type actually exists before dropping.

class sqlalchemy.dialects.postgresql.HSTORE

Represent the PostgreSQL HSTORE type.

The HSTORE type stores dictionaries containing strings, e.g.:

data_table = Table('data_table', metadata,
    Column('id', Integer, primary_key=True),
    Column('data', HSTORE)
)
with engine.connect() as conn:
    conn.execute(
        data_table.insert(),
        data = {"key1": "value1", "key2": "value2"}
    )