Subject: Should we document how column DEFAULT expressions work?

From: James Coleman <[email-masked]>
Date: Tue, 25 Jun 2024 16:51:05 -0400

Hello,

It's possible I'm the only one who's been in this situation, but I've
multiple times found myself explaining to a user how column DEFAULT
expressions work: namely how the quoting on an expression following
the keyword DEFAULT controls whether or not the expression is
evaluated at the time of the DDL statement or at the time of an
insertion.

In my experience this is non-obvious to users, and the quoting makes a
big difference.

Is this something that we should document explicitly? I don't see it
called out in the CREATE TABLE reference page, but it's possible I'm
missing something.

Thanks,
James Coleman

---

From: Tom Lane <[email-masked]>
Date: Tue, 25 Jun 2024 16:59:29 -0400

> evaluated at the time of the DDL statement or at the time of an

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.

			regards, tom lane

---

From: James Coleman <[email-masked]>
Date: Tue, 25 Jun 2024 19:05:04 -0400

> 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.

So I guess what I'm asking about would be limited to those cases (I
assume there are a few others...but I haven't gone digging through the
source yet).

Regards,
James Coleman

---

From: Tom Lane <[email-masked]>
Date: Tue, 25 Jun 2024 19:11:24 -0400

> 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.

			regards, tom lane