Postgresql format function of boolean values cast to jsonb

Question

When I try to format() a postgresql boolean value and cast the value to a jsonb object, PostgreSQL raises an exception:

# select format('%s', true)::jsonb;
ERROR:  invalid input syntax for type json
DETAIL:  Token "t" is invalid.
CONTEXT:  JSON data, line 1: t

I am fairly sure that formatting a boolean value gives either t or f rather than the full word, which is what json expects it to be.

There already is a solution to this by using the case statement

 select format('%s', case when true then 'true' else 'false' end)::jsonb;
 format 
--------
  true
(1 row)

However, while this may seem fine for the trivial case above, when it's used in functions and anything more complicated it looks quite out of place. Is there a neater solution to this?

The context is that I have a function returning a hash of keys mapping to boolean flags, which are decided by functions. A sample of what one function could look like is

return format( '{
        "is_valid" : %s,
        "is_authorized" : %s,
        "is_not_delegated" : %s,
        }',
        is_valid(function_argument),
        is_authorized(function_argument),
        not is_delegated(function_argument)
 )::jsonb;

cetver · Accepted Answer · 2015-09-16 08:45:10Z

1

select 
    row_to_json(q)
from (
    select 
        true is_valid,
        false is_authorized
) q

answered Sep 16, 2015 at 8:45

cetver

11.9k6 gold badges42 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Shamas S Over a year ago

Please consider adding some detail to your answer.

Diego Over a year ago

this answer is not using the format function

Constantin Voita · Accepted Answer · 2021-05-10 06:25:39Z

0

You can use cast from boolean to text like as my_column::text

select format('{"level":%s,"cluster":%s}', rl.level, rl.cluster::text)

answered May 10, 2021 at 6:25

Constantin Voita

11 bronze badge

Comments

Constantin Voita · Accepted Answer · 2022-01-27 21:47:07Z

0

You can use something like this:

select format('%s', true::text)::jsonb;

answered Jan 27, 2022 at 21:47

Constantin Voita

11 bronze badge

Collectives™ on Stack Overflow

Postgresql format function of boolean values cast to jsonb

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related