From 57dcfdacaa75ddac3a9246512f3deac4aef10cf0 Mon Sep 17 00:00:00 2001 From: David Lord Date: Sun, 17 May 2026 16:09:16 -0400 Subject: [PATCH] document postgresql_nulls_not_distinct ### Description https://github.com/sqlalchemy/sqlalchemy/issues/8240 and https://github.com/sqlalchemy/sqlalchemy/pull/9834 added support for `NULLS NOT DISTINCT` to the PostgreSQL dialect, but didn't add it to the docs (only the change log). This adds a section to the "Constraint Options" section of the PostgreSQL dialect docs. ### Checklist This pull request is: - [x] A documentation / typographical / small typing error fix - Good to go, no issue or tests are needed - [ ] A short code fix - please include the issue number, and create an issue if none exists, which must include a complete example of the issue. one line code fixes without an issue and demonstration will not be accepted. - Please include: `Fixes: #` in the commit message - please include tests. one line code fixes without tests will not be accepted. - [ ] A new feature implementation - please include the issue number, and create an issue if none exists, which must include a complete example of how the feature would look. - Please include: `Fixes: #` in the commit message - please include tests. **Have a nice day!** Closes: #13279 Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/13279 Pull-request-sha: cdc858cd88fbf86661662147210f9587117aa593 Change-Id: I3f2c8fe346d3235fa8ba12c4d9ab712ddb840230 --- doc/build/changelog/changelog_20.rst | 2 +- lib/sqlalchemy/dialects/postgresql/base.py | 23 ++++++++++++++++++++++ 2 files changed, 24 insertions(+), 1 deletion(-) diff --git a/doc/build/changelog/changelog_20.rst b/doc/build/changelog/changelog_20.rst index e6fa9ddcd2..14a3889c54 100644 --- a/doc/build/changelog/changelog_20.rst +++ b/doc/build/changelog/changelog_20.rst @@ -4118,7 +4118,7 @@ :tags: usecase, postgresql :tickets: 8240 - Added support for PostgreSQL 10 ``NULLS NOT DISTINCT`` feature of + Added support for PostgreSQL 15 ``NULLS NOT DISTINCT`` feature of unique indexes and unique constraint using the dialect option ``postgresql_nulls_not_distinct``. Updated the reflection logic to also correctly take this option diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index 1702bc70c9..77b6a9d261 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -1528,6 +1528,29 @@ database to use its default of ``VIRTUAL``; on PostgreSQL 17 and earlier, +``NULLS NOT DISTINCT`` +^^^^^^^^^^^^^^^^^^^^^^ + +By default, two ``null`` values are not considered equal for unique constraints +and indexes. Therefore, seemingly duplicate rows may be stored if one of the +values in the constraint is ``null``. This default behavior is implementation +defined, so other SQL dialects may behave differently than PostgreSQL. + +The ``NULLS NOT DISTINCT`` clause can be used to change this behavior, treating +null values as equal and preventing unintended duplicate rows. The opposite +``NULLS DISTINCT`` clause can also be used to make PostgreSQL's default behavior +explict. + +The ``postgresql_nulls_not_distinct`` parameter can be set to ``True`` to +add the ``NULLS NOT DISTINCT`` clause, or ``False`` to add ``NULLS DISTINCT``. +Not setting it, or passing ``None``, will not add a clause and keep the default +behavior. + +This feature requires PostgreSQL 15 or later. + +.. versionadded:: 2.0.16 + + .. _postgresql_table_valued_overview: Table values, Table and Column valued functions, Row and Tuple objects -- 2.47.3