Re: Should we document how column DEFAULT expressions work? - Mailing list pgsql-hackers
| From | David G. Johnston |
|---|---|
| Subject | Re: Should we document how column DEFAULT expressions work? |
| Date | |
| Msg-id | CAKFQuwbyjn0p5aKz1sKfJHQCTbGy1xhWaNR2xdnXv-X4oPdOGA@mail.gmail.com Whole thread Raw |
| In response to | Re: Should we document how column DEFAULT expressions work? (David Rowley <dgrowleyml@gmail.com>) |
| Responses |
Re: Should we document how column DEFAULT expressions work?
Re: Should we document how column DEFAULT expressions work? |
| List | pgsql-hackers |
On Tue, Jun 25, 2024 at 9:50 PM David Rowley <dgrowleyml@gmail.com> wrote:
On Wed, 26 Jun 2024 at 13:31, David G. Johnston
<david.g.johnston@gmail.com> wrote:
> 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.
Annoyingly even this advice isn't correct:
postgres=# create table tdts2 (ts timestamptz default 'now()');
CREATE TABLE
postgres=# \d tdts2
Table "public.tdts2"
Column | Type | Collation | Nullable | Default
--------+--------------------------+-----------+----------+-------------------------------------------
----------------
ts | timestamp with time zone | | | '2024-06-25 18:05:33.055377-07'::timestamp
with time zone
CREATE TABLE
postgres=# \d tdts2
Table "public.tdts2"
Column | Type | Collation | Nullable | Default
--------+--------------------------+-----------+----------+-------------------------------------------
----------------
ts | timestamp with time zone | | | '2024-06-25 18:05:33.055377-07'::timestamp
with time zone
I expected writing what looked like the function now() to be delayed evaluated but since I put it into quotes, the OPs complaint, it got read as the literal with ignored extra bits.
FWIW, I disagree that we need to write anything about that in this
part of the documentation. I think any argument for doing this could
equally be applied to something like re-iterating what the operator
precedence rules for arithmetic are, and I don't think that should be
mentioned.
I disagree on this equivalence. The time literals are clear deviations from expected behavior. Knowing operator precedence rules, they apply everywhere equally. And we should document the deviations directly where they happen. Even if it's just a short link back to the source that describes the deviation. I'm fine with something less verbose pointing only to the data types page, but not with nothing.
Also, what about all the other places where someone could
use one of the special timestamp input values? Should CREATE VIEW get
a memo too? How about PREPARE?
Yes.
If people don't properly understand these special timestamp input
values, then maybe the documentation in [1] needs to be improved.
Recall, and awareness, is the greater issue, not comprehension. This intends to increase the former. I don't believe the latter is an issue, though I haven't deep dived into it.
And the whole type casting happening right away just seems misleading.
postgres=# create table testboold2 (expr boolean default boolean 'false');
CREATE TABLE
postgres=# \d testboold2
Table "public.testboold2"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
expr | boolean | | | false
CREATE TABLE
postgres=# \d testboold2
Table "public.testboold2"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
expr | boolean | | | false
I would expect 'f' in the default column if the boolean casting of the literal happened sooner. Or I'd expect to see "boolean 'false'" as the default expression if it is captured as-is.
So yes, saving an expression into the default column has nuances that should be documented where default is defined.
Maybe the wording needs to be:
"If the default expression contains any constants [1] they are converted into their typed value during create table execution. Thus time constants [1] save into the default expression the time the command was executed."
I'd be happy to be pointed to other constants that resolve to an execution-time specific environment in a similar manner. If there is another one I'll rethink the wisdom of trying to document all of them in each place. But reminding people that time is special and we have these special values seems to provide meaningful reader benefit for the cost of a couple of sentences repeated in a few places. That were valid a decade ago no more or less than they are valid now.
David J.
pgsql-hackers by date: