Thread: Re: Cast of numeric()

Re: Cast of numeric()

From
Peter Eisentraut
Date:
Bruce Momjian writes:

> OK, this is making me rethink my suggestion in the book of using type()
> to do typecasts.  Seems I should recommend CAST (val AS type), as wordy
> as it is, or maybe val::type?

Okay, while we're discussing the implicit type conversions, let's also
look at the explicit ones. There are currently four different ways to
make a cast:

1. 'foo'::date
2. CAST('foo' AS DATE)
3. date('foo')
4. DATE 'foo'

It has been observed before that 1. is in conflict with SQL3 so it should
probably be considered obsolescent.

Nr. 2 may be wordy but hey that's SQL. Arguably it's also the clearest.
(Remember that most SQL queries are issued by programs that need to be
maintained, not lazy humans.)

The third is something that only C++ folks could ever have come up with.
:) Seriously, as has been observed, it will not work for types that
require type modifiers, or conversely you cannot provide type modifiers
for types that could use one. Furthermore, it does not take into account
type name aliases (integer vs int4, etc.). Also, couldn't a cast from type
A to B be done via text so that in fact no function B(A) would have to
exist for the cast to work?

The last notation is tricky to merge into the PostgreSQL type system
because in standard SQL this isn't a cast at all. While 2. first makes
'foo' character and then converts it to date, 4. will make it a date right
away. This is perhaps like 10000L vs (long)10000 in C and might actually
make a difference in theory. In any case, that notation does not work in
general anyway (for a start: type modifiers, numbers, expressions in place
of 'foo').

So what I would humbly propose unto you as the recommended syntax in
practice and documentation is this:

* To specify the type of a text-based _literal_ (such as lseg, date, inet)
use
TYPE 'value'

* To evaluate an expression and the convert it to a different data type
use
CAST(expr AS type)

Everything else might only impede the progress to world domination ...


-- 
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden