Serial and identity columns in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of inserting rows with SERIAL column

Consider the table users created as:

CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT);

After inserting three rows:

INSERT INTO users (name) VALUES ('Alice'), ('Bob'), ('Charlie');
SELECT * FROM users ORDER BY id;

What is the output?

PostgreSQL

CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT);
INSERT INTO users (name) VALUES ('Alice'), ('Bob'), ('Charlie');
SELECT * FROM users ORDER BY id;

A

1 | Alice
2 | Bob
3 | Charlie

B

0 | Alice
1 | Bob
2 | Charlie

C

NULL | Alice
NULL | Bob
NULL | Charlie

D

1 | Alice
1 | Bob
1 | Charlie

💡 Hint

Remember that SERIAL creates an auto-incrementing integer starting at 1.

The SERIAL type creates an integer column that auto-increments starting at 1 by default. Each inserted row gets a unique id incremented by 1.

📝 Syntax

intermediate

Correct syntax to create an IDENTITY column

Which of the following SQL statements correctly creates a table products with an id column as an IDENTITY column starting at 1000?

A CREATE TABLE products (id SERIAL START 1000, name TEXT);

B CREATE TABLE products (id INT GENERATED ALWAYS AS IDENTITY (START 1000), name TEXT);

C CREATE TABLE products (id INT IDENTITY START 1000, name TEXT);

D CREATE TABLE products (id INT GENERATED BY DEFAULT AS IDENTITY (START WITH 1000), name TEXT);

💡 Hint

Check the exact syntax for IDENTITY columns in PostgreSQL.

The correct syntax uses GENERATED BY DEFAULT AS IDENTITY with START WITH to specify the starting value.

🧠 Conceptual

advanced

Difference between SERIAL and IDENTITY columns

Which statement best describes the difference between SERIAL and IDENTITY columns in PostgreSQL?

A SERIAL is a pseudo-type that creates a sequence and sets a default; IDENTITY is a SQL standard feature that defines auto-increment behavior within the column definition.

B SERIAL columns cannot be primary keys; IDENTITY columns can only be used as primary keys.

C SERIAL columns are deprecated and no longer supported; IDENTITY columns are the only way to auto-increment.

D SERIAL columns automatically update on row update; IDENTITY columns only increment on insert.

💡 Hint

Think about how SERIAL and IDENTITY are implemented internally.

SERIAL creates a sequence object and sets a default value using nextval(). IDENTITY columns are defined by SQL standard syntax and managed internally by PostgreSQL.

🔧 Debug

advanced

Why does this INSERT fail with IDENTITY column?

Given the table:

CREATE TABLE orders (order_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, product TEXT);

Which INSERT statement will cause an error?

A INSERT INTO orders (product) VALUES ('Book');

B INSERT INTO orders DEFAULT VALUES;

C INSERT INTO orders (order_id, product) VALUES (5, 'Pen');

D INSERT INTO orders (product) VALUES ('Notebook');

💡 Hint

Check the behavior of GENERATED ALWAYS AS IDENTITY when inserting explicit values.

For GENERATED ALWAYS AS IDENTITY, you cannot insert explicit values into the identity column; it is generated automatically. Option C tries to insert a value explicitly, causing an error.

❓ optimization

expert

Best practice for large tables needing auto-increment keys

You have a very large table that requires a unique auto-incrementing primary key. Which approach is best for performance and future-proofing in PostgreSQL?

A Use SERIAL with default INT type and no caching.

B Use BIGINT GENERATED BY DEFAULT AS IDENTITY with CACHE to reduce sequence calls.

C Manually create a sequence and increment it in application code before insert.

D Use UUID columns generated by the application instead of numeric auto-increment.

💡 Hint

Consider sequence caching and data type size for large tables.

Using BIGINT IDENTITY with CACHE improves performance by reducing sequence access overhead and supports very large numbers. SERIAL uses INT and no caching by default, which can limit scalability.