In PostgreSQL 13, what is the difference between GENERATED ALWAYS AS IDENTITY and GENERATED BY DEFAULT AS IDENTITY?

The GENERATED ALWAYS AS IDENTITY constraint enforces that the value for the identity column is always generated by the system using the associated sequence. Any attempt to manually insert or update a value in this column will result in an error, unless the OVERRIDING SYSTEM VALUE clause is used.

Conversely, GENERATED BY DEFAULT AS IDENTITY allows a user to provide a specific value for the column in an INSERT statement. If a value is provided, it overrides the sequence-generated default. If no value is supplied for the column, the system generates one as the default.

B. They are not identical; they define different behaviors for handling user-supplied values during INSERT operations.

C. Both identity types use integer-based sequences (smallint, integer, bigint), not UUIDs. serial is an older, non-standard pseudo-type.

D. This is the opposite of the correct behavior. BY DEFAULT explicitly allows manual insertion, which overrides the default generation.

---

1. PostgreSQL 13 Official Documentation, CREATE TABLE command: In the section describing column constraints, it states: "ALWAYS will always generate a value for the identity column. An attempt to insert or update a user-supplied value will be rejected with an error... BY DEFAULT will generate a value for the identity column if one is not supplied... but a user-supplied value can be inserted by explicitly specifying it."

Source: PostgreSQL 13 Documentation, SQL Commands, CREATE TABLE.

2. PostgreSQL 13 Official Documentation, Data Definition, Identity Columns: This section clarifies the behavior with examples: "If you use GENERATED BY DEFAULT, then the user-specified value is used... If you use GENERATED ALWAYS, a user-specified value is only accepted if the INSERT statement specifies OVERRIDING SYSTEM VALUE."

Source: PostgreSQL 13 Documentation, Chapter 5. Data Definition, Section 5.4. Identity Columns.