DEFAULT values and expressions in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the default value inserted?

Consider the table users with a column created_at TIMESTAMP DEFAULT now(). What will be the value of created_at after running INSERT INTO users DEFAULT VALUES RETURNING created_at;?

PostgreSQL

CREATE TABLE users (id SERIAL PRIMARY KEY, created_at TIMESTAMP DEFAULT now());
INSERT INTO users DEFAULT VALUES RETURNING created_at;

A The timestamp 1970-01-01 00:00:00

B NULL

C The current timestamp at insertion time (e.g., 2024-06-01 12:00:00)

D An error because no value was provided

💡 Hint

The DEFAULT clause with now() sets the current time automatically.

The DEFAULT value now() is a function that returns the current timestamp when the row is inserted. So the inserted row gets the current time automatically.

📝 Syntax

intermediate

Which DEFAULT expression is valid?

Which of the following DEFAULT expressions for a column score INTEGER is syntactically valid in PostgreSQL?

A DEFAULT 10 + 5

B DEFAULT '10 + 5'

C DEFAULT 10 + '5'

D DEFAULT (10 + 5)

💡 Hint

DEFAULT expressions can be arithmetic expressions but must be enclosed in parentheses if complex.

Option D uses parentheses to enclose the arithmetic expression, which is valid. Option D is invalid syntax. Option D sets a string, which is invalid for INTEGER. Option D mixes integer and string causing error.

❓ optimization

advanced

Optimizing DEFAULT expressions with volatile functions

You have a column random_val INTEGER DEFAULT floor(random() * 100). What is a potential downside of this DEFAULT expression when inserting multiple rows in a single statement?

A Each row gets a different random value because DEFAULT is evaluated per row

B All rows get the same random value because DEFAULT is evaluated once per statement

C The query will fail because random() cannot be used in DEFAULT

D The random values are always zero due to floor()

💡 Hint

DEFAULT expressions with volatile functions are evaluated once per statement, not per row.

In PostgreSQL, DEFAULT expressions are evaluated once per statement, so all rows inserted in one statement get the same random value. To get different values per row, use triggers or generate values in the insert query.

🔧 Debug

advanced

Why does this DEFAULT expression cause an error?

Given the column definition price NUMERIC DEFAULT 'abc', what error will occur when inserting a row without specifying price?

PostgreSQL

CREATE TABLE products (id SERIAL PRIMARY KEY, price NUMERIC DEFAULT 'abc');
INSERT INTO products DEFAULT VALUES;

A ERROR: invalid input syntax for type numeric: "abc"

B ERROR: column price does not exist

C ERROR: syntax error at or near 'abc'

D No error, price is set to 0

💡 Hint

The DEFAULT value must be compatible with the column type.

The DEFAULT value 'abc' is a string that cannot be cast to numeric, so PostgreSQL raises an invalid input syntax error.

🧠 Conceptual

expert

How does PostgreSQL handle DEFAULT expressions with volatile functions in multi-row inserts?

Consider a table with a column val INTEGER DEFAULT (floor(random() * 10)). You run INSERT INTO table DEFAULT VALUES, DEFAULT VALUES, DEFAULT VALUES;. What will be the values of val in the inserted rows?

A All three rows have the same random value because DEFAULT is evaluated once per statement

B Each row has a different random value because DEFAULT is evaluated per row

C The insert fails because random() cannot be used in DEFAULT

D The values are NULL because DEFAULT cannot use functions

💡 Hint

Think about how PostgreSQL evaluates DEFAULT expressions with volatile functions in multi-row inserts.

PostgreSQL evaluates DEFAULT expressions once per statement, so all rows inserted in one statement get the same value from the volatile function random().