Postgresql easy
In modern PostgreSQL, what is the standard-compliant way to define a column that automatically increments for unique record identification?
- A AUTO_INCREMENT: A standard keyword used in MySQL to manage sequences.
- B GENERATED ALWAYS AS IDENTITY: The SQL-standard way to manage ID sequences.
- C PRIMARY KEY INCREMENT: A specialized constraint for table-level UID logic.
- D UUID_V4_DEFAULT: A global identifier that replaces the need for integers.
Answer: B. GENERATED ALWAYS AS IDENTITY: The SQL-standard way to manage ID sequences.
While `SERIAL` is common in older tutorials, `GENERATED ALWAYS AS IDENTITY` is the modern SQL Standard approach. It provides better ownership semantics and prevents manual insertion bypasses more effectively.
One system design question a day, with spaced repetition so the ones you miss come back.
Get the appRelated questions
- In a PostgreSQL environment, what functional characteristic distinguishes a `MATERIALIZED VIEW` from a standard `VIEW`?
- Which standard PostgreSQL utility is designed to extract a single database into a script or archive file for backup purposes?
- Why would PostgreSQL reject a `DELETE` operation on a record despite the user having full administrative privileges?
- The 'Heap Only Tuples' (HOT) optimization improves UPDATE performance. Which specific set of conditions must be met for a HOT update to occur?
- Which native command-line utility is used to execute SQL queries and manage PostgreSQL databases from a terminal session?
- What is the specific purpose of 'Freezing' a row (modifying its `xmin` status) during a major VACUUM operation?