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.