On Sun, Jun 30, 2024 at 8:16 PM David G. Johnston <david.g.johns...@gmail.com> wrote: > > On Sun, Jun 30, 2024 at 4:55 PM David Rowley <dgrowle...@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 catalog and eval'd during runtime. > If the only thing you are providing is a single literal and not some compound > expression it isn't that obvious that you are supposed to provide an unquoted > expression - which feels like it should be immediately evaluated - 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
