1.. currentmodule:: sqlalchemy.schema 2 3.. _metadata_defaults_toplevel: 4 5.. _metadata_defaults: 6 7Column INSERT/UPDATE Defaults 8============================= 9 10Column INSERT and UPDATE defaults refer to functions that create a **default 11value** for a particular column in a row as an INSERT or UPDATE statement is 12proceeding against that row, in the case where **no value was provided to the 13INSERT or UPDATE statement for that column**. That is, if a table has a column 14called "timestamp", and an INSERT statement proceeds which does not include a 15value for this column, an INSERT default would create a new value, such as 16the current time, that is used as the value to be INSERTed into the "timestamp" 17column. If the statement *does* include a value for this column, then the 18default does *not* take place. 19 20Column defaults can be server-side functions or constant values which are 21defined in the database along with the schema in :term:`DDL`, or as SQL 22expressions which are rendered directly within an INSERT or UPDATE statement 23emitted by SQLAlchemy; they may also be client-side Python functions or 24constant values which are invoked by SQLAlchemy before data is passed to the 25database. 26 27.. note:: 28 29 A column default handler should not be confused with a construct that 30 intercepts and modifies incoming values for INSERT and UPDATE statements 31 which *are* provided to the statement as it is invoked. This is known 32 as :term:`data marshalling`, where a column value is modified in some way 33 by the application before being sent to the database. SQLAlchemy provides 34 a few means of achieving this which include using :ref:`custom datatypes 35 <types_typedecorator>`, :ref:`SQL execution events <core_sql_events>` and 36 in the ORM :ref:`custom validators <simple_validators>` as well as 37 :ref:`attribute events <orm_attribute_events>`. Column defaults are only 38 invoked when there is **no value present** for a column in a SQL 39 :term:`DML` statement. 40 41 42SQLAlchemy provides an array of features regarding default generation 43functions which take place for non-present values during INSERT and UPDATE 44statements. Options include: 45 46* Scalar values used as defaults during INSERT and UPDATE operations 47* Python functions which execute upon INSERT and UPDATE operations 48* SQL expressions which are embedded in INSERT statements (or in some cases execute beforehand) 49* SQL expressions which are embedded in UPDATE statements 50* Server side default values used during INSERT 51* Markers for server-side triggers used during UPDATE 52 53The general rule for all insert/update defaults is that they only take effect 54if no value for a particular column is passed as an ``execute()`` parameter; 55otherwise, the given value is used. 56 57Scalar Defaults 58--------------- 59 60The simplest kind of default is a scalar value used as the default value of a column:: 61 62 Table("mytable", metadata_obj, 63 Column("somecolumn", Integer, default=12) 64 ) 65 66Above, the value "12" will be bound as the column value during an INSERT if no 67other value is supplied. 68 69A scalar value may also be associated with an UPDATE statement, though this is 70not very common (as UPDATE statements are usually looking for dynamic 71defaults):: 72 73 Table("mytable", metadata_obj, 74 Column("somecolumn", Integer, onupdate=25) 75 ) 76 77 78Python-Executed Functions 79------------------------- 80 81The :paramref:`_schema.Column.default` and :paramref:`_schema.Column.onupdate` keyword arguments also accept Python 82functions. These functions are invoked at the time of insert or update if no 83other value for that column is supplied, and the value returned is used for 84the column's value. Below illustrates a crude "sequence" that assigns an 85incrementing counter to a primary key column:: 86 87 # a function which counts upwards 88 i = 0 89 def mydefault(): 90 global i 91 i += 1 92 return i 93 94 t = Table("mytable", metadata_obj, 95 Column('id', Integer, primary_key=True, default=mydefault), 96 ) 97 98It should be noted that for real "incrementing sequence" behavior, the 99built-in capabilities of the database should normally be used, which may 100include sequence objects or other autoincrementing capabilities. For primary 101key columns, SQLAlchemy will in most cases use these capabilities 102automatically. See the API documentation for 103:class:`~sqlalchemy.schema.Column` including the :paramref:`_schema.Column.autoincrement` flag, as 104well as the section on :class:`~sqlalchemy.schema.Sequence` later in this 105chapter for background on standard primary key generation techniques. 106 107To illustrate onupdate, we assign the Python ``datetime`` function ``now`` to 108the :paramref:`_schema.Column.onupdate` attribute:: 109 110 import datetime 111 112 t = Table("mytable", metadata_obj, 113 Column('id', Integer, primary_key=True), 114 115 # define 'last_updated' to be populated with datetime.now() 116 Column('last_updated', DateTime, onupdate=datetime.datetime.now), 117 ) 118 119When an update statement executes and no value is passed for ``last_updated``, 120the ``datetime.datetime.now()`` Python function is executed and its return 121value used as the value for ``last_updated``. Notice that we provide ``now`` 122as the function itself without calling it (i.e. there are no parenthesis 123following) - SQLAlchemy will execute the function at the time the statement 124executes. 125 126.. _context_default_functions: 127 128Context-Sensitive Default Functions 129~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 130 131The Python functions used by :paramref:`_schema.Column.default` and 132:paramref:`_schema.Column.onupdate` may also make use of the current statement's 133context in order to determine a value. The `context` of a statement is an 134internal SQLAlchemy object which contains all information about the statement 135being executed, including its source expression, the parameters associated with 136it and the cursor. The typical use case for this context with regards to 137default generation is to have access to the other values being inserted or 138updated on the row. To access the context, provide a function that accepts a 139single ``context`` argument:: 140 141 def mydefault(context): 142 return context.get_current_parameters()['counter'] + 12 143 144 t = Table('mytable', metadata_obj, 145 Column('counter', Integer), 146 Column('counter_plus_twelve', Integer, default=mydefault, onupdate=mydefault) 147 ) 148 149The above default generation function is applied so that it will execute for 150all INSERT and UPDATE statements where a value for ``counter_plus_twelve`` was 151otherwise not provided, and the value will be that of whatever value is present 152in the execution for the ``counter`` column, plus the number 12. 153 154For a single statement that is being executed using "executemany" style, e.g. 155with multiple parameter sets passed to :meth:`_engine.Connection.execute`, the 156user-defined function is called once for each set of parameters. For the use case of 157a multi-valued :class:`_expression.Insert` construct (e.g. with more than one VALUES 158clause set up via the :meth:`_expression.Insert.values` method), the user-defined function 159is also called once for each set of parameters. 160 161When the function is invoked, the special method 162:meth:`.DefaultExecutionContext.get_current_parameters` is available from 163the context object (an subclass of :class:`.DefaultExecutionContext`). This 164method returns a dictionary of column-key to values that represents the 165full set of values for the INSERT or UPDATE statement. In the case of a 166multi-valued INSERT construct, the subset of parameters that corresponds to 167the individual VALUES clause is isolated from the full parameter dictionary 168and returned alone. 169 170.. versionadded:: 1.2 171 172 Added :meth:`.DefaultExecutionContext.get_current_parameters` method, 173 which improves upon the still-present 174 :attr:`.DefaultExecutionContext.current_parameters` attribute 175 by offering the service of organizing multiple VALUES clauses 176 into individual parameter dictionaries. 177 178Client-Invoked SQL Expressions 179------------------------------ 180 181The :paramref:`_schema.Column.default` and :paramref:`_schema.Column.onupdate` keywords may 182also be passed SQL expressions, which are in most cases rendered inline within the 183INSERT or UPDATE statement:: 184 185 t = Table("mytable", metadata_obj, 186 Column('id', Integer, primary_key=True), 187 188 # define 'create_date' to default to now() 189 Column('create_date', DateTime, default=func.now()), 190 191 # define 'key' to pull its default from the 'keyvalues' table 192 Column('key', String(20), default=select(keyvalues.c.key).where(keyvalues.c.type='type1')), 193 194 # define 'last_modified' to use the current_timestamp SQL function on update 195 Column('last_modified', DateTime, onupdate=func.utc_timestamp()) 196 ) 197 198Above, the ``create_date`` column will be populated with the result of the 199``now()`` SQL function (which, depending on backend, compiles into ``NOW()`` 200or ``CURRENT_TIMESTAMP`` in most cases) during an INSERT statement, and the 201``key`` column with the result of a SELECT subquery from another table. The 202``last_modified`` column will be populated with the value of 203the SQL ``UTC_TIMESTAMP()`` MySQL function when an UPDATE statement is 204emitted for this table. 205 206.. note:: 207 208 When using SQL functions with the :attr:`.func` construct, we "call" the 209 named function, e.g. with parenthesis as in ``func.now()``. This differs 210 from when we specify a Python callable as a default such as 211 ``datetime.datetime``, where we pass the function itself, but we don't 212 invoke it ourselves. In the case of a SQL function, invoking 213 ``func.now()`` returns the SQL expression object that will render the 214 "NOW" function into the SQL being emitted. 215 216Default and update SQL expressions specified by :paramref:`_schema.Column.default` and 217:paramref:`_schema.Column.onupdate` are invoked explicitly by SQLAlchemy when an 218INSERT or UPDATE statement occurs, typically rendered inline within the DML 219statement except in certain cases listed below. This is different than a 220"server side" default, which is part of the table's DDL definition, e.g. as 221part of the "CREATE TABLE" statement, which are likely more common. For 222server side defaults, see the next section :ref:`server_defaults`. 223 224When a SQL expression indicated by :paramref:`_schema.Column.default` is used with 225primary key columns, there are some cases where SQLAlchemy must "pre-execute" 226the default generation SQL function, meaning it is invoked in a separate SELECT 227statement, and the resulting value is passed as a parameter to the INSERT. 228This only occurs for primary key columns for an INSERT statement that is being 229asked to return this primary key value, where RETURNING or ``cursor.lastrowid`` 230may not be used. An :class:`_expression.Insert` construct that specifies the 231:paramref:`~.expression.insert.inline` flag will always render default expressions 232inline. 233 234When the statement is executed with a single set of parameters (that is, it is 235not an "executemany" style execution), the returned 236:class:`~sqlalchemy.engine.CursorResult` will contain a collection accessible 237via :meth:`_engine.CursorResult.postfetch_cols` which contains a list of all 238:class:`~sqlalchemy.schema.Column` objects which had an inline-executed 239default. Similarly, all parameters which were bound to the statement, including 240all Python and SQL expressions which were pre-executed, are present in the 241:meth:`_engine.CursorResult.last_inserted_params` or 242:meth:`_engine.CursorResult.last_updated_params` collections on 243:class:`~sqlalchemy.engine.CursorResult`. The 244:attr:`_engine.CursorResult.inserted_primary_key` collection contains a list of primary 245key values for the row inserted (a list so that single-column and 246composite-column primary keys are represented in the same format). 247 248.. _server_defaults: 249 250Server-invoked DDL-Explicit Default Expressions 251----------------------------------------------- 252 253A variant on the SQL expression default is the :paramref:`_schema.Column.server_default`, which gets 254placed in the CREATE TABLE statement during a :meth:`_schema.Table.create` operation: 255 256.. sourcecode:: python+sql 257 258 t = Table('test', metadata_obj, 259 Column('abc', String(20), server_default='abc'), 260 Column('created_at', DateTime, server_default=func.sysdate()), 261 Column('index_value', Integer, server_default=text("0")) 262 ) 263 264A create call for the above table will produce:: 265 266 CREATE TABLE test ( 267 abc varchar(20) default 'abc', 268 created_at datetime default sysdate, 269 index_value integer default 0 270 ) 271 272The above example illustrates the two typical use cases for :paramref:`_schema.Column.server_default`, 273that of the SQL function (SYSDATE in the above example) as well as a server-side constant 274value (the integer "0" in the above example). It is advisable to use the 275:func:`_expression.text` construct for any literal SQL values as opposed to passing the 276raw value, as SQLAlchemy does not typically perform any quoting or escaping on 277these values. 278 279Like client-generated expressions, :paramref:`_schema.Column.server_default` can accommodate 280SQL expressions in general, however it is expected that these will usually be simple 281functions and expressions, and not the more complex cases like an embedded SELECT. 282 283 284.. _triggered_columns: 285 286Marking Implicitly Generated Values, timestamps, and Triggered Columns 287---------------------------------------------------------------------- 288 289Columns which generate a new value on INSERT or UPDATE based on other 290server-side database mechanisms, such as database-specific auto-generating 291behaviors such as seen with TIMESTAMP columns on some platforms, as well as 292custom triggers that invoke upon INSERT or UPDATE to generate a new value, 293may be called out using :class:`.FetchedValue` as a marker:: 294 295 from sqlalchemy.schema import FetchedValue 296 297 t = Table('test', metadata_obj, 298 Column('id', Integer, primary_key=True), 299 Column('abc', TIMESTAMP, server_default=FetchedValue()), 300 Column('def', String(20), server_onupdate=FetchedValue()) 301 ) 302 303The :class:`.FetchedValue` indicator does not affect the rendered DDL for the 304CREATE TABLE. Instead, it marks the column as one that will have a new value 305populated by the database during the process of an INSERT or UPDATE statement, 306and for supporting databases may be used to indicate that the column should be 307part of a RETURNING or OUTPUT clause for the statement. Tools such as the 308SQLAlchemy ORM then make use of this marker in order to know how to get at the 309value of the column after such an operation. In particular, the 310:meth:`.ValuesBase.return_defaults` method can be used with an :class:`_expression.Insert` 311or :class:`_expression.Update` construct to indicate that these values should be 312returned. 313 314For details on using :class:`.FetchedValue` with the ORM, see 315:ref:`orm_server_defaults`. 316 317.. warning:: The :paramref:`_schema.Column.server_onupdate` directive 318 **does not** currently produce MySQL's 319 "ON UPDATE CURRENT_TIMESTAMP()" clause. See 320 :ref:`mysql_timestamp_onupdate` for background on how to produce 321 this clause. 322 323 324.. seealso:: 325 326 :ref:`orm_server_defaults` 327 328.. _defaults_sequences: 329 330Defining Sequences 331------------------ 332 333SQLAlchemy represents database sequences using the 334:class:`~sqlalchemy.schema.Sequence` object, which is considered to be a 335special case of "column default". It only has an effect on databases which have 336explicit support for sequences, which currently includes PostgreSQL, Oracle, 337MariaDB 10.3 or greater, and Firebird. The :class:`~sqlalchemy.schema.Sequence` 338object is otherwise ignored. 339 340The :class:`~sqlalchemy.schema.Sequence` may be placed on any column as a 341"default" generator to be used during INSERT operations, and can also be 342configured to fire off during UPDATE operations if desired. It is most 343commonly used in conjunction with a single integer primary key column:: 344 345 table = Table("cartitems", metadata_obj, 346 Column( 347 "cart_id", 348 Integer, 349 Sequence('cart_id_seq', metadata=metadata_obj), primary_key=True), 350 Column("description", String(40)), 351 Column("createdate", DateTime()) 352 ) 353 354Where above, the table "cartitems" is associated with a sequence named 355"cart_id_seq". When INSERT statements take place for "cartitems", and no value 356is passed for the "cart_id" column, the "cart_id_seq" sequence will be used to 357generate a value. Typically, the sequence function is embedded in the 358INSERT statement, which is combined with RETURNING so that the newly generated 359value can be returned to the Python code:: 360 361 INSERT INTO cartitems (cart_id, description, createdate) 362 VALUES (next_val(cart_id_seq), 'some description', '2015-10-15 12:00:15') 363 RETURNING cart_id 364 365When the :class:`~sqlalchemy.schema.Sequence` is associated with a 366:class:`_schema.Column` as its **Python-side** default generator, the 367:class:`.Sequence` will also be subject to "CREATE SEQUENCE" and "DROP 368SEQUENCE" DDL when similar DDL is emitted for the owning :class:`_schema.Table`. 369This is a limited scope convenience feature that does not accommodate for 370inheritance of other aspects of the :class:`_schema.MetaData`, such as the default 371schema. Therefore, it is best practice that for a :class:`.Sequence` which 372is local to a certain :class:`_schema.Column` / :class:`_schema.Table`, that it be 373explicitly associated with the :class:`_schema.MetaData` using the 374:paramref:`.Sequence.metadata` parameter. See the section 375:ref:`sequence_metadata` for more background on this. 376 377Associating a Sequence on a SERIAL column 378~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 379 380PostgreSQL's SERIAL datatype is an auto-incrementing type that implies 381the implicit creation of a PostgreSQL sequence when CREATE TABLE is emitted. 382If a :class:`_schema.Column` specifies an explicit :class:`.Sequence` object 383which also specifies a ``True`` value for the :paramref:`.Sequence.optional` 384boolean flag, the :class:`.Sequence` will not take effect under PostgreSQL, 385and the SERIAL datatype will proceed normally. Instead, the :class:`.Sequence` 386will only take effect when used against other sequence-supporting 387databases, currently Oracle and Firebird. 388 389Executing a Sequence Standalone 390~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 391 392A SEQUENCE is a first class schema object in SQL and can be used to generate 393values independently in the database. If you have a :class:`.Sequence` 394object, it can be invoked with its "next value" instruction by 395passing it directly to a SQL execution method:: 396 397 with my_engine.connect() as conn: 398 seq = Sequence('some_sequence') 399 nextid = conn.execute(seq) 400 401In order to embed the "next value" function of a :class:`.Sequence` 402inside of a SQL statement like a SELECT or INSERT, use the :meth:`.Sequence.next_value` 403method, which will render at statement compilation time a SQL function that is 404appropriate for the target backend:: 405 406 >>> my_seq = Sequence('some_sequence') 407 >>> stmt = select(my_seq.next_value()) 408 >>> print(stmt.compile(dialect=postgresql.dialect())) 409 SELECT nextval('some_sequence') AS next_value_1 410 411.. _sequence_metadata: 412 413Associating a Sequence with the MetaData 414~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 415 416For many years, the SQLAlchemy documentation referred to the 417example of associating a :class:`.Sequence` with a table as follows:: 418 419 table = Table("cartitems", metadata_obj, 420 Column("cart_id", Integer, Sequence('cart_id_seq'), 421 primary_key=True), 422 Column("description", String(40)), 423 Column("createdate", DateTime()) 424 ) 425 426While the above is a prominent idiomatic pattern, it is recommended that 427the :class:`.Sequence` in most cases be explicitly associated with the 428:class:`_schema.MetaData`, using the :paramref:`.Sequence.metadata` parameter:: 429 430 table = Table("cartitems", metadata_obj, 431 Column( 432 "cart_id", 433 Integer, 434 Sequence('cart_id_seq', metadata=metadata_obj), primary_key=True), 435 Column("description", String(40)), 436 Column("createdate", DateTime()) 437 ) 438 439The :class:`.Sequence` object is a first class 440schema construct that can exist independently of any table in a database, and 441can also be shared among tables. Therefore SQLAlchemy does not implicitly 442modify the :class:`.Sequence` when it is associated with a :class:`_schema.Column` 443object as either the Python-side or server-side default generator. While the 444CREATE SEQUENCE / DROP SEQUENCE DDL is emitted for a :class:`.Sequence` 445defined as a Python side generator at the same time the table itself is subject 446to CREATE or DROP, this is a convenience feature that does not imply that the 447:class:`.Sequence` is fully associated with the :class:`_schema.MetaData` object. 448 449Explicitly associating the :class:`.Sequence` with :class:`_schema.MetaData` 450allows for the following behaviors: 451 452* The :class:`.Sequence` will inherit the :paramref:`_schema.MetaData.schema` 453 parameter specified to the target :class:`_schema.MetaData`, which 454 affects the production of CREATE / DROP DDL, if any. 455 456* The :meth:`.Sequence.create` and :meth:`.Sequence.drop` methods 457 automatically use the engine bound to the :class:`_schema.MetaData` 458 object, if any. 459 460* The :meth:`_schema.MetaData.create_all` and :meth:`_schema.MetaData.drop_all` 461 methods will emit CREATE / DROP for this :class:`.Sequence`, 462 even if the :class:`.Sequence` is not associated with any 463 :class:`_schema.Table` / :class:`_schema.Column` that's a member of this 464 :class:`_schema.MetaData`. 465 466Since the vast majority of cases that deal with :class:`.Sequence` expect 467that :class:`.Sequence` to be fully "owned" by the associated :class:`_schema.Table` 468and that options like default schema are propagated, setting the 469:paramref:`.Sequence.metadata` parameter should be considered a best practice. 470 471Associating a Sequence as the Server Side Default 472~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 473 474.. note:: The following technique is known to work only with the PostgreSQL 475 database. It does not work with Oracle. 476 477The preceding sections illustrate how to associate a :class:`.Sequence` with a 478:class:`_schema.Column` as the **Python side default generator**:: 479 480 Column( 481 "cart_id", Integer, Sequence('cart_id_seq', metadata=metadata_obj), 482 primary_key=True) 483 484In the above case, the :class:`.Sequence` will automatically be subject 485to CREATE SEQUENCE / DROP SEQUENCE DDL when the related :class:`_schema.Table` 486is subject to CREATE / DROP. However, the sequence will **not** be present 487as the server-side default for the column when CREATE TABLE is emitted. 488 489If we want the sequence to be used as a server-side default, 490meaning it takes place even if we emit INSERT commands to the table from 491the SQL command line, we can use the :paramref:`_schema.Column.server_default` 492parameter in conjunction with the value-generation function of the 493sequence, available from the :meth:`.Sequence.next_value` method. Below 494we illustrate the same :class:`.Sequence` being associated with the 495:class:`_schema.Column` both as the Python-side default generator as well as 496the server-side default generator:: 497 498 cart_id_seq = Sequence('cart_id_seq', metadata=metadata_obj) 499 table = Table("cartitems", metadata_obj, 500 Column( 501 "cart_id", Integer, cart_id_seq, 502 server_default=cart_id_seq.next_value(), primary_key=True), 503 Column("description", String(40)), 504 Column("createdate", DateTime()) 505 ) 506 507or with the ORM:: 508 509 class CartItem(Base): 510 __tablename__ = 'cartitems' 511 512 cart_id_seq = Sequence('cart_id_seq', metadata=Base.metadata) 513 cart_id = Column( 514 Integer, cart_id_seq, 515 server_default=cart_id_seq.next_value(), primary_key=True) 516 description = Column(String(40)) 517 createdate = Column(DateTime) 518 519When the "CREATE TABLE" statement is emitted, on PostgreSQL it would be 520emitted as:: 521 522 CREATE TABLE cartitems ( 523 cart_id INTEGER DEFAULT nextval('cart_id_seq') NOT NULL, 524 description VARCHAR(40), 525 createdate TIMESTAMP WITHOUT TIME ZONE, 526 PRIMARY KEY (cart_id) 527 ) 528 529Placement of the :class:`.Sequence` in both the Python-side and server-side 530default generation contexts ensures that the "primary key fetch" logic 531works in all cases. Typically, sequence-enabled databases also support 532RETURNING for INSERT statements, which is used automatically by SQLAlchemy 533when emitting this statement. However if RETURNING is not used for a particular 534insert, then SQLAlchemy would prefer to "pre-execute" the sequence outside 535of the INSERT statement itself, which only works if the sequence is 536included as the Python-side default generator function. 537 538The example also associates the :class:`.Sequence` with the enclosing 539:class:`_schema.MetaData` directly, which again ensures that the :class:`.Sequence` 540is fully associated with the parameters of the :class:`_schema.MetaData` collection 541including the default schema, if any. 542 543.. seealso:: 544 545 :ref:`postgresql_sequences` - in the PostgreSQL dialect documentation 546 547 :ref:`oracle_returning` - in the Oracle dialect documentation 548 549.. _computed_ddl: 550 551Computed Columns (GENERATED ALWAYS AS) 552-------------------------------------- 553 554.. versionadded:: 1.3.11 555 556The :class:`.Computed` construct allows a :class:`_schema.Column` to be declared in 557DDL as a "GENERATED ALWAYS AS" column, that is, one which has a value that is 558computed by the database server. The construct accepts a SQL expression 559typically declared textually using a string or the :func:`_expression.text` construct, in 560a similar manner as that of :class:`.CheckConstraint`. The SQL expression is 561then interpreted by the database server in order to determine the value for the 562column within a row. 563 564Example:: 565 566 from sqlalchemy import Table, Column, MetaData, Integer, Computed 567 568 metadata_obj = MetaData() 569 570 square = Table( 571 "square", 572 metadata_obj, 573 Column("id", Integer, primary_key=True), 574 Column("side", Integer), 575 Column("area", Integer, Computed("side * side")), 576 Column("perimeter", Integer, Computed("4 * side")), 577 ) 578 579The DDL for the ``square`` table when run on a PostgreSQL 12 backend will look 580like:: 581 582 CREATE TABLE square ( 583 id SERIAL NOT NULL, 584 side INTEGER, 585 area INTEGER GENERATED ALWAYS AS (side * side) STORED, 586 perimeter INTEGER GENERATED ALWAYS AS (4 * side) STORED, 587 PRIMARY KEY (id) 588 ) 589 590Whether the value is persisted upon INSERT and UPDATE, or if it is calculated 591on fetch, is an implementation detail of the database; the former is known as 592"stored" and the latter is known as "virtual". Some database implementations 593support both, but some only support one or the other. The optional 594:paramref:`.Computed.persisted` flag may be specified as ``True`` or ``False`` 595to indicate if the "STORED" or "VIRTUAL" keyword should be rendered in DDL, 596however this will raise an error if the keyword is not supported by the target 597backend; leaving it unset will use a working default for the target backend. 598 599The :class:`.Computed` construct is a subclass of the :class:`.FetchedValue` 600object, and will set itself up as both the "server default" and "server 601onupdate" generator for the target :class:`_schema.Column`, meaning it will be treated 602as a default generating column when INSERT and UPDATE statements are generated, 603as well as that it will be fetched as a generating column when using the ORM. 604This includes that it will be part of the RETURNING clause of the database 605for databases which support RETURNING and the generated values are to be 606eagerly fetched. 607 608.. note:: A :class:`_schema.Column` that is defined with the :class:`.Computed` 609 construct may not store any value outside of that which the server applies 610 to it; SQLAlchemy's behavior when a value is passed for such a column 611 to be written in INSERT or UPDATE is currently that the value will be 612 ignored. 613 614"GENERATED ALWAYS AS" is currently known to be supported by: 615 616* MySQL version 5.7 and onwards 617 618* MariaDB 10.x series and onwards 619 620* PostgreSQL as of version 12 621 622* Oracle - with the caveat that RETURNING does not work correctly with UPDATE 623 (a warning will be emitted to this effect when the UPDATE..RETURNING that 624 includes a computed column is rendered) 625 626* Microsoft SQL Server 627 628* SQLite as of version 3.31 629 630* Firebird 631 632When :class:`.Computed` is used with an unsupported backend, if the target 633dialect does not support it, a :class:`.CompileError` is raised when attempting 634to render the construct. Otherwise, if the dialect supports it but the 635particular database server version in use does not, then a subclass of 636:class:`.DBAPIError`, usually :class:`.OperationalError`, is raised when the 637DDL is emitted to the database. 638 639.. seealso:: 640 641 :class:`.Computed` 642 643.. _identity_ddl: 644 645Identity Columns (GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY) 646----------------------------------------------------------------- 647 648.. versionadded:: 1.4 649 650The :class:`.Identity` construct allows a :class:`_schema.Column` to be declared 651as an identity column and rendered in DDL as "GENERATED { ALWAYS | BY DEFAULT } 652AS IDENTITY". An identity column has its value automatically generated by the 653database server using an incrementing (or decrementing) sequence. The construct 654shares most of its option to control the database behaviour with 655:class:`.Sequence`. 656 657Example:: 658 659 from sqlalchemy import Table, Column, MetaData, Integer, Computed 660 661 metadata_obj = MetaData() 662 663 data = Table( 664 "data", 665 metadata_obj, 666 Column('id', Integer, Identity(start=42, cycle=True), primary_key=True), 667 Column('data', String) 668 ) 669 670The DDL for the ``data`` table when run on a PostgreSQL 12 backend will look 671like:: 672 673 CREATE TABLE data ( 674 id INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 42 CYCLE) NOT NULL, 675 data VARCHAR, 676 PRIMARY KEY (id) 677 ) 678 679The database will generate a value for the ``id`` column upon insert, 680starting from ``42``, if the statement did not already contain a value for 681the ``id`` column. 682An identity column can also require that the database generates the value 683of the column, ignoring the value passed with the statement or raising an 684error, depending on the backend. To activate this mode, set the parameter 685:paramref:`_schema.Identity.always` to ``True`` in the 686:class:`.Identity` construct. Updating the previous 687example to include this parameter will generate the following DDL:: 688 689 CREATE TABLE data ( 690 id INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 42 CYCLE) NOT NULL, 691 data VARCHAR, 692 PRIMARY KEY (id) 693 ) 694 695The :class:`.Identity` construct is a subclass of the :class:`.FetchedValue` 696object, and will set itself up as the "server default" generator for the 697target :class:`_schema.Column`, meaning it will be treated 698as a default generating column when INSERT statements are generated, 699as well as that it will be fetched as a generating column when using the ORM. 700This includes that it will be part of the RETURNING clause of the database 701for databases which support RETURNING and the generated values are to be 702eagerly fetched. 703 704The :class:`.Identity` construct is currently known to be supported by: 705 706* PostgreSQL as of version 10. 707 708* Oracle as of version 12. It also supports passing ``always=None`` to 709 enable the default generated mode and the parameter ``on_null=True`` to 710 specify "ON NULL" in conjunction with a "BY DEFAULT" identity column. 711 712* Microsoft SQL Server. MSSQL uses a custom syntax that only supports the 713 ``start`` and ``increment`` parameters, and ignores all other. 714 715When :class:`.Identity` is used with an unsupported backend, it is ignored, 716and the default SQLAlchemy logic for autoincrementing columns is used. 717 718An error is raised when a :class:`_schema.Column` specifies both an 719:class:`.Identity` and also sets :paramref:`_schema.Column.autoincrement` 720to ``False``. 721 722.. seealso:: 723 724 :class:`.Identity` 725 726 727Default Objects API 728------------------- 729 730.. autoclass:: Computed 731 :members: 732 733 734.. autoclass:: ColumnDefault 735 736 737.. autoclass:: DefaultClause 738 739 740.. autoclass:: DefaultGenerator 741 742 743.. autoclass:: FetchedValue 744 745 746.. autoclass:: Sequence 747 :members: 748 749 750.. autoclass:: Identity 751 :members: 752