Release: 1.1.0b3 | Release Date: July 26, 2016

SQLAlchemy 1.1 Documentation

PostgreSQL

Support for the PostgreSQL database.

DBAPI Support

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

Sequences/SERIAL

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'), 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.

To force the usage of RETURNING by default off, specify the flag implicit_returning=False to create_engine().

Transaction Isolation Level

All Postgresql dialects support setting of transaction isolation level both via a dialect-specific parameter create_engine.isolation_level accepted by create_engine(), as well as the Connection.execution_options.isolation_level argument as passed to Connection.execution_options(). When using a non-psycopg2 dialect, this feature works by issuing the command SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL <level> for each new connection. For the special AUTOCOMMIT isolation level, DBAPI-specific techniques are used.

To set isolation level using create_engine():

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

To set using per-connection execution options:

connection = engine.connect()
connection = connection.execution_options(
    isolation_level="READ COMMITTED"
)

Valid values for isolation_level include:

  • READ COMMITTED
  • READ UNCOMMITTED
  • REPEATABLE READ
  • SERIALIZABLE
  • AUTOCOMMIT - on psycopg2 / pg8000 only

Remote-Schema Table Introspection and Postgresql search_path

The Postgresql dialect can reflect tables from any schema. The Table.schema argument, or alternatively the MetaData.reflect.schema argument determines which schema will be searched for the table or tables. The reflected Table objects will in all cases retain this .schema attribute as was specified. However, 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
>>> engine = create_engine("postgresql://scott:tiger@localhost/test")
>>> with engine.connect() as conn:
...     conn.execute("SET search_path TO test_schema, public")
...     meta = MetaData()
...     referring = Table('referring', meta,
...                       autoload=True, autoload_with=conn)
...
<sqlalchemy.engine.result.ResultProxy object at 0x101612ed0>

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

>>> meta.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("SET search_path TO test_schema, public")
...     meta = MetaData()
...     referring = Table('referring', meta, autoload=True,
...                       autoload_with=conn,
...                       postgresql_ignore_search_path=True)
...
<sqlalchemy.engine.result.ResultProxy object at 0x1016126d0>

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

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

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.

New in version 0.9.2: Added the postgresql_ignore_search_path dialect-level option accepted by Table and MetaData.reflect().

See also

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 postgresql.dml.insert() function, which provides the generative methods on_conflict_do_update() and 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']
)

conn.execute(do_nothing_stmt)

do_update_stmt = insert_stmt.on_conflict_do_update(
    constraint='pk_my_table',
    set_=dict(data='updated value')
)

conn.execute(do_update_stmt)

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')
    )
    
    do_update_stmt = insert_stmt.on_conflict_do_update(
        index_elements=[my_table.c.id],
        set_=dict(data='updated value')
    )
  • 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:

    from sqlalchemy.dialects.postgresql import insert
    
    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)
        )
    conn.execute(stmt)
  • 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')
    )
    
    do_update_stmt = insert_stmt.on_conflict_do_update(
        constraint='my_table_pk',
        set_=dict(data='updated value')
    )
  • 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')
    )

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:

from sqlalchemy.dialects.postgresql import insert

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')
    )
conn.execute(do_update_stmt)

Warning

The Insert.on_conflict_do_update() method does not take into account Python-side default UPDATE values or generation functions, e.g. 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.

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

from sqlalchemy.dialects.postgresql import insert

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)
    )
conn.execute(do_update_stmt)

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:

from sqlalchemy.dialects.postgresql import insert

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)
    )
conn.execute(on_update_stmt)

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

from sqlalchemy.dialects.postgresql import insert

stmt = insert(my_table).values(id='some_id', data='inserted value')
stmt = stmt.on_conflict_do_nothing(index_elements=['id'])
conn.execute(stmt)

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:

from sqlalchemy.dialects.postgresql import insert

stmt = insert(my_table).values(id='some_id', data='inserted value')
stmt = stmt.on_conflict_do_nothing()
conn.execute(stmt)

New in version 1.1: Added support for Postgresql ON CONFLICT clauses

See also

INSERT .. ON CONFLICT - 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.

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=tbl.c.value > 10)

Operator Classes

PostgreSQL allows the specification of an operator class for each column of an index (see http://www.postgresql.org/docs/8.3/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'
                        })

New in version 0.7.2: postgresql_ops keyword argument to Index construct.

Note that the keys in the postgresql_ops dictionary 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.

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 http://www.postgresql.org/docs/8.3/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).

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=True), 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(conn)

Bases: sqlalchemy.engine.reflection.Inspector

get_enums(schema=None)

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.

get_foreign_table_names(schema=None)

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 tha report a relkind value of f.

New in version 1.0.0.

get_table_oid(table_name, schema=None)

Return the OID for the given table name.

get_view_names(schema=None, include=('plain', 'materialized'))

Return all view names in schema.

Parameters:
  • schema – Optional, retrieve names from a non-default schema. For special quoting, use quoted_name.
  • include

    specify which types of views to return. Passed as a string value (for a single type) or a tuple (for any number of types). Defaults to ('plain', 'materialized').

    New in version 1.1.

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", ...))

New in version 1.0.0.

