Right, let’s talk about IDENTITY columns. You’ve probably used SERIAL before—PostgreSQL’s old-school, convenience-wrapper way to make an auto-incrementing column. It’s fine. It works. But under the hood, it’s just a sequence plopped onto a column with a default value. The SQL standard has a more explicit, more powerful, and frankly, less janky way to do this: the IDENTITY column. It’s the grown-up version, and it’s what you should be using for new tables unless you have a very specific reason not to.

The core concept is simple: you tell PostgreSQL that a column’s values should be automatically generated. The big question is how you want to interact with this automation. Do you want to be the boss, or are you happy to let the database drive? This is the difference between ALWAYS and BY DEFAULT.

The Gist: ALWAYS vs. BY DEFAULT

The choice boils down to one of control and intent.

  • GENERATED ALWAYS AS IDENTITY means the column’s value is always, exclusively generated by the sequence. If you try to INSERT a value into this column, the database will throw a big, fat error in your face. It’s the database’s way of saying, “I got this, buddy. Back off.”
  • GENERATED BY DEFAULT AS IDENTITY is more of a suggestion. It says, “Hey, if you don’t provide a value, I’ll happily generate one for you. But if you do provide a value, I’ll just use that and won’t complain.” This is the flexible, sometimes-too-trusting friend.

Here’s how you declare them. Notice we’re being explicit and not just relying on a pseudo-type like SERIAL.

CREATE TABLE team_members (
    -- The strict, no-backtalk version
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE backup_entries (
    -- The chill, go-with-the-flow version
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    data TEXT NOT NULL
);

Why You’d Use ALWAYS (The “Good Default”)

Use ALWAYS. Seriously, just use it. 99% of the time, this is what you want. The whole point of a surrogate primary key is that it’s an arbitrary, unique identifier whose value you should not care about. You should never be manually inserting a value for an id column; that’s the database’s job. ALWAYS enforces this contract.

Imagine you’re restoring data from a backup. You INSERT a row and, out of habit, specify the id. With BY DEFAULT, the database shrugs and uses your value, potentially causing a future conflict when the sequence eventually generates that same number. With ALWAYS, it stops you immediately. It’s a guardrail against shooting yourself in the foot.

-- This will fail beautifully with ALWAYS. This is a GOOD thing.
INSERT INTO team_members (id, name) VALUES (100, 'Dave');

-- ERROR: cannot insert into column "id"
-- DETAIL: Column "id" is an identity column defined as GENERATED ALWAYS.

The (Rare) Case for BY DEFAULT

So when would you ever use BY DEFAULT? The use case is narrow: when you need to manually specify a value for a one-off operation, like during a data migration from an old system where you need to preserve legacy IDs.

But even then, you have to be incredibly careful. You must manually advance the sequence afterward to avoid future primary key violations. It’s a manual, error-prone process.

-- This works with BY DEFAULT, but it's a trap waiting to spring.
INSERT INTO backup_entries (id, data) VALUES (500, 'Legacy data');

-- You MUST do this to avoid a future duplicate key error.
-- This is the kind of housekeeping SERIAL never made you do.
SELECT setval(pg_get_serial_sequence('backup_entries', 'id'), 500);

See what I mean? Janky. You’re now responsible for the bookkeeping the IDENTITY feature is supposed to handle. This is why ALWAYS should be your default choice.

Overriding ALWAYS (Because Sometimes Rules Are Meant to Be Broken)

Okay, so you used ALWAYS like a responsible developer, but now you have a legitimate, one-in-a-million scenario where you absolutely must insert a specific value. PostgreSQL has an escape hatch for this, but you have to use it explicitly to prove you know what you’re doing: the OVERRIDING SYSTEM VALUE clause.

-- This is how you tell the database, "I know what I'm doing, I promise."
INSERT INTO team_members (id, name)
OVERRIDING SYSTEM VALUE
VALUES (100, 'Dave');

This command is your signed permission slip. It tells the strict ALWAYS guard, “It’s cool, they’re with me.” Use it sparingly, and always remember to manually setval() afterward, just like with BY DEFAULT. The fact that you have to be this explicit is a feature, not a bug. It forces you to acknowledge the potential danger of what you’re doing.