Question 115063 in PostgreSQL Question posted in
The official documentation can be found here.

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

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?

Tags:

2

Answers


  1. 0 
    SELECT id,
    user_id,
    coalesce(date_unit, '')
FROM table_name
WHERE user_id = $1
    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search