
A quick question:

in 7.3 the following no longer works:

  template1=> select 0::bool;
  ERROR:  Cannot cast type integer to boolean

The statement must be rewritten as this:

  template1=> select '0'::bool;
   bool 
  ------
   f
  (1 row)

Is there a reason for this?
I ask because the former query works in 7.1.3 and 7.2.1,
but I haven't seen any mention of a change in 7.3 (at
least not in the release notes).

Apologies if this has been discussed to death previously,
but it might be worth mentioning somewhere as a "gotcha".


Ian Barwick
barwick@gmx.net




Wed, 27 Nov 2002 01:19:05 +0100
Ian Barwick <barwick@gmx.net>
Boolean casting in 7.3 -> changed?
Ian Barwick <barwick@gmx.net> writes:
> in 7.3 the following no longer works:
>   template1=> select 0::bool;
>   ERROR:  Cannot cast type integer to boolean

Note that both old and new versions reject
	select 0::int4::bool;

I believe the behavioral change is a consequence of Rod Taylor's
DOMAIN patch: it essentially eliminated the old parser_typecast_constant()
routine in order to ensure that constraints associated with a domain
would get applied in examples like "select 0::domaintypename".

I wasn't totally happy with Rod's patch, for reasons that I couldn't put
my finger on at the time, but perhaps my hindbrain understood that there
would be noticeable behavioral changes.  But be that as it may, the code
is in there now and is unlikely to get reverted.  There isn't any place
in our docs that promises that you can coerce an integer-looking literal
to bool --- and one could argue that allowing such is just opening the
door for typos.

			regards, tom lane

Wed, 27 Nov 2002 00:23:15 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Boolean casting in 7.3 -> changed? 
On Wednesday 27 November 2002 06:23, Tom Lane wrote:
> Ian Barwick <barwick@gmx.net> writes:
> > in 7.3 the following no longer works:
> >   template1=> select 0::bool;
> >   ERROR:  Cannot cast type integer to boolean
>
> Note that both old and new versions reject
> 	select 0::int4::bool;
>
> I believe the behavioral change is a consequence of Rod Taylor's
> DOMAIN patch: it essentially eliminated the old parser_typecast_constant()
> routine in order to ensure that constraints associated with a domain
> would get applied in examples like "select 0::domaintypename".
>
> I wasn't totally happy with Rod's patch, for reasons that I couldn't put
> my finger on at the time, but perhaps my hindbrain understood that there
> would be noticeable behavioral changes.  But be that as it may, the code
> is in there now and is unlikely to get reverted.  There isn't any place
> in our docs that promises that you can coerce an integer-looking literal
> to bool --- and one could argue that allowing such is just opening the
> door for typos.

Thanks for the explanation. I'm not screaming for a reversion ;-), but 
changing behaviour which was implicitly valid in previous
versions is bound to cause a few people a little head scratching
when converting applications to 7.3 (I'm sure I can't be the only one).

How about a line in HISTORY under "Migration to version 7.3" along
the lines of:

"Casting integers to boolean (for example, 0::bool) is no longer allowed,
use '0'::bool instead".


Ian Barwick
barwick@gmx.net


Wed, 27 Nov 2002 08:21:29 +0100
Ian Barwick <barwick@gmx.net>
Re: Boolean casting in 7.3 -> changed?
Ian Barwick <barwick@gmx.net> writes:
> Thanks for the explanation. I'm not screaming for a reversion ;-), but 
> changing behaviour which was implicitly valid in previous
> versions is bound to cause a few people a little head scratching
> when converting applications to 7.3 (I'm sure I can't be the only one).

It's possible we will be able to go back to the old behavior in 7.4;
I haven't reviewed Rod's latest round of DOMAIN patches, but it looked
like it was trying to set up a less ad-hoc way of handling domain
constraints.  But I'm not sure if behavioral flip-flopping is a good
thing.  Might be better to leave it as-is.

			regards, tom lane

Wed, 27 Nov 2002 10:23:39 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Boolean casting in 7.3 -> changed? 
Ian Barwick writes:

> "Casting integers to boolean (for example, 0::bool) is no longer allowed,
> use '0'::bool instead".

This advice would probably only cause more confusion, because we are now
moving into the direction that character strings are no longer acceptable
as numeric data.

Note that

x <> 0

is also a perfectly good way to convert integers to booleans, and a more
portable one at that.

Finally, you can always create your own cast.

-- 
Peter Eisentraut   peter_e@gmx.net


Thu, 28 Nov 2002 00:07:17 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: Boolean casting in 7.3 -> changed?
Peter Eisentraut <peter_e@gmx.net> writes:
> Ian Barwick writes:
>> "Casting integers to boolean (for example, 0::bool) is no longer allowed,
>> use '0'::bool instead".

> This advice would probably only cause more confusion, because we are now
> moving into the direction that character strings are no longer acceptable
> as numeric data.

Yes, phrased that way it's just misleading.  We do not and did not have
a general int-to-bool cast (though it may be reasonable to add one, now
that we could mark it explicit-only).  The case that worked in 7.2 and
before was only for numeric-looking *literals* being cast to bool (or
any other type for that matter) --- parser_typecast_constant would
essentially act as though the literal had quotes around it, whether
it actually did or not.  Thus in the old code, the validity of, say,
	42::bool
would depend on whether bool's input converter would accept the string
'42'.  In the new code, 42 is taken to be an int4 constant and the
validity of the expression depends on whether there is an int4-to-bool
cast.

7.2:

regression=# select 42::bool;
ERROR:  Bad boolean external representation '42'

