Subquery select values and save into enum in postgresql

Question

I'm trying to remove a value from an existing postgresql enum datatype. My research tells me that I need to remake the enum data type excluding the value I do not want. My idea for accomplishing this is to get all values from the existing data type via

select e.enumlabel as enum_value from pg_type t join pg_enum e on t.oid = e.enumtypid join pg_catalog.pg_namespace n ON n.oid = t.typnamespace where t.typname = 'alert_level' where e.enumlabel is not 'value i want to exclude'

create type alert_type2 as enum ('ABOVE QUERY HERE') ::alert_type

and take these values I want and somehow insert them into a new data type

So my question: Is it possible to get values from a subquery and create an enum data type from the query results? Is there a better way to do this?

I've also tried something like this but I cant figure out how to exclude values from the select enum_range.

create type alert_type2 as enum (select enum_range(NULL::alert_type)::text)

klin · Accepted Answer · 2016-06-19 22:56:14Z

You have to exceute a dynamic query inside a function or DO block, example:

create type fruits as enum ('apple', 'pear', 'banana');

do $$
begin
    execute format(
        'create type fruits_without_pear as enum (%s)',
        (
            select array_to_string(array_agg(quote_literal(e)), ',')
            from unnest(enum_range(null::fruits)) e
            where e <> 'pear'
        )
    );
end $$;

select enum_range(null::fruits_without_pear);

   enum_range   
----------------
 {apple,banana}
(1 row)

Read about:

Notes about used query.

select enum_range(null::fruits)

     enum_range      
---------------------
 {apple,pear,banana}
(1 row)

We want to transform the result to the string 'apple','banana' to use it as a parameter in format(). First, remove 'pear' from the list:

select e
from unnest(enum_range(null::fruits)) e
where e <> 'pear';

   e    
--------
 apple
 banana
(2 rows)

next, quote the elements and aggregate them into an array:

select array_agg(quote_literal(e))
from unnest(enum_range(null::fruits)) e
where e <> 'pear'

     array_agg      
--------------------
 {'apple','banana'}
(1 row)

finally, transform an array to string:

select array_to_string(array_agg(quote_literal(e)), ',')
from unnest(enum_range(null::fruits)) e
where e <> 'pear'

 array_to_string  
------------------
 'apple','banana'
(1 row)

However, we should have done it a little simpler ;)

select string_agg(quote_literal(e), ',')
from unnest(enum_range(null::fruits)) e
where e <> 'pear'

 array_to_string  
------------------
 'apple','banana'
(1 row)

Thank you my good sir. Works excellently! Can you point me to any documentation that might explain your answer?

Collectives™ on Stack Overflow

Subquery select values and save into enum in postgresql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest