Is there a way to return "true"/"false" string for boolean type?

On 7 January 2014 19:11, ChoonSoo Park <luispark@gmail.com> wrote:

Hello Gurus,

I have several tables with lots of boolean columns.
When I run select query for the tables, I always get 't' or 'f' for
boolean types.

Is there a way to return 'true' or 'false' string for boolean type except
using CASE WHEN ... clause?
I mean global postgres configuration setting to return 'true'/'false'
instead of t/f.

Thank you,
Choon Park

Hi,
if you cast the boolean values to text, then you should get 'true'/'false':

SELECT true::boolean::text, false::boolean::text;
text | text
------+-------
true | false
(1 row)

Does it solve your problem? Why do you want to have true/false instead of
t/f?

regards,
Szymon

On Tue, Jan 7, 2014 at 1:29 PM, Szymon Guz <mabewlun@gmail.com> wrote:

On 7 January 2014 19:11, ChoonSoo Park <luispark@gmail.com> wrote:

Hello Gurus,

I have several tables with lots of boolean columns.
When I run select query for the tables, I always get 't' or 'f' for
boolean types.

Is there a way to return 'true' or 'false' string for boolean type except
using CASE WHEN ... clause?
I mean global postgres configuration setting to return 'true'/'false'
instead of t/f.

Thank you,
Choon Park

Hi,
if you cast the boolean values to text, then you should get 'true'/'false':

SELECT true::boolean::text, false::boolean::text;
text | text
------+-------
true | false
(1 row)

Does it solve your problem? Why do you want to have true/false instead of
t/f?

regards,
Szymon

In the custom stored function, I'm returning a resultset using hstore
function.
RETURN QUERY SELECT a few other columns, hstore(t.*) FROM table t WHERE
condition.

I don't want to change it to

SELECT a few other columns, hstore('c1', CAST(t.c1 AS TEXT)) ||
hstore('c2', CAST(t.c2 AS TEXT)) || ...hstore('cn', t.cn::text) || ... FROM
table t WHERE condition.

Thanks,
Choon Park

On Tue, Jan 7, 2014 at 10:41 AM, ChoonSoo Park <luispark@gmail.com> wrote:

On Tue, Jan 7, 2014 at 1:29 PM, Szymon Guz <mabewlun@gmail.com> wrote:

On 7 January 2014 19:11, ChoonSoo Park <luispark@gmail.com> wrote:

Hello Gurus,

I have several tables with lots of boolean columns.
When I run select query for the tables, I always get 't' or 'f' for
boolean types.

Is there a way to return 'true' or 'false' string for boolean type
except using CASE WHEN ... clause?
I mean global postgres configuration setting to return 'true'/'false'
instead of t/f.

Thank you,
Choon Park

Hi,
if you cast the boolean values to text, then you should get
'true'/'false':

SELECT true::boolean::text, false::boolean::text;
text | text
------+-------
true | false
(1 row)

Does it solve your problem? Why do you want to have true/false instead of
t/f?

regards,
Szymon

In the custom stored function, I'm returning a resultset using hstore
function.
RETURN QUERY SELECT a few other columns, hstore(t.*) FROM table t WHERE
condition.

I don't want to change it to

SELECT a few other columns, hstore('c1', CAST(t.c1 AS TEXT)) ||
hstore('c2', CAST(t.c2 AS TEXT)) || ...hstore('cn', t.cn::text) || ...
FROM table t WHERE condition.

Can you use json instead of hstore?

# select * from test;
id | b
----+---
1 | t
2 | f

# select to_json(test) from test;
to_json
--------------------
{"id":1,"b":true}
{"id":2,"b":false}

Joe

On Tue, Jan 7, 2014 at 2:55 PM, Joe Van Dyk <joe@tanga.com> wrote:

On Tue, Jan 7, 2014 at 10:41 AM, ChoonSoo Park <luispark@gmail.com> wrote:

On Tue, Jan 7, 2014 at 1:29 PM, Szymon Guz <mabewlun@gmail.com> wrote:

On 7 January 2014 19:11, ChoonSoo Park <luispark@gmail.com> wrote:

Hello Gurus,

I have several tables with lots of boolean columns.
When I run select query for the tables, I always get 't' or 'f' for
boolean types.

Is there a way to return 'true' or 'false' string for boolean type
except using CASE WHEN ... clause?
I mean global postgres configuration setting to return 'true'/'false'
instead of t/f.

Thank you,
Choon Park

Hi,
if you cast the boolean values to text, then you should get
'true'/'false':

SELECT true::boolean::text, false::boolean::text;
text | text
------+-------
true | false
(1 row)

Does it solve your problem? Why do you want to have true/false instead
of t/f?

regards,
Szymon

In the custom stored function, I'm returning a resultset using hstore
function.
RETURN QUERY SELECT a few other columns, hstore(t.*) FROM table t WHERE
condition.

I don't want to change it to

SELECT a few other columns, hstore('c1', CAST(t.c1 AS TEXT)) ||
hstore('c2', CAST(t.c2 AS TEXT)) || ...hstore('cn', t.cn::text) || ...
FROM table t WHERE condition.

Can you use json instead of hstore?

# select * from test;
id | b
----+---
1 | t
2 | f

# select to_json(test) from test;
to_json
--------------------
{"id":1,"b":true}
{"id":2,"b":false}

Joe

to_json can be a good solution for me. Unfortunately, at this moment, we
don't have a plan to upgrade 9.1 to 9.3. I should still rely on hstore
function to hold key/value pairs.

-Choon Park

On Tue, Jan 7, 2014 at 10:11 AM, ChoonSoo Park <luispark@gmail.com> wrote:

Hello Gurus,

I have several tables with lots of boolean columns.
When I run select query for the tables, I always get 't' or 'f' for
boolean types.

Is there a way to return 'true' or 'false' string for boolean type except
using CASE WHEN ... clause?
I mean global postgres configuration setting to return 'true'/'false'
instead of t/f.

Question for others..

hstore(record) doesn't seem to cast the rows to text using the normal text
cast, right? Why is that?

If I have a table with a boolean column and I call hstore(table), I see
't', not 'true'.

But true::text is 'true' and false::text is 'false'.