Support for ``VIRTUAL`` computed columns on PostgreSQL 18 and later has
been added. The default behavior when :paramref:`.Computed.persisted` is
not specified has been changed to align with PostgreSQL 18's default of
``VIRTUAL``. When :paramref:`.Computed.persisted` is not specified, no
keyword is rendered on PostgreSQL 18 and later; on older versions a
warning is emitted and ``STORED`` is used as the default. To explicitly
request ``STORED`` behavior on all PostgreSQL versions, specify
``persisted=True``.
Fixes: #12866
Change-Id: Ic2ebdbe79e230a88370cf2b3503d2d1815f72a39
Support for storage parameters in ``CREATE TABLE`` using the ``WITH``
clause has been added. The ``postgresql_with`` dialect option of
:class:`_schema.Table` accepts a mapping of key/value options.
The PostgreSQL dialect now support reflection of table options, including
the storage parameters, table access method and table spaces. These options
are automatically reflected when autoloading a table, and are also
available via the :meth:`_engine.Inspector.get_table_options` and
:meth:`_engine.Inspector.get_multi_table_optionsmethod` methods.
Fixes: #10909Closes: #12584Closes: #12684
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/12584
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/12684
Pull-request-sha: a660459de9
Change-Id: I33e3d8b8eb0c02530933cb124e3d375ca4af7db2
Some improvements to the reflection of PostgreSQL to avoid
unnecessary queries when not needed.
Fixes: #12908
Change-Id: Ic3da50ee43670f26d3159f5ec9a235a9a1963b8a
Added support for reflection of collation in types for PostgreSQL.
The ``collation`` will be set only if different from the default
one for the type.
References #6511Closes: #12510
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/12510
Pull-request-sha: c0a390314e
Change-Id: I269a194d526a0689a4b38f10456d28539c73cffb
Named types such as :class:`_postgresql.ENUM` and
:class:`_postgresql.DOMAIN` (as well as the dialect-agnostic
:class:`_types.Enum` version) are now more strongly associated with the
:class:`_schema.MetaData` at the top of the table hierarchy and are
de-associated with any particular :class:`_schema.Table` they may be a part
of. This better represents how PostgreSQL named types exist independently
of any particular table, and that they may be used across many tables
simultaneously. The change impacts the behavior of the "default schema"
for a named type, as well as the CREATE/DROP behavior in relationship to
the :class:`.MetaData` and :class:`.Table` construct. The change also
includes a new :class:`.CheckFirst` enumeration which allows fine grained
control over "check" queries during DDL operations, as well as that the
:paramref:`_types.SchemaType.inherit_schema` parameter is deprecated and
will emit a deprecation warning when used. See the migration notes for
full details.
Fixes: #12690
Change-Id: I9752a9d52774ae760ec3448f62fc5046c58d68f5
Added new parameter :paramref:`.Enum.create_type` to the Core
:class:`.Enum` class. This parameter is automatically passed to the
corresponding :class:`_postgresql.ENUM` native type during DDL operations,
allowing control over whether the PostgreSQL ENUM type is implicitly
created or dropped within DDL operations that are otherwise targeting
tables only. This provides control over the
:paramref:`_postgresql.ENUM.create_type` behavior without requiring
explicit creation of a :class:`_postgresql.ENUM` object.
Fixes: #10604
Change-Id: I450003ec2a2a65c119fe7ca8ff201392ce6b91e1
on the theme of 2.0/2.1 are relatively similar right now, do
some more cleanup removing the py3k suffixes that we dont need anymore.
I'm not sure that the logic that would search for these suffixes is
even present anymore and I would guess we removed it when we removed
py2k support. However, I am planning on possibly bringing it back
for some of the py314 stuff, so I still want to be able to do
suffix stuff. that will be for another patch.
Change-Id: I929e6edd922f8d5f943acce77191fb1e3035b42c
The :meth:`_types.ARRAY.Comparator.any` and
:meth:`_types.ARRAY.Comparator.all` methods for the :class:`_types.ARRAY`
type are now deprecated for removal; these two methods along with
:func:`_postgresql.Any` and :func:`_postgresql.All` have been legacy for
some time as they are superseded by the :func:`_sql.any_` and
:func:`_sql.all_` functions, which feature more intutive use.
Fixes: #10821
Change-Id: I8eb3bbcb98af4ee60a21767dc3bdac771cbc0b4c
Fixed issue where the :func:`_sql.any_` and :func:`_sql.all_` aggregation
operators would not correctly coerce the datatype of the compared value, in
those cases where the compared value were not a simple int/str etc., such
as a Python ``Enum`` or other custom value. This would lead to execution
time errors for these values. This issue is essentially the same as
🎫`6515` which was for the now-legacy :meth:`.ARRAY.any` and
:meth:`.ARRAY.all` methods.
Fixes: #12874
Change-Id: I980894c23b9974bc84d584a1a4c5fae72dded6d3
A :class:`.CompileError` is raised if attempting to create a PostgreSQL
:class:`_postgresql.ENUM` or :class:`_postgresql.DOMAIN` datatype using a
name that matches a known pg_catalog datatype name, and a default schema is
not specified. These types must be explicit within a schema in order to
be differentiated from the built-in pg_catalog type. The "public" or
otherwise default schema is not chosen by default here since the type can
only be reflected back using the explicit schema name as well (it is
otherwise not visible due to the pg_catalog name). Pull request courtesy
Kapil Dagur.
We originally thought we were going to do some default logic for the
default / "public" schema however this produces a type that is not
symmetric to its reflection, since the schema name must be explicit
for our current reflection queries.
So since it's an extremely bad idea to make an ENUM/DOMAIN with a
reserved type name anyway, we raise a compileerror if the type
has a known name. this is not robust against other names that
might exist in pg_catalog or other schemas that are in the search
path with these names. People just have to know what they're doing
here, the error here only covers a small subset of real world cases.
Fixes: #12761Closes: #12822
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/12822
Pull-request-sha: 793cf27be2
Change-Id: I9df9c216ab4102f06685a64ae7e55db1b3a9535e
Added new generalized aggregate function ordering to functions via the
:func:`_functions.FunctionElement.aggregate_order_by` method, which
receives an expression and generates the appropriate embedded "ORDER BY" or
"WITHIN GROUP (ORDER BY)" phrase depending on backend database. This new
function supersedes the use of the PostgreSQL
:func:`_postgresql.aggregate_order_by` function, which remains present for
backward compatibility. To complement the new parameter, the
:paramref:`_functions.aggregate_strings.order_by` which adds ORDER BY
capability to the :class:`_functions.aggregate_strings` dialect-agnostic
function which works for all included backends. Thanks much to Reuven
Starodubski with help on this patch.
Co-authored-by: Mike Bayer <mike_mp@zzzcomputing.com>
Fixes: #12853Closes: #12856
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/12856
Pull-request-sha: d93fb591751227eb1f96052ea3ad449f511f70b3
Change-Id: I8eb41ff2d57695963a358b5f0017ca9372f15f70
Fixed issue where selecting an enum array column containing NULL values
would fail to parse properly in the PostgreSQL dialect. The
:func:`._split_enum_values` function now correctly handles NULL entries by
converting them to Python ``None`` values.
Fixes: #12847
Change-Id: I39d10bc1be6b458da7e5d3f4b740f8faafd0adc5
Added a new concept of "operator classes" to the SQL operators supported by
SQLAlchemy, represented within the enum :class:`.OperatorClass`. The
purpose of this structure is to provide an extra layer of validation when a
particular kind of SQL operation is used with a particular datatype, to
catch early the use of an operator that does not have any relevance to the
datatype in use; a simple example is an integer or numeric column used with
a "string match" operator.
Fixes: #12736
Change-Id: I44f46d7326aef6847dbf0cf7a325833f8e347da6
Fixed regression in PostgreSQL dialect where JSONB subscription syntax
would generate incorrect SQL for JSONB-returning functions, causing syntax
errors. The dialect now properly wraps function calls and expressions in
parentheses when using the ``[]`` subscription syntax, generating
``(function_call)[index]`` instead of ``function_call[index]`` to comply
with PostgreSQL syntax requirements.
Fixes: #12778
Change-Id: If1238457e6bba6a933023b26519a41aa5de4dbcd
Fixes bug that would mistakenly interpret a domain or enum type
with name starting in ``interval`` as an ``INTERVAL`` type while
reflecting a table.
Fixes: #12744
Change-Id: I89ab287c3847ca545691afe73f26d86bf2337ae0
Added support for PostgreSQL 14+ JSONB subscripting syntax. When connected
to PostgreSQL 14 or later, JSONB columns now automatically use the native
subscript notation ``jsonb_col['key']`` instead of the arrow operator
``jsonb_col -> 'key'`` for both read and write operations. This provides
better compatibility with PostgreSQL's native JSONB subscripting feature
while maintaining backward compatibility with older PostgreSQL versions.
JSON columns continue to use the traditional arrow syntax regardless of
PostgreSQL version.
Fixes: #10927
Change-Id: I4b3a8a55a71f2ca3d95416a7b350b785574631eb
Adds a new ``str`` subclass :class:`dialects.postgresql.BitString`
representing PostgreSQL bitstrings in python, that includes
functionality for converting to and from ``int`` and ``bytes``, in
addition to implementing utility methods and operators for dealing
with bits.
This new class is returned automatically by the :class:`postgresql.BIT`
type.
Fixes: #10556Closes: #12594
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/12594
Pull-request-sha: da47f40739
Change-Id: I64685660527c23666f7351b2c393fa86dfb643ea
Fill the `postgresql_ops` key of PostgreSQL's `dialect_options` returned by get_multi_indexes() with a mapping from column names to the operator class, if it's not the default for respective data type.
As we need to join on ``pg_catalog.pg_opclass``, the table definition is added to ``postgresql.pg_catalog``.
Fixes#8664.
Closes: #12504
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/12504
Pull-request-sha: 8fdf93e1b2
Change-Id: I8789c1e9d15f8cc9a7205f492ec730570f19bbcc
Changed the default value of :paramref:`_types.Enum.inherit_schema` to
``True`` when :paramref:`_types.Enum.schema` and
:paramref:`_types.Enum.metadata` parameters are not provided.
The same behavior has been applied also to PostgreSQL
:class:`_postgresql.DOMAIN` type.
Fixes: #10594
Change-Id: Id3d819e3608974353e365cd063d9c5e40a071e73
Added support for the pow operator (``**``), with a default SQL
implementation of the ``POW()`` function. On Oracle Database, PostgreSQL
and MSSQL it renders as ``POWER()``. As part of this change, the operator
routes through a new first class ``func`` member :class:`_functions.pow`,
which renders on Oracle Database, PostgreSQL and MSSQL as ``POWER()``.
Fixes: #8579Closes: #8580
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/8580
Pull-request-sha: 041b2ef474
Change-Id: I371bd44ed3e58f2d55ef705aeec7d04710c97f23
This is supported both for schema definition and reflection.
Fixes#10665.
Closes: #12485
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/12485
Pull-request-sha: 1aabea7b55ece9fc0c6e069b777d4404ac01f964
Change-Id: I81d23966f84390dd1b03f0d13284ce6d883ee24e
When building a PostgreSQL ``ARRAY`` literal using
:class:`_postgresql.array` with an empty ``clauses`` argument, the
:paramref:`_postgresql.array.type_` parameter is now significant in that it
will be used to render the resulting ``ARRAY[]`` SQL expression with a
cast, such as ``ARRAY[]::INTEGER``. Pull request courtesy Denis Laxalde.
Fixes: #12432Closes: #12435
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/12435
Pull-request-sha: 9633d3c15d
Change-Id: I29ed7bd0562b82351d22de0658fb46c31cfe44f6
Added syntax extension :func:`_postgresql.distinct_on` to build ``DISTINCT
ON`` clauses. The old api, that passed columns to
:meth:`_sql.Select.distinct`, is now deprecated.
Fixes: #12342
Change-Id: Ia6a7e647a11e57b6ac2f50848778c20dc55eaf54
Added support for specifying a list of columns for ``SET NULL`` and ``SET
DEFAULT`` actions of ``ON DELETE`` clause of foreign key definition on
PostgreSQL. Pull request courtesy Denis Laxalde.
Fixes: #11595Closes: #12421
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/12421
Pull-request-sha: d0394db706
Change-Id: I036a559ae4a8efafe9ba64d776a840bd785a7397
Fixed issue in PostgreSQL network types :class:`_postgresql.INET`,
:class:`_postgresql.CIDR`, :class:`_postgresql.MACADDR`,
:class:`_postgresql.MACADDR8` where sending string values to compare to
these types would render an explicit CAST to VARCHAR, causing some SQL /
driver combinations to fail. Pull request courtesy Denis Laxalde.
Fixes: #12060Closes: #12412
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/12412
Pull-request-sha: 029fda7f2d
Change-Id: Id4a502ebc119775567cacddbabef2ce9715c1a9f
Fixed compiler issue in the PostgreSQL dialect where incorrect keywords
would be passed when using "FOR UPDATE OF" inside of a subquery.
Fixes: #12417
Change-Id: I6255d165e8e719e1786e78aa60ee8e6a95af1dcb
Add SQL typing to reflection query used to retrieve a the structure
of IDENTITY columns, adding explicit JSON typing to the query to suit
unusual PostgreSQL driver configurations that don't support JSON natively.
Fixed issue affecting PostgreSQL 17.3 and greater where reflection of
domains with "NOT NULL" as part of their definition would include an
invalid constraint entry in the data returned by
:meth:`_postgresql.PGInspector.get_domains` corresponding to an additional
"NOT NULL" constraint that isn't a CHECK constraint; the existing
``"nullable"`` entry in the dictionary already indicates if the domain
includes a "not null" constraint. Note that such domains also cannot be
reflected on PostgreSQL 17.0 through 17.2 due to a bug on the PostgreSQL
side; if encountering errors in reflection of domains which include NOT
NULL, upgrade to PostgreSQL server 17.3 or greater.
Fixes: #11751
Change-Id: I8e69de51601dca3257186e38c6f699fbfd9014c6
Adjusted the asyncpg connection wrapper so that the asyncpg
``.transaction()`` call sends ``None`` for isolation_level if not otherwise
set in the SQLAlchemy dialect/wrapper, thereby allowing asyncpg to make use
of the server level setting for isolation_level in the absense of a
client-level setting. Previously, this behavior of asyncpg was blocked by a
hardcoded ``read_committed``.
Fixes: #12159
Change-Id: I2cd878a5059a8fefc9557a9b8e056fedaee2e9a4
Adjusted the asyncpg dialect so that an empty SQL string, which is valid
for PostgreSQL server, may be successfully processed at the dialect level,
such as when using :meth:`.Connection.exec_driver_sql`. Pull request
courtesy Andrew Jackson.
Closes: #12220
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/12220
Pull-request-sha: 45c94febee
Change-Id: I870df9e31f4a229939e76c702724c25073329282
in 2.1 we want these structures to be cacheable, so start
by cleaning up types and adding coercions to enforce those types.
these will be more locked down in 2.1 as we will need to move
bound parameter coercion outside of compilation, but here
do some small starts and introduce in 2.0.
in one interest of cachability, a "literal_binds" that found
its way into SQLite's compiler is replaced with "literal_execute",
the difference being that the latter is cacheable. This literal
is apparently necessary to suit SQLite's query planner for
the "index criteria" portion of the on conflict clause that otherwise
can't work with a real bound parameter.
Change-Id: I4d66ec1473321616a1707da324a7dfe7a61ec94e
<!-- Provide a general summary of your proposed changes in the Title field above -->
### Description
Fixes#12093
### Checklist
<!-- go over following points. check them with an `x` if they do apply, (they turn into clickable checkboxes once the PR is submitted, so no need to do everything at once)
-->
This pull request is:
- [ ] A documentation / typographical / small typing error fix
- Good to go, no issue or tests are needed
- [x] 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: #<issue number>` 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: #<issue number>` in the commit message
- please include tests.
**Have a nice day!**
Closes: #12094
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/12094
Pull-request-sha: 3f900e96b9
Change-Id: I4c3945eec6a931acd0a8c1682988c5f26e96a499
Render bind cast for ``JSON`` and ``JSONB`` datatype on every dialect.
Previously this was only enabled in a subset of dialects.
Fixes: #11994
Change-Id: Ib085deb3e84034dac9e4f4057d32f055d5533e52
Fixed critical issue in the asyncpg driver where a rollback or commit that
fails specifically for the ``MissingGreenlet`` condition or any other error
that is not raised by asyncpg itself would discard the asyncpg transaction
in any case, even though the transaction were still idle, leaving to a
server side condition with an idle transaction that then goes back into the
connection pool. The flags for "transaction closed" are now not reset for
errors that are raised outside of asyncpg itself. When asyncpg itself
raises an error for ``.commit()`` or ``.rollback()``, asyncpg does then
discard of this transaction.
Fixes: #11819
Change-Id: I12f0532788b03ea63fb47a7af21e07c37effb070
Michael Bayer