How to replace a BIT with a STRING value in a select statement in PostgreSQL

Question

How can I replace a BIT value in the dataset returned by a SELECT statement with a STRING (of words, no numbers) in PostgreSQL? I'm very new to SQL in general, so my experience and googleing has not proved bountiful.

In this particular case, c.visible is a bit value storing either 0 (is visible) or 1 (not visible). In my query, I want to show all results where c.visible = 0, but display 'this course is visible to students.' instead of '0' in the dataset returned by this select statement. I've tried casting it as text in the SELECT statement, then changing the value in a CASE WHEN statement in the WHERE clause, but this results in an invalid input syntax error. c.visibility is the only attribute in the database that records this information.

For example:

--------------------------------------------------------
| 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.' 
                           ^

What is the correct method to do this?

It is unclear whether you want to do a select (retrieve records) or an update (change data in some records). Please clarify what you are trying to do here. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jun 25, 2019 at 4:56
@TimBiegeleisen, I am trying to retrieve records, but display '0' as 'This course is visible to students.' in the resulting data. Muhammad provided the solution - I should've been using the case within the select clause instead. — Zectzozda
– Zectzozda, Commented Jun 25, 2019 at 5:41
For flags like "visible"/"not visible" a boolean column is a much better choice than a bit column — user330315
– user330315, Commented Jun 25, 2019 at 5:48
@a_horse_with_no_name - I agree. However, this is how the database backend of Moodle is (a very popular LMS), so I am unable to change this w/o risk. Thanks for the reminder, though! — Zectzozda
– Zectzozda, Commented Jun 25, 2019 at 6:07
XMLDB here for anyone interested: examulator.com/er/xml/all_tables.html — Zectzozda
– Zectzozda, Commented Jun 25, 2019 at 6:15

Muhammad Vakili · Accepted Answer · 2019-06-25 04:57:01Z

2

You can't modify returned values inside where clause, but you can do it in select clause

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

answered Jun 25, 2019 at 4:57

Muhammad Vakili

7085 silver badges19 bronze badges

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

2 Comments

Zectzozda Over a year ago

Legend! This does it beautifully. Thanks for the quick response! +1

Gordon Linoff Over a year ago

How does a solution archaic join syntax get upvoted?

Collectives™ on Stack Overflow

How to replace a BIT with a STRING value in a select statement in PostgreSQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related