SQL Error [22P02]: ERROR: invalid input value for enum tableName.date

I got this error when I try to get the list of all rows on my table. This only happens because I include one of the column in the SELECT. The column itself is an enum column and I wanna use COALESCE for the column in case it meets a null value.

This is a simplication of my code

SELECT id,
    user_id,
    coalesce(date_unit, '') date_unit
FROM table_name
WHERE user_id = $1

I got this error when I try to run it

SQL Error [22P02]: ERROR: invalid input value for enum table_name.date_unit: ""

This is the error when I run it using SQLX On Golang

Pq: invalid input value for enum table_name.date_unit: \"\"

date_unit itself is an enum which has restricted values. It only accepts day and month as value in the table. But lots of rows have null value in date_unit.

I wanna convert it to "" or empty string if date_unit value is null.

Is there a problem with the COALESCE with enum values? How should I use COALESCE to work with what I wanna do?

CodePudding user response：

The answer is found in the comment section of the question.

To officiate it, as date_unit is not a string type, it cannot be returned when querying (invalid data type). As such, when querying, we should convert date_unit to string type.

This can be done using the query:

SELECT id,
    user_id,
    COALESCE(date_unit::text, '')
FROM table_name
WHERE user_id = $1

CodePudding user response：

SELECT id, user_id, coalesce(date_unit, '') FROM table_name WHERE user_id = $1

it might be help you