MIME-Version: 1.0
References: <CAAaqYe-wQPbchCUE7pocPNEQ3PMY8a98Txmxc7q0+FvXtk1vvg@mail.gmail.com>
 <1362410.1719349169@sss.pgh.pa.us> <CAAaqYe9_RE2WximHuYRpyfU-H2G+W--49e_8GDqS-vHLOzPvmg@mail.gmail.com>
 <1390099.1719357084@sss.pgh.pa.us> <CAKFQuwZkgm0yqHhf05thqu1gzUK__gXBL5tyBuFvKoOe7Dd6=A@mail.gmail.com>
 <CAApHDvoiHYxM2VQYZeiF7JAdP7Y0JxEr86NZpUjoZ9AA4cpzYw@mail.gmail.com>
 <CAKFQuwbyjn0p5aKz1sKfJHQCTbGy1xhWaNR2xdnXv-X4oPdOGA@mail.gmail.com>
 <CAApHDvqShXhOat03iEV1VqXM68DBJ+zM95Ghbu0rcqs8KnhQQQ@mail.gmail.com>
 <1642803.1719447063@sss.pgh.pa.us> <CAApHDvouPRyga3-5ZYLt_RUhU942bk0WPVgro+U=R3cvBRXbeQ@mail.gmail.com>
 <ef4d9b38-b7a5-4c85-ba12-062929c2d136@eisentraut.org>
In-Reply-To: <ef4d9b38-b7a5-4c85-ba12-062929c2d136@eisentraut.org>
From: David Rowley <dgrowleyml@gmail.com>
Date: Mon, 1 Jul 2024 11:54:50 +1200
Message-ID: <CAApHDvrURTcDn3GTsmXzm9VqT3acLh=T=aUYMuKrtozw0nq8Tg@mail.gmail.com>
Subject: Re: Should we document how column DEFAULT expressions work?
To: Peter Eisentraut <peter@eisentraut.org>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, "David G. Johnston" <david.g.johnston@gmail.com>, 
	James Coleman <jtc331@gmail.com>, pgsql-hackers <pgsql-hackers@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Archived-At: <https://www.postgresql.org/message-id/CAApHDvrURTcDn3GTsmXzm9VqT3acLh%3DT%3DaUYMuKrtozw0nq8Tg%40mail.gmail.com>
Precedence: bulk

On Thu, 27 Jun 2024 at 23:57, Peter Eisentraut <peter@eisentraut.org> wrote:
> Maybe we should really be thinking about deprecating these special
> values and steering users more urgently toward more robust alternatives.
>
> Imagine if 'random' were a valid input value for numeric types.

I think there are valid reasons to use the special timestamp input
values.  One that I can think of is for use with partition pruning. If
you have a time-range partitioned table and want the planner to prune
the partitions rather than the executor, you could use
'now'::timestamp in your queries to allow the planner to prune. That
works providing that you never use that in combination with PREPARE
and never put the query with the WHERE clause inside a VIEW. I don't
have any other good examples, but I suppose that if someone needed to
capture the time some statement was executed and record that
somewhere, sort of like the __DATE__ and __TIME__ macros in C. Perhaps
that's useful to record the last time some DDL script was executed.

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?

David