How to let PostgreSQL accept text as enum value

Question

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

To get this UPDATE table SET enum_col = 'enum_value'::text; to work do instead: UPDATE table SET enum_col = 'enum_value'::some_enum_type;. — Adrian Klaver
– Adrian Klaver, Commented Dec 11, 2020 at 15:23
@AdrianKlaver this certainly helps, but unfortunately at this point I cannot touch the C# code, plus it is some generic code. — Bart Friederichs
– Bart Friederichs, Commented Dec 11, 2020 at 15:24
If possible you should prefer using tables with foreign keys instead of enum types which are mostly difficult to handle (update, delete values, inserting with frameworks, ...) — S-Man
– S-Man, Commented Dec 11, 2020 at 15:30
Unfortunately it looks like you are out of luck. The type check is done early in the process so a BEFORE trigger does not work. A custom CAST is not going to work as the source(text) is to generic. — Adrian Klaver
– Adrian Klaver, Commented Dec 11, 2020 at 15:43

Laurenz Albe · Accepted Answer · 2024-09-09 21:21:22Z

5

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

edited Sep 9, 2024 at 21:21

answered Dec 11, 2020 at 18:27

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

cdhowie Over a year ago

Clarification: You only need to be a super-user to create a binary-coercible cast, which this is not.

Laurenz Albe Over a year ago

@cdhowie Huh? text and an enum (which is a real under the hood) are not binary coercible.

cdhowie Over a year ago

Exactly. Binary-coercible casts convert one type to another by only changing type information, without modifying the actual bit representation. You need to be a super-user to create that kind of cast. The kind of cast in the answer is not that kind, so you don't need to be a super-user to create it. The last sentence of the answer is therefore incorrect.

Laurenz Albe Over a year ago

Ah, not I understand you. Thanks for the comment; I have removed that claim.

Collectives™ on Stack Overflow

How to let PostgreSQL accept text as enum value

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related