Current:

regression=# select 42::bool;
ERROR:  Cannot cast type integer to boolean

			regards, tom lane

Wed, 27 Nov 2002 18:18:42 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Boolean casting in 7.3 -> changed? 
Does this mean that in the future '342' may not be valid as an insert into a 
numeric field and that we should be using 342 instead?

On Wednesday 27 November 2002 05:07 pm, (Via wrote:
> Ian Barwick writes:
> > "Casting integers to boolean (for example, 0::bool) is no longer allowed,
> > use '0'::bool instead".
>
> This advice would probably only cause more confusion, because we are now
> moving into the direction that character strings are no longer acceptable
> as numeric data.
>
> Note that
>
> x <> 0
>
> is also a perfectly good way to convert integers to booleans, and a more
> portable one at that.
>
> Finally, you can always create your own cast.


Wed, 27 Nov 2002 18:34:12 -0600
David Walker <pgsql@grax.com>
Re: Boolean casting in 7.3 -> changed?
On Thursday 28 November 2002 00:18, Tom Lane wrote:
> Peter Eisentraut <peter_e@gmx.net> writes:
> > Ian Barwick writes:
> >> "Casting integers to boolean (for example, 0::bool) is no longer
> >> allowed, use '0'::bool instead".
> >
> > This advice would probably only cause more confusion, because we are now
> > moving into the direction that character strings are no longer acceptable
> > as numeric data.
>
> Yes, phrased that way it's just misleading.

OK, what I am trying to say is something like:

"If you are upgrading an application to PostgreSQL 7.3
and are having problems with boolean casts which look like
0::bool or 1::bool, which previously worked without any problem,
(although not explicitly supported) you will need to rewrite them
to use the values listed here:

http://www.postgresql.org/idocs/index.php?datatype-boolean.html .

Doing things like '0'::bool will also work but is not recommended."

because that's a problem I came across but found no mention of,
so I thought I would point it out for the benefit of anyone else
who might encounter it ;-)

For reference, the reason why I was casting integer-like literals
to boolean in the first place is: 
 - Perl application used to run on a combination of MySQL and Oracle;
 - Perl doesn't have a boolean data type, but the values 0 and 1
   in scalar context do the job just as well;
 - MySQL happily accepts literals for boolean column types,
   e.g. INSERT INTO table_with_boolean_column 
                    (boolean_column) 
             VALUES (0)
 - the same statement in PostgreSQL produced
"ERROR:  Attribute 'boolean_column' is of type 'bool' but expression is of type 'int4'
         You will need to rewrite or cast the expression"
 - so I did what it said and wrote 0::bool -  and thought
   no further of it, until now when I began the upgrade.
 - being in a bit of a hurry I put tried '0'::bool and it worked...
 - having rtfm, obviously just '0' and no mucking about with casting
   is better anyway...

Peter Eisentraut <peter_e@gmx.net> wrote:
> Note that
>
> x <> 0
>
> is also a perfectly good way to convert integers to booleans, and a more
> portable one at that.

Ah, that is a useful tip. 

Thanks for the information

Ian Barwick
barwick@gmx.net


Thu, 28 Nov 2002 19:45:21 +0100
Ian Barwick <barwick@gmx.net>
Re: Boolean casting in 7.3 -> changed?
On Wednesday, November 27, 2002, at 04:34  PM, David Walker wrote:

> Does this mean that in the future '342' may not be valid as an insert 
> into a
> numeric field and that we should be using 342 instead?

I didn't see an answer to this question, but I sincerely hope that the 
answer is
"no." Otherwise, dynamic interfaces are going to have a much harder 
time.

Take DBI (and DBD::Pg), for example. Most DBI users don't specify a 
data type when using placeholders. Therefore, DBD::Pg (and other DBDs, 
including DBD::Oracle) assume that the data types are strings. So it's 
not unusual for DBD::Pg to execute a query like this:

   INSERT INTO foo (numtype, varchartype, datetime, inttype)
        VALUES ('23.4', 'string', '2002-11-30 00:00:00', '12');

In order to allow the flexibility to remain, AFAICT PostgreSQL has to 
continue to allow strings to be converted to numbers on the back end.

Regards,

David

-- 
David Wheeler                                     AIM: dwTheory
david@wheeler.net                                 ICQ: 15726394
http://david.wheeler.net/                      Yahoo!: dew7e
                                                Jabber: Theory@jabber.org


Sat, 30 Nov 2002 12:18:23 -0800
David Wheeler <david@wheeler.net>
Re: Boolean casting in 7.3 -> changed?

> I didn't see an answer to this question, but I sincerely hope that the
> answer is
> "no." Otherwise, dynamic interfaces are going to have a much harder
> time.
>
> Take DBI (and DBD::Pg), for example. Most DBI users don't specify a
> data type when using placeholders. Therefore, DBD::Pg (and other DBDs,
> including DBD::Oracle) assume that the data types are strings. So it's
> not unusual for DBD::Pg to execute a query like this:
>
>    INSERT INTO foo (numtype, varchartype, datetime, inttype)
>         VALUES ('23.4', 'string', '2002-11-30 00:00:00', '12');
>
> In order to allow the flexibility to remain, AFAICT PostgreSQL has to
> continue to allow strings to be converted to numbers on the back end.

I have to agree with david on this one.  It's essential that quoted numbers
be allowed into number fields.  I have no problem with putting numbers in
boolean fields though.

Chris


Sat, 30 Nov 2002 13:01:39 -0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: Boolean casting in 7.3 -> changed?