diff options
Diffstat (limited to 'lib/sqlalchemy/dialects/postgresql/base.py')
| -rw-r--r-- | lib/sqlalchemy/dialects/postgresql/base.py | 82 |
1 files changed, 60 insertions, 22 deletions
diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index c56cccd8d..53c54ab65 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -43,40 +43,75 @@ case. To force the usage of RETURNING by default off, specify the flag ``implicit_returning=False`` to :func:`_sa.create_engine`. -PostgreSQL 10 IDENTITY columns -^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ +PostgreSQL 10 and above IDENTITY columns +^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ -PostgreSQL 10 has a new IDENTITY feature that supersedes the use of SERIAL. -Built-in support for rendering of IDENTITY is not available yet, however the -following compilation hook may be used to replace occurrences of SERIAL with -IDENTITY:: +PostgreSQL 10 and above have a new IDENTITY feature that supersedes the use +of SERIAL. The :class:`_schema.Identity` construct in a +:class:`_schema.Column` can be used to control its behavior:: - from sqlalchemy.schema import CreateColumn - from sqlalchemy.ext.compiler import compiles + from sqlalchemy import Table, Column, MetaData, Integer, Computed + metadata = MetaData() - @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), + data = Table( + "data", + metadata, + Column( + 'id', Integer, Identity(start=42, cycle=True), primary_key=True + ), Column('data', String) ) -Will generate on the backing database as:: +The CREATE TABLE for the above :class:`_schema.Table` object would be: + +.. sourcecode:: sql - CREATE TABLE t ( - id INT GENERATED BY DEFAULT AS IDENTITY NOT NULL, + CREATE TABLE data ( + id INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 42 CYCLE) + NOT NULL, data VARCHAR, PRIMARY KEY (id) ) +.. versionchanged:: 1.4 Added :class:`_schema.Identity` construct + in a :class:`_schema.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 NOT NULL, + data VARCHAR, + PRIMARY KEY (id) + ) + .. _postgresql_isolation_level: Transaction Isolation Level @@ -1996,6 +2031,7 @@ class PGDDLCompiler(compiler.DDLCompiler): self.dialect.supports_smallserial or not isinstance(impl_type, sqltypes.SmallInteger) ) + and column.identity is None and ( column.default is None or ( @@ -2022,6 +2058,8 @@ class PGDDLCompiler(compiler.DDLCompiler): if column.computed is not None: colspec += " " + self.process(column.computed) + if column.identity is not None: + colspec += " " + self.process(column.identity) if not column.nullable: colspec += " NOT NULL" |