DEFAULT values and expressions in PostgreSQL - Deep Dive

Overview - DEFAULT values and expressions

What is it?

DEFAULT values and expressions in PostgreSQL let you specify a value that a column will automatically get if no value is provided when inserting a new row. This means you don't have to always write every value explicitly. You can use simple constants like numbers or strings, or more complex expressions like functions or calculations. This helps keep your data consistent and your insert commands shorter.

Why it matters

Without DEFAULT values, every insert would need all column values specified, which is tedious and error-prone. DEFAULTs save time and reduce mistakes by filling in common or calculated values automatically. They also help enforce business rules, like setting creation timestamps or default statuses, making your database smarter and more reliable.

Where it fits

Before learning DEFAULT values, you should understand basic table creation and inserting data in PostgreSQL. After mastering DEFAULTs, you can explore triggers and generated columns for more advanced automatic data handling.

Mental Model

Core Idea

A DEFAULT value or expression is a preset answer your database gives when you forget to provide one during data entry.

Think of it like...

It's like when you order coffee and don't specify milk or sugar, the barista automatically adds the usual amount you like without asking every time.

┌─────────────┐
│ Insert Row  │
└──────┬──────┘
       │
       ▼
┌─────────────────────────────┐
│ Check each column for value │
└─────────────┬───────────────┘
              │
    ┌─────────┴─────────┐
    │                   │
Value given?       No value?
    │                   │
    ▼                   ▼
Use given value   Use DEFAULT value/expression
    │                   │
    └─────────┬─────────┘
              ▼
       Insert complete row

Build-Up - 7 Steps

1

Foundation

What is a DEFAULT value

Concept: Introduce the idea that columns can have preset values used when no explicit value is given.

In PostgreSQL, when you create a table, you can specify DEFAULT values for columns. For example, if you have a column 'status' and you want it to be 'active' unless told otherwise, you write: CREATE TABLE users (id SERIAL, status TEXT DEFAULT 'active'); Now, if you insert a row without specifying 'status', it will automatically be 'active'.

Result

Inserting into users without 'status' sets 'status' to 'active' automatically.

Understanding DEFAULT values helps you reduce repetitive data entry and ensures columns have meaningful values even when omitted.

2

Foundation

Using DEFAULT expressions

Concept: DEFAULT can be not just a fixed value but an expression or function call.

You can use functions or calculations as DEFAULTs. For example, to set a timestamp when a row is created: CREATE TABLE events (id SERIAL, created_at TIMESTAMP DEFAULT now()); Here, 'now()' is a function that returns the current time. Each new row gets the current time if you don't provide 'created_at'.

Result

New rows get the current timestamp automatically if 'created_at' is not specified.

Knowing DEFAULT expressions lets you automate dynamic values like timestamps or calculated fields.

3

Intermediate

Overriding DEFAULT on insert

🤔Before reading on: If you specify a value for a column with a DEFAULT, do you think the DEFAULT is used or your value is saved? Commit to your answer.

Concept: When inserting, explicitly given values always override DEFAULTs.

If you insert a row and provide a value for a column that has a DEFAULT, PostgreSQL uses your value, not the DEFAULT. For example, with the 'status' column defaulting to 'active', if you insert ('inactive'), the row stores 'inactive'. DEFAULT only applies when you omit the column in the insert.

Result

Explicit values replace DEFAULTs; DEFAULTs only fill missing data.

Understanding this prevents confusion about when DEFAULTs apply and helps you control data precisely.

4

Intermediate

DEFAULT with NULL values

🤔Before reading on: If you insert NULL explicitly into a column with a DEFAULT, do you think the DEFAULT is used or NULL is stored? Commit to your answer.

Concept: DEFAULT is used only when the column is omitted, not when NULL is explicitly inserted.

If you insert NULL explicitly into a column with a DEFAULT, PostgreSQL stores NULL, not the DEFAULT. For example, inserting (NULL) into 'status' stores NULL, even if 'status' has DEFAULT 'active'. DEFAULT only triggers when the column is missing from the insert statement.

Result

