Thread: Default conversion of type numeric to text in 7.1.3

Hi,

    The excellently detailed Postgres manual, seems to indicate that
the concatenation operator "||"  converts the right and left operands of
all base types to text with the exception of boolean which cannot be cast
as any other type. However I find that the conversion of numeric to text
fails with an error mesage indicating that || cannot concatenate text with
numeric.  To make the concatenation work, I am forced to explicity cast
numeric with a text(numeric).

    Is this correct behaviour consistent with the manual? Also, what
special problems prevent the conversion of boolean to text? We could fix
things so that text(boolean)  return the strings TRUE or FALSE depending
on the argument.

Many thanks in advance

Regards,
Gurunandan

"Gurunandan R. Bhat" <grbhat@exocore.com> writes:
>     The excellently detailed Postgres manual, seems to indicate that
> the concatenation operator "||"  converts the right and left operands of
> all base types to text with the exception of boolean which cannot be cast
> as any other type.

Uh, where did you get that impression?  || is a string operator only
(and maybe a bit string and bytea operator, depending on which release
you are using).

There are implicit coercions to text for some datatypes.  It's my
feeling that such implicit coercions are dangerous, and that we ought
to avoid them rather than add more.  Too many implicit coercion paths,
and you'll never be sure what a multi-datatype expression is going to
be interpreted to do.

> Also, what
> special problems prevent the conversion of boolean to text?

None other than no one having got round to it.  Again, though, I'd not
like to see such a conversion occur implicitly.  Example:

regression=# select true || false;
ERROR:  Unable to identify an operator '||' for types 'boolean' and 'boolean'
        You will have to retype this query using an explicit cast

regression=# create function text(boolean) returns text as '
regression'# select case when $1 then ''true'' else ''false'' end
regression'# ' language 'sql';
CREATE

regression=# select true || false;
 ?column?
-----------
 truefalse
(1 row)

IMHO this is not good; I should have needed to write

regression=# select text(true) || text(false);
 ?column?
-----------
 truefalse
(1 row)

to make it clear what I wanted.

            regards, tom lane

On Wed, 21 Nov 2001, Tom Lane wrote:

> "Gurunandan R. Bhat" <grbhat@exocore.com> writes:

> >     The excellently detailed Postgres manual, seems to indicate that
> > the concatenation operator "||"  converts the right and left operands of
> > all base types to text with the exception of boolean which cannot be cast
> > as any other type.
>
> Uh, where did you get that impression?  || is a string operator only
> (and maybe a bit string and bytea operator, depending on which release
> you are using).

True. I wasnt referring to the operator per se, but rather to the ability
of the parser to "promote" a data type to text merely by virtue of the
fact that a function declared as text(boolean) is available and that an
operator "expects" certain data types as operands.  I probably misread the
following extract from the manual:

Extract from Section 5.1

"...The Postgres parser uses the convention that all type conversion
    functions take a single argument of the source type and are named with
    the same name as the target type. Any function meeting these criteria
    is considered to be a valid conversion function, and may be used by
    the parser as such.  This simple assumption gives the parser the power
    to explore type conversion possibilities without hardcoding, allowing
    extended user-defined types to use these same features transparently.

    An additional heuristic is provided in the parser to allow better
    guesses at proper behavior for SQL standard types. There are several
    basic type categories defined: boolean, numeric, string, bitstring,
    datetime, timespan, geometric, network, and user-defined. Each
    category, with the exception of user-defined, has a preferred type
    which is preferentially selected when there is ambiguity. In the
    user-defined category, each type is its own preferred type."

> There are implicit coercions to text for some datatypes.

It appears that implicit coercions can be made to happen for all data
types except numeric and boolean. And my question was : why exclude these
two? Any special reason?

> It's my feeling that such implicit coercions are dangerous, and that
> we ought to avoid them rather than add more.  Too many implicit
> coercion paths, and you'll never be sure what a multi-datatype
> expression is going to be interpreted to do.

Point. And thanks for the very clear example of the possible dangers. I
have made the appropriate changes to my code.

Thanks and regards,
Gurunandan