sql - 如何在PostgreSQL的select语句中用STRING值替换BIT

在PostgreSQL中，如何将SELECT语句返回的数据集中的一个位值替换为一个字符串（单词，无数字）？一般来说，我对SQL还很陌生，所以我的经验和google并没有被证明是丰富的。
在这种特殊情况下，c.visible是存储0（可见）或1（不可见）的位值。在我的查询中，我想显示c.visible=0的所有结果，但在该select语句返回的数据集中显示“this course is visible to students.”，而不是“0”。我曾尝试在SELECT语句中将其转换为文本，然后在WHERE子句中的CASE-WHEN语句中更改值，但这会导致无效的输入语法错误。可见性是数据库中记录此信息的唯一属性。
例如：

--------------------------------------------------------
| CourseName | ID | CourseVisibility                   |
--------------------------------------------------------
|    ABC     | 10 | This course is visible to students.|
--------------------------------------------------------
|    DEF     | 22 | This course is visible to students.|
--------------------------------------------------------

SELECT c.fullname AS CourseName, c.id AS ID, CAST(c.visible AS TEXT) AS CourseVisibilty
FROM prefix_course c, prefix_logstore_standard_log lsl
WHERE
    c.visible = 0
    CASE WHEN c.visible = '0'
        THEN c.visible = 'This course is visible to students.'
    END
    AND lsl.courseid = c.id
    AND lsl.timecreated BETWEEN extract(epoch from NOW()- INTERVAL '12 month') AND extract(epoch from NOW())
GROUP BY c.fullname, c.id, c.visible

ERROR:  invalid input syntax for integer: "This course is visible to students."
LINE 5:   THEN c.visible = 'This course is visible to students.'
                           ^

正确的方法是什么？

最佳答案

不能在where子句中修改返回值，但可以在select子句中修改

SELECT
    c.fullname AS CourseName,
    c.id AS ID,
    CASE WHEN c.visible = '0'
    THEN 'This course is visible to students.'
    ELSE 'This course is not visible to students.'
    END AS CourseVisibilty
FROM prefix_course c, prefix_logstore_standard_log lsl
WHERE
    lsl.courseid = c.id
    AND lsl.timecreated BETWEEN extract(epoch from NOW()- INTERVAL '12 month') AND extract(epoch from NOW())
GROUP BY c.fullname, c.id, c.visible

关于sql - 如何在PostgreSQL的select语句中用STRING值替换BIT，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/56747011/