Re: Should we document how column DEFAULT expressions work?

On Sun, Jun 30, 2024 at 8:16 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:
>
> On Sun, Jun 30, 2024 at 4:55 PM David Rowley <dgrowleyml@gmail.com> wrote:
>>
>>
>> I'd like to know what led someone down the path of doing something
>> like DEFAULT 'now()'::timestamp in a CREATE TABLE. Could it be a
>> faulty migration tool that created these and people copy them thinking
>> it's a legitimate syntax?
>>
>
> My thought process on this used to be:  Provide a text string of the expression that is then stored within the
catalogand eval'd during runtime.  If the only thing you are providing is a single literal and not some compound
expressionit isn't that obvious that you are supposed to provide an unquoted expression - which feels like it should be
immediatelyevaluated - versus something that is a constant.  Kinda like dynamic SQL. 

I have a similar story to tell: I've honestly never thought about it
deeply until I started this thread, but just through experimentation a
few things were obvious:

- now() as a function call gives you the current timestamp in a query
- now() as a function call in a DDL DEFAULT clause sets that as a
default function call
- Quoting that function call (using the function call syntax is the
natural thing to try, I think, if you've already done the first two)
-- because some examples online show quoting it -- gives you DDL time
evaluation.

So I suspect -- though I've been doing this for so long I couldn't
tell you for certain -- that I largely intuitive the behavior by
observation.

And similarly to David J. I'd then assumed -- but never had a need to
test it -- that this was generalized.

I think DDL is also different conceptually from SQL/DML here in a kind
of insidious way: the "bare" function call in DEFAULT is *not*
executed as part of the query for DDL like it is with other queries.

Hope this helps explain things.

James Coleman