codekingpro/portable-devtools
114k
1# dialects/postgresql/base.py
2# Copyright (C) 2005-2024 the SQLAlchemy authors and contributors
3# <see AUTHORS file>
4#
5# This module is part of SQLAlchemy and is released under
6# the MIT License: https://www.opensource.org/licenses/mit-license.php
7# mypy: ignore-errors
8
9r"""
10.. dialect:: postgresql
11 :name: PostgreSQL
12 :full_support: 12, 13, 14, 15
13 :normal_support: 9.6+
14 :best_effort: 9+
15
16.. _postgresql_sequences:
17
18Sequences/SERIAL/IDENTITY
19-------------------------
20
21PostgreSQL supports sequences, and SQLAlchemy uses these as the default means
22of creating new primary key values for integer-based primary key columns. When
23creating tables, SQLAlchemy will issue the ``SERIAL`` datatype for
24integer-based primary key columns, which generates a sequence and server side
25default corresponding to the column.
26
27To specify a specific named sequence to be used for primary key generation,
28use the :func:`~sqlalchemy.schema.Sequence` construct::
29
30 Table(
31 "sometable",
32 metadata,
33 Column(
34 "id", Integer, Sequence("some_id_seq", start=1), primary_key=True
35 )
36 )
37
38When SQLAlchemy issues a single INSERT statement, to fulfill the contract of
39having the "last insert identifier" available, a RETURNING clause is added to
40the INSERT statement which specifies the primary key columns should be
41returned after the statement completes. The RETURNING functionality only takes
42place if PostgreSQL 8.2 or later is in use. As a fallback approach, the
43sequence, whether specified explicitly or implicitly via ``SERIAL``, is
44executed independently beforehand, the returned value to be used in the
45subsequent insert. Note that when an
46:func:`~sqlalchemy.sql.expression.insert()` construct is executed using
47"executemany" semantics, the "last inserted identifier" functionality does not
48apply; no RETURNING clause is emitted nor is the sequence pre-executed in this
49case.
50
51
52PostgreSQL 10 and above IDENTITY columns
53^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
54
55PostgreSQL 10 and above have a new IDENTITY feature that supersedes the use
56of SERIAL. The :class:`_schema.Identity` construct in a
57:class:`_schema.Column` can be used to control its behavior::
58
59 from sqlalchemy import Table, Column, MetaData, Integer, Computed
60
61 metadata = MetaData()
62
63 data = Table(
64 "data",
65 metadata,
66 Column(
67 'id', Integer, Identity(start=42, cycle=True), primary_key=True
68 ),
69 Column('data', String)
70 )
71
72The CREATE TABLE for the above :class:`_schema.Table` object would be:
73
74.. sourcecode:: sql
75
76 CREATE TABLE data (
77 id INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 42 CYCLE),
78 data VARCHAR,
79 PRIMARY KEY (id)
80 )
81
82.. versionchanged:: 1.4 Added :class:`_schema.Identity` construct
83 in a :class:`_schema.Column` to specify the option of an autoincrementing
84 column.
85
86.. note::
87
88 Previous versions of SQLAlchemy did not have built-in support for rendering
89 of IDENTITY, and could use the following compilation hook to replace
90 occurrences of SERIAL with IDENTITY::
91
92 from sqlalchemy.schema import CreateColumn
93 from sqlalchemy.ext.compiler import compiles
94
95
96 @compiles(CreateColumn, 'postgresql')
97 def use_identity(element, compiler, **kw):
98 text = compiler.visit_create_column(element, **kw)
99 text = text.replace(
100 "SERIAL", "INT GENERATED BY DEFAULT AS IDENTITY"
101 )
102 return text
103
104 Using the above, a table such as::
105
106 t = Table(
107 't', m,
108 Column('id', Integer, primary_key=True),
109 Column('data', String)
110 )
111
112 Will generate on the backing database as::
113
114 CREATE TABLE t (
115 id INT GENERATED BY DEFAULT AS IDENTITY,
116 data VARCHAR,
117 PRIMARY KEY (id)
118 )
119
120.. _postgresql_ss_cursors:
121
122Server Side Cursors
123-------------------
124
125Server-side cursor support is available for the psycopg2, asyncpg
126dialects and may also be available in others.
127
128Server side cursors are enabled on a per-statement basis by using the
129:paramref:`.Connection.execution_options.stream_results` connection execution
130option::
131
132 with engine.connect() as conn:
133 result = conn.execution_options(stream_results=True).execute(text("select * from table"))
134
135Note that some kinds of SQL statements may not be supported with
136server side cursors; generally, only SQL statements that return rows should be
137used with this option.
138
139.. deprecated:: 1.4 The dialect-level server_side_cursors flag is deprecated
140 and will be removed in a future release. Please use the
141 :paramref:`_engine.Connection.stream_results` execution option for
142 unbuffered cursor support.
143
144.. seealso::
145
146 :ref:`engine_stream_results`
147
148.. _postgresql_isolation_level:
149
150Transaction Isolation Level
151---------------------------
152
153Most SQLAlchemy dialects support setting of transaction isolation level
154using the :paramref:`_sa.create_engine.isolation_level` parameter
155at the :func:`_sa.create_engine` level, and at the :class:`_engine.Connection`
156level via the :paramref:`.Connection.execution_options.isolation_level`
157parameter.
158
159For PostgreSQL dialects, this feature works either by making use of the
160DBAPI-specific features, such as psycopg2's isolation level flags which will
161embed the isolation level setting inline with the ``"BEGIN"`` statement, or for
162DBAPIs with no direct support by emitting ``SET SESSION CHARACTERISTICS AS
163TRANSACTION ISOLATION LEVEL <level>`` ahead of the ``"BEGIN"`` statement
164emitted by the DBAPI. For the special AUTOCOMMIT isolation level,
165DBAPI-specific techniques are used which is typically an ``.autocommit``
166flag on the DBAPI connection object.
167
168To set isolation level using :func:`_sa.create_engine`::
169
170 engine = create_engine(
171 "postgresql+pg8000://scott:tiger@localhost/test",
172 isolation_level = "REPEATABLE READ"
173 )
174
175To set using per-connection execution options::
176
177 with engine.connect() as conn:
178 conn = conn.execution_options(
179 isolation_level="REPEATABLE READ"
180 )
181 with conn.begin():
182 # ... work with transaction
183
184There are also more options for isolation level configurations, such as
185"sub-engine" objects linked to a main :class:`_engine.Engine` which each apply
186different isolation level settings. See the discussion at
187:ref:`dbapi_autocommit` for background.
188
189Valid values for ``isolation_level`` on most PostgreSQL dialects include:
190
191* ``READ COMMITTED``
192* ``READ UNCOMMITTED``
193* ``REPEATABLE READ``
194* ``SERIALIZABLE``
195* ``AUTOCOMMIT``
196
197.. seealso::
198
199 :ref:`dbapi_autocommit`
200
201 :ref:`postgresql_readonly_deferrable`
202
203 :ref:`psycopg2_isolation_level`
204
205 :ref:`pg8000_isolation_level`
206
207.. _postgresql_readonly_deferrable:
208
209Setting READ ONLY / DEFERRABLE
210------------------------------
211
212Most PostgreSQL dialects support setting the "READ ONLY" and "DEFERRABLE"
213characteristics of the transaction, which is in addition to the isolation level
214setting. These two attributes can be established either in conjunction with or
215independently of the isolation level by passing the ``postgresql_readonly`` and
216``postgresql_deferrable`` flags with
217:meth:`_engine.Connection.execution_options`. The example below illustrates
218passing the ``"SERIALIZABLE"`` isolation level at the same time as setting
219"READ ONLY" and "DEFERRABLE"::
220
221 with engine.connect() as conn:
222 conn = conn.execution_options(
223 isolation_level="SERIALIZABLE",
224 postgresql_readonly=True,
225 postgresql_deferrable=True
226 )
227 with conn.begin():
228 # ... work with transaction
229
230Note that some DBAPIs such as asyncpg only support "readonly" with
231SERIALIZABLE isolation.
232
233.. versionadded:: 1.4 added support for the ``postgresql_readonly``
234 and ``postgresql_deferrable`` execution options.
235
236.. _postgresql_reset_on_return:
237
238Temporary Table / Resource Reset for Connection Pooling
239-------------------------------------------------------
240
241The :class:`.QueuePool` connection pool implementation used
242by the SQLAlchemy :class:`.Engine` object includes
243:ref:`reset on return <pool_reset_on_return>` behavior that will invoke
244the DBAPI ``.rollback()`` method when connections are returned to the pool.
245While this rollback will clear out the immediate state used by the previous
246transaction, it does not cover a wider range of session-level state, including
247temporary tables as well as other server state such as prepared statement
248handles and statement caches. The PostgreSQL database includes a variety
249of commands which may be used to reset this state, including
250``DISCARD``, ``RESET``, ``DEALLOCATE``, and ``UNLISTEN``.
251
252
253To install
254one or more of these commands as the means of performing reset-on-return,
255the :meth:`.PoolEvents.reset` event hook may be used, as demonstrated
256in the example below. The implementation
257will end transactions in progress as well as discard temporary tables
258using the ``CLOSE``, ``RESET`` and ``DISCARD`` commands; see the PostgreSQL
259documentation for background on what each of these statements do.
260
261The :paramref:`_sa.create_engine.pool_reset_on_return` parameter
262is set to ``None`` so that the custom scheme can replace the default behavior
263completely. The custom hook implementation calls ``.rollback()`` in any case,
264as it's usually important that the DBAPI's own tracking of commit/rollback
265will remain consistent with the state of the transaction::
266
267
268 from sqlalchemy import create_engine
269 from sqlalchemy import event
270
271 postgresql_engine = create_engine(
272 "postgresql+pyscopg2://scott:tiger@hostname/dbname",
273
274 # disable default reset-on-return scheme
275 pool_reset_on_return=None,
276 )
277
278
279 @event.listens_for(postgresql_engine, "reset")
280 def _reset_postgresql(dbapi_connection, connection_record, reset_state):
281 if not reset_state.terminate_only:
282 dbapi_connection.execute("CLOSE ALL")
283 dbapi_connection.execute("RESET ALL")
284 dbapi_connection.execute("DISCARD TEMP")
285
286 # so that the DBAPI itself knows that the connection has been
287 # reset
288 dbapi_connection.rollback()
289
290.. versionchanged:: 2.0.0b3 Added additional state arguments to
291 the :meth:`.PoolEvents.reset` event and additionally ensured the event
292 is invoked for all "reset" occurrences, so that it's appropriate
293 as a place for custom "reset" handlers. Previous schemes which
294 use the :meth:`.PoolEvents.checkin` handler remain usable as well.
295
296.. seealso::
297
298 :ref:`pool_reset_on_return` - in the :ref:`pooling_toplevel` documentation
299
300.. _postgresql_alternate_search_path:
301
302Setting Alternate Search Paths on Connect
303------------------------------------------
304
305The PostgreSQL ``search_path`` variable refers to the list of schema names
306that will be implicitly referenced when a particular table or other
307object is referenced in a SQL statement. As detailed in the next section
308:ref:`postgresql_schema_reflection`, SQLAlchemy is generally organized around
309the concept of keeping this variable at its default value of ``public``,
310however, in order to have it set to any arbitrary name or names when connections
311are used automatically, the "SET SESSION search_path" command may be invoked
312for all connections in a pool using the following event handler, as discussed
313at :ref:`schema_set_default_connections`::
314
315 from sqlalchemy import event
316 from sqlalchemy import create_engine
317
318 engine = create_engine("postgresql+psycopg2://scott:tiger@host/dbname")
319
320 @event.listens_for(engine, "connect", insert=True)
321 def set_search_path(dbapi_connection, connection_record):
322 existing_autocommit = dbapi_connection.autocommit
323 dbapi_connection.autocommit = True
324 cursor = dbapi_connection.cursor()
325 cursor.execute("SET SESSION search_path='%s'" % schema_name)
326 cursor.close()
327 dbapi_connection.autocommit = existing_autocommit
328
329The reason the recipe is complicated by use of the ``.autocommit`` DBAPI
330attribute is so that when the ``SET SESSION search_path`` directive is invoked,
331it is invoked outside of the scope of any transaction and therefore will not
332be reverted when the DBAPI connection has a rollback.
333
334.. seealso::
335
336 :ref:`schema_set_default_connections` - in the :ref:`metadata_toplevel` documentation
337
338
339
340
341.. _postgresql_schema_reflection:
342
343Remote-Schema Table Introspection and PostgreSQL search_path
344------------------------------------------------------------
345
346.. admonition:: Section Best Practices Summarized
347
348 keep the ``search_path`` variable set to its default of ``public``, without
349 any other schema names. Ensure the username used to connect **does not**
350 match remote schemas, or ensure the ``"$user"`` token is **removed** from
351 ``search_path``. For other schema names, name these explicitly
352 within :class:`_schema.Table` definitions. Alternatively, the
353 ``postgresql_ignore_search_path`` option will cause all reflected
354 :class:`_schema.Table` objects to have a :attr:`_schema.Table.schema`
355 attribute set up.
356
357The PostgreSQL dialect can reflect tables from any schema, as outlined in
358:ref:`metadata_reflection_schemas`.
359
360In all cases, the first thing SQLAlchemy does when reflecting tables is
361to **determine the default schema for the current database connection**.
362It does this using the PostgreSQL ``current_schema()``
363function, illustated below using a PostgreSQL client session (i.e. using
364the ``psql`` tool)::
365
366 test=> select current_schema();
367 current_schema
368 ----------------
369 public
370 (1 row)
371
372Above we see that on a plain install of PostgreSQL, the default schema name
373is the name ``public``.
374
375However, if your database username **matches the name of a schema**, PostgreSQL's
376default is to then **use that name as the default schema**. Below, we log in
377using the username ``scott``. When we create a schema named ``scott``, **it
378implicitly changes the default schema**::
379
380 test=> select current_schema();
381 current_schema
382 ----------------
383 public
384 (1 row)
385
386 test=> create schema scott;
387 CREATE SCHEMA
388 test=> select current_schema();
389 current_schema
390 ----------------
391 scott
392 (1 row)
393
394The behavior of ``current_schema()`` is derived from the
395`PostgreSQL search path
396<https://www.postgresql.org/docs/current/static/ddl-schemas.html#DDL-SCHEMAS-PATH>`_
397variable ``search_path``, which in modern PostgreSQL versions defaults to this::
398
399 test=> show search_path;
400 search_path
401 -----------------
402 "$user", public
403 (1 row)
404
405Where above, the ``"$user"`` variable will inject the current username as the
406default schema, if one exists. Otherwise, ``public`` is used.
407
408When a :class:`_schema.Table` object is reflected, if it is present in the
409schema indicated by the ``current_schema()`` function, **the schema name assigned
410to the ".schema" attribute of the Table is the Python "None" value**. Otherwise, the
411".schema" attribute will be assigned the string name of that schema.
412
413With regards to tables which these :class:`_schema.Table`
414objects refer to via foreign key constraint, a decision must be made as to how
415the ``.schema`` is represented in those remote tables, in the case where that
416remote schema name is also a member of the current ``search_path``.
417
418By default, the PostgreSQL dialect mimics the behavior encouraged by
419PostgreSQL's own ``pg_get_constraintdef()`` builtin procedure. This function
420returns a sample definition for a particular foreign key constraint,
421omitting the referenced schema name from that definition when the name is
422also in the PostgreSQL schema search path. The interaction below
423illustrates this behavior::
424
425 test=> CREATE TABLE test_schema.referred(id INTEGER PRIMARY KEY);
426 CREATE TABLE
427 test=> CREATE TABLE referring(
428 test(> id INTEGER PRIMARY KEY,
429 test(> referred_id INTEGER REFERENCES test_schema.referred(id));
430 CREATE TABLE
431 test=> SET search_path TO public, test_schema;
432 test=> SELECT pg_catalog.pg_get_constraintdef(r.oid, true) FROM
433 test-> pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n
434 test-> ON n.oid = c.relnamespace
435 test-> JOIN pg_catalog.pg_constraint r ON c.oid = r.conrelid
436 test-> WHERE c.relname='referring' AND r.contype = 'f'
437 test-> ;
438 pg_get_constraintdef
439 ---------------------------------------------------
440 FOREIGN KEY (referred_id) REFERENCES referred(id)
441 (1 row)
442
443Above, we created a table ``referred`` as a member of the remote schema
444``test_schema``, however when we added ``test_schema`` to the
445PG ``search_path`` and then asked ``pg_get_constraintdef()`` for the
446``FOREIGN KEY`` syntax, ``test_schema`` was not included in the output of
447the function.
448
449On the other hand, if we set the search path back to the typical default
450of ``public``::
451
452 test=> SET search_path TO public;
453 SET
454
455The same query against ``pg_get_constraintdef()`` now returns the fully
456schema-qualified name for us::
457
458 test=> SELECT pg_catalog.pg_get_constraintdef(r.oid, true) FROM
459 test-> pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n
460 test-> ON n.oid = c.relnamespace
461 test-> JOIN pg_catalog.pg_constraint r ON c.oid = r.conrelid
462 test-> WHERE c.relname='referring' AND r.contype = 'f';
463 pg_get_constraintdef
464 ---------------------------------------------------------------
465 FOREIGN KEY (referred_id) REFERENCES test_schema.referred(id)
466 (1 row)
467
468SQLAlchemy will by default use the return value of ``pg_get_constraintdef()``
469in order to determine the remote schema name. That is, if our ``search_path``
470were set to include ``test_schema``, and we invoked a table
471reflection process as follows::
472
473 >>> from sqlalchemy import Table, MetaData, create_engine, text
474 >>> engine = create_engine("postgresql+psycopg2://scott:tiger@localhost/test")
475 >>> with engine.connect() as conn:
476 ... conn.execute(text("SET search_path TO test_schema, public"))
477 ... metadata_obj = MetaData()
478 ... referring = Table('referring', metadata_obj,
479 ... autoload_with=conn)
480 ...
481 <sqlalchemy.engine.result.CursorResult object at 0x101612ed0>
482
483The above process would deliver to the :attr:`_schema.MetaData.tables`
484collection
485``referred`` table named **without** the schema::
486
487 >>> metadata_obj.tables['referred'].schema is None
488 True
489
490To alter the behavior of reflection such that the referred schema is
491maintained regardless of the ``search_path`` setting, use the
492``postgresql_ignore_search_path`` option, which can be specified as a
493dialect-specific argument to both :class:`_schema.Table` as well as
494:meth:`_schema.MetaData.reflect`::
495
496 >>> with engine.connect() as conn:
497 ... conn.execute(text("SET search_path TO test_schema, public"))
498 ... metadata_obj = MetaData()
499 ... referring = Table('referring', metadata_obj,
500 ... autoload_with=conn,
501 ... postgresql_ignore_search_path=True)
502 ...
503 <sqlalchemy.engine.result.CursorResult object at 0x1016126d0>
504
505We will now have ``test_schema.referred`` stored as schema-qualified::
506
507 >>> metadata_obj.tables['test_schema.referred'].schema
508 'test_schema'
509
510.. sidebar:: Best Practices for PostgreSQL Schema reflection
511
512 The description of PostgreSQL schema reflection behavior is complex, and
513 is the product of many years of dealing with widely varied use cases and
514 user preferences. But in fact, there's no need to understand any of it if
515 you just stick to the simplest use pattern: leave the ``search_path`` set
516 to its default of ``public`` only, never refer to the name ``public`` as
517 an explicit schema name otherwise, and refer to all other schema names
518 explicitly when building up a :class:`_schema.Table` object. The options
519 described here are only for those users who can't, or prefer not to, stay
520 within these guidelines.
521
522.. seealso::
523
524 :ref:`reflection_schema_qualified_interaction` - discussion of the issue
525 from a backend-agnostic perspective
526
527 `The Schema Search Path
528 <https://www.postgresql.org/docs/current/static/ddl-schemas.html#DDL-SCHEMAS-PATH>`_
529 - on the PostgreSQL website.
530
531INSERT/UPDATE...RETURNING
532-------------------------
533
534The dialect supports PG 8.2's ``INSERT..RETURNING``, ``UPDATE..RETURNING`` and
535``DELETE..RETURNING`` syntaxes. ``INSERT..RETURNING`` is used by default
536for single-row INSERT statements in order to fetch newly generated
537primary key identifiers. To specify an explicit ``RETURNING`` clause,
538use the :meth:`._UpdateBase.returning` method on a per-statement basis::
539
540 # INSERT..RETURNING
541 result = table.insert().returning(table.c.col1, table.c.col2).\
542 values(name='foo')
543 print(result.fetchall())
544
545 # UPDATE..RETURNING
546 result = table.update().returning(table.c.col1, table.c.col2).\
547 where(table.c.name=='foo').values(name='bar')
548 print(result.fetchall())
549
550 # DELETE..RETURNING
551 result = table.delete().returning(table.c.col1, table.c.col2).\
552 where(table.c.name=='foo')
553 print(result.fetchall())
554
555.. _postgresql_insert_on_conflict:
556
557INSERT...ON CONFLICT (Upsert)
558------------------------------
559
560Starting with version 9.5, PostgreSQL allows "upserts" (update or insert) of
561rows into a table via the ``ON CONFLICT`` clause of the ``INSERT`` statement. A
562candidate row will only be inserted if that row does not violate any unique
563constraints. In the case of a unique constraint violation, a secondary action
564can occur which can be either "DO UPDATE", indicating that the data in the
565target row should be updated, or "DO NOTHING", which indicates to silently skip
566this row.
567
568Conflicts are determined using existing unique constraints and indexes. These
569constraints may be identified either using their name as stated in DDL,
570or they may be inferred by stating the columns and conditions that comprise
571the indexes.
572
573SQLAlchemy provides ``ON CONFLICT`` support via the PostgreSQL-specific
574:func:`_postgresql.insert()` function, which provides
575the generative methods :meth:`_postgresql.Insert.on_conflict_do_update`
576and :meth:`~.postgresql.Insert.on_conflict_do_nothing`:
577
578.. sourcecode:: pycon+sql
579
580 >>> from sqlalchemy.dialects.postgresql import insert
581 >>> insert_stmt = insert(my_table).values(
582 ... id='some_existing_id',
583 ... data='inserted value')
584 >>> do_nothing_stmt = insert_stmt.on_conflict_do_nothing(
585 ... index_elements=['id']
586 ... )
587 >>> print(do_nothing_stmt)
588 {printsql}INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s)
589 ON CONFLICT (id) DO NOTHING
590 {stop}
591
592 >>> do_update_stmt = insert_stmt.on_conflict_do_update(
593 ... constraint='pk_my_table',
594 ... set_=dict(data='updated value')
595 ... )
596 >>> print(do_update_stmt)
597 {printsql}INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s)
598 ON CONFLICT ON CONSTRAINT pk_my_table DO UPDATE SET data = %(param_1)s
599
600.. seealso::
601
602 `INSERT .. ON CONFLICT
603 <https://www.postgresql.org/docs/current/static/sql-insert.html#SQL-ON-CONFLICT>`_
604 - in the PostgreSQL documentation.
605
606Specifying the Target
607^^^^^^^^^^^^^^^^^^^^^
608
609Both methods supply the "target" of the conflict using either the
610named constraint or by column inference:
611
612* The :paramref:`_postgresql.Insert.on_conflict_do_update.index_elements` argument
613 specifies a sequence containing string column names, :class:`_schema.Column`
614 objects, and/or SQL expression elements, which would identify a unique
615 index:
616
617 .. sourcecode:: pycon+sql
618
619 >>> do_update_stmt = insert_stmt.on_conflict_do_update(
620 ... index_elements=['id'],
621 ... set_=dict(data='updated value')
622 ... )
623 >>> print(do_update_stmt)
624 {printsql}INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s)
625 ON CONFLICT (id) DO UPDATE SET data = %(param_1)s
626 {stop}
627
628 >>> do_update_stmt = insert_stmt.on_conflict_do_update(
629 ... index_elements=[my_table.c.id],
630 ... set_=dict(data='updated value')
631 ... )
632 >>> print(do_update_stmt)
633 {printsql}INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s)
634 ON CONFLICT (id) DO UPDATE SET data = %(param_1)s
635
636* When using :paramref:`_postgresql.Insert.on_conflict_do_update.index_elements` to
637 infer an index, a partial index can be inferred by also specifying the
638 use the :paramref:`_postgresql.Insert.on_conflict_do_update.index_where` parameter:
639
640 .. sourcecode:: pycon+sql
641
642 >>> stmt = insert(my_table).values(user_email='a@b.com', data='inserted data')
643 >>> stmt = stmt.on_conflict_do_update(
644 ... index_elements=[my_table.c.user_email],
645 ... index_where=my_table.c.user_email.like('%@gmail.com'),
646 ... set_=dict(data=stmt.excluded.data)
647 ... )
648 >>> print(stmt)
649 {printsql}INSERT INTO my_table (data, user_email)
650 VALUES (%(data)s, %(user_email)s) ON CONFLICT (user_email)
651 WHERE user_email LIKE %(user_email_1)s DO UPDATE SET data = excluded.data
652
653* The :paramref:`_postgresql.Insert.on_conflict_do_update.constraint` argument is
654 used to specify an index directly rather than inferring it. This can be
655 the name of a UNIQUE constraint, a PRIMARY KEY constraint, or an INDEX:
656
657 .. sourcecode:: pycon+sql
658
659 >>> do_update_stmt = insert_stmt.on_conflict_do_update(
660 ... constraint='my_table_idx_1',
661 ... set_=dict(data='updated value')
662 ... )
663 >>> print(do_update_stmt)
664 {printsql}INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s)
665 ON CONFLICT ON CONSTRAINT my_table_idx_1 DO UPDATE SET data = %(param_1)s
666 {stop}
667
668 >>> do_update_stmt = insert_stmt.on_conflict_do_update(
669 ... constraint='my_table_pk',
670 ... set_=dict(data='updated value')
671 ... )
672 >>> print(do_update_stmt)
673 {printsql}INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s)
674 ON CONFLICT ON CONSTRAINT my_table_pk DO UPDATE SET data = %(param_1)s
675 {stop}
676
677* The :paramref:`_postgresql.Insert.on_conflict_do_update.constraint` argument may
678 also refer to a SQLAlchemy construct representing a constraint,
679 e.g. :class:`.UniqueConstraint`, :class:`.PrimaryKeyConstraint`,
680 :class:`.Index`, or :class:`.ExcludeConstraint`. In this use,
681 if the constraint has a name, it is used directly. Otherwise, if the
682 constraint is unnamed, then inference will be used, where the expressions
683 and optional WHERE clause of the constraint will be spelled out in the
684 construct. This use is especially convenient
685 to refer to the named or unnamed primary key of a :class:`_schema.Table`
686 using the
687 :attr:`_schema.Table.primary_key` attribute:
688
689 .. sourcecode:: pycon+sql
690
691 >>> do_update_stmt = insert_stmt.on_conflict_do_update(
692 ... constraint=my_table.primary_key,
693 ... set_=dict(data='updated value')
694 ... )
695 >>> print(do_update_stmt)
696 {printsql}INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s)
697 ON CONFLICT (id) DO UPDATE SET data = %(param_1)s
698
699The SET Clause
700^^^^^^^^^^^^^^^
701
702``ON CONFLICT...DO UPDATE`` is used to perform an update of the already
703existing row, using any combination of new values as well as values
704from the proposed insertion. These values are specified using the
705:paramref:`_postgresql.Insert.on_conflict_do_update.set_` parameter. This
706parameter accepts a dictionary which consists of direct values
707for UPDATE:
708
709.. sourcecode:: pycon+sql
710
711 >>> stmt = insert(my_table).values(id='some_id', data='inserted value')
712 >>> do_update_stmt = stmt.on_conflict_do_update(
713 ... index_elements=['id'],
714 ... set_=dict(data='updated value')
715 ... )
716 >>> print(do_update_stmt)
717 {printsql}INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s)
718 ON CONFLICT (id) DO UPDATE SET data = %(param_1)s
719
720.. warning::
721
722 The :meth:`_expression.Insert.on_conflict_do_update`
723 method does **not** take into
724 account Python-side default UPDATE values or generation functions, e.g.
725 those specified using :paramref:`_schema.Column.onupdate`.
726 These values will not be exercised for an ON CONFLICT style of UPDATE,
727 unless they are manually specified in the
728 :paramref:`_postgresql.Insert.on_conflict_do_update.set_` dictionary.
729
730Updating using the Excluded INSERT Values
731^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
732
733In order to refer to the proposed insertion row, the special alias
734:attr:`~.postgresql.Insert.excluded` is available as an attribute on
735the :class:`_postgresql.Insert` object; this object is a
736:class:`_expression.ColumnCollection`
737which alias contains all columns of the target
738table:
739
740.. sourcecode:: pycon+sql
741
742 >>> stmt = insert(my_table).values(
743 ... id='some_id',
744 ... data='inserted value',
745 ... author='jlh'
746 ... )
747 >>> do_update_stmt = stmt.on_conflict_do_update(
748 ... index_elements=['id'],
749 ... set_=dict(data='updated value', author=stmt.excluded.author)
750 ... )
751 >>> print(do_update_stmt)
752 {printsql}INSERT INTO my_table (id, data, author)
753 VALUES (%(id)s, %(data)s, %(author)s)
754 ON CONFLICT (id) DO UPDATE SET data = %(param_1)s, author = excluded.author
755
756Additional WHERE Criteria
757^^^^^^^^^^^^^^^^^^^^^^^^^
758
759The :meth:`_expression.Insert.on_conflict_do_update` method also accepts
760a WHERE clause using the :paramref:`_postgresql.Insert.on_conflict_do_update.where`
761parameter, which will limit those rows which receive an UPDATE:
762
763.. sourcecode:: pycon+sql
764
765 >>> stmt = insert(my_table).values(
766 ... id='some_id',
767 ... data='inserted value',
768 ... author='jlh'
769 ... )
770 >>> on_update_stmt = stmt.on_conflict_do_update(
771 ... index_elements=['id'],
772 ... set_=dict(data='updated value', author=stmt.excluded.author),
773 ... where=(my_table.c.status == 2)
774 ... )
775 >>> print(on_update_stmt)
776 {printsql}INSERT INTO my_table (id, data, author)
777 VALUES (%(id)s, %(data)s, %(author)s)
778 ON CONFLICT (id) DO UPDATE SET data = %(param_1)s, author = excluded.author
779 WHERE my_table.status = %(status_1)s
780
781Skipping Rows with DO NOTHING
782^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
783
784``ON CONFLICT`` may be used to skip inserting a row entirely
785if any conflict with a unique or exclusion constraint occurs; below
786this is illustrated using the
787:meth:`~.postgresql.Insert.on_conflict_do_nothing` method:
788
789.. sourcecode:: pycon+sql
790
791 >>> stmt = insert(my_table).values(id='some_id', data='inserted value')
792 >>> stmt = stmt.on_conflict_do_nothing(index_elements=['id'])
793 >>> print(stmt)
794 {printsql}INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s)
795 ON CONFLICT (id) DO NOTHING
796
797If ``DO NOTHING`` is used without specifying any columns or constraint,
798it has the effect of skipping the INSERT for any unique or exclusion
799constraint violation which occurs:
800
801.. sourcecode:: pycon+sql
802
803 >>> stmt = insert(my_table).values(id='some_id', data='inserted value')
804 >>> stmt = stmt.on_conflict_do_nothing()
805 >>> print(stmt)
806 {printsql}INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s)
807 ON CONFLICT DO NOTHING
808
809.. _postgresql_match:
810
811Full Text Search
812----------------
813
814PostgreSQL's full text search system is available through the use of the
815:data:`.func` namespace, combined with the use of custom operators
816via the :meth:`.Operators.bool_op` method. For simple cases with some
817degree of cross-backend compatibility, the :meth:`.Operators.match` operator
818may also be used.
819
820.. _postgresql_simple_match:
821
822Simple plain text matching with ``match()``
823^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
824
825The :meth:`.Operators.match` operator provides for cross-compatible simple
826text matching. For the PostgreSQL backend, it's hardcoded to generate
827an expression using the ``@@`` operator in conjunction with the
828``plainto_tsquery()`` PostgreSQL function.
829
830On the PostgreSQL dialect, an expression like the following::
831
832 select(sometable.c.text.match("search string"))
833
834would emit to the database::
835
836 SELECT text @@ plainto_tsquery('search string') FROM table
837
838Above, passing a plain string to :meth:`.Operators.match` will automatically
839make use of ``plainto_tsquery()`` to specify the type of tsquery. This
840establishes basic database cross-compatibility for :meth:`.Operators.match`
841with other backends.
842
843.. versionchanged:: 2.0 The default tsquery generation function used by the
844 PostgreSQL dialect with :meth:`.Operators.match` is ``plainto_tsquery()``.
845
846 To render exactly what was rendered in 1.4, use the following form::
847
848 from sqlalchemy import func
849
850 select(
851 sometable.c.text.bool_op("@@")(func.to_tsquery("search string"))
852 )
853
854 Which would emit::
855
856 SELECT text @@ to_tsquery('search string') FROM table
857
858Using PostgreSQL full text functions and operators directly
859^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
860
861Text search operations beyond the simple use of :meth:`.Operators.match`
862may make use of the :data:`.func` namespace to generate PostgreSQL full-text
863functions, in combination with :meth:`.Operators.bool_op` to generate
864any boolean operator.
865
866For example, the query::
867
868 select(
869 func.to_tsquery('cat').bool_op("@>")(func.to_tsquery('cat & rat'))
870 )
871
872would generate:
873
874.. sourcecode:: sql
875
876 SELECT to_tsquery('cat') @> to_tsquery('cat & rat')
877
878
879The :class:`_postgresql.TSVECTOR` type can provide for explicit CAST::
880
881 from sqlalchemy.dialects.postgresql import TSVECTOR
882 from sqlalchemy import select, cast
883 select(cast("some text", TSVECTOR))
884
885produces a statement equivalent to::
886
887 SELECT CAST('some text' AS TSVECTOR) AS anon_1
888
889The ``func`` namespace is augmented by the PostgreSQL dialect to set up
890correct argument and return types for most full text search functions.
891These functions are used automatically by the :attr:`_sql.func` namespace
892assuming the ``sqlalchemy.dialects.postgresql`` package has been imported,
893or :func:`_sa.create_engine` has been invoked using a ``postgresql``
894dialect. These functions are documented at:
895
896* :class:`_postgresql.to_tsvector`
897* :class:`_postgresql.to_tsquery`
898* :class:`_postgresql.plainto_tsquery`
899* :class:`_postgresql.phraseto_tsquery`
900* :class:`_postgresql.websearch_to_tsquery`
901* :class:`_postgresql.ts_headline`
902
903Specifying the "regconfig" with ``match()`` or custom operators
904^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
905
906PostgreSQL's ``plainto_tsquery()`` function accepts an optional
907"regconfig" argument that is used to instruct PostgreSQL to use a
908particular pre-computed GIN or GiST index in order to perform the search.
909When using :meth:`.Operators.match`, this additional parameter may be
910specified using the ``postgresql_regconfig`` parameter, such as::
911
912 select(mytable.c.id).where(
913 mytable.c.title.match('somestring', postgresql_regconfig='english')
914 )
915
916Which would emit::
917
918 SELECT mytable.id FROM mytable
919 WHERE mytable.title @@ plainto_tsquery('english', 'somestring')
920
921When using other PostgreSQL search functions with :data:`.func`, the
922"regconfig" parameter may be passed directly as the initial argument::
923
924 select(mytable.c.id).where(
925 func.to_tsvector("english", mytable.c.title).bool_op("@@")(
926 func.to_tsquery("english", "somestring")
927 )
928 )
929
930produces a statement equivalent to::
931
932 SELECT mytable.id FROM mytable
933 WHERE to_tsvector('english', mytable.title) @@
934 to_tsquery('english', 'somestring')
935
936It is recommended that you use the ``EXPLAIN ANALYZE...`` tool from
937PostgreSQL to ensure that you are generating queries with SQLAlchemy that
938take full advantage of any indexes you may have created for full text search.
939
940.. seealso::
941
942 `Full Text Search <https://www.postgresql.org/docs/current/textsearch-controls.html>`_ - in the PostgreSQL documentation
943
944
945FROM ONLY ...
946-------------
947
948The dialect supports PostgreSQL's ONLY keyword for targeting only a particular
949table in an inheritance hierarchy. This can be used to produce the
950``SELECT ... FROM ONLY``, ``UPDATE ONLY ...``, and ``DELETE FROM ONLY ...``
951syntaxes. It uses SQLAlchemy's hints mechanism::
952
953 # SELECT ... FROM ONLY ...
954 result = table.select().with_hint(table, 'ONLY', 'postgresql')
955 print(result.fetchall())
956
957 # UPDATE ONLY ...
958 table.update(values=dict(foo='bar')).with_hint('ONLY',
959 dialect_name='postgresql')
960
961 # DELETE FROM ONLY ...
962 table.delete().with_hint('ONLY', dialect_name='postgresql')
963
964
965.. _postgresql_indexes:
966
967PostgreSQL-Specific Index Options
968---------------------------------
969
970Several extensions to the :class:`.Index` construct are available, specific
971to the PostgreSQL dialect.
972
973Covering Indexes
974^^^^^^^^^^^^^^^^
975
976The ``postgresql_include`` option renders INCLUDE(colname) for the given
977string names::
978
979 Index("my_index", table.c.x, postgresql_include=['y'])
980
981would render the index as ``CREATE INDEX my_index ON table (x) INCLUDE (y)``
982
983Note that this feature requires PostgreSQL 11 or later.
984
985.. versionadded:: 1.4
986
987.. _postgresql_partial_indexes:
988
989Partial Indexes
990^^^^^^^^^^^^^^^
991
992Partial indexes add criterion to the index definition so that the index is
993applied to a subset of rows. These can be specified on :class:`.Index`
994using the ``postgresql_where`` keyword argument::
995
996 Index('my_index', my_table.c.id, postgresql_where=my_table.c.value > 10)
997
998.. _postgresql_operator_classes:
999
1000Operator Classes
1001^^^^^^^^^^^^^^^^
1002
1003PostgreSQL allows the specification of an *operator class* for each column of
1004an index (see
1005https://www.postgresql.org/docs/current/interactive/indexes-opclass.html).
1006The :class:`.Index` construct allows these to be specified via the
1007``postgresql_ops`` keyword argument::
1008
1009 Index(
1010 'my_index', my_table.c.id, my_table.c.data,
1011 postgresql_ops={
1012 'data': 'text_pattern_ops',
1013 'id': 'int4_ops'
1014 })
1015
1016Note that the keys in the ``postgresql_ops`` dictionaries are the
1017"key" name of the :class:`_schema.Column`, i.e. the name used to access it from
1018the ``.c`` collection of :class:`_schema.Table`, which can be configured to be
1019different than the actual name of the column as expressed in the database.
1020
1021If ``postgresql_ops`` is to be used against a complex SQL expression such
1022as a function call, then to apply to the column it must be given a label
1023that is identified in the dictionary by name, e.g.::
1024
1025 Index(
1026 'my_index', my_table.c.id,
1027 func.lower(my_table.c.data).label('data_lower'),
1028 postgresql_ops={
1029 'data_lower': 'text_pattern_ops',
1030 'id': 'int4_ops'
1031 })
1032
1033Operator classes are also supported by the
1034:class:`_postgresql.ExcludeConstraint` construct using the
1035:paramref:`_postgresql.ExcludeConstraint.ops` parameter. See that parameter for
1036details.
1037
1038.. versionadded:: 1.3.21 added support for operator classes with
1039 :class:`_postgresql.ExcludeConstraint`.
1040
1041
1042Index Types
1043^^^^^^^^^^^
1044
1045PostgreSQL provides several index types: B-Tree, Hash, GiST, and GIN, as well
1046as the ability for users to create their own (see
1047https://www.postgresql.org/docs/current/static/indexes-types.html). These can be
1048specified on :class:`.Index` using the ``postgresql_using`` keyword argument::
1049
1050 Index('my_index', my_table.c.data, postgresql_using='gin')
1051
1052The value passed to the keyword argument will be simply passed through to the
1053underlying CREATE INDEX command, so it *must* be a valid index type for your
1054version of PostgreSQL.
1055
1056.. _postgresql_index_storage:
1057
1058Index Storage Parameters
1059^^^^^^^^^^^^^^^^^^^^^^^^
1060
1061PostgreSQL allows storage parameters to be set on indexes. The storage
1062parameters available depend on the index method used by the index. Storage
1063parameters can be specified on :class:`.Index` using the ``postgresql_with``
1064keyword argument::
1065
1066 Index('my_index', my_table.c.data, postgresql_with={"fillfactor": 50})
1067
1068PostgreSQL allows to define the tablespace in which to create the index.
1069The tablespace can be specified on :class:`.Index` using the
1070``postgresql_tablespace`` keyword argument::
1071
1072 Index('my_index', my_table.c.data, postgresql_tablespace='my_tablespace')
1073
1074Note that the same option is available on :class:`_schema.Table` as well.
1075
1076.. _postgresql_index_concurrently:
1077
1078Indexes with CONCURRENTLY
1079^^^^^^^^^^^^^^^^^^^^^^^^^
1080
1081The PostgreSQL index option CONCURRENTLY is supported by passing the
1082flag ``postgresql_concurrently`` to the :class:`.Index` construct::
1083
1084 tbl = Table('testtbl', m, Column('data', Integer))
1085
1086 idx1 = Index('test_idx1', tbl.c.data, postgresql_concurrently=True)
1087
1088The above index construct will render DDL for CREATE INDEX, assuming
1089PostgreSQL 8.2 or higher is detected or for a connection-less dialect, as::
1090
1091 CREATE INDEX CONCURRENTLY test_idx1 ON testtbl (data)
1092
1093For DROP INDEX, assuming PostgreSQL 9.2 or higher is detected or for
1094a connection-less dialect, it will emit::
1095
1096 DROP INDEX CONCURRENTLY test_idx1
1097
1098When using CONCURRENTLY, the PostgreSQL database requires that the statement
1099be invoked outside of a transaction block. The Python DBAPI enforces that
1100even for a single statement, a transaction is present, so to use this
1101construct, the DBAPI's "autocommit" mode must be used::
1102
1103 metadata = MetaData()
1104 table = Table(
1105 "foo", metadata,
1106 Column("id", String))
1107 index = Index(
1108 "foo_idx", table.c.id, postgresql_concurrently=True)
1109
1110 with engine.connect() as conn:
1111 with conn.execution_options(isolation_level='AUTOCOMMIT'):
1112 table.create(conn)
1113
1114.. seealso::
1115
1116 :ref:`postgresql_isolation_level`
1117
1118.. _postgresql_index_reflection:
1119
1120PostgreSQL Index Reflection
1121---------------------------
1122
1123The PostgreSQL database creates a UNIQUE INDEX implicitly whenever the
1124UNIQUE CONSTRAINT construct is used. When inspecting a table using
1125:class:`_reflection.Inspector`, the :meth:`_reflection.Inspector.get_indexes`
1126and the :meth:`_reflection.Inspector.get_unique_constraints`
1127will report on these
1128two constructs distinctly; in the case of the index, the key
1129``duplicates_constraint`` will be present in the index entry if it is
1130detected as mirroring a constraint. When performing reflection using
1131``Table(..., autoload_with=engine)``, the UNIQUE INDEX is **not** returned
1132in :attr:`_schema.Table.indexes` when it is detected as mirroring a
1133:class:`.UniqueConstraint` in the :attr:`_schema.Table.constraints` collection
1134.
1135
1136Special Reflection Options
1137--------------------------
1138
1139The :class:`_reflection.Inspector`
1140used for the PostgreSQL backend is an instance
1141of :class:`.PGInspector`, which offers additional methods::
1142
1143 from sqlalchemy import create_engine, inspect
1144
1145 engine = create_engine("postgresql+psycopg2://localhost/test")
1146 insp = inspect(engine) # will be a PGInspector
1147
1148 print(insp.get_enums())
1149
1150.. autoclass:: PGInspector
1151 :members:
1152
1153.. _postgresql_table_options:
1154
1155PostgreSQL Table Options
1156------------------------
1157
1158Several options for CREATE TABLE are supported directly by the PostgreSQL
1159dialect in conjunction with the :class:`_schema.Table` construct:
1160
1161* ``INHERITS``::
1162
1163 Table("some_table", metadata, ..., postgresql_inherits="some_supertable")
1164
1165 Table("some_table", metadata, ..., postgresql_inherits=("t1", "t2", ...))
1166
1167* ``ON COMMIT``::
1168
1169 Table("some_table", metadata, ..., postgresql_on_commit='PRESERVE ROWS')
1170
1171* ``PARTITION BY``::
1172
1173 Table("some_table", metadata, ...,
1174 postgresql_partition_by='LIST (part_column)')
1175
1176 .. versionadded:: 1.2.6
1177
1178* ``TABLESPACE``::
1179
1180 Table("some_table", metadata, ..., postgresql_tablespace='some_tablespace')
1181
1182 The above option is also available on the :class:`.Index` construct.
1183
1184* ``USING``::
1185
1186 Table("some_table", metadata, ..., postgresql_using='heap')
1187
1188 .. versionadded:: 2.0.26
1189
1190* ``WITH OIDS``::
1191
1192 Table("some_table", metadata, ..., postgresql_with_oids=True)
1193
1194* ``WITHOUT OIDS``::
1195
1196 Table("some_table", metadata, ..., postgresql_with_oids=False)
1197
1198.. seealso::
1199
1200 `PostgreSQL CREATE TABLE options