Right, let’s talk about DEFAULT values. This is where you stop treating your database like a passive spreadsheet and start making it do the work for you. The DEFAULT clause is your way of telling the database: “Look, if I don’t bother to specify a value for this column when I insert a row, don’t just yell at me with a NOT NULL error. Use this instead.” It’s the single best way to keep your application code from being cluttered with boilerplate logic for setting trivial, predictable values.

You can provide three types of defaults: a literal value (a static number or string), a function (which runs at insert time), or a sequence (for auto-incrementing IDs). The choice matters, and getting it wrong leads to some truly baffling behavior.

Literal Values: The Simple Stuff

This is the most straightforward use. You just plop a static value in there. It’s perfect for columns where the vast majority of rows will share a common value, but you still need the flexibility to override it occasionally.

CREATE TABLE user_signups (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    -- 99% of our users are mere mortals, not admins
    account_type VARCHAR(10) DEFAULT 'user' NOT NULL,
    -- Let's assume a default, minimal avatar
    avatar_url TEXT DEFAULT '/images/default-avatar.png',
    created_at TIMESTAMPTZ NOT NULL
);

Now, when you insert, you can omit those columns and the database will fill them in sensibly.

INSERT INTO user_signups (username, created_at)
VALUES ('cool_coder', NOW());

-- The result will be: 
-- id: 1, username: 'cool_coder', account_type: 'user', 
-- avatar_url: '/images/default-avatar.png', created_at: <now>

Pitfall Alert: The default is only applied if the column is completely absent from the INSERT statement or if you explicitly DEFAULT keyword. If you explicitly insert NULL and the column allows it, NULL goes in. The default isn’t a “fallback” for nulls; it’s a replacement for omission.

Functions: The Power Move

This is where it gets interesting. You can use any immutable function (and a few volatile ones that make sense) as a default. The king here is NOW() or CURRENT_TIMESTAMP for recording when a row was created. This is so much better than generating the timestamp in your application code because it uses the database’s single source of time truth, avoiding nightmarish clock drift issues across your application servers.

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT,
    is_published BOOLEAN DEFAULT FALSE,
    -- This is the important bit. The function is evaluated
    -- at the moment the row is inserted.
    created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
    updated_at TIMESTAMPTZ
);

Inserting into this table is a breeze:

INSERT INTO posts (title, content) 
VALUES ('My First Post', 'This is pretty cool.');

-- The `id`, `is_published`, and `created_at` are all handled for us.

Massive “Gotcha!” – The CURRENT_TIMESTAMP Family: NOW(), CURRENT_TIMESTAMP, CURRENT_DATE, etc., are all what we call “stable” functions. Their value changes over time, but crucially, within a single transaction, they return the same value. This is brilliant because it means all timestamps for rows inserted in the same transaction are identical, which makes sorting and reasoning about their order perfectly consistent. If you used your app server’s time, you could get two timestamps microseconds apart, implying an order that doesn’t exist in the transaction log.

Sequences: The Auto-Increment Illusion (Made Real)

You’ve probably used SERIAL or BIGSERIAL pseudo-types. This is the magic behind that. Under the hood, SERIAL is just a convenient shorthand for three separate objects: creating a sequence, setting the column’s default to get the next value from that sequence, and setting the sequence as owned by the column.

-- What 'SERIAL' actually does:
CREATE TABLE events (
    id INTEGER NOT NULL, -- No serial here, we'll do it manually
    name TEXT
);

CREATE SEQUENCE events_id_seq;
ALTER TABLE events 
  ALTER COLUMN id 
  SET DEFAULT nextval('events_id_seq');
ALTER SEQUENCE events_id_seq OWNED BY events.id;

You’d never actually write this because SERIAL exists for a reason, but understanding the mechanism is key. The DEFAULT clause is calling nextval() on the sequence each time a row is inserted.

Critical Best Practice: Never, ever use the DEFAULT to manage columns like updated_at. The default is only evaluated on insert. For updated_at, you need a BEFORE UPDATE trigger. A default NOW() on an updated_at column is a lie; it will only ever show the creation time. This mistake is so common it should be considered a rite of passage. Welcome to the club. We’ve all done it. Now go write a trigger.