Re: [GENERAL] now i'm really confused. insert/update does autocast, where sometimes.

Am Dienstag, 6. Mai 2008 schrieb Daniel Schuchardt:
> so it depends on ? if i need an explicit cast?

A type cast can be attempted in three different contexts (see also CREATE CAST
reference page):

- implicitly
- storage assignment
- explicitly

The explicit case is if you call CAST() or ::. This is always allowed if an
appropriate cast routine is defined.

The implicit case is if an operator or function requires type A and the
expression is of type B. This cast is only performed if the cast routine is
defined and allowed for the implicit context. The change in 8.3 was
to "downgrade" many casting functions from implicit to assignment or
explicit.

The storage assignment case is if the value has type A and is about to be
stored into a column of type B. This is allowed if the cast routine is
allowed for the assignment context. This is, perhaps surprisingly, a
separate level between implicit and explicit casts. So in reference to your
subject line, yes, INSERT and UPDATE do have different casting behavior than
SELECT. (This is not really accurate, because the WHERE clause of an UPDATE
would follow the same rules as a WHERE clause in SELECT. The assignment
context only applies for values that are really going to be stored.)

This is all in line with the SQL standard.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general