0

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?

new line
  • 183
  • 2
  • 9
  • 3
    I think, `''` (a string) does not match the type of the column `date_unit`, so you probably need to type-convert `date_unit` _in_ the call to `coalesce` to string first. – kostix Aug 19 '22 at 09:24
  • @kostix how should I do that? – new line Aug 19 '22 at 09:29
  • 3
    `SELECT COALESCE(date_unit::text, '') ...` – mkopriva Aug 19 '22 at 09:39
  • 2
    [This](https://stackoverflow.com/a/38280096/720999), but @mkopriva beat me to this :-) – kostix Aug 19 '22 at 10:15
  • 1
    To understand the underlying issue, consider that in the typical data model of a classic SQL-enabled database each column has a type; the type effectively limits the set of possible values a column may have in a row. But if a column is nullable, `NULL` is sort of a single extra value with peculiar properties, but it still has the type assigned to the column. In your example, the `date_unit` column in a row can have three values, one of which is `NULL`, but they all are of the same enum type, and you cannot `COALESCE` them to a value of another (a string). – kostix Aug 19 '22 at 10:20
  • Thanks @kostix & @mkopriva! I get it now, just tried your solutions and it wokrs! – new line Aug 19 '22 at 14:47

2 Answers2

1

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
Sebry
  • 137
  • 5
0
SELECT id,
    user_id,
    coalesce(date_unit, '')
FROM table_name
WHERE user_id = $1
Don
  • 3,876
  • 10
  • 47
  • 76
Anton Kviatsinski
  • 1