public inbox for [email protected]
help / color / mirror / Atom feedFrom: Bruce Momjian <[email protected]>
To: David Rowley <[email protected]>
Cc: David G. Johnston <[email protected]>
Cc: Peter Eisentraut <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: James Coleman <[email protected]>
Cc: pgsql-hackers <[email protected]>
Subject: Re: Should we document how column DEFAULT expressions work?
Date: Fri, 5 Jul 2024 16:31:13 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAApHDvrQ--Mzp00Yn=3SVB=YS_ZB15aeLywaK0n05-adk8WWmw@mail.gmail.com>
References: <CAKFQuwbyjn0p5aKz1sKfJHQCTbGy1xhWaNR2xdnXv-X4oPdOGA@mail.gmail.com>
<CAApHDvqShXhOat03iEV1VqXM68DBJ+zM95Ghbu0rcqs8KnhQQQ@mail.gmail.com>
<[email protected]>
<CAApHDvouPRyga3-5ZYLt_RUhU942bk0WPVgro+U=R3cvBRXbeQ@mail.gmail.com>
<[email protected]>
<CAApHDvrURTcDn3GTsmXzm9VqT3acLh=T=aUYMuKrtozw0nq8Tg@mail.gmail.com>
<CAKFQuwbOOQ20=Pb5j-k+Sws22H5W9tnj-vve4MC-GQ9RmmtTxA@mail.gmail.com>
<CAApHDvogKE8RrHx=qmYfvpD_AiVLX14nLvvSrPbFCXWgiYrKxg@mail.gmail.com>
<CAKFQuwZjuQX4H48MbaHZAX3s__LoHgkXsf2Eg--E=qtWFss0bg@mail.gmail.com>
<CAApHDvrQ--Mzp00Yn=3SVB=YS_ZB15aeLywaK0n05-adk8WWmw@mail.gmail.com>
On Mon, Jul 1, 2024 at 02:52:42PM +1200, David Rowley wrote:
> On Mon, 1 Jul 2024 at 13:41, David G. Johnston
> <[email protected]> wrote:
> > I presume the relatively new atomic SQL functions pose a similar hazard.
>
> Do you have an example of this?
>
> > The fact that 'now()'::timestamp fails to fail doesn't help...
>
> If that's the case, maybe a tiny step towards what Peter proposed is
> just to make trailing punctuation fail for timestamp special values in
> v18.
I dug into this and I have a suggestion at the end. First, the special
values like 'now' are the only values that can be optionally quoted:
SELECT current_timestamp::timestamptz;
current_timestamp
-------------------------------
2024-07-05 15:15:22.692072-04
SELECT 'current_timestamp'::timestamptz;
ERROR: invalid input syntax for type timestamp with time zone: "current_timestamp"
Also interestingly, "now" without quotes requires parentheses to make it
a function call:
SELECT 'now'::timestamptz;
timestamptz
-------------------------------
2024-07-05 15:17:11.394182-04
SELECT 'now()'::timestamptz;
timestamptz
-------------------------------
2024-07-05 15:17:15.201621-04
SELECT now()::timestamptz;
now
-------------------------------
2024-07-05 15:17:21.925611-04
SELECT now::timestamptz;
ERROR: column "now" does not exist
LINE 1: SELECT now::timestamptz;
^
And the quoting shows "now" evaluation at function creation time:
CREATE OR REPLACE FUNCTION testnow() RETURNS timestamptz LANGUAGE SQL
RETURN 'now'::timestamptz;
SELECT testnow();
SELECT pg_sleep(5);
SELECT testnow();
testnow
-------------------------------
2024-07-05 15:19:38.915255-04
testnow
-------------------------------
2024-07-05 15:19:38.915255-04 -- same
---------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION testnow() RETURNS timestamptz LANGUAGE SQL
RETURN 'now()'::timestamptz;
SELECT testnow();
SELECT pg_sleep(5);
SELECT testnow();
testnow
-------------------------------
2024-07-05 15:20:41.475997-04
testnow
-------------------------------
2024-07-05 15:20:41.475997-04 -- same
---------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION testnow() RETURNS timestamptz LANGUAGE SQL
RETURN now()::timestamptz;
SELECT testnow();
SELECT pg_sleep(5);
SELECT testnow();
testnow
-------------------------------
2024-07-05 15:21:18.204574-04
testnow
-------------------------------
2024-07-05 15:21:23.210442-04 -- different
I don't think we can bounce people around to different sections to
explain this --- I think we need text in the CREATE TABLE ... DEFAULT
section. I think the now() case is unusual since there are few cases
where function calls can be put inside of single quotes.
I have written the attached patch to clarify the behavior.
--
Bruce Momjian <[email protected]> https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
Attachments:
[text/x-diff] create_default.diff (881B, ../[email protected]/2-create_default.diff)
download | inline diff:
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index f19306e7760..9bab4ec141e 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -888,6 +888,14 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
match the data type of the column.
</para>
+ <para>
+ Note, a string that returns a volatile result once cast to
+ a data type, like <literal>'now'::timestamptz</literal> and
+ <literal>'now()'::timestamptz</literal>, is evaluated at table
+ creation time, while <literal>now()::timestamptz</literal> (without
+ quotes) is evaluated at data insertion time.
+ </para>
+
<para>
The default expression will be used in any insert operation that
does not specify a value for the column. If there is no default
view thread (17+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Should we document how column DEFAULT expressions work?
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox