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.)
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