Explicit NULL values override DEFAULTs and are stored as NULL.

Knowing this helps avoid unintended NULLs and clarifies how DEFAULT interacts with NULL.

5

Intermediate

DEFAULT and INSERT syntax shortcuts

Concept: PostgreSQL allows using the keyword DEFAULT in insert statements to explicitly use the default value.

You can write INSERT INTO users (id, status) VALUES (1, DEFAULT); to explicitly insert the default value for 'status'. This is useful when you want to specify some columns but still use defaults for others.

Result

The row inserts with 'status' set to its DEFAULT value.

Using DEFAULT in inserts gives you fine control to mix explicit and default values.

6

Advanced

DEFAULT expressions with volatile functions

🤔Before reading on: Do you think DEFAULT expressions with functions like random() are evaluated once or every time a row is inserted? Commit to your answer.

Concept: DEFAULT expressions are evaluated each time a row is inserted, so volatile functions produce different results per row.

If you use a volatile function like random() as a DEFAULT, PostgreSQL calls it for every new row. For example: CREATE TABLE dice_rolls (id SERIAL, roll FLOAT DEFAULT random()*6 + 1); Each inserted row gets a different random number between 1 and 7.

Result

Each row inserted has a unique random roll value.

Understanding evaluation timing helps you design dynamic defaults that change per row.

7

Expert

DEFAULT values and performance considerations

🤔Before reading on: Do you think using complex DEFAULT expressions affects insert performance significantly? Commit to your answer.

Concept: Complex DEFAULT expressions can impact insert speed, especially if they call expensive functions or subqueries.

When you use DEFAULT expressions that involve calculations, function calls, or subqueries, PostgreSQL must evaluate them for each inserted row. This can slow down bulk inserts. For example, a DEFAULT that queries another table or performs heavy math will add overhead. Planning DEFAULTs carefully balances convenience and performance.

Result

Insert operations may slow down if DEFAULT expressions are complex or costly.

Knowing performance impact guides you to keep DEFAULT expressions efficient or use triggers for heavy logic.

Under the Hood

When you insert a row, PostgreSQL checks each column. If the insert statement omits a column, PostgreSQL looks for a DEFAULT value or expression defined in the table schema. If found, it evaluates the expression at insert time and uses that value. If no DEFAULT exists, it inserts NULL or errors if the column is NOT NULL. DEFAULT expressions are stored as part of the table metadata and evaluated by the query executor during insertion.

Why designed this way?

DEFAULT values were designed to simplify data entry and enforce consistent data without requiring application logic. Storing expressions rather than fixed values allows dynamic defaults like timestamps or random numbers. This design balances flexibility and simplicity, avoiding the need for triggers for common default behaviors.

┌───────────────┐
│ Insert Command│
└───────┬───────┘
        │
        ▼
┌─────────────────────────────┐
│ For each column in the row:  │
│  Is value provided?          │
└─────────────┬───────────────┘
              │
      ┌───────┴───────┐
      │               │
     Yes             No
      │               │
      ▼               ▼
Use given value  Check for DEFAULT
                      │
              ┌───────┴────────┐
              │                │
           DEFAULT?         No DEFAULT
              │                │
              ▼                ▼
     Evaluate expression   Insert NULL or error
              │
              ▼
       Store value in row

Myth Busters - 4 Common Misconceptions

Quick: If you insert NULL explicitly into a column with a DEFAULT, does PostgreSQL use the DEFAULT or store NULL? Commit to your answer.

Common Belief: Inserting NULL into a column with a DEFAULT will cause PostgreSQL to use the DEFAULT value instead of NULL.

Reality: PostgreSQL stores the explicit NULL value; DEFAULT is only used when the column is omitted in the insert.

Why it matters: Assuming DEFAULT replaces NULL can lead to unexpected NULLs in your data, causing bugs or incorrect application behavior.

Quick: Does specifying a value for a column with a DEFAULT cause the DEFAULT to be ignored or used? Commit to your answer.

Common Belief: If you specify a value for a column with a DEFAULT, PostgreSQL still applies the DEFAULT value.

