POSTGRESQL-ESSENTIALS practice question 10 of 15
In PostgreSQL 13, what is the difference between GENERATED ALWAYS AS IDENTITY and GENERATED BY DEFAULT AS IDENTITY?
Full set: 71 questionsThe 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.
Keep practicing
Take the free POSTGRESQL-ESSENTIALS practice test
Ten exam-style questions with answers and explanations, plus the exam facts and study guides.
More questions
Other POSTGRESQL-ESSENTIALS practice questions
- Question 1What is the purpose of the autovacuum parameter in PostgreSQL?
- Question 2Which psql meta-command is used to display all roles and their attributes?
- Question 3SERIAL and IDENTITY columns can be used to add an auto-incrementing column in a Postgres …
- Question 4You are connected to a PostgreSQL database. After executing a complex query an error mess…
- Question 5Which PostgreSQL configuration parameter controls the amount of memory used for sorting o…
- Question 6You want to set the maximum time to complete client authentication to 10 seconds. Which p…
