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