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
    c.id as c_id,
    c.category_name as category_name,
    p.person_name as is_subscribed
from category c
full join category_subscription cs on c.id = cs.category_id
full join person p on p.id = 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