Re: Cast of numeric()

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?

CAST(val AS type) is defined in SQL92. istm that the others are
available at the whim of our current implementation, since when push
comes to shove we might have to choose between having one of our
non-standard mechanisms or having some other new features.

An example is SQL3 enumerated types, which use the double-colon
notation, but with value and type reversed from our syntax :(

- Thomas

--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California

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