How to cast "truthy" and "falsey" strings to boolean in Postgres?

Suppose the following:

create table person(
  id serial primary key,
  person_name varchar(55)
create table category(
  id serial primary key,
  category_name varchar(55)
create table category_subscription(
  id serial primary key,
  category_id bigint references category(id),
  person_id bigint references person(id)

insert into person values(1, 'george');
insert into category values(10, 'homework'), (20, 'promotion'), (30, 'delivery');
insert into category_subscription values(100, 10, 1), (200, 30, 1);

Here is my query:

select as c_id,
    c.category_name as category_name,
    p.person_name as is_subscribed
from category c
full join category_subscription cs on = cs.category_id
full join person p on = cs.person_id

This works as is, but I want to cast is_subscribed to boolean.

Expected result:

        "id": 10,
        "category_name": "homework",
        "is_subscribed": true
        "id": 20,
        "category_name": "promotion",
        "is_subscribed": false
        "id": 30,
        "category_name": "delivery",
        "is_subscribed": true

I’ve tried coalesce(p.person_name, false) <> true and p.person_name::boolean as is_subscribed, but nothing worked.

How can I accomplish this?

>Solution :

Test for NULL:

p.person_name is not null as is_subscribed

Leave a Reply