CODEWITHSUNDEEP
postgresqlcastingenums
Bart Friederichs
Bart Friederichs

Reputation: 33491

How to cast string value to enum

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?

Upvotes: 42

Views: 48243

Answers (4)

Farhad
Farhad

Reputation: 97

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

'critical'::"enum_log_priority"

Upvotes: 0

Michele Dorigatti
Michele Dorigatti

Reputation: 817

Postgres supports also the cast function:

cast(priority AS enum_log_priority);

Upvotes: 6

roman
roman

Reputation: 117337

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 |

Upvotes: 6

Rakesh Soni
Rakesh Soni

Reputation: 10877

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

Upvotes: 67

Related Questions