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.
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
[EMAIL PROTECTED] ICQ: 15726394
http://david.wheeler.net/ Yahoo!: dew7e
Jabber: [EMAIL PROTECTED]
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Boolean casting in 7.3 - changed?
-Original Message- From: David Wheeler [mailto:[EMAIL PROTECTED]] 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. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 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
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Boolean casting in 7.3 - changed?
On Thursday 28 November 2002 00:18, Tom Lane wrote: Peter Eisentraut [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 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. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Boolean casting in 7.3 - changed?
Peter Eisentraut [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] 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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Boolean casting in 7.3 - changed?
Ian Barwick [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Boolean casting in 7.3 - changed?
On Wednesday 27 November 2002 06:23, Tom Lane wrote: (B Ian Barwick [EMAIL PROTECTED] writes: (B in 7.3 the following no longer works: (Btemplate1= select 0::bool; (BERROR: Cannot cast type integer to boolean (B (B Note that both old and new versions reject (B select 0::int4::bool; (B (B I believe the behavioral change is a consequence of Rod Taylor's (B DOMAIN patch: it essentially eliminated the old parser_typecast_constant() (B routine in order to ensure that constraints associated with a domain (B would get applied in examples like "select 0::domaintypename". (B (B I wasn't totally happy with Rod's patch, for reasons that I couldn't put (B my finger on at the time, but perhaps my hindbrain understood that there (B would be noticeable behavioral changes. But be that as it may, the code (B is in there now and is unlikely to get reverted. There isn't any place (B in our docs that promises that you can coerce an integer-looking literal (B to bool --- and one could argue that allowing such is just opening the (B door for typos. (B (BThanks for the explanation. I'm not screaming for a reversion ;-), but (Bchanging behaviour which was implicitly valid in previous (Bversions is bound to cause a few people a little head scratching (Bwhen converting applications to 7.3 (I'm sure I can't be the only one). (B (BHow about a line in HISTORY under "Migration to version 7.3" along (Bthe lines of: (B (B"Casting integers to boolean (for example, 0::bool) is no longer allowed, (Buse '0'::bool instead". (B (B (BIan Barwick ([EMAIL PROTECTED] (B (B (B---(end of broadcast)--- (BTIP 5: Have you checked our extensive FAQ? (B (Bhttp://www.postgresql.org/users-lounge/docs/faq.html
