Search code examples
postgresqltypesnpgsql

How to let PostgreSQL accept text as enum value

When I have a table with an ENUM column, and I insert like this:

UPDATE table SET enum_col = 'enum_value';

this works fine, but when explicitly casting it, I get an error:

UPDATE table SET enum_col = 'enum_value'::text;

ERROR:  column "enum_col" is of type some_enum_type but expression is of type text
LINE 1: UPDATE table SET enum_col = 'enum_value'::text;
                                    ^
HINT:  You will need to rewrite or cast the expression.

I understand the error, but is there some setting I can use, so PostgreSQL will accept it anyway?

(Some extra background, I am using Npgsql to update data in a database, and even though I do not put the ::text in my query, I do get this error. It looks like Npgsql adds the explicit value types when using parametrised queries.)

Solution

  • You can create a cast from text to your enum type:

    CREATE TYPE my_enum AS ENUM ('one', 'two', 'three');

CREATE CAST (text AS my_enum) WITH INOUT AS ASSIGNMENT;

CREATE TABLE mytab (enum_col my_enum);

INSERT INTO mytab VALUES ('one'::text);
INSERT 0 1