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.

Practice

1.

What is the main purpose of using SERIAL or IDENTITY columns in PostgreSQL?

easy

A. To automatically generate unique numbers for new rows

B. To store large text data efficiently

C. To create foreign key relationships

D. To improve query performance by indexing

Solution

1. Step 1: Understand the role of SERIAL and IDENTITY

   Both SERIAL and IDENTITY columns are designed to automatically generate unique numeric values for new rows in a table.

2. Step 2: Identify the main purpose

   This automatic numbering helps avoid manual entry and ensures unique identifiers for each row.

3. Final Answer:

   To automatically generate unique numbers for new rows -> Option A

4. Quick Check:

   Serial and identity columns = auto unique numbers [OK]

Hint: They auto-generate unique IDs for new rows [OK]

Common Mistakes:

• Thinking SERIAL stores text data
• Confusing SERIAL with indexing
• Assuming SERIAL creates foreign keys

2.

Which of the following is the correct syntax to create a table with an IDENTITY column named id in PostgreSQL?

CREATE TABLE users (
  id ???,
  name TEXT
);

easy

A. INT GENERATED AS IDENTITY

B. SERIAL PRIMARY KEY

C. INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY

D. INTEGER AUTO_INCREMENT PRIMARY KEY

Solution

1. Step 1: Recall IDENTITY syntax in PostgreSQL

   The correct syntax uses GENERATED ALWAYS AS IDENTITY for identity columns.

2. Step 2: Match the option with correct syntax

   INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY uses INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, which is valid PostgreSQL syntax.

3. Final Answer:

   INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY -> Option C

4. Quick Check:

   IDENTITY syntax = GENERATED ALWAYS AS IDENTITY [OK]

Hint: IDENTITY uses GENERATED ALWAYS AS IDENTITY syntax [OK]

Common Mistakes:

• Using AUTO_INCREMENT (MySQL syntax)
• Confusing SERIAL with IDENTITY syntax
• Omitting GENERATED ALWAYS or BY DEFAULT

3.

Given the table creation:

CREATE TABLE products (
  product_id SERIAL PRIMARY KEY,
  name TEXT
);

INSERT INTO products (name) VALUES ('Pen'), ('Pencil'), ('Eraser');

SELECT product_id FROM products ORDER BY product_id;

What will be the output of the SELECT query?

medium

A. [3, 2, 1]

B. [0, 1, 2]

C. [NULL, NULL, NULL]

D. [1, 2, 3]

Solution

1. Step 1: Understand SERIAL behavior on insert

   The SERIAL column auto-increments starting from 1 for each inserted row.

2. Step 2: Check inserted rows and ordering

   Three rows inserted get product_id values 1, 2, and 3 respectively. Ordering by product_id returns them in ascending order.

3. Final Answer:

   [1, 2, 3] -> Option D

4. Quick Check:

   SERIAL starts at 1 and increments [OK]

Hint: SERIAL starts numbering at 1 by default [OK]

Common Mistakes:

• Assuming numbering starts at 0
• Expecting NULL values for SERIAL
• Thinking order is descending by default

4.

Consider this table creation:

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

Which of the following statements will cause an error when inserting data?

medium

A. INSERT INTO orders (order_id, product) VALUES (5, 'Pen'), (5, 'Pencil');

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

C. INSERT INTO orders (product) VALUES ('Book');

D. INSERT INTO orders (order_id, product) VALUES (DEFAULT, 'Notebook');

Solution

1. Step 1: Understand GENERATED BY DEFAULT AS IDENTITY behavior

   This allows manual insertion of order_id or automatic generation if omitted.

2. Step 2: Analyze each insert statement

   INSERT INTO orders (order_id, product) VALUES (5, 'Pen'), (5, 'Pencil'); tries to insert two rows with the same order_id = 5, violating the primary key uniqueness constraint, causing an error.

3. Final Answer:

   INSERT with duplicate order_id values causes error -> Option A

4. Quick Check:

   Duplicate primary keys cause insert errors [OK]

Hint: Duplicate primary keys cause insert errors [OK]

Common Mistakes:

• Assuming GENERATED BY DEFAULT forbids manual IDs
• Ignoring primary key uniqueness
• Thinking DEFAULT keyword causes errors

5.

You want to create a table employees with a IDENTITY column named id that automatically generates unique IDs but also allows you to insert specific IDs manually when needed. Which is the best way to define the ID column?

hard

A. Use SERIAL PRIMARY KEY for the ID column

B. Use INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY

C. Use INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY

D. Use INTEGER without any auto-generation

Solution

1. Step 1: Understand difference between GENERATED ALWAYS and BY DEFAULT

   GENERATED ALWAYS AS IDENTITY disallows manual inserts; BY DEFAULT allows manual ID insertion.

2. Step 2: Match requirement to syntax

   Since manual insertion is needed sometimes, GENERATED BY DEFAULT AS IDENTITY is the best choice.

3. Final Answer:

   INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY -> Option B

4. Quick Check:

   BY DEFAULT allows manual IDs, ALWAYS does not [OK]

Hint: BY DEFAULT allows manual ID insertion [OK]

Common Mistakes:

• Choosing GENERATED ALWAYS which forbids manual IDs
• Using SERIAL which is less flexible
• Not using any auto-generation and risking duplicates