ARRAY Types

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

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:

HSTORE Type

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

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:

Using ENUM with ARRAY

The combination of ENUM and ARRAY is not directly supported by backend DBAPIs at this time. In order to send and receive an ARRAY of ENUM, use the following workaround type:

class ArrayOfEnum(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.

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, NUMERIC, OID, REAL, SMALLINT, TEXT, \
    TIME, TIMESTAMP, UUID, VARCHAR, INT4RANGE, INT8RANGE, NUMRANGE, \
    DATERANGE, TSRANGE, TSTZRANGE, TSVECTOR

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

class sqlalchemy.dialects.postgresql.aggregate_order_by(target, order_by)

Bases: sqlalchemy.sql.expression.ColumnElement

Represent a Postgresql aggregate order by expression.

E.g.:

from sqlalchemy.dialects.postgresql import aggregate_order_by
expr = func.array_agg(aggregate_order_by(table.c.a, table.c.b.desc()))
stmt = select([expr])

would represent the expression:

SELECT array_agg(a ORDER BY b DESC) FROM table;

Similarly:

expr = func.string_agg(
    table.c.a,
    aggregate_order_by(literal_column("','"), table.c.a)
)
stmt = select([expr])

Would represent:

SELECT string_agg(a, ',' ORDER BY a) FROM table;

New in version 1.1.

See also

array_agg

class sqlalchemy.dialects.postgresql.array(clauses, **kw)

Bases: sqlalchemy.sql.expression.Tuple

A Postgresql ARRAY literal.

This is used to produce ARRAY literals in SQL expressions, e.g.:

from sqlalchemy.dialects.postgresql import array
from sqlalchemy.dialects import postgresql
from sqlalchemy import select, func

stmt = select([
                array([1,2]) + array([3,4,5])
            ])

print stmt.compile(dialect=postgresql.dialect())

Produces the SQL:

SELECT ARRAY[%(param_1)s, %(param_2)s] ||
    ARRAY[%(param_3)s, %(param_4)s, %(param_5)s]) AS anon_1

An instance of array will always have the datatype ARRAY. The “inner” type of the array is inferred from the values present, unless the type_ keyword argument is passed:

array(['foo', 'bar'], type_=CHAR)

New in version 0.8: Added the array literal type.

See also:

postgresql.ARRAY

class sqlalchemy.dialects.postgresql.ARRAY(item_type, as_tuple=False, dimensions=None, zero_indexes=False)

Bases: sqlalchemy.sql.expression.SchemaEventTarget, sqlalchemy.types.ARRAY

Postgresql ARRAY type.

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

The postgresql.ARRAY type is constructed in the same way as the core types.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 postgresql.ARRAY type provides all operations defined on the core types.ARRAY type, including support for “dimensions”, indexed access, and simple matching such as types.ARRAY.Comparator.any() and types.ARRAY.Comparator.all(). postgresql.ARRAY class also provides PostgreSQL-specific methods for containment operations, including postgresql.ARRAY.Comparator.contains() postgresql.ARRAY.Comparator.contained_by(), and postgresql.ARRAY.Comparator.overlap(), e.g.:

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

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

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

See also

types.ARRAY - base array type

postgresql.array - produces a literal array value.

class Comparator(expr)

Bases: sqlalchemy.types.Comparator

Define comparison operations for ARRAY.

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

contained_by(other)

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

contains(other, **kwargs)

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

overlap(other)

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

ARRAY.__init__(item_type, as_tuple=False, dimensions=None, zero_indexes=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.

sqlalchemy.dialects.postgresql.array_agg(*arg, **kw)

Postgresql-specific form of array_agg, ensures return type is postgresql.ARRAY and not the plain types.ARRAY.

New in version 1.1.

sqlalchemy.dialects.postgresql.Any(other, arrexpr, operator=<built-in function eq>)

A synonym for the ARRAY.Comparator.any() method.

This method is legacy and is here for backwards-compatiblity.

sqlalchemy.dialects.postgresql.All(other, arrexpr, operator=<built-in function eq>)

A synonym for the ARRAY.Comparator.all() method.

This method is legacy and is here for backwards-compatiblity.

class sqlalchemy.dialects.postgresql.BIT(length=None, varying=False)

Bases: sqlalchemy.types.TypeEngine

class sqlalchemy.dialects.postgresql.BYTEA(length=None)

Bases: sqlalchemy.types.LargeBinary

__init__(length=None)
inherited from the __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

Bases: sqlalchemy.types.TypeEngine

class sqlalchemy.dialects.postgresql.DOUBLE_PRECISION(precision=None, asdecimal=False, decimal_return_scale=None, **kwargs)

Bases: sqlalchemy.types.Float

__init__(precision=None, asdecimal=False, decimal_return_scale=None, **kwargs)
inherited from the __init__() method of Float

Construct a Float.

Parameters:
  • precision – the numeric precision for use in DDL CREATE TABLE.
  • 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. Specfiying 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.

  • **kwargs – deprecated. Additional arguments here are ignored by the default Float type. For database specific floats that support additional arguments, see that dialect’s documentation for details, such as sqlalchemy.dialects.mysql.FLOAT.
class sqlalchemy.dialects.postgresql.ENUM(*enums, **kw)

Bases: sqlalchemy.types.Enum

Postgresql ENUM type.

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

When the builtin type types.Enum is used and the Enum.native_enum flag is left at its default of True, the Postgresql backend will use a postgresql.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 types.Enum or postgresql.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 types.Enum or postgresql.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 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.

__init__(*enums, **kw)

Construct an ENUM.

Arguments are the same as that of types.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 create() or 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 create() and drop() methods can be used to emit SQL to a target bind.

New in version 0.7.4.

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.
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(text_type=None)

Bases: sqlalchemy.types.Indexable, sqlalchemy.types.Concatenable, sqlalchemy.types.TypeEngine

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"}
    )

