Coverage for /pythoncovmergedfiles/medio/medio/usr/local/lib/python3.8/site-packages/sqlalchemy/dialects/postgresql/base.py: 26%

1# postgresql/base.py 

2# Copyright (C) 2005-2022 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 

8r""" 

9.. dialect:: postgresql 

10 :name: PostgreSQL 

11 :full_support: 9.6, 10, 11, 12, 13, 14 

12 :normal_support: 9.6+ 

13 :best_effort: 8+ 

14 

15.. _postgresql_sequences: 

16 

17Sequences/SERIAL/IDENTITY 

18------------------------- 

19 

20PostgreSQL supports sequences, and SQLAlchemy uses these as the default means 

21of creating new primary key values for integer-based primary key columns. When 

22creating tables, SQLAlchemy will issue the ``SERIAL`` datatype for 

23integer-based primary key columns, which generates a sequence and server side 

24default corresponding to the column. 

25 

26To specify a specific named sequence to be used for primary key generation, 

27use the :func:`~sqlalchemy.schema.Sequence` construct:: 

28 

29 Table('sometable', metadata, 

30 Column('id', Integer, Sequence('some_id_seq'), primary_key=True) 

31 ) 

32 

33When SQLAlchemy issues a single INSERT statement, to fulfill the contract of 

34having the "last insert identifier" available, a RETURNING clause is added to 

35the INSERT statement which specifies the primary key columns should be 

36returned after the statement completes. The RETURNING functionality only takes 

37place if PostgreSQL 8.2 or later is in use. As a fallback approach, the 

38sequence, whether specified explicitly or implicitly via ``SERIAL``, is 

39executed independently beforehand, the returned value to be used in the 

40subsequent insert. Note that when an 

41:func:`~sqlalchemy.sql.expression.insert()` construct is executed using 

42"executemany" semantics, the "last inserted identifier" functionality does not 

43apply; no RETURNING clause is emitted nor is the sequence pre-executed in this 

44case. 

45 

46To force the usage of RETURNING by default off, specify the flag 

47``implicit_returning=False`` to :func:`_sa.create_engine`. 

48 

49PostgreSQL 10 and above IDENTITY columns 

50^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 

51 

52PostgreSQL 10 and above have a new IDENTITY feature that supersedes the use 

53of SERIAL. The :class:`_schema.Identity` construct in a 

54:class:`_schema.Column` can be used to control its behavior:: 

55 

56 from sqlalchemy import Table, Column, MetaData, Integer, Computed 

57 

58 metadata = MetaData() 

59 

60 data = Table( 

61 "data", 

62 metadata, 

63 Column( 

64 'id', Integer, Identity(start=42, cycle=True), primary_key=True 

65 ), 

66 Column('data', String) 

67 ) 

68 

69The CREATE TABLE for the above :class:`_schema.Table` object would be: 

70 

71.. sourcecode:: sql 

72 

73 CREATE TABLE data ( 

74 id INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 42 CYCLE), 

75 data VARCHAR, 

76 PRIMARY KEY (id) 

77 ) 

78 

79.. versionchanged:: 1.4 Added :class:`_schema.Identity` construct 

80 in a :class:`_schema.Column` to specify the option of an autoincrementing 

81 column. 

82 

83.. note:: 

84 

85 Previous versions of SQLAlchemy did not have built-in support for rendering 

86 of IDENTITY, and could use the following compilation hook to replace 

87 occurrences of SERIAL with IDENTITY:: 

88 

89 from sqlalchemy.schema import CreateColumn 

90 from sqlalchemy.ext.compiler import compiles 

91 

92 

93 @compiles(CreateColumn, 'postgresql') 

94 def use_identity(element, compiler, **kw): 

95 text = compiler.visit_create_column(element, **kw) 

96 text = text.replace( 

97 "SERIAL", "INT GENERATED BY DEFAULT AS IDENTITY" 

98 ) 

99 return text 

100 

101 Using the above, a table such as:: 

102 

103 t = Table( 

104 't', m, 

105 Column('id', Integer, primary_key=True), 

106 Column('data', String) 

107 ) 

108 

109 Will generate on the backing database as:: 

110 

111 CREATE TABLE t ( 

112 id INT GENERATED BY DEFAULT AS IDENTITY, 

113 data VARCHAR, 

114 PRIMARY KEY (id) 

115 ) 

116 

117.. _postgresql_ss_cursors: 

118 

119Server Side Cursors 

120------------------- 

121 

122Server-side cursor support is available for the psycopg2, asyncpg 

123dialects and may also be available in others. 

124 

125Server side cursors are enabled on a per-statement basis by using the 

126:paramref:`.Connection.execution_options.stream_results` connection execution 

127option:: 

128 

129 with engine.connect() as conn: 

130 result = conn.execution_options(stream_results=True).execute(text("select * from table")) 

131 

132Note that some kinds of SQL statements may not be supported with 

133server side cursors; generally, only SQL statements that return rows should be 

134used with this option. 

135 

136.. deprecated:: 1.4 The dialect-level server_side_cursors flag is deprecated 

137 and will be removed in a future release. Please use the 

138 :paramref:`_engine.Connection.stream_results` execution option for 

139 unbuffered cursor support. 

140 

141.. seealso:: 

142 

143 :ref:`engine_stream_results` 

144 

145.. _postgresql_isolation_level: 

146 

147Transaction Isolation Level 

148--------------------------- 

149 

150Most SQLAlchemy dialects support setting of transaction isolation level 

151using the :paramref:`_sa.create_engine.isolation_level` parameter 

152at the :func:`_sa.create_engine` level, and at the :class:`_engine.Connection` 

153level via the :paramref:`.Connection.execution_options.isolation_level` 

154parameter. 

155 

156For PostgreSQL dialects, this feature works either by making use of the 

157DBAPI-specific features, such as psycopg2's isolation level flags which will 

158embed the isolation level setting inline with the ``"BEGIN"`` statement, or for 

159DBAPIs with no direct support by emitting ``SET SESSION CHARACTERISTICS AS 

160TRANSACTION ISOLATION LEVEL <level>`` ahead of the ``"BEGIN"`` statement 

161emitted by the DBAPI. For the special AUTOCOMMIT isolation level, 

162DBAPI-specific techniques are used which is typically an ``.autocommit`` 

163flag on the DBAPI connection object. 

164 

165To set isolation level using :func:`_sa.create_engine`:: 

166 

167 engine = create_engine( 

168 "postgresql+pg8000://scott:tiger@localhost/test", 

169 isolation_level = "REPEATABLE READ" 

170 ) 

171 

172To set using per-connection execution options:: 

173 

174 with engine.connect() as conn: 

175 conn = conn.execution_options( 

176 isolation_level="REPEATABLE READ" 

177 ) 

178 with conn.begin(): 

179 # ... work with transaction 

180 

181There are also more options for isolation level configurations, such as 

182"sub-engine" objects linked to a main :class:`_engine.Engine` which each apply 

183different isolation level settings. See the discussion at 

184:ref:`dbapi_autocommit` for background. 

185 

186Valid values for ``isolation_level`` on most PostgreSQL dialects include: 

187 

188* ``READ COMMITTED`` 

189* ``READ UNCOMMITTED`` 

190* ``REPEATABLE READ`` 

191* ``SERIALIZABLE`` 

192* ``AUTOCOMMIT`` 

193 

194.. seealso:: 

195 

196 :ref:`dbapi_autocommit` 

197 

198 :ref:`postgresql_readonly_deferrable` 

199 

200 :ref:`psycopg2_isolation_level` 

201 

202 :ref:`pg8000_isolation_level` 

203 

204.. _postgresql_readonly_deferrable: 

205 

206Setting READ ONLY / DEFERRABLE 

207------------------------------ 

208 

209Most PostgreSQL dialects support setting the "READ ONLY" and "DEFERRABLE" 

210characteristics of the transaction, which is in addition to the isolation level 

211setting. These two attributes can be established either in conjunction with or 

212independently of the isolation level by passing the ``postgresql_readonly`` and 

213``postgresql_deferrable`` flags with 

214:meth:`_engine.Connection.execution_options`. The example below illustrates 

215passing the ``"SERIALIZABLE"`` isolation level at the same time as setting 

216"READ ONLY" and "DEFERRABLE":: 

217 

218 with engine.connect() as conn: 

219 conn = conn.execution_options( 

220 isolation_level="SERIALIZABLE", 

221 postgresql_readonly=True, 

222 postgresql_deferrable=True 

223 ) 

224 with conn.begin(): 

225 # ... work with transaction 

226 

227Note that some DBAPIs such as asyncpg only support "readonly" with 

228SERIALIZABLE isolation. 

229 

230.. versionadded:: 1.4 added support for the ``postgresql_readonly`` 

231 and ``postgresql_deferrable`` execution options. 

232 

233.. _postgresql_reset_on_return: 

234 

235Temporary Table / Resource Reset for Connection Pooling 

236------------------------------------------------------- 

237 

238The :class:`.QueuePool` connection pool implementation used 

239by the SQLAlchemy :class:`_sa.Engine` object includes 

240:ref:`reset on return <pool_reset_on_return>` behavior that will invoke 

241the DBAPI ``.rollback()`` method when connections are returned to the pool. 

242While this rollback will clear out the immediate state used by the previous 

243transaction, it does not cover a wider range of session-level state, including 

244temporary tables as well as other server state such as prepared statement 

245handles and statement caches. The PostgreSQL database includes a variety 

246of commands which may be used to reset this state, including 

247``DISCARD``, ``RESET``, ``DEALLOCATE``, and ``UNLISTEN``. 

248 

249 

250To install 

251one or more of these commands as the means of performing reset-on-return, 

252the :meth:`.PoolEvents.reset` event hook may be used, as demonstrated 

253in the example below (**requires SQLAlchemy 1.4.43 or greater**). The implementation 

254will end transactions in progress as well as discard temporary tables 

255using the ``CLOSE``, ``RESET`` and ``DISCARD`` commands; see the PostgreSQL 

256documentation for background on what each of these statements do. 

257 

258The :paramref:`_sa.create_engine.pool_reset_on_return` parameter 

259is set to ``None`` so that the custom scheme can replace the default behavior 

260completely. The custom hook implementation calls ``.rollback()`` in any case, 

261as it's usually important that the DBAPI's own tracking of commit/rollback 

262will remain consistent with the state of the transaction:: 

263 

264 

265 from sqlalchemy import create_engine 

266 from sqlalchemy import event 

267 

268 postgresql_engine = create_engine( 

269 "postgresql+pyscopg2://scott:tiger@hostname/dbname", 

270 

271 # disable default reset-on-return scheme 

272 pool_reset_on_return=None, 

273 ) 

274 

275 

276 @event.listens_for(postgresql_engine, "reset") 

277 def _reset_postgresql(dbapi_connection, connection_record, reset_state): 

278 dbapi_connection.execute("CLOSE ALL") 

279 dbapi_connection.execute("RESET ALL") 

280 dbapi_connection.execute("DISCARD TEMP") 

281 

282 # so that the DBAPI itself knows that the connection has been 

283 # reset 

284 dbapi_connection.rollback() 

285 

286.. versionchanged:: 1.4.43 Ensured the :meth:`.PoolEvents.reset` event 

287 is invoked for all "reset" occurrences, so that it's appropriate 

288 as a place for custom "reset" handlers. Previous schemes which 

289 use the :meth:`.PoolEvents.checkin` handler remain usable as well. 

290 

291.. seealso:: 

292 

293 :ref:`pool_reset_on_return` - in the :ref:`pooling_toplevel` documentation 

294 

295.. _postgresql_alternate_search_path: 

296 

297Setting Alternate Search Paths on Connect 

298------------------------------------------ 

299 

300The PostgreSQL ``search_path`` variable refers to the list of schema names 

301that will be implicitly referred towards when a particular table or other 

302object is referenced in a SQL statement. As detailed in the next section 

303:ref:`postgresql_schema_reflection`, SQLAlchemy is generally organized around 

304the concept of keeping this variable at its default value of ``public``, 

305however, in order to have it set to any arbitrary name or names when connections 

306are used automatically, the "SET SESSION search_path" command may be invoked 

307for all connections in a pool using the following event handler, as discussed 

308at :ref:`schema_set_default_connections`:: 

309 

310 from sqlalchemy import event 

311 from sqlalchemy import create_engine 

312 

313 engine = create_engine("postgresql+psycopg2://scott:tiger@host/dbname") 

314 

315 @event.listens_for(engine, "connect", insert=True) 

316 def set_search_path(dbapi_connection, connection_record): 

317 existing_autocommit = dbapi_connection.autocommit 

318 dbapi_connection.autocommit = True 

319 cursor = dbapi_connection.cursor() 

320 cursor.execute("SET SESSION search_path='%s'" % schema_name) 

321 cursor.close() 

322 dbapi_connection.autocommit = existing_autocommit 

323 

324The reason the recipe is complicated by use of the ``.autocommit`` DBAPI 

325attribute is so that when the ``SET SESSION search_path`` directive is invoked, 

326it is invoked outside of the scope of any transaction and therefore will not 

327be reverted when the DBAPI connection has a rollback. 

328 

329.. seealso:: 

330 

331 :ref:`schema_set_default_connections` - in the :ref:`metadata_toplevel` documentation 

332 

333 

334 

335 

336.. _postgresql_schema_reflection: 

337 

338Remote-Schema Table Introspection and PostgreSQL search_path 

339------------------------------------------------------------ 

340 

341.. admonition:: Section Best Practices Summarized 

342 

343 keep the ``search_path`` variable set to its default of ``public``, without 

344 any other schema names. For other schema names, name these explicitly 

345 within :class:`_schema.Table` definitions. Alternatively, the 

346 ``postgresql_ignore_search_path`` option will cause all reflected 

347 :class:`_schema.Table` objects to have a :attr:`_schema.Table.schema` 

348 attribute set up. 

349 

350The PostgreSQL dialect can reflect tables from any schema, as outlined in 

351:ref:`metadata_reflection_schemas`. 

352 

353With regards to tables which these :class:`_schema.Table` 

354objects refer to via foreign key constraint, a decision must be made as to how 

355the ``.schema`` is represented in those remote tables, in the case where that 

356remote schema name is also a member of the current 

357`PostgreSQL search path 

358<https://www.postgresql.org/docs/current/static/ddl-schemas.html#DDL-SCHEMAS-PATH>`_. 

359 

360By default, the PostgreSQL dialect mimics the behavior encouraged by 

361PostgreSQL's own ``pg_get_constraintdef()`` builtin procedure. This function 

362returns a sample definition for a particular foreign key constraint, 

363omitting the referenced schema name from that definition when the name is 

364also in the PostgreSQL schema search path. The interaction below 

365illustrates this behavior:: 

366 

367 test=> CREATE TABLE test_schema.referred(id INTEGER PRIMARY KEY); 

368 CREATE TABLE 

369 test=> CREATE TABLE referring( 

370 test(> id INTEGER PRIMARY KEY, 

371 test(> referred_id INTEGER REFERENCES test_schema.referred(id)); 

372 CREATE TABLE 

373 test=> SET search_path TO public, test_schema; 

374 test=> SELECT pg_catalog.pg_get_constraintdef(r.oid, true) FROM 

375 test-> pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n 

376 test-> ON n.oid = c.relnamespace 

377 test-> JOIN pg_catalog.pg_constraint r ON c.oid = r.conrelid 

378 test-> WHERE c.relname='referring' AND r.contype = 'f' 

379 test-> ; 

380 pg_get_constraintdef 

381 --------------------------------------------------- 

382 FOREIGN KEY (referred_id) REFERENCES referred(id) 

383 (1 row) 

384 

385Above, we created a table ``referred`` as a member of the remote schema 

386``test_schema``, however when we added ``test_schema`` to the 

387PG ``search_path`` and then asked ``pg_get_constraintdef()`` for the 

388``FOREIGN KEY`` syntax, ``test_schema`` was not included in the output of 

389the function. 

390 

391On the other hand, if we set the search path back to the typical default 

392of ``public``:: 

393 

394 test=> SET search_path TO public; 

395 SET 

396 

397The same query against ``pg_get_constraintdef()`` now returns the fully 

398schema-qualified name for us:: 

399 

400 test=> SELECT pg_catalog.pg_get_constraintdef(r.oid, true) FROM 

401 test-> pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n 

402 test-> ON n.oid = c.relnamespace 

403 test-> JOIN pg_catalog.pg_constraint r ON c.oid = r.conrelid 

404 test-> WHERE c.relname='referring' AND r.contype = 'f'; 

405 pg_get_constraintdef 

406 --------------------------------------------------------------- 

407 FOREIGN KEY (referred_id) REFERENCES test_schema.referred(id) 

408 (1 row) 

409 

410SQLAlchemy will by default use the return value of ``pg_get_constraintdef()`` 

411in order to determine the remote schema name. That is, if our ``search_path`` 

412were set to include ``test_schema``, and we invoked a table 

413reflection process as follows:: 

414 

415 >>> from sqlalchemy import Table, MetaData, create_engine, text 

416 >>> engine = create_engine("postgresql://scott:tiger@localhost/test") 

417 >>> with engine.connect() as conn: 

418 ... conn.execute(text("SET search_path TO test_schema, public")) 

419 ... metadata_obj = MetaData() 

420 ... referring = Table('referring', metadata_obj, 

421 ... autoload_with=conn) 

422 ... 

423 <sqlalchemy.engine.result.CursorResult object at 0x101612ed0> 

424 

425The above process would deliver to the :attr:`_schema.MetaData.tables` 

426collection 

427``referred`` table named **without** the schema:: 

428 

429 >>> metadata_obj.tables['referred'].schema is None 

430 True 

431 

432To alter the behavior of reflection such that the referred schema is 

433maintained regardless of the ``search_path`` setting, use the 

434``postgresql_ignore_search_path`` option, which can be specified as a 

435dialect-specific argument to both :class:`_schema.Table` as well as 

436:meth:`_schema.MetaData.reflect`:: 

437 

438 >>> with engine.connect() as conn: 

439 ... conn.execute(text("SET search_path TO test_schema, public")) 

440 ... metadata_obj = MetaData() 

441 ... referring = Table('referring', metadata_obj, 

442 ... autoload_with=conn, 

443 ... postgresql_ignore_search_path=True) 

444 ... 

445 <sqlalchemy.engine.result.CursorResult object at 0x1016126d0> 

446 

447We will now have ``test_schema.referred`` stored as schema-qualified:: 

448 

449 >>> metadata_obj.tables['test_schema.referred'].schema 

450 'test_schema' 

451 

452.. sidebar:: Best Practices for PostgreSQL Schema reflection 

453 

454 The description of PostgreSQL schema reflection behavior is complex, and 

455 is the product of many years of dealing with widely varied use cases and 

456 user preferences. But in fact, there's no need to understand any of it if 

457 you just stick to the simplest use pattern: leave the ``search_path`` set 

458 to its default of ``public`` only, never refer to the name ``public`` as 

459 an explicit schema name otherwise, and refer to all other schema names 

460 explicitly when building up a :class:`_schema.Table` object. The options 

461 described here are only for those users who can't, or prefer not to, stay 

462 within these guidelines. 

463 

464Note that **in all cases**, the "default" schema is always reflected as 

465``None``. The "default" schema on PostgreSQL is that which is returned by the 

466PostgreSQL ``current_schema()`` function. On a typical PostgreSQL 

467installation, this is the name ``public``. So a table that refers to another 

468which is in the ``public`` (i.e. default) schema will always have the 

469``.schema`` attribute set to ``None``. 

470 

471.. seealso:: 

472 

473 :ref:`reflection_schema_qualified_interaction` - discussion of the issue 

474 from a backend-agnostic perspective 

475 

476 `The Schema Search Path 

477 <https://www.postgresql.org/docs/current/static/ddl-schemas.html#DDL-SCHEMAS-PATH>`_ 

478 - on the PostgreSQL website. 

479 

480INSERT/UPDATE...RETURNING 

481------------------------- 

482 

483The dialect supports PG 8.2's ``INSERT..RETURNING``, ``UPDATE..RETURNING`` and 

484``DELETE..RETURNING`` syntaxes. ``INSERT..RETURNING`` is used by default 

485for single-row INSERT statements in order to fetch newly generated 

486primary key identifiers. To specify an explicit ``RETURNING`` clause, 

487use the :meth:`._UpdateBase.returning` method on a per-statement basis:: 

488 

489 # INSERT..RETURNING 

490 result = table.insert().returning(table.c.col1, table.c.col2).\ 

491 values(name='foo') 

492 print(result.fetchall()) 

493 

494 # UPDATE..RETURNING 

495 result = table.update().returning(table.c.col1, table.c.col2).\ 

496 where(table.c.name=='foo').values(name='bar') 

497 print(result.fetchall()) 

498 

499 # DELETE..RETURNING 

500 result = table.delete().returning(table.c.col1, table.c.col2).\ 

501 where(table.c.name=='foo') 

502 print(result.fetchall()) 

503 

504.. _postgresql_insert_on_conflict: 

505 

506INSERT...ON CONFLICT (Upsert) 

507------------------------------ 

508 

509Starting with version 9.5, PostgreSQL allows "upserts" (update or insert) of 

510rows into a table via the ``ON CONFLICT`` clause of the ``INSERT`` statement. A 

511candidate row will only be inserted if that row does not violate any unique 

512constraints. In the case of a unique constraint violation, a secondary action 

513can occur which can be either "DO UPDATE", indicating that the data in the 

514target row should be updated, or "DO NOTHING", which indicates to silently skip 

515this row. 

516 

517Conflicts are determined using existing unique constraints and indexes. These 

518constraints may be identified either using their name as stated in DDL, 

519or they may be inferred by stating the columns and conditions that comprise 

520the indexes. 

521 

522SQLAlchemy provides ``ON CONFLICT`` support via the PostgreSQL-specific 

523:func:`_postgresql.insert()` function, which provides 

524the generative methods :meth:`_postgresql.Insert.on_conflict_do_update` 

525and :meth:`~.postgresql.Insert.on_conflict_do_nothing`: 

526 

527.. sourcecode:: pycon+sql 

528 

529 >>> from sqlalchemy.dialects.postgresql import insert 

530 >>> insert_stmt = insert(my_table).values( 

531 ... id='some_existing_id', 

532 ... data='inserted value') 

533 >>> do_nothing_stmt = insert_stmt.on_conflict_do_nothing( 

534 ... index_elements=['id'] 

535 ... ) 

536 >>> print(do_nothing_stmt) 

537 {opensql}INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s) 

538 ON CONFLICT (id) DO NOTHING 

539 {stop} 

540 

541 >>> do_update_stmt = insert_stmt.on_conflict_do_update( 

542 ... constraint='pk_my_table', 

543 ... set_=dict(data='updated value') 

544 ... ) 

545 >>> print(do_update_stmt) 

546 {opensql}INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s) 

547 ON CONFLICT ON CONSTRAINT pk_my_table DO UPDATE SET data = %(param_1)s 

548 

549.. versionadded:: 1.1 

550 

551.. seealso:: 

552 

553 `INSERT .. ON CONFLICT 

554 <https://www.postgresql.org/docs/current/static/sql-insert.html#SQL-ON-CONFLICT>`_ 

555 - in the PostgreSQL documentation. 

556 

557Specifying the Target 

558^^^^^^^^^^^^^^^^^^^^^ 

559 

560Both methods supply the "target" of the conflict using either the 

561named constraint or by column inference: 

562 

563* The :paramref:`_postgresql.Insert.on_conflict_do_update.index_elements` argument 

564 specifies a sequence containing string column names, :class:`_schema.Column` 

565 objects, and/or SQL expression elements, which would identify a unique 

566 index: 

567 

568 .. sourcecode:: pycon+sql 

569 

570 >>> do_update_stmt = insert_stmt.on_conflict_do_update( 

571 ... index_elements=['id'], 

572 ... set_=dict(data='updated value') 

573 ... ) 

574 >>> print(do_update_stmt) 

575 {opensql}INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s) 

576 ON CONFLICT (id) DO UPDATE SET data = %(param_1)s 

577 {stop} 

578 

579 >>> do_update_stmt = insert_stmt.on_conflict_do_update( 

580 ... index_elements=[my_table.c.id], 

581 ... set_=dict(data='updated value') 

582 ... ) 

583 >>> print(do_update_stmt) 

584 {opensql}INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s) 

585 ON CONFLICT (id) DO UPDATE SET data = %(param_1)s 

586 

587* When using :paramref:`_postgresql.Insert.on_conflict_do_update.index_elements` to 

588 infer an index, a partial index can be inferred by also specifying the 

589 use the :paramref:`_postgresql.Insert.on_conflict_do_update.index_where` parameter: 

590 

591 .. sourcecode:: pycon+sql 

592 

593 >>> stmt = insert(my_table).values(user_email='a@b.com', data='inserted data') 

594 >>> stmt = stmt.on_conflict_do_update( 

595 ... index_elements=[my_table.c.user_email], 

596 ... index_where=my_table.c.user_email.like('%@gmail.com'), 

597 ... set_=dict(data=stmt.excluded.data) 

598 ... ) 

599 >>> print(stmt) 

600 {opensql}INSERT INTO my_table (data, user_email) 

601 VALUES (%(data)s, %(user_email)s) ON CONFLICT (user_email) 

602 WHERE user_email LIKE %(user_email_1)s DO UPDATE SET data = excluded.data 

603 

604* The :paramref:`_postgresql.Insert.on_conflict_do_update.constraint` argument is 

605 used to specify an index directly rather than inferring it. This can be 

606 the name of a UNIQUE constraint, a PRIMARY KEY constraint, or an INDEX: 

607 

608 .. sourcecode:: pycon+sql 

609 

610 >>> do_update_stmt = insert_stmt.on_conflict_do_update( 

611 ... constraint='my_table_idx_1', 

612 ... set_=dict(data='updated value') 

613 ... ) 

614 >>> print(do_update_stmt) 

615 {opensql}INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s) 

616 ON CONFLICT ON CONSTRAINT my_table_idx_1 DO UPDATE SET data = %(param_1)s 

617 {stop} 

618 

619 >>> do_update_stmt = insert_stmt.on_conflict_do_update( 

620 ... constraint='my_table_pk', 

621 ... set_=dict(data='updated value') 

622 ... ) 

623 >>> print(do_update_stmt) 

624 {opensql}INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s) 

625 ON CONFLICT ON CONSTRAINT my_table_pk DO UPDATE SET data = %(param_1)s 

626 {stop} 

627 

628* The :paramref:`_postgresql.Insert.on_conflict_do_update.constraint` argument may 

629 also refer to a SQLAlchemy construct representing a constraint, 

630 e.g. :class:`.UniqueConstraint`, :class:`.PrimaryKeyConstraint`, 

631 :class:`.Index`, or :class:`.ExcludeConstraint`. In this use, 

632 if the constraint has a name, it is used directly. Otherwise, if the 

633 constraint is unnamed, then inference will be used, where the expressions 

634 and optional WHERE clause of the constraint will be spelled out in the 

635 construct. This use is especially convenient 

636 to refer to the named or unnamed primary key of a :class:`_schema.Table` 

637 using the 

638 :attr:`_schema.Table.primary_key` attribute: 

639 

640 .. sourcecode:: pycon+sql 

641 

642 >>> do_update_stmt = insert_stmt.on_conflict_do_update( 

643 ... constraint=my_table.primary_key, 

644 ... set_=dict(data='updated value') 

645 ... ) 

646 >>> print(do_update_stmt) 

647 {opensql}INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s) 

648 ON CONFLICT (id) DO UPDATE SET data = %(param_1)s 

649 

650The SET Clause 

651^^^^^^^^^^^^^^^ 

652 

653``ON CONFLICT...DO UPDATE`` is used to perform an update of the already 

654existing row, using any combination of new values as well as values 

655from the proposed insertion. These values are specified using the 

656:paramref:`_postgresql.Insert.on_conflict_do_update.set_` parameter. This 

657parameter accepts a dictionary which consists of direct values 

658for UPDATE: 

659 

660.. sourcecode:: pycon+sql 

661 

662 >>> stmt = insert(my_table).values(id='some_id', data='inserted value') 

663 >>> do_update_stmt = stmt.on_conflict_do_update( 

664 ... index_elements=['id'], 

665 ... set_=dict(data='updated value') 

666 ... ) 

667 >>> print(do_update_stmt) 

668 {opensql}INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s) 

669 ON CONFLICT (id) DO UPDATE SET data = %(param_1)s 

670 

671.. warning:: 

672 

673 The :meth:`_expression.Insert.on_conflict_do_update` 

674 method does **not** take into 

675 account Python-side default UPDATE values or generation functions, e.g. 

676 those specified using :paramref:`_schema.Column.onupdate`. 

677 These values will not be exercised for an ON CONFLICT style of UPDATE, 

678 unless they are manually specified in the 

679 :paramref:`_postgresql.Insert.on_conflict_do_update.set_` dictionary. 

680 

681Updating using the Excluded INSERT Values 

682^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 

683 

684In order to refer to the proposed insertion row, the special alias 

685:attr:`~.postgresql.Insert.excluded` is available as an attribute on 

686the :class:`_postgresql.Insert` object; this object is a 

687:class:`_expression.ColumnCollection` 

688which alias contains all columns of the target 

689table: 

690 

691.. sourcecode:: pycon+sql 

692 

693 >>> stmt = insert(my_table).values( 

694 ... id='some_id', 

695 ... data='inserted value', 

696 ... author='jlh' 

697 ... ) 

698 >>> do_update_stmt = stmt.on_conflict_do_update( 

699 ... index_elements=['id'], 

700 ... set_=dict(data='updated value', author=stmt.excluded.author) 

701 ... ) 

702 >>> print(do_update_stmt) 

703 {opensql}INSERT INTO my_table (id, data, author) 

704 VALUES (%(id)s, %(data)s, %(author)s) 

705 ON CONFLICT (id) DO UPDATE SET data = %(param_1)s, author = excluded.author 

706 

707Additional WHERE Criteria 

708^^^^^^^^^^^^^^^^^^^^^^^^^ 

709 

710The :meth:`_expression.Insert.on_conflict_do_update` method also accepts 

711a WHERE clause using the :paramref:`_postgresql.Insert.on_conflict_do_update.where` 

712parameter, which will limit those rows which receive an UPDATE: 

713 

714.. sourcecode:: pycon+sql 

715 

716 >>> stmt = insert(my_table).values( 

717 ... id='some_id', 

718 ... data='inserted value', 

719 ... author='jlh' 

720 ... ) 

721 >>> on_update_stmt = stmt.on_conflict_do_update( 

722 ... index_elements=['id'], 

723 ... set_=dict(data='updated value', author=stmt.excluded.author), 

724 ... where=(my_table.c.status == 2) 

725 ... ) 

726 >>> print(on_update_stmt) 

727 {opensql}INSERT INTO my_table (id, data, author) 

728 VALUES (%(id)s, %(data)s, %(author)s) 

729 ON CONFLICT (id) DO UPDATE SET data = %(param_1)s, author = excluded.author 

730 WHERE my_table.status = %(status_1)s 

731 

732Skipping Rows with DO NOTHING 

733^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 

734 

735``ON CONFLICT`` may be used to skip inserting a row entirely 

736if any conflict with a unique or exclusion constraint occurs; below 

737this is illustrated using the 

738:meth:`~.postgresql.Insert.on_conflict_do_nothing` method: 

739 

740.. sourcecode:: pycon+sql 

741 

742 >>> stmt = insert(my_table).values(id='some_id', data='inserted value') 

743 >>> stmt = stmt.on_conflict_do_nothing(index_elements=['id']) 

744 >>> print(stmt) 

745 {opensql}INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s) 

746 ON CONFLICT (id) DO NOTHING 

747 

748If ``DO NOTHING`` is used without specifying any columns or constraint, 

749it has the effect of skipping the INSERT for any unique or exclusion 

750constraint violation which occurs: 

751 

752.. sourcecode:: pycon+sql 

753 

754 >>> stmt = insert(my_table).values(id='some_id', data='inserted value') 

755 >>> stmt = stmt.on_conflict_do_nothing() 

756 >>> print(stmt) 

757 {opensql}INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s) 

758 ON CONFLICT DO NOTHING 

759 

760.. _postgresql_match: 

761 

762Full Text Search 

763---------------- 

764 

765SQLAlchemy makes available the PostgreSQL ``@@`` operator via the 

766:meth:`_expression.ColumnElement.match` method on any textual column expression. 

767 

768On the PostgreSQL dialect, an expression like the following:: 

769 

770 select(sometable.c.text.match("search string")) 

771 

772will emit to the database:: 

773 

774 SELECT text @@ to_tsquery('search string') FROM table 

775 

776Various other PostgreSQL text search functions such as ``to_tsquery()``, 

777``to_tsvector()``, and ``plainto_tsquery()`` are available by explicitly using 

778the standard SQLAlchemy :data:`.func` construct. 

779 

780For example:: 

781 

782 select(func.to_tsvector('fat cats ate rats').match('cat & rat')) 

783 

784Emits the equivalent of:: 

785 

786 SELECT to_tsvector('fat cats ate rats') @@ to_tsquery('cat & rat') 

787 

788The :class:`_postgresql.TSVECTOR` type can provide for explicit CAST:: 

789 

790 from sqlalchemy.dialects.postgresql import TSVECTOR 

791 from sqlalchemy import select, cast 

792 select(cast("some text", TSVECTOR)) 

793 

794produces a statement equivalent to:: 

795 

796 SELECT CAST('some text' AS TSVECTOR) AS anon_1 

797 

798.. tip:: 

799 

800 It's important to remember that text searching in PostgreSQL is powerful but complicated, 

801 and SQLAlchemy users are advised to reference the PostgreSQL documentation 

802 regarding 

803 `Full Text Search <https://www.postgresql.org/docs/current/textsearch-controls.html>`_. 

804 

805 There are important differences between ``to_tsquery`` and 

806 ``plainto_tsquery``, the most significant of which is that ``to_tsquery`` 

807 expects specially formatted "querytext" that is written to PostgreSQL's own 

808 specification, while ``plainto_tsquery`` expects unformatted text that is 

809 transformed into ``to_tsquery`` compatible querytext. This means the input to 

810 ``.match()`` under PostgreSQL may be incompatible with the input to 

811 ``.match()`` under another database backend. SQLAlchemy users who support 

812 multiple backends are advised to carefully implement their usage of 

813 ``.match()`` to work around these constraints. 

814 

815Full Text Searches in PostgreSQL are influenced by a combination of: the 

816PostgreSQL setting of ``default_text_search_config``, the ``regconfig`` used 

817to build the GIN/GiST indexes, and the ``regconfig`` optionally passed in 

818during a query. 

819 

820When performing a Full Text Search against a column that has a GIN or 

821GiST index that is already pre-computed (which is common on full text 

822searches) one may need to explicitly pass in a particular PostgreSQL 

823``regconfig`` value to ensure the query-planner utilizes the index and does 

824not re-compute the column on demand. 

825 

826In order to provide for this explicit query planning, or to use different 

827search strategies, the ``match`` method accepts a ``postgresql_regconfig`` 

828keyword argument:: 

829 

830 select(mytable.c.id).where( 

831 mytable.c.title.match('somestring', postgresql_regconfig='english') 

832 ) 

833 

834Emits the equivalent of:: 

835 

836 SELECT mytable.id FROM mytable 

837 WHERE mytable.title @@ to_tsquery('english', 'somestring') 

838 

839One can also specifically pass in a `'regconfig'` value to the 

840``to_tsvector()`` command as the initial argument:: 

841 

842 select(mytable.c.id).where( 

843 func.to_tsvector('english', mytable.c.title )\ 

844 .match('somestring', postgresql_regconfig='english') 

845 ) 

846 

847produces a statement equivalent to:: 

848 

849 SELECT mytable.id FROM mytable 

850 WHERE to_tsvector('english', mytable.title) @@ 

851 to_tsquery('english', 'somestring') 

852 

853It is recommended that you use the ``EXPLAIN ANALYZE...`` tool from 

854PostgreSQL to ensure that you are generating queries with SQLAlchemy that 

855take full advantage of any indexes you may have created for full text search. 

856 

857.. seealso:: 

858 

859 `Full Text Search <https://www.postgresql.org/docs/current/textsearch-controls.html>`_ - in the PostgreSQL documentation 

860 

861 

862FROM ONLY ... 

863------------- 

864 

865The dialect supports PostgreSQL's ONLY keyword for targeting only a particular 

866table in an inheritance hierarchy. This can be used to produce the 

867``SELECT ... FROM ONLY``, ``UPDATE ONLY ...``, and ``DELETE FROM ONLY ...`` 

868syntaxes. It uses SQLAlchemy's hints mechanism:: 

869 

870 # SELECT ... FROM ONLY ... 

871 result = table.select().with_hint(table, 'ONLY', 'postgresql') 

872 print(result.fetchall()) 

873 

874 # UPDATE ONLY ... 

875 table.update(values=dict(foo='bar')).with_hint('ONLY', 

876 dialect_name='postgresql') 

877 

878 # DELETE FROM ONLY ... 

879 table.delete().with_hint('ONLY', dialect_name='postgresql') 

880 

881 

882.. _postgresql_indexes: 

883 

884PostgreSQL-Specific Index Options 

885--------------------------------- 

886 

887Several extensions to the :class:`.Index` construct are available, specific 

888to the PostgreSQL dialect. 

889 

890Covering Indexes 

891^^^^^^^^^^^^^^^^ 

892 

893The ``postgresql_include`` option renders INCLUDE(colname) for the given 

894string names:: 

895 

896 Index("my_index", table.c.x, postgresql_include=['y']) 

897 

898would render the index as ``CREATE INDEX my_index ON table (x) INCLUDE (y)`` 

899 

900Note that this feature requires PostgreSQL 11 or later. 

901 

902.. versionadded:: 1.4 

903 

904.. _postgresql_partial_indexes: 

905 

906Partial Indexes 

907^^^^^^^^^^^^^^^ 

908 

909Partial indexes add criterion to the index definition so that the index is 

910applied to a subset of rows. These can be specified on :class:`.Index` 

911using the ``postgresql_where`` keyword argument:: 

912 

913 Index('my_index', my_table.c.id, postgresql_where=my_table.c.value > 10) 

914 

915.. _postgresql_operator_classes: 

916 

917Operator Classes 

918^^^^^^^^^^^^^^^^ 

919 

920PostgreSQL allows the specification of an *operator class* for each column of 

921an index (see 

922https://www.postgresql.org/docs/current/interactive/indexes-opclass.html). 

923The :class:`.Index` construct allows these to be specified via the 

924``postgresql_ops`` keyword argument:: 

925 

926 Index( 

927 'my_index', my_table.c.id, my_table.c.data, 

928 postgresql_ops={ 

929 'data': 'text_pattern_ops', 

930 'id': 'int4_ops' 

931 }) 

932 

933Note that the keys in the ``postgresql_ops`` dictionaries are the 

934"key" name of the :class:`_schema.Column`, i.e. the name used to access it from 

935the ``.c`` collection of :class:`_schema.Table`, which can be configured to be 

936different than the actual name of the column as expressed in the database. 

937 

938If ``postgresql_ops`` is to be used against a complex SQL expression such 

939as a function call, then to apply to the column it must be given a label 

940that is identified in the dictionary by name, e.g.:: 

941 

942 Index( 

943 'my_index', my_table.c.id, 

944 func.lower(my_table.c.data).label('data_lower'), 

945 postgresql_ops={ 

946 'data_lower': 'text_pattern_ops', 

947 'id': 'int4_ops' 

948 }) 

949 

950Operator classes are also supported by the 

951:class:`_postgresql.ExcludeConstraint` construct using the 

952:paramref:`_postgresql.ExcludeConstraint.ops` parameter. See that parameter for 

953details. 

954 

955.. versionadded:: 1.3.21 added support for operator classes with 

956 :class:`_postgresql.ExcludeConstraint`. 

957 

958 

959Index Types 

960^^^^^^^^^^^ 

961 

962PostgreSQL provides several index types: B-Tree, Hash, GiST, and GIN, as well 

963as the ability for users to create their own (see 

964https://www.postgresql.org/docs/current/static/indexes-types.html). These can be 

965specified on :class:`.Index` using the ``postgresql_using`` keyword argument:: 

966 

967 Index('my_index', my_table.c.data, postgresql_using='gin') 

968 

969The value passed to the keyword argument will be simply passed through to the 

970underlying CREATE INDEX command, so it *must* be a valid index type for your 

971version of PostgreSQL. 

972 

973.. _postgresql_index_storage: 

974 

975Index Storage Parameters 

976^^^^^^^^^^^^^^^^^^^^^^^^ 

977 

978PostgreSQL allows storage parameters to be set on indexes. The storage 

979parameters available depend on the index method used by the index. Storage 

980parameters can be specified on :class:`.Index` using the ``postgresql_with`` 

981keyword argument:: 

982 

983 Index('my_index', my_table.c.data, postgresql_with={"fillfactor": 50}) 

984 

985.. versionadded:: 1.0.6 

986 

987PostgreSQL allows to define the tablespace in which to create the index. 

988The tablespace can be specified on :class:`.Index` using the 

989``postgresql_tablespace`` keyword argument:: 

990 

991 Index('my_index', my_table.c.data, postgresql_tablespace='my_tablespace') 

992 

993.. versionadded:: 1.1 

994 

995Note that the same option is available on :class:`_schema.Table` as well. 

996 

997.. _postgresql_index_concurrently: 

998 

999Indexes with CONCURRENTLY 

1000^^^^^^^^^^^^^^^^^^^^^^^^^ 

1001 

1002The PostgreSQL index option CONCURRENTLY is supported by passing the 

1003flag ``postgresql_concurrently`` to the :class:`.Index` construct:: 

1004 

1005 tbl = Table('testtbl', m, Column('data', Integer)) 

1006 

1007 idx1 = Index('test_idx1', tbl.c.data, postgresql_concurrently=True) 

1008 

1009The above index construct will render DDL for CREATE INDEX, assuming 

1010PostgreSQL 8.2 or higher is detected or for a connection-less dialect, as:: 

1011 

1012 CREATE INDEX CONCURRENTLY test_idx1 ON testtbl (data) 

1013 

1014For DROP INDEX, assuming PostgreSQL 9.2 or higher is detected or for 

1015a connection-less dialect, it will emit:: 

1016 

1017 DROP INDEX CONCURRENTLY test_idx1 

1018 

1019.. versionadded:: 1.1 support for CONCURRENTLY on DROP INDEX. The 

1020 CONCURRENTLY keyword is now only emitted if a high enough version 

1021 of PostgreSQL is detected on the connection (or for a connection-less 

1022 dialect). 

1023 

1024When using CONCURRENTLY, the PostgreSQL database requires that the statement 

1025be invoked outside of a transaction block. The Python DBAPI enforces that 

1026even for a single statement, a transaction is present, so to use this 

1027construct, the DBAPI's "autocommit" mode must be used:: 

1028 

1029 metadata = MetaData() 

1030 table = Table( 

1031 "foo", metadata, 

1032 Column("id", String)) 

1033 index = Index( 

1034 "foo_idx", table.c.id, postgresql_concurrently=True) 

1035 

1036 with engine.connect() as conn: 

1037 with conn.execution_options(isolation_level='AUTOCOMMIT'): 

1038 table.create(conn) 

1039 

1040.. seealso:: 

1041 

1042 :ref:`postgresql_isolation_level` 

1043 

1044.. _postgresql_index_reflection: 

1045 

1046PostgreSQL Index Reflection 

1047--------------------------- 

1048 

1049The PostgreSQL database creates a UNIQUE INDEX implicitly whenever the 

1050UNIQUE CONSTRAINT construct is used. When inspecting a table using 

1051:class:`_reflection.Inspector`, the :meth:`_reflection.Inspector.get_indexes` 

1052and the :meth:`_reflection.Inspector.get_unique_constraints` 

1053will report on these 

1054two constructs distinctly; in the case of the index, the key 

1055``duplicates_constraint`` will be present in the index entry if it is 

1056detected as mirroring a constraint. When performing reflection using 

1057``Table(..., autoload_with=engine)``, the UNIQUE INDEX is **not** returned 

1058in :attr:`_schema.Table.indexes` when it is detected as mirroring a 

1059:class:`.UniqueConstraint` in the :attr:`_schema.Table.constraints` collection 

1060. 

1061 

1062.. versionchanged:: 1.0.0 - :class:`_schema.Table` reflection now includes 

1063 :class:`.UniqueConstraint` objects present in the 

1064 :attr:`_schema.Table.constraints` 

1065 collection; the PostgreSQL backend will no longer include a "mirrored" 

1066 :class:`.Index` construct in :attr:`_schema.Table.indexes` 

1067 if it is detected 

1068 as corresponding to a unique constraint. 

1069 

1070Special Reflection Options 

1071-------------------------- 

1072 

1073The :class:`_reflection.Inspector` 

1074used for the PostgreSQL backend is an instance 

1075of :class:`.PGInspector`, which offers additional methods:: 

1076 

1077 from sqlalchemy import create_engine, inspect 

1078 

1079 engine = create_engine("postgresql+psycopg2://localhost/test") 

1080 insp = inspect(engine) # will be a PGInspector 

1081 

1082 print(insp.get_enums()) 

1083 

1084.. autoclass:: PGInspector 

1085 :members: 

1086 

1087.. _postgresql_table_options: 

1088 

1089PostgreSQL Table Options 

1090------------------------ 

1091 

1092Several options for CREATE TABLE are supported directly by the PostgreSQL 

1093dialect in conjunction with the :class:`_schema.Table` construct: 

1094 

1095* ``TABLESPACE``:: 

1096 

1097 Table("some_table", metadata, ..., postgresql_tablespace='some_tablespace') 

1098 

1099 The above option is also available on the :class:`.Index` construct. 

1100 

1101* ``ON COMMIT``:: 

1102 

1103 Table("some_table", metadata, ..., postgresql_on_commit='PRESERVE ROWS') 

1104 

1105* ``WITH OIDS``:: 

1106 

1107 Table("some_table", metadata, ..., postgresql_with_oids=True) 

1108 

1109* ``WITHOUT OIDS``:: 

1110 

1111 Table("some_table", metadata, ..., postgresql_with_oids=False) 

1112 

1113* ``INHERITS``:: 

1114 

1115 Table("some_table", metadata, ..., postgresql_inherits="some_supertable") 

1116 

1117 Table("some_table", metadata, ..., postgresql_inherits=("t1", "t2", ...)) 

1118 

1119 .. versionadded:: 1.0.0 

1120 

1121* ``PARTITION BY``:: 

1122 

1123 Table("some_table", metadata, ..., 

1124 postgresql_partition_by='LIST (part_column)') 

1125 

1126 .. versionadded:: 1.2.6 

1127 

1128.. seealso:: 

1129 

1130 `PostgreSQL CREATE TABLE options 

1131 <https://www.postgresql.org/docs/current/static/sql-createtable.html>`_ - 

1132 in the PostgreSQL documentation. 

1133 

1134.. _postgresql_constraint_options: 

1135 

1136PostgreSQL Constraint Options 

1137----------------------------- 

1138 

1139The following option(s) are supported by the PostgreSQL dialect in conjunction 

1140with selected constraint constructs: 

1141 

1142* ``NOT VALID``: This option applies towards CHECK and FOREIGN KEY constraints 

1143 when the constraint is being added to an existing table via ALTER TABLE, 

1144 and has the effect that existing rows are not scanned during the ALTER 

1145 operation against the constraint being added. 

1146 

1147 When using a SQL migration tool such as `Alembic <https://alembic.sqlalchemy.org>`_ 

1148 that renders ALTER TABLE constructs, the ``postgresql_not_valid`` argument 

1149 may be specified as an additional keyword argument within the operation 

1150 that creates the constraint, as in the following Alembic example:: 

1151 

1152 def update(): 

1153 op.create_foreign_key( 

1154 "fk_user_address", 

1155 "address", 

1156 "user", 

1157 ["user_id"], 

1158 ["id"], 

1159 postgresql_not_valid=True 

1160 ) 

1161 

1162 The keyword is ultimately accepted directly by the 

1163 :class:`_schema.CheckConstraint`, :class:`_schema.ForeignKeyConstraint` 

1164 and :class:`_schema.ForeignKey` constructs; when using a tool like 

1165 Alembic, dialect-specific keyword arguments are passed through to 

1166 these constructs from the migration operation directives:: 

1167 

1168 CheckConstraint("some_field IS NOT NULL", postgresql_not_valid=True) 

1169 

1170 ForeignKeyConstraint(["some_id"], ["some_table.some_id"], postgresql_not_valid=True) 

1171 

1172 .. versionadded:: 1.4.32 

1173 

1174 .. seealso:: 

1175 

1176 `PostgreSQL ALTER TABLE options 

1177 <https://www.postgresql.org/docs/current/static/sql-altertable.html>`_ - 

1178 in the PostgreSQL documentation. 

1179 

1180.. _postgresql_table_valued_overview: 

1181 

1182Table values, Table and Column valued functions, Row and Tuple objects 

1183----------------------------------------------------------------------- 

1184 

1185PostgreSQL makes great use of modern SQL forms such as table-valued functions, 

1186tables and rows as values. These constructs are commonly used as part 

1187of PostgreSQL's support for complex datatypes such as JSON, ARRAY, and other 

1188datatypes. SQLAlchemy's SQL expression language has native support for 

1189most table-valued and row-valued forms. 

1190 

1191.. _postgresql_table_valued: 

1192 

1193Table-Valued Functions 

1194^^^^^^^^^^^^^^^^^^^^^^^ 

1195 

1196Many PostgreSQL built-in functions are intended to be used in the FROM clause 

1197of a SELECT statement, and are capable of returning table rows or sets of table 

1198rows. A large portion of PostgreSQL's JSON functions for example such as 

1199``json_array_elements()``, ``json_object_keys()``, ``json_each_text()``, 

1200``json_each()``, ``json_to_record()``, ``json_populate_recordset()`` use such 

1201forms. These classes of SQL function calling forms in SQLAlchemy are available 

1202using the :meth:`_functions.FunctionElement.table_valued` method in conjunction 

1203with :class:`_functions.Function` objects generated from the :data:`_sql.func` 

1204namespace. 

1205 

1206Examples from PostgreSQL's reference documentation follow below: 

1207 

1208* ``json_each()``:: 

1209 

1210 >>> from sqlalchemy import select, func 

1211 >>> stmt = select(func.json_each('{"a":"foo", "b":"bar"}').table_valued("key", "value")) 

1212 >>> print(stmt) 

1213 SELECT anon_1.key, anon_1.value 

1214 FROM json_each(:json_each_1) AS anon_1 

1215 

1216* ``json_populate_record()``:: 

1217 

1218 >>> from sqlalchemy import select, func, literal_column 

1219 >>> stmt = select( 

1220 ... func.json_populate_record( 

1221 ... literal_column("null::myrowtype"), 

1222 ... '{"a":1,"b":2}' 

1223 ... ).table_valued("a", "b", name="x") 

1224 ... ) 

1225 >>> print(stmt) 

1226 SELECT x.a, x.b 

1227 FROM json_populate_record(null::myrowtype, :json_populate_record_1) AS x 

1228 

1229* ``json_to_record()`` - this form uses a PostgreSQL specific form of derived 

1230 columns in the alias, where we may make use of :func:`_sql.column` elements with 

1231 types to produce them. The :meth:`_functions.FunctionElement.table_valued` 

1232 method produces a :class:`_sql.TableValuedAlias` construct, and the method 

1233 :meth:`_sql.TableValuedAlias.render_derived` method sets up the derived 

1234 columns specification:: 

1235 

1236 >>> from sqlalchemy import select, func, column, Integer, Text 

1237 >>> stmt = select( 

1238 ... func.json_to_record('{"a":1,"b":[1,2,3],"c":"bar"}').table_valued( 

1239 ... column("a", Integer), column("b", Text), column("d", Text), 

1240 ... ).render_derived(name="x", with_types=True) 

1241 ... ) 

1242 >>> print(stmt) 

1243 SELECT x.a, x.b, x.d 

1244 FROM json_to_record(:json_to_record_1) AS x(a INTEGER, b TEXT, d TEXT) 

1245 

1246* ``WITH ORDINALITY`` - part of the SQL standard, ``WITH ORDINALITY`` adds an 

1247 ordinal counter to the output of a function and is accepted by a limited set 

1248 of PostgreSQL functions including ``unnest()`` and ``generate_series()``. The 

1249 :meth:`_functions.FunctionElement.table_valued` method accepts a keyword 

1250 parameter ``with_ordinality`` for this purpose, which accepts the string name 

1251 that will be applied to the "ordinality" column:: 

1252 

1253 >>> from sqlalchemy import select, func 

1254 >>> stmt = select( 

1255 ... func.generate_series(4, 1, -1). 

1256 ... table_valued("value", with_ordinality="ordinality"). 

1257 ... render_derived() 

1258 ... ) 

1259 >>> print(stmt) 

1260 SELECT anon_1.value, anon_1.ordinality 

1261 FROM generate_series(:generate_series_1, :generate_series_2, :generate_series_3) 

1262 WITH ORDINALITY AS anon_1(value, ordinality) 

1263 

1264.. versionadded:: 1.4.0b2 

1265 

1266.. seealso:: 

1267 

1268 :ref:`tutorial_functions_table_valued` - in the :ref:`unified_tutorial` 

1269 

1270.. _postgresql_column_valued: 

1271 

1272Column Valued Functions 

1273^^^^^^^^^^^^^^^^^^^^^^^ 

1274 

1275Similar to the table valued function, a column valued function is present 

1276in the FROM clause, but delivers itself to the columns clause as a single 

1277scalar value. PostgreSQL functions such as ``json_array_elements()``, 

1278``unnest()`` and ``generate_series()`` may use this form. Column valued functions are available using the 

1279:meth:`_functions.FunctionElement.column_valued` method of :class:`_functions.FunctionElement`: 

1280 

1281* ``json_array_elements()``:: 

1282 

1283 >>> from sqlalchemy import select, func 

1284 >>> stmt = select(func.json_array_elements('["one", "two"]').column_valued("x")) 

1285 >>> print(stmt) 

1286 SELECT x 

1287 FROM json_array_elements(:json_array_elements_1) AS x 

1288 

1289* ``unnest()`` - in order to generate a PostgreSQL ARRAY literal, the 

1290 :func:`_postgresql.array` construct may be used:: 

1291 

1292 

1293 >>> from sqlalchemy.dialects.postgresql import array 

1294 >>> from sqlalchemy import select, func 

1295 >>> stmt = select(func.unnest(array([1, 2])).column_valued()) 

1296 >>> print(stmt) 

1297 SELECT anon_1 

1298 FROM unnest(ARRAY[%(param_1)s, %(param_2)s]) AS anon_1 

1299 

1300 The function can of course be used against an existing table-bound column 

1301 that's of type :class:`_types.ARRAY`:: 

1302 

1303 >>> from sqlalchemy import table, column, ARRAY, Integer 

1304 >>> from sqlalchemy import select, func 

1305 >>> t = table("t", column('value', ARRAY(Integer))) 

1306 >>> stmt = select(func.unnest(t.c.value).column_valued("unnested_value")) 

1307 >>> print(stmt) 

1308 SELECT unnested_value 

1309 FROM unnest(t.value) AS unnested_value 

1310 

1311.. seealso:: 

1312 

1313 :ref:`tutorial_functions_column_valued` - in the :ref:`unified_tutorial` 

1314 

1315 

1316Row Types 

1317^^^^^^^^^ 

1318 

1319Built-in support for rendering a ``ROW`` may be approximated using 

1320``func.ROW`` with the :attr:`_sa.func` namespace, or by using the 

1321:func:`_sql.tuple_` construct:: 

1322 

1323 >>> from sqlalchemy import table, column, func, tuple_ 

1324 >>> t = table("t", column("id"), column("fk")) 

1325 >>> stmt = t.select().where( 

1326 ... tuple_(t.c.id, t.c.fk) > (1,2) 

1327 ... ).where( 

1328 ... func.ROW(t.c.id, t.c.fk) < func.ROW(3, 7) 

1329 ... ) 

1330 >>> print(stmt) 

1331 SELECT t.id, t.fk 

1332 FROM t 

1333 WHERE (t.id, t.fk) > (:param_1, :param_2) AND ROW(t.id, t.fk) < ROW(:ROW_1, :ROW_2) 

1334 

1335.. seealso:: 

1336 

1337 `PostgreSQL Row Constructors 

1338 <https://www.postgresql.org/docs/current/sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS>`_ 

1339 

1340 `PostgreSQL Row Constructor Comparison 

1341 <https://www.postgresql.org/docs/current/functions-comparisons.html#ROW-WISE-COMPARISON>`_ 

1342 

1343Table Types passed to Functions 

1344^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 

1345 

1346PostgreSQL supports passing a table as an argument to a function, which it 

1347refers towards as a "record" type. SQLAlchemy :class:`_sql.FromClause` objects 

1348such as :class:`_schema.Table` support this special form using the 

1349:meth:`_sql.FromClause.table_valued` method, which is comparable to the 

1350:meth:`_functions.FunctionElement.table_valued` method except that the collection 

1351of columns is already established by that of the :class:`_sql.FromClause` 

1352itself:: 

1353 

1354 

1355 >>> from sqlalchemy import table, column, func, select 

1356 >>> a = table( "a", column("id"), column("x"), column("y")) 

1357 >>> stmt = select(func.row_to_json(a.table_valued())) 

1358 >>> print(stmt) 

1359 SELECT row_to_json(a) AS row_to_json_1 

1360 FROM a 

1361 

1362.. versionadded:: 1.4.0b2 

1363 

1364 

1365ARRAY Types 

1366----------- 

1367 

1368The PostgreSQL dialect supports arrays, both as multidimensional column types 

1369as well as array literals: 

1370 

1371* :class:`_postgresql.ARRAY` - ARRAY datatype 

1372 

1373* :class:`_postgresql.array` - array literal 

1374 

1375* :func:`_postgresql.array_agg` - ARRAY_AGG SQL function 

1376 

1377* :class:`_postgresql.aggregate_order_by` - helper for PG's ORDER BY aggregate 

1378 function syntax. 

1379 

1380JSON Types 

1381---------- 

1382 

1383The PostgreSQL dialect supports both JSON and JSONB datatypes, including 

1384psycopg2's native support and support for all of PostgreSQL's special 

1385operators: 

1386 

1387* :class:`_postgresql.JSON` 

1388 

1389* :class:`_postgresql.JSONB` 

1390 

1391HSTORE Type 

1392-----------