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.