Thread: Format of BOOLEAN

Hello

When I SELECT a row with a BOOLEAN in it I get either 't' or 'f'.
But when I use this same value in the WHERE condition I get an error,
because PostgreSQL demands either 'true' or 'false'.

Example:

testdb=# create table testtable (acolumn BOOLEAN);
CREATE
testdb=# INSERT INTO testtable VALUES (true);
INSERT 161246 1
testdb=# SELECT * FROM testtable;
 acolumn
---------
 t
(1 row)

testdb=# SELECT * FROM testtable WHERE acolumn = t;
ERROR:  Attribute 't' not found

This is a real problem for me since I am currently writing on a mostly
database independent engine. PostgreSQL would be the the first time that
I can't write into a database what I read from it.

Is it possible to change the settings of PostgreSQL somewhere so that
I get 'true' or 'false'?

Thank you!

"Lehmeier, Michael" <michael.lehmeier@cognitech.de> writes:

> Hello
>
> When I SELECT a row with a BOOLEAN in it I get either 't' or 'f'.
> But when I use this same value in the WHERE condition I get an error,
> because PostgreSQL demands either 'true' or 'false'.

If you quote the value you get back from Postgres (which you probably
should be doing anyway) it will work:

foo=# create table test1 (f1 boolean);
CREATE
foo=# insert into test1 values ('t');
INSERT 31078 1
foo=# select * from test1;
 f1
----
 t
(1 row)

foo=# select * from test1 where f1='t';
 f1
----
 t
(1 row)

-Doug
--
The rain man gave me two cures; he said jump right in,
The first was Texas medicine--the second was just railroad gin,
And like a fool I mixed them, and it strangled up my mind,
Now people just get uglier, and I got no sense of time...          --Dylan

On Thu, 7 Jun 2001, Lehmeier, Michael wrote:

> Hello
>
> When I SELECT a row with a BOOLEAN in it I get either 't' or 'f'.
> But when I use this same value in the WHERE condition I get an error,
> because PostgreSQL demands either 'true' or 'false'.
>
> Example:
>
> testdb=# create table testtable (acolumn BOOLEAN);
> CREATE
> testdb=# INSERT INTO testtable VALUES (true);
> INSERT 161246 1
> testdb=# SELECT * FROM testtable;
>  acolumn
> ---------
>  t
> (1 row)
>
> testdb=# SELECT * FROM testtable WHERE acolumn = t;
> ERROR:  Attribute 't' not found
>
> This is a real problem for me since I am currently writing on a mostly
> database independent engine. PostgreSQL would be the the first time that
> I can't write into a database what I read from it.

you mean read from a db what you wrote into it

>
> Is it possible to change the settings of PostgreSQL somewhere so that
> I get 'true' or 'false'?
>
> Thank you!

You have to give

SELECT * FROM testtable WHERE col='t';    [single quote the value]

It also works with WHERE col='true';


cheers,
thalis

> -----Message d'origine-----
> De:    Lehmeier, Michael [SMTP:michael.lehmeier@cognitech.de]
> Date:    jeudi 7 juin 2001 18:06
> Objet:    [GENERAL] Format of BOOLEAN
>
> testdb=# SELECT * FROM testtable WHERE acolumn = t;
> ERROR:  Attribute 't' not found

testdb=# SELECT * FROM testtable WHERE acolumn = 't';

Don't forget the single quote around the t : a boolean is a single char...

NH

Lehmeier, Michael writes:

> testdb=# SELECT * FROM testtable WHERE acolumn = t;
> ERROR:  Attribute 't' not found

SELECT * FROM testtable WHERE acolumn = 't';

Or better yet (SQL compatible):

SELECT * FROM testtable WHERE acolumn is true;

The latter is a special syntax, whereas in the former the 't' is just an
ordinary data literal.

> Is it possible to change the settings of PostgreSQL somewhere so that
> I get 'true' or 'false'?

case when acolumn then 'true' else 'false' end

--
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter