What does DEFAULT CURRENT_TIMESTAMP in a CREATE TABLE column definition mean, and when is it evaluated?

## What DEFAULT declares A `CREATE TABLE` statement is a list of column definitions, each of which is a column name, a data type, and an optional set of column options and constraints. `DEFAULT <value expression>` is one of those options. It records in the catalog an expression the engine will use to produce a value for that column whenever an `INSERT` statement does not provide one. Declaring a default changes nothing else about the column: it does not make the column required, it does not restrict what may later be stored there, and it does not touch rows that already exist. ```sql CREATE TABLE orders ( order_id BIGINT NOT NULL, status VARCHAR(20) DEFAULT 'NEW', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, discount DECIMAL(5,2) DEFAULT 0 ); ``` ## The expression is evaluated at insert time The single most common misunderstanding is that `DEFAULT CURRENT_TIMESTAMP` freezes the moment the table was created. It does not. What the catalog holds is the *expression* `CURRENT_TIMESTAMP`; the engine evaluates it while executing the `INSERT`. A row inserted this morning and a row inserted a year later therefore carry different `created_at` values, which is exactly what makes this a working audit column with no application code involved. The same is true of literal defaults — `DEFAULT 'NEW'` is supplied afresh for each row that omits `status`. One nuance is worth knowing: the datetime value functions are not re-evaluated at every microsecond within a statement. Standard SQL requires that all such functions in a single statement return the same value, and PostgreSQL holds `CURRENT_TIMESTAMP` fixed for the whole transaction. So a 100-row `INSERT` will normally stamp all 100 rows with an identical timestamp. That is per-statement stability, not per-table stability. ## What may legally appear in a DEFAULT Standard SQL keeps the set of allowed defaults small: a literal of a compatible type, the keyword `NULL`, or a niladic (argument-free) built-in such as `CURRENT_DATE`, `CURRENT_TIME`, `CURRENT_TIMESTAMP`, `LOCALTIMESTAMP`, `CURRENT_USER` or `SESSION_USER`. Two things are firmly outside the clause: - **Another column of the same row.** `total DECIMAL(10,2) DEFAULT price * quantity` is not a default; a default must be computable without looking at the rest of the row. A column derived from siblings is a generated (computed) column, a different feature with different syntax. - **Anything requiring the row to already exist.** The default is produced as part of building the row, so it cannot depend on the row's key, its position, or other rows. Engines extend this in different directions. PostgreSQL accepts any expression that does not reference other columns of the table, including function calls. MySQL requires an expression default to be wrapped in parentheses — `DEFAULT (UUID())` — while `DEFAULT CURRENT_TIMESTAMP` is accepted bare. Because the permitted set genuinely differs, treat anything beyond a literal or a datetime function as engine-specific and verify it. ## DEFAULT NULL and no DEFAULT at all For a nullable column the two are equivalent: a column with no `DEFAULT` clause defaults to `NULL`, and writing `DEFAULT NULL` merely documents that. The declaration is worth writing when you want a reader to see that the absence of a default was a decision rather than an oversight. ## What a DEFAULT does not do A default is a convenience for writers, not a guarantee for readers. It says what happens when a value is *missing from the statement*; it does not police what values may be stored, does not make a column mandatory, and does not repair existing rows. If a column must always hold a value, that is `NOT NULL`; if a value must satisfy a rule, that is a `CHECK` constraint. `DEFAULT` and `NOT NULL` are frequently declared together — `created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP` is a standard pairing — precisely because they answer two different questions: what goes in when nothing is offered, and whether nothing is allowed at all. ## Practical guidance Use defaults for the values that are genuinely mechanical: creation timestamps, status columns whose initial state is fixed, counters starting at zero, boolean flags. Keep them simple enough that a reader of the `CREATE TABLE` statement can predict what a row will contain. And remember that the default lives in the schema, so changing it later is a schema change with its own migration, not a code deploy.