What is the difference between GENERATED ALWAYS AS IDENTITY and GENERATED BY DEFAULT AS IDENTITY?
answer
- Both generate; they differ on who may override
- One of them makes an explicit id an error
- There is a per-statement escape clause
- OVERRIDING SYSTEM VALUE versus OVERRIDING USER VALUE
basics
~20 sGENERATED ALWAYS rejects an INSERT that supplies its own value for the column unless the statement says OVERRIDING SYSTEM VALUE. GENERATED BY DEFAULT accepts a supplied value and only generates one when the column is omitted or given DEFAULT.
solid answer
~50 sBoth attach a sequence generator to the column; they differ in who wins when an INSERT names the column. With `GENERATED ALWAYS AS IDENTITY`, supplying a value is an **error** — the engine insists on generating it. You can override that deliberately per statement with `INSERT INTO t (id, name) OVERRIDING SYSTEM VALUE VALUES (7, 'a')`. With `GENERATED BY DEFAULT AS IDENTITY`, a supplied value is simply used, and the generator only runs when the column is omitted or written as `DEFAULT`. The mirror-image clause `OVERRIDING USER VALUE` tells the engine to discard the value the statement supplied and generate one anyway — useful for a bulk INSERT ... SELECT that carries an old id column you want ignored. Choose `ALWAYS` for surrogate keys nothing outside the database should choose; it turns an accidental hard-coded id into a loud error rather than a silent collision later. Choose `BY DEFAULT` when legitimate loads carry their own keys.
go deeper
Remember the headline: ALWAYS refuses an id you supply, BY DEFAULT accepts it. Know that writing DEFAULT for the column always means 'let the database generate it'.
Name both OVERRIDING clauses and say which declaration each belongs to, and mention that the rule covers UPDATE too, not only INSERT.
Argue the default for a schema: ALWAYS for surrogate keys so a hard-coded id fails loudly, plus the operational note that explicit values do not advance the generator and the import must reseed it.
Own it as policy — which tables may carry externally-chosen keys at all, how imports and environment copies are sanctioned, and what migrating legacy serial-style columns to ALWAYS costs across services.
## The two declarations ```sql CREATE TABLE a (id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name TEXT); CREATE TABLE b (id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, name TEXT); ``` Both columns are backed by a sequence generator and both are implicitly `NOT NULL`. The difference is purely about **write authority**: who is allowed to decide the value on an INSERT or UPDATE. ## GENERATED ALWAYS — the database owns the value ```sql INSERT INTO a (name) VALUES ('ok'); -- generated INSERT INTO a (id, name) VALUES (7, 'boom'); -- error ``` The second statement fails. The standard's escape hatch is a clause on the INSERT itself: ```sql INSERT INTO a (id, name) OVERRIDING SYSTEM VALUE VALUES (7, 'ok'); ``` The clause sits between the column list and `VALUES`, and it applies to that one statement. The design intent is that overriding a system-generated value is a conscious, auditable act — a data migration, a restore, a test fixture — not something that can happen because a developer copied a literal into an INSERT. Writing `DEFAULT` for the column is always allowed and always means "generate one": ```sql INSERT INTO a (id, name) VALUES (DEFAULT, 'ok'); ``` ## GENERATED BY DEFAULT — the statement wins if it speaks up ```sql INSERT INTO b (name) VALUES ('generated'); -- generator runs INSERT INTO b (id, name) VALUES (7, 'explicit'); -- 7 is stored, no error ``` Here the generator is a fallback. Anything the statement supplies is taken at face value, subject only to the ordinary constraints on the column — which is exactly why the `PRIMARY KEY` matters: it is the constraint, not the identity declaration, that stops two rows ending up with id 7. The mirror clause is `OVERRIDING USER VALUE`: ```sql INSERT INTO b (id, name) OVERRIDING USER VALUE SELECT legacy_id, legacy_name FROM staging; ``` The supplied `legacy_id` values are discarded and the generator supplies fresh ones. This is genuinely useful: it lets you feed a wide `SELECT` straight into the table without hand-editing the column list to drop the old key. ## Which clause is legal where The two clauses are not interchangeable and their names describe what is being overridden, not who is doing it: - `OVERRIDING SYSTEM VALUE` — "ignore the system's value, take mine". Required for an `ALWAYS` column when you supply a value. - `OVERRIDING USER VALUE` — "ignore the user's value, take the system's". Meaningful on a `BY DEFAULT` column. Engines differ in how strictly they police using the wrong one, so treat each as belonging to its own declaration. ## UPDATE, not just INSERT The same authority rule applies to UPDATE. `UPDATE a SET id = 9 WHERE …` is rejected for an `ALWAYS` identity column, while the same statement against a `BY DEFAULT` column succeeds. That is often the more valuable half of the protection: renumbering a key by accident is far worse than inserting one, because foreign keys elsewhere already point at the old value. ## How to choose Default to `ALWAYS` for surrogate keys. The whole point of a surrogate is that it has no meaning outside the database, so no client has any business choosing one; making that an error catches the mistake at the statement rather than months later as a duplicate-key incident or a mysterious renumbering. Reach for `BY DEFAULT` when explicit keys are part of normal operation: importing an existing dataset that must keep its identifiers, replicating rows between environments, or seeding fixtures with stable ids that tests assert on. Note that PostgreSQL's older `serial` shorthand behaves like `BY DEFAULT` — it is only a column `DEFAULT` drawing from a sequence, and any statement can override it — so migrating `serial` to `GENERATED ALWAYS AS IDENTITY` is a real behavioural tightening, not a cosmetic rewrite. Whichever you choose, remember what neither declaration does: supplying explicit values does not advance the generator. Load rows with explicit ids and the generator still sits where it was, so a later generated value can collide with an imported one and be rejected by the primary key. That is a separate step to handle in the same maintenance window.
- Does the rule apply to UPDATE as well as INSERT?Yes. `UPDATE … SET id = 9` is rejected on a `GENERATED ALWAYS` identity column and accepted on a `GENERATED BY DEFAULT` one. That is often the more valuable protection, since silently renumbering a key breaks every foreign key already pointing at the old value.
- Why would you ever use OVERRIDING USER VALUE?To feed a wide `INSERT ... SELECT` from a staging table straight into the target without stripping the old key column: the supplied values are discarded and the generator produces fresh ones. It keeps the column lists aligned instead of forcing you to hand-edit them.
- How does PostgreSQL's serial shorthand compare with these two forms?`serial` is only a column `DEFAULT` drawing from a sequence, so it behaves like `GENERATED BY DEFAULT` — any statement can supply its own value and win. Converting a `serial` column to `GENERATED ALWAYS AS IDENTITY` is a genuine behavioural change, not a cosmetic one.
ALWAYS is a numbered ticket machine at the door: you take what it prints, and slipping in your own ticket needs a supervisor's override. BY DEFAULT is a sign-in sheet: write your own number if you have one, otherwise the machine gives you the next.
saying these in an interview costs you the question
- Thinks GENERATED ALWAYS silently ignores a supplied value
- Believes an explicit id can never be inserted into an ALWAYS column
- Says the choice only affects INSERT, not UPDATE
- Assumes supplying explicit ids advances the generator
- Treats PostgreSQL serial as equivalent to GENERATED ALWAYS