Reality: PostgreSQL uses the explicitly provided value and ignores the DEFAULT in that case.

Why it matters: Misunderstanding this can cause confusion about why your data doesn't have expected default values.

Quick: Are DEFAULT expressions evaluated once when the table is created or every time a row is inserted? Commit to your answer.

Common Belief: DEFAULT expressions are evaluated once when the table is created and reused for all rows.

Reality: DEFAULT expressions are evaluated each time a row is inserted, so volatile functions produce different results per row.

Why it matters: This affects how dynamic defaults like timestamps or random values behave, which is critical for correct data.

Quick: Does using complex DEFAULT expressions have no impact on insert performance? Commit to your answer.

Common Belief: DEFAULT expressions do not affect insert performance significantly, even if complex.

Reality: Complex or costly DEFAULT expressions can slow down inserts because they are evaluated for every row.

Why it matters: Ignoring performance impact can cause slow database operations and scalability issues.

Expert Zone

1

DEFAULT expressions can reference immutable functions only; using volatile functions is allowed but can cause unexpected behavior in some contexts like index expressions.

2

When using DEFAULT with GENERATED columns, the interaction can be subtle because GENERATED columns compute values automatically and cannot have explicit inserts.

3

DEFAULT values are stored in the system catalogs, so changing a DEFAULT requires an ALTER TABLE command, which can lock the table briefly.

When NOT to use

Avoid using DEFAULT expressions for complex logic or cross-table dependencies; instead, use triggers or application logic. Also, do not rely on DEFAULTs for data validation or conditional logic, which should be handled by constraints or triggers.

Production Patterns

In production, DEFAULTs are commonly used for timestamps (e.g., created_at DEFAULT now()), status flags, and simple calculated values. Complex defaults are often replaced by triggers for better control. DEFAULTs are also used with INSERT ... ON CONFLICT to simplify upserts.

Connections

Triggers

Builds-on

Understanding DEFAULT values helps grasp triggers because both automate data changes, but triggers allow more complex logic beyond simple defaults.

Immutable and Volatile Functions

Same domain, related behavior

Knowing how function volatility affects DEFAULT expressions clarifies when defaults produce consistent or varying results.

Factory Pattern (Software Design)

Similar pattern

DEFAULT values act like a factory that produces default objects (values) when none are provided, showing how database design and software design share concepts.

Common Pitfalls

#1 Expecting DEFAULT to apply when inserting NULL explicitly.

Wrong approach:

INSERT INTO users (id, status) VALUES (1, NULL); -- expects 'active' but stores NULL

Correct approach:

INSERT INTO users (id) VALUES (1); -- omitting 'status' uses DEFAULT 'active'

Root cause:

Misunderstanding that DEFAULT only applies when the column is omitted, not when NULL is explicitly given.

#2 Assuming DEFAULT expressions are evaluated once and reused.

Wrong approach:

CREATE TABLE rolls (id SERIAL, roll FLOAT DEFAULT random()*6 + 1);
-- expecting all rows to have same roll value

Correct approach:

Each insert evaluates DEFAULT anew, so each row gets a different random roll.

Root cause:

Not realizing DEFAULT expressions are evaluated at insert time, not at table creation.

#3 Using complex subqueries in DEFAULT expressions causing slow inserts.

Wrong approach:

CREATE TABLE orders (id SERIAL, discount NUMERIC DEFAULT (SELECT max_discount FROM discounts));

Correct approach:

Use triggers or application logic to set discount values instead of subqueries in DEFAULT.

Root cause:

Believing DEFAULT expressions can safely include expensive queries without performance cost.

Key Takeaways

DEFAULT values provide automatic column values when inserts omit those columns, simplifying data entry.

DEFAULT expressions can be constants or functions evaluated at insert time, enabling dynamic defaults like timestamps.

Explicitly inserting NULL stores NULL and does not trigger DEFAULT values.

DEFAULT values are overridden by explicitly provided column values during inserts.

Complex DEFAULT expressions can impact performance and should be used thoughtfully or replaced by triggers.

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