HSTORE provides for a wide range of operations, including:

  • Index operations:

    data_table.c.data['some key'] == 'some value'
  • Containment operations:

    data_table.c.data.has_key('some key')
    
    data_table.c.data.has_all(['one', 'two', 'three'])
  • Concatenation:

    data_table.c.data + {"k1": "v1"}

For a full list of special methods see HSTORE.comparator_factory.

For usage with the SQLAlchemy ORM, it may be desirable to combine the usage of HSTORE with MutableDict dictionary now part of the sqlalchemy.ext.mutable extension. This extension will allow “in-place” changes to the dictionary, e.g. addition of new keys or replacement/removal of existing keys to/from the current dictionary, to produce events which will be detected by the unit of work:

from sqlalchemy.ext.mutable import MutableDict

class MyClass(Base):
    __tablename__ = 'data_table'

    id = Column(Integer, primary_key=True)
    data = Column(MutableDict.as_mutable(HSTORE))

my_object = session.query(MyClass).one()

# in-place mutation, requires Mutable extension
# in order for the ORM to detect
my_object.data['some_key'] = 'some value'

session.commit()

When the sqlalchemy.ext.mutable extension is not used, the ORM will not be alerted to any changes to the contents of an existing dictionary, unless that dictionary value is re-assigned to the HSTORE-attribute itself, thus generating a change event.

New in version 0.8.

See also

hstore - render the Postgresql hstore() function.

class Comparator(expr)

Bases: sqlalchemy.types.Comparator, sqlalchemy.types.Comparator

Define comparison operations for HSTORE.

array()

Text array expression. Returns array of alternating keys and values.

contained_by(other)

Boolean expression. Test if keys are a proper subset of the keys of the argument jsonb expression.

contains(other, **kwargs)

Boolean expression. Test if keys (or array) are a superset of/contained the keys of the argument jsonb expression.

defined(key)

Boolean expression. Test for presence of a non-NULL value for the key. Note that the key may be a SQLA expression.

delete(key)

HStore expression. Returns the contents of this hstore with the given key deleted. Note that the key may be a SQLA expression.

has_all(other)

Boolean expression. Test for presence of all keys in jsonb

has_any(other)

Boolean expression. Test for presence of any key in jsonb

has_key(other)

Boolean expression. Test for presence of a key. Note that the key may be a SQLA expression.

keys()

Text array expression. Returns array of keys.

matrix()

Text array expression. Returns array of [key, value] pairs.

slice(array)

HStore expression. Returns a subset of an hstore defined by array of keys.

vals()

Text array expression. Returns array of values.

HSTORE.__init__(text_type=None)

Construct a new HSTORE.

Parameters:text_type

the type that should be used for indexed values. Defaults to types.Text.

New in version 1.1.0.

HSTORE.comparator_factory

alias of Comparator

class sqlalchemy.dialects.postgresql.hstore(*args, **kwargs)

Bases: sqlalchemy.sql.functions.GenericFunction

Construct an hstore value within a SQL expression using the Postgresql hstore() function.

The hstore function accepts one or two arguments as described in the Postgresql documentation.

E.g.:

from sqlalchemy.dialects.postgresql import array, hstore

select([hstore('key1', 'value1')])

select([
        hstore(
            array(['key1', 'key2', 'key3']),
            array(['value1', 'value2', 'value3'])
        )
    ])

New in version 0.8.

See also

HSTORE - the Postgresql HSTORE datatype.

type

alias of HSTORE

class sqlalchemy.dialects.postgresql.INET

Bases: sqlalchemy.types.TypeEngine

__init__
inherited from the __init__ attribute of object

x.__init__(...) initializes x; see help(type(x)) for signature

class sqlalchemy.dialects.postgresql.INTERVAL(precision=None)

Bases: sqlalchemy.types.TypeEngine

Postgresql INTERVAL type.

The INTERVAL type may not be supported on all DBAPIs. It is known to work on psycopg2 and not pg8000 or zxjdbc.

class sqlalchemy.dialects.postgresql.JSON(none_as_null=False, astext_type=None)

Bases: sqlalchemy.types.JSON

Represent the Postgresql JSON type.

This type is a specialization of the Core-level types.JSON type. Be sure to read the documentation for types.JSON for important tips regarding treatment of NULL values and ORM use.

Changed in version 1.1: postgresql.JSON is now a Postgresql- specific specialization of the new types.JSON type.

