pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcast of integer to bool doesn't work (anymore?)
7+ messages / 3 participants
[nested] [flat]
* cast of integer to bool doesn't work (anymore?)
@ 2003-03-21 13:36 Eric Veldhuyzen <ericv@xs4all.net>
0 siblings, 2 replies; 7+ messages in thread
From: Eric Veldhuyzen @ 2003-03-21 13:36 UTC (permalink / raw)
To: pgsql-sql
Hi,
Here at my work we use on the production servers PostgreSQL version
7.2.3, but I have version 7.3.2 (from the Debian distribution) on my
workstation. Now I noticed yesterday that the query 'select 0::boolean;'
works on the production server, it gives the output:
# select 0::boolean;
bool
------
f
(1 row)
Perfect. But when I try this on my local version of postgreSQL I get
this:
# select 0::boolean;
ERROR: Cannot cast type integer to boolean
Is there someone who can axplaint to me why this is happening, did I
forget to do something to make this work, or is this some kind of new
'feature' of the 7.3 version?
--
Eric Veldhuyzen
xs4all NSA team
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: cast of integer to bool doesn't work (anymore?)
@ 2003-03-21 14:19 Eric Veldhuyzen <ericv@xs4all.net>
parent: Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 1 reply; 7+ messages in thread
From: Eric Veldhuyzen @ 2003-03-21 14:19 UTC (permalink / raw)
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>; +Cc: Eric Veldhuyzen <ericv@xs4all.net>; pgsql-sql
On Fri, Mar 21, 2003 at 03:54:56PM -0200, Achilleus Mantzios wrote:
> On Fri, 21 Mar 2003, Eric Veldhuyzen wrote:
>
> > Hi,
> >
> > Here at my work we use on the production servers PostgreSQL version
> > 7.2.3, but I have version 7.3.2 (from the Debian distribution) on my
> > workstation. Now I noticed yesterday that the query 'select 0::boolean;'
> > works on the production server, it gives the output:
> >
> > # select 0::boolean;
> > bool
> > ------
> > f
> > (1 row)
> >
> > Perfect. But when I try this on my local version of postgreSQL I get
> > this:
> >
> > # select 0::boolean;
> > ERROR: Cannot cast type integer to boolean
>
> Just wrap 0 with single quotes,
> e.g.
> # select '0'::boolean;
Yeah, I tried that, and yes, that does work from the psql prompt. This
basically means that I fool psql into thinking that it is a string, and
then forcing it to cast to a boolean, right? But my problem is that it
does not work when I use that in the prepare statement from perl. For
example:
my $sth = $dbh->prepare(
"INSERT INTO object_def (name, meant_as_subobject) VALUES (?,?::bool)");
$sth->execute('test', 0);
This code gives me the error
"DBD::Pg::st execute failed: ERROR: Cannot cast type integer to boolean"
on 7.3, but it works on 7.2 and below. If I change the prepare to
my $sth = $dbh->prepare(
"INSERT INTO object_def (name, meant_as_subobject) VALUES (?,'?'::bool)");
I will get the error
"execute called with 2 bind variables, 1 needed" because the
perl database driver can't handle the quote characters in a prepare statement.
This here is ofcourse a simplyfied example, in our real project the
prepare statement is dynamically build by a library that knows the
database schema, and tries to do conversions while builing
the prepared statement. One if them is checking the columntypes, and
replace the ? with ?::bool in the prepared statement if the columntype
is of boolean type. Another is something similar if the columntype is a
timestamp or date (I haven't tested if that part still works though, I
realize now).
--
Eric Veldhuyzen
xs4all NSA team
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: cast of integer to bool doesn't work (anymore?)
@ 2003-03-21 15:01 Eric Veldhuyzen <ericv@xs4all.net>
parent: Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 1 reply; 7+ messages in thread
From: Eric Veldhuyzen @ 2003-03-21 15:01 UTC (permalink / raw)
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>; +Cc: Eric Veldhuyzen <ericv@xs4all.net>; pgsql-sql
On Fri, Mar 21, 2003 at 04:46:17PM -0200, Achilleus Mantzios wrote:
>
> Currently (7.3) all input can be handled if fed as text.
> So what you can do is simply:
>
> my $sth = $dbh->prepare(
> "INSERT INTO object_def (name, meant_as_subobject) VALUES (?,?)");
> $sth->execute('test', '0');
Ah, thanks, that seems to work, with only minor modifications to our
code. Is there any reason why integers are no longer convertable to
booleans?
--
Eric Veldhuyzen
xs4all NSA team
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: cast of integer to bool doesn't work (anymore?)
@ 2003-03-21 15:07 Tom Lane <tgl@sss.pgh.pa.us>
parent: Eric Veldhuyzen <ericv@xs4all.net>
1 sibling, 0 replies; 7+ messages in thread
From: Tom Lane @ 2003-03-21 15:07 UTC (permalink / raw)
To: Eric Veldhuyzen <ericv@xs4all.net>; +Cc: pgsql-sql
Eric Veldhuyzen <ericv@xs4all.net> writes:
> # select 0::boolean;
> bool=20
> ------
> f
> (1 row)
> Perfect.
Not so perfect as you think --- 7.2 does not allow casting from int to
bool any more than 7.3 does. Try this:
regression=# select 1::int::boolean;
ERROR: Cannot cast type 'integer' to 'boolean'
7.2 is interpreting your query like
regression=# select '1'::boolean;
bool
------
t
(1 row)
which still works in 7.3 --- but we tightened up the other case, for
reasons I don't recall at the moment but you can find in the mailing
list archives.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: cast of integer to bool doesn't work (anymore?)
@ 2003-03-21 17:54 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: Eric Veldhuyzen <ericv@xs4all.net>
1 sibling, 1 reply; 7+ messages in thread
From: Achilleus Mantzios @ 2003-03-21 17:54 UTC (permalink / raw)
To: Eric Veldhuyzen <ericv@xs4all.net>; +Cc: pgsql-sql
On Fri, 21 Mar 2003, Eric Veldhuyzen wrote:
> Hi,
>
> Here at my work we use on the production servers PostgreSQL version
> 7.2.3, but I have version 7.3.2 (from the Debian distribution) on my
> workstation. Now I noticed yesterday that the query 'select 0::boolean;'
> works on the production server, it gives the output:
>
> # select 0::boolean;
> bool
> ------
> f
> (1 row)
>
> Perfect. But when I try this on my local version of postgreSQL I get
> this:
>
> # select 0::boolean;
> ERROR: Cannot cast type integer to boolean
Just wrap 0 with single quotes,
e.g.
# select '0'::boolean;
>
> Is there someone who can axplaint to me why this is happening, did I
> forget to do something to make this work, or is this some kind of new
> 'feature' of the 7.3 version?
>
>
--
==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel: +30-210-8981112
fax: +30-210-8981877
email: achill@matrix.gatewaynet.com
mantzios@softlab.ece.ntua.gr
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: cast of integer to bool doesn't work (anymore?)
@ 2003-03-21 18:46 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: Eric Veldhuyzen <ericv@xs4all.net>
0 siblings, 1 reply; 7+ messages in thread
From: Achilleus Mantzios @ 2003-03-21 18:46 UTC (permalink / raw)
To: Eric Veldhuyzen <ericv@xs4all.net>; +Cc: pgsql-sql
On Fri, 21 Mar 2003, Eric Veldhuyzen wrote:
> On Fri, Mar 21, 2003 at 03:54:56PM -0200, Achilleus Mantzios wrote:
> > On Fri, 21 Mar 2003, Eric Veldhuyzen wrote:
> >
> > > Hi,
> > >
> > > Here at my work we use on the production servers PostgreSQL version
> > > 7.2.3, but I have version 7.3.2 (from the Debian distribution) on my
> > > workstation. Now I noticed yesterday that the query 'select 0::boolean;'
> > > works on the production server, it gives the output:
> > >
> > > # select 0::boolean;
> > > bool
> > > ------
> > > f
> > > (1 row)
> > >
> > > Perfect. But when I try this on my local version of postgreSQL I get
> > > this:
> > >
> > > # select 0::boolean;
> > > ERROR: Cannot cast type integer to boolean
> >
> > Just wrap 0 with single quotes,
> > e.g.
> > # select '0'::boolean;
>
> Yeah, I tried that, and yes, that does work from the psql prompt. This
> basically means that I fool psql into thinking that it is a string, and
> then forcing it to cast to a boolean, right? But my problem is that it
> does not work when I use that in the prepare statement from perl. For
> example:
>
> my $sth = $dbh->prepare(
> "INSERT INTO object_def (name, meant_as_subobject) VALUES (?,?::bool)");
> $sth->execute('test', 0);
>
> This code gives me the error
> "DBD::Pg::st execute failed: ERROR: Cannot cast type integer to boolean"
> on 7.3, but it works on 7.2 and below. If I change the prepare to
>
> my $sth = $dbh->prepare(
> "INSERT INTO object_def (name, meant_as_subobject) VALUES (?,'?'::bool)");
>
> I will get the error
> "execute called with 2 bind variables, 1 needed" because the
> perl database driver can't handle the quote characters in a prepare statement.
Currently (7.3) all input can be handled if fed as text.
So what you can do is simply:
my $sth = $dbh->prepare(
"INSERT INTO object_def (name, meant_as_subobject) VALUES (?,?)");
$sth->execute('test', '0');
>
> This here is ofcourse a simplyfied example, in our real project the
> prepare statement is dynamically build by a library that knows the
> database schema, and tries to do conversions while builing
> the prepared statement. One if them is checking the columntypes, and
> replace the ? with ?::bool in the prepared statement if the columntype
> is of boolean type. Another is something similar if the columntype is a
> timestamp or date (I haven't tested if that part still works though, I
> realize now).
>
>
--
==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel: +30-210-8981112
fax: +30-210-8981877
email: achill@matrix.gatewaynet.com
mantzios@softlab.ece.ntua.gr
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: cast of integer to bool doesn't work (anymore?)
@ 2003-03-21 19:20 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: Eric Veldhuyzen <ericv@xs4all.net>
0 siblings, 0 replies; 7+ messages in thread
From: Achilleus Mantzios @ 2003-03-21 19:20 UTC (permalink / raw)
To: Eric Veldhuyzen <ericv@xs4all.net>; +Cc: pgsql-sql
On Fri, 21 Mar 2003, Eric Veldhuyzen wrote:
> On Fri, Mar 21, 2003 at 04:46:17PM -0200, Achilleus Mantzios wrote:
> >
> > Currently (7.3) all input can be handled if fed as text.
> > So what you can do is simply:
> >
> > my $sth = $dbh->prepare(
> > "INSERT INTO object_def (name, meant_as_subobject) VALUES (?,?)");
> > $sth->execute('test', '0');
>
> Ah, thanks, that seems to work, with only minor modifications to our
> code. Is there any reason why integers are no longer convertable to
> booleans?
There has been some general rectification on the casting system in 7.3.*.
>
>
--
==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel: +30-210-8981112
fax: +30-210-8981877
email: achill@matrix.gatewaynet.com
mantzios@softlab.ece.ntua.gr
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2003-03-21 19:20 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-21 13:36 cast of integer to bool doesn't work (anymore?) Eric Veldhuyzen <ericv@xs4all.net>
2003-03-21 15:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-03-21 17:54 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2003-03-21 14:19 ` Eric Veldhuyzen <ericv@xs4all.net>
2003-03-21 18:46 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2003-03-21 15:01 ` Eric Veldhuyzen <ericv@xs4all.net>
2003-03-21 19:20 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox