Обсуждение: cast of integer to bool doesn't work (anymore?)
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
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
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
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
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
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
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