How to cast string value to enum

Question

I have a table with an enum type in it, and I created a function to add data to that table. I want that function to be generous in what to accept, so I take a text as the enum type and want to cast it later.

This is the enum:

CREATE TYPE public.enum_log_priority AS ENUM (
    'critical','error','warning','notice','debug'
);

And this is the function:

CREATE OR REPLACE FUNCTION public.log_write(
    _message text,
    _priority text
) RETURNS integer AS
$body$
BEGIN
    _priority = lower(_priority);
    INSERT INTO log (message, priority) VALUES (_message, _priority);

    RETURN 0;
END
$body$
LANGUAGE 'plpgsql';

I know this doesn't work:

ERROR: column "priority" is of type enum_log_priority but expression is of type text

but how can I do this?

Please specify the structure of enum_log_priority with all possible values — Rakesh Soni
– Rakesh Soni, Commented Sep 5, 2013 at 9:11

Community · Accepted Answer · 2017-05-23 12:09:20Z

69

Use syntax like below during insertion

'critical'::enum_log_priority

Please see some link as well

http://www.postgresql.org/docs/9.1/static/datatype-enum.html

Inserting into custom SQL types with prepared statements in java

java enum and postgresql enum

edited May 23, 2017 at 12:09

CommunityBot

11 silver badge

answered Sep 5, 2013 at 9:17

Rakesh Soni

11k6 gold badges48 silver badges53 bronze badges

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

Comments

roman · Accepted Answer · 2013-09-05 12:06:20Z

6

change your function like this:

CREATE OR REPLACE FUNCTION public.log_write(
    _message text,
    _priority text
) RETURNS integer AS
$body$
BEGIN
    _priority = lower(_priority);
    INSERT INTO log (message, priority) VALUES (_message, _priority::enum_log_priority);

    RETURN 0;
END
$body$
LANGUAGE 'plpgsql';

| sql fiddle demo |

answered Sep 5, 2013 at 12:06

roman

118k30 gold badges205 silver badges209 bronze badges

Comments

Michele Dorigatti · Accepted Answer · 2021-12-06 14:59:38Z

6

Postgres supports also the cast function:

cast(priority AS enum_log_priority);

answered Dec 6, 2021 at 14:59

Michele Dorigatti

8191 gold badge9 silver badges19 bronze badges

5 Comments

mik3fly-4steri5k Over a year ago

not working for me, same error

Michele Dorigatti Over a year ago

please post a question stating what you tried, which error you get and reference this answer

mik3fly-4steri5k Over a year ago

"ERROR: column "type" is of type geotargettypenum but expression is of type text" with cast(type AS geotargettypeenum); ENUM is create type geotargettypeenum as enum ('ADMIN_ZONE', 'BUFFER', 'INSEE_CODE', 'IRIS_CODE', 'POLYGON', 'POSTAL_CODE');

Michele Dorigatti Over a year ago

Not here. You need to write your own question.

Michele Dorigatti Over a year ago

you don't need the cast, your expression is not an enum, but a text

Farhad · Accepted Answer · 2023-01-06 21:29:34Z

0

Consider that sometimes (for example in Prisma raw queries) you need to put the enum type inside quotations.

'critical'::"enum_log_priority"

answered Jan 6, 2023 at 21:29

Farhad

1071 silver badge4 bronze badges

Collectives™ on Stack Overflow

How to cast string value to enum

4 Answers 4

Comments

Comments

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related