Format of BOOLEAN

"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