Re: Should we document how column DEFAULT expressions work?

On Tue, Jun 25, 2024 at 4:11 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> James Coleman <jtc331(at)gmail(dot)com> writes:
> > On Tue, Jun 25, 2024 at 4:59 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >> Uh ... what? I recall something about that with respect to certain
> >> features such as nextval(), but you're making it sound like there
> >> is something generic going on with DEFAULT.
>
> > Hmm, I guess I'd never considered anything besides cases like
> > nextval() and now(), but I see now that now() must also be special
> > cased (when quoted) since 'date_trunc(day, now())'::timestamp doesn't
> > work but 'now()'::timestamp does.
>
> Hmm, both of those behaviors are documented, but not in the same place
> and possibly not anywhere near where you looked for info about
> DEFAULT. For instance, the Tip at the bottom of section 9.9.5
>
>
> https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT
>
> explains about how 'now'::timestamp isn't what to use in DEFAULT.
>
>
I'd suggest adding to:

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.

The default expression will be used in any insert operation that does not
specify a value for the column. If there is no default for a column, then
the default is null.

+ Be aware that the [special timestamp values 1] are resolved immediately,
not upon insert. Use the [date/time constructor functions 2] to produce a
time relative to the future insertion.

[1]
https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-DATETIME-SPECIAL-VALUES
[2]
https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT

David J.