CREATE temp TABLE demo1(
    col_a int,
    col_b int DEFAULT (col_a)
);

PostgreSQL have generated columns, triggers. But why cannot use column reference in DEFAULT expression.

since the manual ddl-default page mention:

The default value can be an expression, which will be evaluated whenever the default value is inserted (not when the table is created). A common example is for a timestamp column to have a default of CURRENT_TIMESTAMP, so that it gets set to the time of row insertion.

Obviously, column references is value expression. https://www.postgresql.org/docs/current/sql-expressions.html#SQL-EXPRESSIONS-COLUMN-REFS

A value expression is one of the following:

* A constant or literal value

CodePudding user response：

The documentation states:

DEFAULT default_expr

The DEFAULT clause assigns a default data value for the column whose column definition it appears within. The value is any variable-free expression (in particular, cross-references to other columns in the current table are not allowed). Subqueries are not allowed either. The data type of the default expression must match the data type of the column.

So the restriction is clearly documented.

But it is fairly easy to see that it would pose problems to allow a column reference in a DEFAULT expression: For one, the value specified in the INSERT statement could be overridden by BEFORE INSERT triggers. In that case, should the value before or after the trigger execution be used? Also, what if the DEFAULT for column a references column b and vice versa? There may be other difficulties that I didn't think of.