Serial and identity columns in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a serial column in PostgreSQL?

A serial column is a special integer column that automatically increments its value for each new row. It uses a sequence behind the scenes to generate unique numbers.

intermediate

How does an identity column differ from a serial column?

An identity column is part of the SQL standard and provides automatic numbering like serial, but it is more flexible and preferred in modern PostgreSQL versions. It can be defined as GENERATED ALWAYS or GENERATED BY DEFAULT.

beginner

What SQL syntax creates a serial column?

Example: <br>CREATE TABLE users (id serial PRIMARY KEY, name text);<br>This creates an integer column id that auto-increments with each new row.

intermediate

Write the SQL to create an identity column named id that always generates a new value.

Example:<br>CREATE TABLE users (id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name text);<br>This creates an id column that automatically generates unique values and does not allow manual inserts.

intermediate

Why might you choose identity columns over serial columns?

Because identity columns follow the SQL standard, offer more control, and avoid creating implicit sequences, making your database design cleaner and more portable.

Which PostgreSQL column type automatically creates a sequence and increments values?

A serial

B varchar

C text

D boolean

The serial type automatically creates a sequence and increments integer values for new rows.

What keyword is used to define an identity column that always generates a value?

A GENERATED BY DEFAULT

B GENERATED ALWAYS

C AUTO_INCREMENT

D SERIAL

GENERATED ALWAYS AS IDENTITY means the database always generates the value automatically.

Which of these is a benefit of using identity columns over serial columns?

A They are part of the SQL standard

B They allow text values

C They do not auto-increment

D They require manual sequence creation

Identity columns follow the SQL standard, making them more portable and standardized.

What happens if you insert a row without specifying a value for a serial column?

A You must provide a value manually

B The insert fails with an error

C The column is set to NULL

D The database generates the next number automatically

Serial columns auto-increment, so the database fills in the next number automatically.

Which statement is true about GENERATED BY DEFAULT AS IDENTITY?

A It is the same as <code>serial</code>

B The database always generates the value and disallows manual inserts

C You can insert your own value or let the database generate one

D It disables auto-increment

GENERATED BY DEFAULT allows manual insertion of values but generates one if none is provided.

Explain the difference between serial and identity columns in PostgreSQL.

Think about how the numbering is generated and standards.

Describe how to create a table with an auto-incrementing primary key using identity columns.

Focus on the syntax for identity columns.