POSTGRESQL-ESSENTIALS practice question 3 of 15
SERIAL and IDENTITY columns can be used to add an auto-incrementing column in a Postgres table. Which of these two column types are SQL standard compliant?
Full set: 71 questionsThe IDENTITY column is the SQL standard-compliant method for creating auto-incrementing columns. This feature was introduced in the SQL:2003 standard and subsequently implemented in PostgreSQL (starting with version 10) to align with this standard. In contrast, the SERIAL pseudo-type is a historical, PostgreSQL-specific shorthand. While functional, SERIAL is a notational convenience for creating a sequence and linking it to an integer column's default value; this specific mechanism is not part of the formal SQL standard. Therefore, IDENTITY is the correct choice for standard compliance.
A. Serial: This is a PostgreSQL-specific convenience syntax and not a feature defined within the SQL standard.
C. None: This is incorrect because the IDENTITY column is explicitly defined in the SQL standard (SQL:2003 and later).
1. PostgreSQL 16 Documentation, CREATE TABLE: In the description of column constraints, it states, "The IDENTITY constraint is the SQL-standard way to create an auto-incrementing column." This directly confirms that IDENTITY is the standard-compliant option.
2. PostgreSQL 16 Documentation, Section 8.1.4. Serial Types: This section describes serial types as "a notational convenience for creating unique identifier columns," highlighting their nature as a PostgreSQL-specific implementation rather than a standard type. It also notes that SERIAL is now superseded by the standard IDENTITY clause.
3. Ramakrishnan, R., & Gehrke, J. (2003). Database Management Systems (3rd ed.). McGraw-Hill. While a textbook, its principles are foundational. Chapter 5, "SQL: Queries, Constraints, Triggers," in discussions of the SQL:1999 and SQL:2003 standards, introduces identity columns as a standard feature for generating key values, which PostgreSQL later adopted. (Note: Specific page numbers vary by edition, but the concept is covered in the chapter on advanced SQL features and schema definition).
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 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…
- Question 7You need to configure your server to accept TCP/IP connections from specific IP addresses…
