``Identity`` implies ``nullable=False``.

Altered the behavior of the :class:`_schema.Identity` construct such that
when applied to a :class:`_schema.Column`, it will automatically imply that
the value of :paramref:`_sql.Column.nullable` should default to ``False``,
in a similar manner as when the :paramref:`_sql.Column.primary_key`
parameter is set to ``True``.   This matches the default behavior of all
supporting databases where ``IDENTITY`` implies ``NOT NULL``.  The
PostgreSQL backend is the only one that supports adding ``NULL`` to an
``IDENTITY`` column, which is here supported by passing a ``True`` value
for the :paramref:`_sql.Column.nullable` parameter at the same time.

Fixes: #5775
Change-Id: I0516d506ff327cff35cda605e8897a27440e0373

1 files changed, 5 insertions, 4 deletions

diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index 7a898cb8a..fd258bc5a 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -68,8 +68,7 @@ The CREATE TABLE for the above :class:`_schema.Table` object would be:
 .. sourcecode:: sql
 
     CREATE TABLE data (
-        id INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 42 CYCLE)
-            NOT NULL,
+        id INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 42 CYCLE),
         data VARCHAR,
         PRIMARY KEY (id)
     )
@@ -107,7 +106,7 @@ The CREATE TABLE for the above :class:`_schema.Table` object would be:
    Will generate on the backing database as::
 
        CREATE TABLE t (
-           id INT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
+           id INT GENERATED BY DEFAULT AS IDENTITY,
            data VARCHAR,
            PRIMARY KEY (id)
        )
@@ -2325,8 +2324,10 @@ class PGDDLCompiler(compiler.DDLCompiler):
         if column.identity is not None:
             colspec += " " + self.process(column.identity)
 
-        if not column.nullable:
+        if not column.nullable and not column.identity:
             colspec += " NOT NULL"
+        elif column.nullable and column.identity:
+            colspec += " NULL"
         return colspec
 
     def visit_check_constraint(self, constraint):