Algebraic data types in Postgres

Question

Is it possible to create an Algebraic data type in Postgres and then use it as a column type?

For example:

CREATE TYPE hoofed AS ENUM('horse', 'goat'); CREATE TYPE monkey AS ENUM('chimp','macaque'); CREATE TYPE ANIMAL AS ENUM(hoofed, monkey); 

This fails:

 syntax error at or near "hoofed" LINE 1: CREATE TYPE ANIMAL AS ENUM(hoofed, monkey); 

Is it possible to do something like this?

Ultimately, what I would like to do is something like this:

 CREATE TABLE zoo ( a ANIMAL, name text ); INSERT INTO zoo(a, name) VALUES('horse', 'bob'); INSERT INTO zoo(a, name) VALUES('macaque', 'jimmy'); 

And so that both entries are independently valid.

EDIT: @ Abihabi87 the answer below allows me to create, in essence, a product type, but it still does not allow me to create a union type as desired.

Answer 1

You cannot create a type enumeration from another type of enumeration:

You can create ANIMAL, like:

 CREATE TYPE ANIMAL AS (h hoofed,m monkey); 

Usage example:

 CREATE TABLE your_table ( a ANIMAL ); INSERT INTO your_table(a) select (select ('horse','macaque')::ANIMAL); 

Answer 2

Use function:

 create or replace function create_enum(name, variadic regtype[]) returns void language plpgsql as $$ begin execute format( 'create type %I as enum(%s)', $1, string_agg(quote_literal(enumlabel), ',' order by enumtypid, enumsortorder)) from pg_enum where enumtypid = any($2); end $$; 

Pass the name of the new type and a list of enum types as arguments:

 select create_enum('animal', 'hoofed', 'monkey'); select enum_range(null::animal) as animal; animal ---------------------------- {horse,goat,chimp,macaque} (1 row) 

Answer 3

Effectively, you are trying to combine two types of enum .
There are several open questions:

• Is it possible to duplicate rows?
• It is assumed that the project must be static (changes to the enum type hoofed do not change the type of animal later) or dynamic (the opposite).
• Combine exactly two types of enum or more?
• Since the order of the elements is significant, what is the order of the elements in animal , which should be?
• Is this a one-time operation or is it intended to be reused?

Assuming no duplicates, static design, two types of enum , the existing order of elements as added and one-time.

You can use the built-in enum_range(anyenum) enumeration support function to get an array of all elements for a given enum type.

 DO $$ BEGIN EXECUTE ( SELECT 'CREATE TYPE animal AS ENUM (' || array_to_string(enum_range(null::hoofed)::text[] || enum_range(null::monkey)::text[], ''',''') || ''')' ); END $$; 

Answer 4

With ENUM types you cannot achieve dynamic type / union. However, with DOMAIN types, you can achieve something similar:

 create function valid_any_domain(anyelement, variadic regtype[]) returns boolean language plpgsql immutable as $func$ declare t regtype; begin foreach t in array $2 loop begin execute format('select $1::%s', t) using $1; exception when not_null_violation or check_violation then continue; end; return true; end loop; return false; end; $func$; create domain hoofed as text check (value in ('horse', 'goat')); create domain monkey as text check (value in ('chimp','macaque')); create domain animal as text check (valid_any_domain(value, 'hoofed', 'monkey')); 

Changing the base types will also dynamically change the composite / merged type, but it requires a manual binding check (especially when some values ​​are removed from the valid spectrum):

 alter domain hoofed drop constraint hoofed_check; alter domain hoofed add check (value in ('horse', 'goat', 'zebra')); alter domain animal validate constraint animal_check; 

http://rextester.com/MBVC62095

Note: however, with DOMAIN types, you will lose the ENUM : custom order property. DOMAIN will always use the base type order.