DEFAULT values and expressions in PostgreSQL - Step-by-Step Execution

Concept Flow - DEFAULT values and expressions

Start INSERT

↓

Check each column

↓

Value provided?

Yes→Use provided value

No↓

Default defined?

Yes→Use DEFAULT value/expression

No↓

Use NULL or error if NOT NULL

↓

Insert row with final values

↓

END

When inserting a row, for each column, if no value is given, PostgreSQL uses the DEFAULT value or expression if defined; otherwise NULL or error if NOT NULL.

Execution Sample

PostgreSQL

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT DEFAULT 'Anonymous',
  created_at TIMESTAMP DEFAULT now()
);

INSERT INTO users (id) VALUES (1);

Creates a table with default values and inserts a row providing only id, so defaults fill other columns.

Execution Table

Step	Column	Value Provided?	Default Defined?	Value Used	Action
1	id	Yes (1)	Yes (serial)	1	Use provided value
2	name	No	Yes ('Anonymous')	'Anonymous'	Use default value
3	created_at	No	Yes (now())	current timestamp	Use default expression
4	Insert row	-	-	(1, 'Anonymous', current timestamp)	Row inserted with defaults
5	End	-	-	-	Insertion complete

💡 All columns processed; defaults used where no value provided; row inserted successfully.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	Final
id	NULL	1	1	1	1
name	NULL	NULL	'Anonymous'	'Anonymous'	'Anonymous'
created_at	NULL	NULL	NULL	current timestamp	current timestamp

Key Moments - 3 Insights

What happens if no value is provided and no DEFAULT is defined?

If the column allows NULL, NULL is used; otherwise, an error occurs. See execution_table row 2 where default exists, but if it didn't, NULL or error would happen.

How does PostgreSQL handle DEFAULT expressions like now()?

It evaluates the expression at insertion time to get the current value, as shown in execution_table row 3 where now() returns the current timestamp.

Can you override a DEFAULT value by providing a value explicitly?

Yes, if you provide a value, PostgreSQL uses it instead of the default, as in execution_table row 1 where id is provided explicitly.

Visual Quiz - 3 Questions

Look at the execution_table, what value is used for the 'name' column at step 2?

A 'Anonymous'

B NULL

C Error

D Empty string

💡 Hint

Check the 'Value Used' column in execution_table row 2.

At which step does PostgreSQL use the provided value instead of the default?

A Step 2

B Step 1

C Step 3

D Step 4

💡 Hint

Look at the 'Value Provided?' column in execution_table; only step 1 has a provided value.

If the 'created_at' column had no DEFAULT, what would happen at step 3?

A Use 'Anonymous' string

B Use current timestamp anyway

C Use NULL if allowed or error if NOT NULL

D Insert fails immediately at step 1

💡 Hint

Refer to key_moments about missing DEFAULT values and NULL handling.

Concept Snapshot

DEFAULT values and expressions in PostgreSQL:
- When inserting, if no value given, use DEFAULT if defined.
- DEFAULT can be a constant or expression (e.g., now()).
- If no DEFAULT and column allows NULL, NULL is used.
- If NOT NULL without DEFAULT and no value, insertion errors.
- Explicit values override DEFAULTs.

Full Transcript

This visual execution shows how PostgreSQL handles DEFAULT values and expressions during an INSERT. For each column, it checks if a value is provided. If yes, it uses that value. If no, it checks if a DEFAULT is defined. If so, it uses the default value or evaluates the default expression like now(). If no default exists, it uses NULL if allowed or raises an error if the column is NOT NULL. The example inserts a row into a users table with id provided, and name and created_at filled by defaults. The step-by-step table tracks each column's value choice and the final inserted row. Key moments clarify common confusions about missing defaults, expression evaluation, and overriding defaults. The quiz tests understanding of these steps.

Practice

1. What does the DEFAULT keyword do in a PostgreSQL table column definition?

easy

A. Sets a value automatically when no value is provided during insert

B. Deletes the column if no value is provided

C. Requires the user to always provide a value

D. Creates an index on the column

Solution

1. Step 1: Understand the purpose of DEFAULT

   The DEFAULT keyword assigns a value automatically if no value is given during an insert operation.

2. Step 2: Compare options with this behavior

   Only Sets a value automatically when no value is provided during insert correctly describes this automatic assignment behavior.

3. Final Answer:

   Sets a value automatically when no value is provided during insert -> Option A

4. Quick Check:

   DEFAULT = automatic value assignment [OK]

Hint: DEFAULT fills missing values automatically [OK]

Common Mistakes:

• Thinking DEFAULT deletes or modifies columns
• Confusing DEFAULT with NOT NULL constraint
• Assuming DEFAULT requires manual input

2. Which of the following is the correct syntax to set a default value of 100 for a column named score in PostgreSQL?

easy

A. ALTER TABLE mytable ALTER COLUMN score SET DEFAULT 100;

B. ALTER TABLE mytable SET DEFAULT score = 100;

