Thread: Boolean casting in 7.3 -> changed?
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
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 rejectselect 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
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
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
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
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
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.
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 havea boolean data type, but the values 0 and 1 in scalar context do the job just as well;- MySQL happily accepts literalsfor 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 orcast the expression"- so I did what it said and wrote 0::bool - and thought no further of it, until now when I beganthe upgrade.- being in a bit of a hurry I put tried '0'::bool and it worked...- having rtfm, obviously just '0' andno 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
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
> -----Original Message----- > From: David Wheeler [mailto:david@wheeler.net] > Sent: 30 November 2002 20:18 > To: David Walker > Cc: PostgreSQL Development > Subject: Re: [HACKERS] 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. pgAdmin will have similar problems. I can work round it for standard types, but how will I tell whether a custom type will reject quoted values? Regards, Dave.
> 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