On Mon, Jul 1, 2024 at 02:52:42PM +1200, David Rowley wrote: > On Mon, 1 Jul 2024 at 13:41, David G. Johnston > <david.g.johns...@gmail.com> 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 <br...@momjian.us> 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