"Wagner, Kurt" <kurt(dot)wagnerextern(at)leoni(dot)com> wrote:
> when writing a character constant elsewhere
> then at first it is interpreted as character constant - right?
> then it is casted to the desired type
No. It was confusing for me, too; but the PostgreSQL behavior is to
treat what the standard calls a <character string literal> as being
of type UNKNOWN -- just like the behavior described in the spec for
NULL. When it is used in some context where the type must be
resolved, it then tries to pick an appropriate type. (I believe
there is some slight preference for type TEXT when there are
multiple possibilities.) This is helpful for those wanting to use
literals of non-standard types. (Many people use PostgreSQL
specifically because of the ability to define custom types and
operators.)
There is an understandable tendency of those who work deep in the
guts of the PostgreSQL software, making all this custom type code
work, that those coming into PostgreSQL from other environments come
with the assumption that these literals will be treated as character
strings. From their perspective there is nothing more natural than
to view such a literal as lacking any type information, with the
obvious implication that you should explicitly give it a type.
Once you shake out any problems from code you are migrating, you'll
find it's not hard to write new code in a way which will work in
either environment.
-Kevin