The operators provided by the Postgresql version of JSON include:

  • Index operations (the -> operator):

    data_table.c.data['some key']
    
    data_table.c.data[5]
  • Index operations returning text (the ->> operator):

    data_table.c.data['some key'].astext == 'some value'
  • Index operations with CAST (equivalent to CAST(col ->> ['some key'] AS <type>)):

    data_table.c.data['some key'].astext.cast(Integer) == 5
  • Path index operations (the #> operator):

    data_table.c.data[('key_1', 'key_2', 5, ..., 'key_n')]
  • Path index operations returning text (the #>> operator):

    data_table.c.data[('key_1', 'key_2', 5, ..., 'key_n')].astext == 'some value'

Changed in version 1.1: The ColumnElement.cast() operator on JSON objects now requires that the JSON.Comparator.astext modifier be called explicitly, if the cast works only from a textual string.

Index operations return an expression object whose type defaults to JSON by default, so that further JSON-oriented instructions may be called upon the result type.

Custom serializers and deserializers are specified at the dialect level, that is using create_engine(). The reason for this is that when using psycopg2, the DBAPI only allows serializers at the per-cursor or per-connection level. E.g.:

engine = create_engine("postgresql://scott:tiger@localhost/test",
                        json_serializer=my_serialize_fn,
                        json_deserializer=my_deserialize_fn
                )

When using the psycopg2 dialect, the json_deserializer is registered against the database using psycopg2.extras.register_default_json.

See also

types.JSON - Core level JSON type

JSONB

class Comparator(expr)

Bases: sqlalchemy.types.Comparator

Define comparison operations for JSON.

astext

On an indexed expression, use the “astext” (e.g. “->>”) conversion when rendered in SQL.

E.g.:

select([data_table.c.data['some key'].astext])
JSON.__init__(none_as_null=False, astext_type=None)

Construct a JSON type.

Parameters:
  • none_as_null

    if True, persist the value None as a SQL NULL value, not the JSON encoding of null. Note that when this flag is False, the null() construct can still be used to persist a NULL value:

    from sqlalchemy import null
    conn.execute(table.insert(), data=null())

    Changed in version 0.9.8: - Added none_as_null, and null() is now supported in order to persist a NULL value.

    See also

    JSON.NULL

  • astext_type

    the type to use for the JSON.Comparator.astext accessor on indexed attributes. Defaults to types.Text.

    New in version 1.1.

JSON.comparator_factory

alias of Comparator

class sqlalchemy.dialects.postgresql.JSONB(none_as_null=False, astext_type=None)

Bases: sqlalchemy.dialects.postgresql.json.JSON

Represent the Postgresql JSONB type.

The JSONB type stores arbitrary JSONB format data, e.g.:

data_table = Table('data_table', metadata,
    Column('id', Integer, primary_key=True),
    Column('data', JSONB)
)

with engine.connect() as conn:
    conn.execute(
        data_table.insert(),
        data = {"key1": "value1", "key2": "value2"}
    )

The JSONB type includes all operations provided by JSON, including the same behaviors for indexing operations. It also adds additional operators specific to JSONB, including JSONB.Comparator.has_key(), JSONB.Comparator.has_all(), JSONB.Comparator.has_any(), JSONB.Comparator.contains(), and JSONB.Comparator.contained_by().

Like the JSON type, the JSONB type does not detect in-place changes when used with the ORM, unless the sqlalchemy.ext.mutable extension is used.

Custom serializers and deserializers are shared with the JSON class, using the json_serializer and json_deserializer keyword arguments. These must be specified at the dialect level using create_engine(). When using psycopg2, the serializers are associated with the jsonb type using psycopg2.extras.register_default_jsonb on a per-connection basis, in the same way that psycopg2.extras.register_default_json is used to register these handlers with the json type.

New in version 0.9.7.

See also

JSON

class Comparator(expr)

Bases: sqlalchemy.dialects.postgresql.json.Comparator

Define comparison operations for JSON.

contained_by(other)

Boolean expression. Test if keys are a proper subset of the keys of the argument jsonb expression.

contains(other, **kwargs)

Boolean expression. Test if keys (or array) are a superset of/contained the keys of the argument jsonb expression.

has_all(other)

Boolean expression. Test for presence of all keys in jsonb

has_any(other)

Boolean expression. Test for presence of any key in jsonb

has_key(other)

Boolean expression. Test for presence of a key. Note that the key may be a SQLA expression.

JSONB.comparator_factory

alias of Comparator

class sqlalchemy.dialects.postgresql.MACADDR

Bases: sqlalchemy.types.TypeEngine

__init__
inherited from the __init__ attribute of object

x.__init__(...) initializes x; see help(type(x)) for signature

class sqlalchemy.dialects.postgresql.OID

Bases: sqlalchemy.types.TypeEngine

Provide the Postgresql OID type.

New in version 0.9.5.

__init__
inherited from the __init__ attribute of object

x.__init__(...) initializes x; see help(type(x)) for signature

class sqlalchemy.dialects.postgresql.REAL(precision=None, asdecimal=False, decimal_return_scale=None, **kwargs)

Bases: sqlalchemy.types.Float

The SQL REAL type.

__init__(precision=None, asdecimal=False, decimal_return_scale=None, **kwargs)
inherited from the __init__() method of Float

Construct a Float.

Parameters:
  • precision – the numeric precision for use in DDL CREATE TABLE.
  • 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. Specfiying 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.

  • **kwargs – deprecated. Additional arguments here are ignored by the default Float type. For database specific floats that support additional arguments, see that dialect’s documentation for details, such as sqlalchemy.dialects.mysql.FLOAT.
class sqlalchemy.dialects.postgresql.TSVECTOR

Bases: sqlalchemy.types.TypeEngine

The postgresql.TSVECTOR type implements the Postgresql text search type TSVECTOR.

It can be used to do full text queries on natural language documents.

New in version 0.9.0.

See also

Full Text Search

__init__
inherited from the __init__ attribute of object

x.__init__(...) initializes x; see help(type(x)) for signature

class sqlalchemy.dialects.postgresql.UUID(as_uuid=False)

Bases: sqlalchemy.types.TypeEngine

Postgresql UUID type.

Represents the UUID column type, interpreting data either as natively returned by the DBAPI or as Python uuid objects.

The UUID type may not be supported on all DBAPIs. It is known to work on psycopg2 and not pg8000.

__init__(as_uuid=False)

Construct a UUID type.

Parameters:as_uuid=False – if True, values will be interpreted as Python uuid objects, converting to/from string via the DBAPI.

Range Types

The new range column types found in PostgreSQL 9.2 onwards are catered for by the following types:

class sqlalchemy.dialects.postgresql.INT4RANGE

Bases: sqlalchemy.dialects.postgresql.ranges.RangeOperators, sqlalchemy.types.TypeEngine

Represent the Postgresql INT4RANGE type.

New in version 0.8.2.

class sqlalchemy.dialects.postgresql.INT8RANGE

Bases: sqlalchemy.dialects.postgresql.ranges.RangeOperators, sqlalchemy.types.TypeEngine

Represent the Postgresql INT8RANGE type.

New in version 0.8.2.

class sqlalchemy.dialects.postgresql.NUMRANGE

Bases: sqlalchemy.dialects.postgresql.ranges.RangeOperators, sqlalchemy.types.TypeEngine

Represent the Postgresql NUMRANGE type.

New in version 0.8.2.

class sqlalchemy.dialects.postgresql.DATERANGE

Bases: sqlalchemy.dialects.postgresql.ranges.RangeOperators, sqlalchemy.types.TypeEngine

Represent the Postgresql DATERANGE type.

New in version 0.8.2.

class sqlalchemy.dialects.postgresql.TSRANGE

Bases: sqlalchemy.dialects.postgresql.ranges.RangeOperators, sqlalchemy.types.TypeEngine

Represent the Postgresql TSRANGE type.

New in version 0.8.2.

class sqlalchemy.dialects.postgresql.TSTZRANGE

Bases: sqlalchemy.dialects.postgresql.ranges.RangeOperators, sqlalchemy.types.TypeEngine

Represent the Postgresql TSTZRANGE type.

New in version 0.8.2.

The types above get most of their functionality from the following mixin:

class sqlalchemy.dialects.postgresql.ranges.RangeOperators

This mixin provides functionality for the Range Operators listed in Table 9-44 of the postgres documentation for Range Functions and Operators. It is used by all the range types provided in the postgres dialect and can likely be used for any range types you create yourself.

No extra support is provided for the Range Functions listed in Table 9-45 of the postgres documentation. For these, the normal func() object should be used.

New in version 0.8.2: Support for Postgresql RANGE operations.

class comparator_factory(expr)

Bases: sqlalchemy.types.Comparator

Define comparison operations for range types.

__ne__(other)

Boolean expression. Returns true if two ranges are not equal

adjacent_to(other)

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

contained_by(other)

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

contains(other, **kw)

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

not_extend_left_of(other)

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

not_extend_right_of(other)

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

overlaps(other)

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

strictly_left_of(other)

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

strictly_right_of(other)

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

Warning

The range type DDL support should work with any Postgres DBAPI driver, however the data types returned may vary. If you are using psycopg2, it’s recommended to upgrade to version 2.5 or later before using these column types.

When instantiating models that use these column types, you should pass whatever data type is expected by the DBAPI driver you’re using for the column type. For psycopg2 these are NumericRange, DateRange, DateTimeRange and DateTimeTZRange or the class you’ve registered with register_range().

For example:

from psycopg2.extras import DateTimeRange
from sqlalchemy.dialects.postgresql import TSRANGE

class RoomBooking(Base):

    __tablename__ = 'room_booking'

    room = Column(Integer(), primary_key=True)
    during = Column(TSRANGE())

booking = RoomBooking(
    room=101,
    during=DateTimeRange(datetime(2013, 3, 23), None)
)

PostgreSQL Constraint Types

SQLAlchemy supports PostgreSQL EXCLUDE constraints via the ExcludeConstraint class:

class sqlalchemy.dialects.postgresql.ExcludeConstraint(*elements, **kw)

Bases: sqlalchemy.schema.ColumnCollectionConstraint

A table-level EXCLUDE constraint.

Defines an EXCLUDE constraint as described in the postgres documentation.

__init__(*elements, **kw)
Parameters:
  • *elements – A sequence of two tuples of the form (column, operator) where column must be a column name or Column object and operator must be a string containing the operator to use.
  • name – Optional, the in-database name of this constraint.
  • deferrable – Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when issuing DDL for this constraint.
  • initially – Optional string. If set, emit INITIALLY <value> when issuing DDL for this constraint.
  • using – Optional string. If set, emit USING <index_method> when issuing DDL for this constraint. Defaults to ‘gist’.
  • where – Optional string. If set, emit WHERE <predicate> when issuing DDL for this constraint.

For example:

from sqlalchemy.dialects.postgresql import ExcludeConstraint, TSRANGE

class RoomBooking(Base):

    __tablename__ = 'room_booking'

    room = Column(Integer(), primary_key=True)
    during = Column(TSRANGE())

    __table_args__ = (
        ExcludeConstraint(('room', '='), ('during', '&&')),
    )

PostgreSQL DML Constructs

sqlalchemy.dialects.postgresql.dml.insert(table, values=None, inline=False, bind=None, prefixes=None, returning=None, return_defaults=False, **dialect_kw)

Construct a new Insert object.

This constructor is mirrored as a public API function; see insert() for a full usage and argument description.

class sqlalchemy.dialects.postgresql.dml.Insert(table, values=None, inline=False, bind=None, prefixes=None, returning=None, return_defaults=False, **dialect_kw)

Bases: sqlalchemy.sql.expression.Insert

Postgresql-specific implementation of INSERT.

Adds methods for PG-specific syntaxes such as ON CONFLICT.

New in version 1.1.

excluded

Provide the excluded namespace for an ON CONFLICT statement

PG’s ON CONFLICT clause allows reference to the row that would be inserted, known as excluded. This attribute provides all columns in this row to be referenaceable.

See also

INSERT...ON CONFLICT (Upsert) - example of how to use Insert.excluded

on_conflict_do_nothing(constraint=None, index_elements=None, index_where=None)

Specifies a DO NOTHING action for ON CONFLICT clause.

The constraint and index_elements arguments are optional, but only one of these can be specified.

Parameters:constraint

The name of a unique or exclusion constraint on the table, or the constraint object itself if it has a .name attribute.

Parameters:index_elements

A sequence consisting of string column names, Column objects, or other column expression objects that will be used to infer a target index.

Parameters:index_where

Additional WHERE criterion that can be used to infer a conditional target index.

New in version 1.1.

on_conflict_do_update(constraint=None, index_elements=None, index_where=None, set_=None, where=None)

Specifies a DO UPDATE SET action for ON CONFLICT clause.

Either the constraint or index_elements argument is required, but only one of these can be specified.

Parameters:constraint

The name of a unique or exclusion constraint on the table, or the constraint object itself if it has a .name attribute.

Parameters:index_elements

A sequence consisting of string column names, Column objects, or other column expression objects that will be used to infer a target index.

Parameters:index_where

Additional WHERE criterion that can be used to infer a conditional target index.

Parameters:set_

Required argument. A dictionary or other mapping object with column names as keys and expressions or literals as values, specifying the SET actions to take.

Warning

This dictionary does not take into account Python-specified 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.

Parameters:where

Optional argument. If present, can be a literal SQL string or an acceptable expression for a WHERE clause that restricts the rows affected by DO UPDATE SET. Rows not meeting the WHERE condition will not be updated (effectively a DO NOTHING for those rows).

New in version 1.1.

psycopg2

Support for the PostgreSQL database via the psycopg2 driver.

DBAPI

Documentation and download information (if applicable) for psycopg2 is available at: http://pypi.python.org/pypi/psycopg2/

Connecting

Connect String:

postgresql+psycopg2://user:password@host:port/dbname[?key=value&key=value...]

psycopg2 Connect Arguments

psycopg2-specific keyword arguments which are accepted by create_engine() are:

  • server_side_cursors: Enable the usage of “server side cursors” for SQL statements which support this feature. What this essentially means from a psycopg2 point of view is that the cursor is created using a name, e.g. connection.cursor('some name'), which has the effect that result rows are not immediately pre-fetched and buffered after statement execution, but are instead left on the server and only retrieved as needed. SQLAlchemy’s ResultProxy uses special row-buffering behavior when this feature is enabled, such that groups of 100 rows at a time are fetched over the wire to reduce conversational overhead. Note that the stream_results=True execution option is a more targeted way of enabling this mode on a per-execution basis.

  • use_native_unicode: Enable the usage of Psycopg2 “native unicode” mode per connection. True by default.

  • isolation_level: This option, available for all PostgreSQL dialects, includes the AUTOCOMMIT isolation level when using the psycopg2 dialect.

  • client_encoding: sets the client encoding in a libpq-agnostic way, using psycopg2’s set_client_encoding() method.

Unix Domain Connections

psycopg2 supports connecting via Unix domain connections. When the host portion of the URL is omitted, SQLAlchemy passes None to psycopg2, which specifies Unix-domain communication rather than TCP/IP communication:

create_engine("postgresql+psycopg2://user:password@/dbname")

By default, the socket file used is to connect to a Unix-domain socket in /tmp, or whatever socket directory was specified when PostgreSQL was built. This value can be overridden by passing a pathname to psycopg2, using host as an additional keyword argument:

create_engine("postgresql+psycopg2://user:password@/dbname?host=/var/lib/postgresql")

See also:

PQconnectdbParams

Per-Statement/Connection Execution Options

The following DBAPI-specific options are respected when used with Connection.execution_options(), Executable.execution_options(), Query.execution_options(), in addition to those not specific to DBAPIs:

  • isolation_level - Set the transaction isolation level for the lifespan of a Connection (can only be set on a connection, not a statement or query). See Psycopg2 Transaction Isolation Level.

  • stream_results - Enable or disable usage of psycopg2 server side cursors - this feature makes use of “named” cursors in combination with special result handling methods so that result rows are not fully buffered. If None or not set, the server_side_cursors option of the Engine is used.

  • max_row_buffer - when using stream_results, an integer value that specifies the maximum number of rows to buffer at a time. This is interpreted by the BufferedRowResultProxy, and if omitted the buffer will grow to ultimately store 1000 rows at a time.

    New in version 1.0.6.

Unicode with Psycopg2

By default, the psycopg2 driver uses the psycopg2.extensions.UNICODE extension, such that the DBAPI receives and returns all strings as Python Unicode objects directly - SQLAlchemy passes these values through without change. Psycopg2 here will encode/decode string values based on the current “client encoding” setting; by default this is the value in the postgresql.conf file, which often defaults to SQL_ASCII. Typically, this can be changed to utf8, as a more useful default:

# postgresql.conf file

# client_encoding = sql_ascii # actually, defaults to database
                             # encoding
client_encoding = utf8

A second way to affect the client encoding is to set it within Psycopg2 locally. SQLAlchemy will call psycopg2’s connection.set_client_encoding() method on all new connections based on the value passed to create_engine() using the client_encoding parameter:

# set_client_encoding() setting;
# works for *all* Postgresql versions
engine = create_engine("postgresql://user:pass@host/dbname",
                       client_encoding='utf8')

This overrides the encoding specified in the Postgresql client configuration. When using the parameter in this way, the psycopg2 driver emits SET client_encoding TO 'utf8' on the connection explicitly, and works in all Postgresql versions.

Note that the client_encoding setting as passed to create_engine() is not the same as the more recently added client_encoding parameter now supported by libpq directly. This is enabled when client_encoding is passed directly to psycopg2.connect(), and from SQLAlchemy is passed using the create_engine.connect_args parameter:

# libpq direct parameter setting;
# only works for Postgresql **9.1 and above**
engine = create_engine("postgresql://user:pass@host/dbname",
                       connect_args={'client_encoding': 'utf8'})

# using the query string is equivalent
engine = create_engine("postgresql://user:pass@host/dbname?client_encoding=utf8")

The above parameter was only added to libpq as of version 9.1 of Postgresql, so using the previous method is better for cross-version support.

Disabling Native Unicode

SQLAlchemy can also be instructed to skip the usage of the psycopg2 UNICODE extension and to instead utilize its own unicode encode/decode services, which are normally reserved only for those DBAPIs that don’t fully support unicode directly. Passing use_native_unicode=False to create_engine() will disable usage of psycopg2.extensions.UNICODE. SQLAlchemy will instead encode data itself into Python bytestrings on the way in and coerce from bytes on the way back, using the value of the create_engine() encoding parameter, which defaults to utf-8. SQLAlchemy’s own unicode encode/decode functionality is steadily becoming obsolete as most DBAPIs now support unicode fully.

Bound Parameter Styles

The default parameter style for the psycopg2 dialect is “pyformat”, where SQL is rendered using %(paramname)s style. This format has the limitation that it does not accommodate the unusual case of parameter names that actually contain percent or parenthesis symbols; as SQLAlchemy in many cases generates bound parameter names based on the name of a column, the presence of these characters in a column name can lead to problems.

There are two solutions to the issue of a schema.Column that contains one of these characters in its name. One is to specify the schema.Column.key for columns that have such names:

measurement = Table('measurement', metadata,
    Column('Size (meters)', Integer, key='size_meters')
)

Above, an INSERT statement such as measurement.insert() will use size_meters as the parameter name, and a SQL expression such as measurement.c.size_meters > 10 will derive the bound parameter name from the size_meters key as well.

Changed in version 1.0.0: - SQL expressions will use Column.key as the source of naming when anonymous bound parameters are created in SQL expressions; previously, this behavior only applied to Table.insert() and Table.update() parameter names.

The other solution is to use a positional format; psycopg2 allows use of the “format” paramstyle, which can be passed to create_engine.paramstyle:

engine = create_engine(
    'postgresql://scott:tiger@localhost:5432/test', paramstyle='format')

With the above engine, instead of a statement like:

INSERT INTO measurement ("Size (meters)") VALUES (%(Size (meters))s)
{'Size (meters)': 1}

we instead see:

INSERT INTO measurement ("Size (meters)") VALUES (%s)
(1, )

Where above, the dictionary style is converted into a tuple with positional style.

Transactions

The psycopg2 dialect fully supports SAVEPOINT and two-phase commit operations.

Psycopg2 Transaction Isolation Level

As discussed in Transaction Isolation Level, all Postgresql dialects support setting of transaction isolation level both via the isolation_level parameter passed to create_engine(), as well as the isolation_level argument used by Connection.execution_options(). When using the psycopg2 dialect, these options make use of psycopg2’s set_isolation_level() connection method, rather than emitting a Postgresql directive; this is because psycopg2’s API-level setting is always emitted at the start of each transaction in any case.

The psycopg2 dialect supports these constants for isolation level:

  • READ COMMITTED
  • READ UNCOMMITTED
  • REPEATABLE READ
  • SERIALIZABLE
  • AUTOCOMMIT

New in version 0.8.2: support for AUTOCOMMIT isolation level when using psycopg2.

NOTICE logging

The psycopg2 dialect will log Postgresql NOTICE messages via the sqlalchemy.dialects.postgresql logger:

import logging
logging.getLogger('sqlalchemy.dialects.postgresql').setLevel(logging.INFO)

HSTORE type

The psycopg2 DBAPI includes an extension to natively handle marshalling of the HSTORE type. The SQLAlchemy psycopg2 dialect will enable this extension by default when psycopg2 version 2.4 or greater is used, and it is detected that the target database has the HSTORE type set up for use. In other words, when the dialect makes the first connection, a sequence like the following is performed:

  1. Request the available HSTORE oids using psycopg2.extras.HstoreAdapter.get_oids(). If this function returns a list of HSTORE identifiers, we then determine that the HSTORE extension is present. This function is skipped if the version of psycopg2 installed is less than version 2.4.
  2. If the use_native_hstore flag is at its default of True, and we’ve detected that HSTORE oids are available, the psycopg2.extensions.register_hstore() extension is invoked for all connections.

The register_hstore() extension has the effect of all Python dictionaries being accepted as parameters regardless of the type of target column in SQL. The dictionaries are converted by this extension into a textual HSTORE expression. If this behavior is not desired, disable the use of the hstore extension by setting use_native_hstore to False as follows:

engine = create_engine("postgresql+psycopg2://scott:tiger@localhost/test",
            use_native_hstore=False)

The HSTORE type is still supported when the psycopg2.extensions.register_hstore() extension is not used. It merely means that the coercion between Python dictionaries and the HSTORE string format, on both the parameter side and the result side, will take place within SQLAlchemy’s own marshalling logic, and not that of psycopg2 which may be more performant.

pg8000

Support for the PostgreSQL database via the pg8000 driver.

DBAPI

Documentation and download information (if applicable) for pg8000 is available at: https://pythonhosted.org/pg8000/

Connecting

Connect String:

postgresql+pg8000://user:password@host:port/dbname[?key=value&key=value...]

Unicode

pg8000 will encode / decode string values between it and the server using the PostgreSQL client_encoding parameter; by default this is the value in the postgresql.conf file, which often defaults to SQL_ASCII. Typically, this can be changed to utf-8, as a more useful default:

#client_encoding = sql_ascii # actually, defaults to database
                             # encoding
client_encoding = utf8

The client_encoding can be overriden for a session by executing the SQL:

SET CLIENT_ENCODING TO ‘utf8’;

SQLAlchemy will execute this SQL on all new connections based on the value passed to create_engine() using the client_encoding parameter:

engine = create_engine(
    "postgresql+pg8000://user:pass@host/dbname", client_encoding='utf8')

pg8000 Transaction Isolation Level

The pg8000 dialect offers the same isolation level settings as that of the psycopg2 dialect:

  • READ COMMITTED
  • READ UNCOMMITTED
  • REPEATABLE READ
  • SERIALIZABLE
  • AUTOCOMMIT

New in version 0.9.5: support for AUTOCOMMIT isolation level when using pg8000.

psycopg2cffi

Support for the PostgreSQL database via the psycopg2cffi driver.

DBAPI

Documentation and download information (if applicable) for psycopg2cffi is available at: http://pypi.python.org/pypi/psycopg2cffi/

Connecting

Connect String:

postgresql+psycopg2cffi://user:password@host:port/dbname[?key=value&key=value...]

psycopg2cffi is an adaptation of psycopg2, using CFFI for the C layer. This makes it suitable for use in e.g. PyPy. Documentation is as per psycopg2.

New in version 1.0.0.

py-postgresql

Support for the PostgreSQL database via the py-postgresql driver.

DBAPI

Documentation and download information (if applicable) for py-postgresql is available at: http://python.projects.pgfoundry.org/

Connecting

Connect String:

postgresql+pypostgresql://user:password@host:port/dbname[?key=value&key=value...]

pygresql

Support for the PostgreSQL database via the pygresql driver.

DBAPI

Documentation and download information (if applicable) for pygresql is available at: http://www.pygresql.org/

Connecting

Connect String:

postgresql+pygresql://user:password@host:port/dbname[?key=value&key=value...]

zxjdbc

Support for the PostgreSQL database via the zxJDBC for Jython driver.

DBAPI

Drivers for this database are available at: http://jdbc.postgresql.org/

Connecting

Connect String:

postgresql+zxjdbc://scott:tiger@localhost/db