Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sMHVP-001U4Q-Qt for pgsql-hackers@arkaria.postgresql.org; Wed, 26 Jun 2024 01:31:27 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sMHVM-001RVQ-6B for pgsql-hackers@arkaria.postgresql.org; Wed, 26 Jun 2024 01:31:24 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sMHVL-001RVC-Op for pgsql-hackers@lists.postgresql.org; Wed, 26 Jun 2024 01:31:24 +0000 Received: from mail-oo1-xc2b.google.com ([2607:f8b0:4864:20::c2b]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sMHVI-00395j-IB for pgsql-hackers@postgresql.org; Wed, 26 Jun 2024 01:31:22 +0000 Received: by mail-oo1-xc2b.google.com with SMTP id 006d021491bc7-5c21ba58227so432456eaf.0 for ; Tue, 25 Jun 2024 18:31:20 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1719365479; x=1719970279; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=jFYSbHz2+vKQxnoJ74GtPXAM5xiaKNc3qGsxpS/4Zoc=; b=hyV6h786S0IME0icMVsV8bjkjZHbo/dZD9SJu2TNzWalfCQi2nkxryYXONp5n9o+tF IBzoGNTT8gBKmomNTIzXPaxTLUkJkQBvYBM9tpNmhbXMiYIufM0hgh1awKeiCx4KvaMm WhhXl8XVuvgjcQ3xnPOYFwnpmCn4f9x5OShULBR5xWJZKY0uCG42a8ONSnDhUGZn74zY A1j5lT2mSRxNYOtRPvEJRakLY1QtFYcvkBU+MADHE+3fPNxfSfmXwU1gGC5NSKRlU/HB c2/HqKMEh0Ozd9TOaJYHCQ26u4ne7q8Fw+rnPrZegl6dxu1JNJY/8Usy8ZbWqNvNNYMN 6r+A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1719365479; x=1719970279; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=jFYSbHz2+vKQxnoJ74GtPXAM5xiaKNc3qGsxpS/4Zoc=; b=H2SBJTsq0g237MtXuaTrcZw+MlnlT0nfXUGDmEShVYkTY1iDzjp0kY0iYuD1N/WGXZ ELNqT7EDYNAlf10tFVrllWkJ02demYpr4OhNsVxy9Qmw5zFe1LpyUmEkB9rzXxm6phYH S/VbZsSW+ijBNKLv8r1EaFQ3zQ2EavIBc7WmJC3B3qO6n0qb1yJFJzoK1KCboZtODaNR pp6pYNEwXEtjGfombYqzs6zWr4YTGxyXwDU3Y6Fdnj+C9zU/jKJ1M5GiRJHVhzCrQgZX 0iXmRloFxRgUVM/b0wjhesiiD5xQqOl77QYcsnwelfpJlSvmD295dgbwmXj3fEW6QGfO tQnw== X-Forwarded-Encrypted: i=1; AJvYcCVY/RNq5jH4u35+FfcvwBYv03965V9G1OtdNFpacvR75map6nL95bkJ0sLIemqOPhcJGDbjTldV9gl/wqTwvmYGpU+eepal4iA3Hn+m X-Gm-Message-State: AOJu0YymOOe/9IC+2lCbXYa1gm1pVTi1PxWX+01gfsXaOo58EqrZJi6Z G6yJmCOypMPhZVYKxHMQc0OqDQBLKD/RLmkecrCFQ6Tp8+plHENmes9iHC6HdVm1U4YmZcpN1vk eyQVlJw6mYId3dL4WsNDOkl8X15w= X-Google-Smtp-Source: AGHT+IFqLv4BGJ8jzD/GPft0ybcBVM+9iE1ZnXH9tQHbT4Wki9x9FD+q54EATvzOJPqVcbIdoQo15niviyzBU6eEtFM= X-Received: by 2002:a05:6870:9111:b0:254:b74e:d654 with SMTP id 586e51a60fabf-25d06bf2090mr8772056fac.22.1719365479440; Tue, 25 Jun 2024 18:31:19 -0700 (PDT) MIME-Version: 1.0 References: <1362410.1719349169@sss.pgh.pa.us> <1390099.1719357084@sss.pgh.pa.us> In-Reply-To: <1390099.1719357084@sss.pgh.pa.us> From: "David G. Johnston" Date: Tue, 25 Jun 2024 18:30:42 -0700 Message-ID: Subject: Re: Should we document how column DEFAULT expressions work? To: Tom Lane Cc: James Coleman , pgsql-hackers Content-Type: multipart/alternative; boundary="000000000000a4283a061bc0f84e" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000a4283a061bc0f84e Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Tue, Jun 25, 2024 at 4:11=E2=80=AFPM Tom Lane wrote: > James Coleman writes: > > On Tue, Jun 25, 2024 at 4:59=E2=80=AFPM Tom Lane wr= ote: > >> 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-DAT= ETIME-SPECIAL-VALUES [2] https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-D= ATETIME-CURRENT David J. --000000000000a4283a061bc0f84e Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Tue, Jun 25, 2024 at 4:11=E2=80=AFPM Tom Lane <tgl@sss.pgh.pa.us> wrote:
=
James Coleman <jtc331@gmail.com> writes:
> On Tue, Jun 25, 2024 at 4:59=E2=80=AFPM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Uh ... what?=C2=A0 I recall something about that with respect to c= ertain
>> features such as nextval(), but you're making it sound like th= ere
>> 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.=C2=A0 For instance, the Tip at the bottom of section 9.9.5

https://ww= w.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-CU= RRENT

explains about how 'now'::timestamp isn't what to use in DEFAUL= T.


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 v= alue is any variable-free expression (in particular, cross-references to ot= her columns in the current table are not allowed). Subqueries are not allow= ed 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 op= eration that does not specify a value for the column. If there is no defaul= t for a column, then the default is null.

+ Be aw= are that the [special timestamp values 1] are resolved immediately, not upo= n insert.=C2=A0 Use the [date/time constructor functions 2] to=C2=A0produce= a time relative to the future insertion.

[1]=C2=A0https://www.postgresql.org/docs/current/datat= ype-datetime.html#DATATYPE-DATETIME-SPECIAL-VALUES
[2]=C2=A0https://www.postgresql.org/docs/current/functions-= datetime.html#FUNCTIONS-DATETIME-CURRENT

David J= .


--000000000000a4283a061bc0f84e--