Re: BUG #5225: create table: cast necessary for constant??

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Sorry about that --- I had confused this case with that of a bare
> NULL literal, which Postgres treats the same as an unadorned
> string literal for type determination purposes. You're right that
> the spec treats them differently. This is feasible for the spec's
> purposes because it has such a paucity of data types.

Yeah, the arguments about how the PostgreSQL behavior makes it
easier to work with user defined types are compelling. The
differences in behavior don't show up often -- I suspect that they
will typically be encountered by those converting from other
products to PostgreSQL. I'm just suggesting that someone put
together a page in the wiki or documentation to describe the
differences in behavior and the normal workarounds. That might
preempt some of the problems, and would be a quick way to help
someone who runs into the issue for the first time.

> Also, I believe that the spec expects you to explicitly mark
> literals that aren't to be treated as plain strings, ie, in
> something like
> TIMESTAMP '2009-12-02 18:28:58'
> you're not really supposed to omit the word TIMESTAMP.

Absolutely true. Although many products will tolerate omission for
date/time literals, that's non-standard behavior. The reason they
do that is pretty much the same reason that PostgreSQL does, but
PostgreSQL takes it farther.

> Postgres has a whole lot of datatypes, including user-added ones,
> and most of them share the unadorned string literal as the base
> case for constants. Giving preference to CHARACTER would make
> that machinery a lot less pleasant to use.

Well put.

-Kevin