C. ALTER TABLE mytable CHANGE score DEFAULT 100;

D. ALTER TABLE mytable ADD DEFAULT score = 100;

Solution

1. Step 1: Recall ALTER TABLE syntax for setting default

   In PostgreSQL, to set a default value, use: ALTER TABLE table ALTER COLUMN column SET DEFAULT value;

2. Step 2: Match syntax with options

   Only ALTER TABLE mytable ALTER COLUMN score SET DEFAULT 100; matches the correct syntax exactly.

3. Final Answer:

   ALTER TABLE mytable ALTER COLUMN score SET DEFAULT 100; -> Option A

4. Quick Check:

   ALTER COLUMN ... SET DEFAULT = correct syntax [OK]

Hint: Use ALTER COLUMN ... SET DEFAULT to set defaults [OK]

Common Mistakes:

• Using SET DEFAULT before column name
• Using CHANGE instead of ALTER COLUMN
• Trying to ADD DEFAULT as a separate clause

3. Given the table definition:

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  order_date DATE DEFAULT CURRENT_DATE,
  status TEXT DEFAULT 'pending'
);

What will be the result of this query?

INSERT INTO orders (status) VALUES ('shipped') RETURNING *;

medium

A. A new row with default status 'pending' and NULL order_date

B. A new row with NULL order_date and status 'shipped'

C. A new row with current date as order_date and status 'shipped'

D. Error because order_date is missing

Solution

1. Step 1: Understand DEFAULT behavior on insert

   Since order_date has DEFAULT CURRENT_DATE, if not provided, it uses today's date automatically.

2. Step 2: Analyze the insert statement

   The insert provides status = 'shipped', so it overrides the default 'pending'. The order_date is omitted, so default applies.

3. Final Answer:

   A new row with current date as order_date and status 'shipped' -> Option C

4. Quick Check:

   DEFAULT fills missing columns, provided columns override [OK]

Hint: Defaults fill missing columns, provided values override [OK]

Common Mistakes:

• Assuming missing columns become NULL instead of default
• Expecting error when default exists
• Confusing default value with NOT NULL constraint

4. Consider this table:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

Which of the following INSERT statements will cause an error?

medium

A. INSERT INTO users (id, username, created_at) VALUES (DEFAULT, 'alice', DEFAULT);

B. INSERT INTO users (username, created_at) VALUES ('bob', DEFAULT);

C. INSERT INTO users (username) VALUES ('alice');

D. INSERT INTO users (created_at) VALUES (NOW());

Solution

1. Step 1: Check NOT NULL column requirements

   The username column is NOT NULL and must be provided in inserts.

2. Step 2: Analyze each insert

   INSERT INTO users (created_at) VALUES (NOW()); inserts only created_at but omits username, causing a NOT NULL violation error.

3. Final Answer:

   INSERT INTO users (created_at) VALUES (NOW()); -> Option D

4. Quick Check:

   NOT NULL columns must be provided [OK]

Hint: NOT NULL columns require values, even with defaults [OK]

Common Mistakes:

• Assuming DEFAULT VALUES inserts NULL for NOT NULL columns
• Thinking DEFAULT can replace NOT NULL columns
• Ignoring NOT NULL constraints

5. You want to create a table products where the created_at column defaults to the current timestamp, and the price column defaults to 9.99 if no price is given. Which of the following CREATE TABLE statements correctly implements this?

hard

A. CREATE TABLE products (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
price NUMERIC DEFAULT 9.99,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP()
);

B. CREATE TABLE products (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
price NUMERIC DEFAULT 9.99,
created_at TIMESTAMP DEFAULT NOW()
);

C. CREATE TABLE products (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
price NUMERIC DEFAULT '9.99',
created_at TIMESTAMP DEFAULT NOW
);

D. CREATE TABLE products (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
price NUMERIC DEFAULT 9.99,
created_at TIMESTAMP DEFAULT CURRENT_DATE
);

Solution

1. Step 1: Check default for price column

   Price should default to numeric 9.99 without quotes (numeric literal).

2. Step 2: Check default for created_at column

   Use NOW() function call for current timestamp, not CURRENT_TIMESTAMP or CURRENT_DATE (date only).

3. Step 3: Validate syntax correctness

   CREATE TABLE products ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, price NUMERIC DEFAULT 9.99, created_at TIMESTAMP DEFAULT NOW() ); uses price NUMERIC DEFAULT 9.99 and created_at TIMESTAMP DEFAULT NOW(), which is correct syntax and behavior.

4. Final Answer:

   CREATE TABLE products ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, price NUMERIC DEFAULT 9.99, created_at TIMESTAMP DEFAULT NOW() ); -> Option B

5. Quick Check:

   Use NOW() for timestamp, numeric literals without quotes [OK]

Hint: Use NOW() for timestamp defaults, numeric literals without quotes [OK]

Common Mistakes:

• Using quotes around numeric default values
• Using CURRENT_DATE instead of CURRENT_TIMESTAMP for timestamps
• Omitting parentheses in NOW() function