问题

[
    {
        "id": 10,
        "category_name": "homework",
        "is_subscribed": true
    },
    {
        "id": 20,
        "category_name": "promotion",
        "is_subscribed": false
    },
    {
        "id": 30,
        "category_name": "delivery",
        "is_subscribed": true
    }
]

<details>
<summary>英文:</summary>

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?

</details>


# 答案1
**得分**: 1

测试 NULL：

    p.person_name 不为空 则为 is_subscribed

<details>
<summary>英文:</summary>

Test for NULL:

    p.person_name is not null as is_subscribed

</details>