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 questions
Answer options
A: Serial
B: Identity Correct answer
C: None
Correct Answer:
B. Identity
Explanation

The 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.

Why Incorrect

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).

References

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).

Scroll to Top