CODEWITHSUNDEEP
djangopostgresqlenums
Richard
Richard

Reputation: 65560

Postgres: add description of an ENUM value?

I've got an ENUM column in Postgres 9.6:

CREATE TYPE my_type AS ENUM('foo', 'bar');

I'd like to add a human-readable description for each value in the enum, e.g. for foo, This is the foo value and it does stuff.

Is there any way to do this in Postgres? I'd like something like Django's choices field.

Upvotes: 11

Views: 2754

Answers (2)

Vao Tsun
Vao Tsun

Reputation: 51609

Nothing fancy can be done I think. Standard comment?..

t=# \x
Expanded display is on.
t=# comment on type my_type is 'foo: something fooish, bar: a place to avoid';
COMMENT
t=# \dT+ my_type
List of data types
-[ RECORD 1 ]-----+---------------------------------------------
Schema            | public
Name              | my_type
Internal name     | my_type
Size              | 4
Elements          | foo                                         +
                  | bar
Owner             | postgres
Access privileges |
Description       | foo: something fooish, bar: a place to avoid

with some nerd sniping:

t=# comment on type my_type is '{"foo": "something fooish", "bar": "a place to avoid"}';
COMMENT
t=# select pg_catalog.obj_description(t.oid, 'pg_type')::json->>'foo' from pg_type t where typname = 'my_type';
     ?column?
------------------
 something fooish
(1 row)

Upvotes: 6

Roman Tkachuk
Roman Tkachuk

Reputation: 3276

Teoretically you can create two type with same dimension and use something like this:

CREATE TYPE my_type AS ENUM('foo', 'bar');
CREATE TYPE my_type_description AS ENUM('foo desc', 'bar desc');

CREATE FUNCTION show_desc(i my_type) RETURNS my_type_description AS $sql$
    SELECT ((enum_range(NULL::my_type_description))[array_length(enum_range(NULL, i), 1)])::my_type_description;
$sql$ LANGUAGE SQL STABLE;

SELECT show_desc('foo');

 show_desc 
-----------
 foo desc
(1 row)

SELECT show_desc('bar');

 show_desc 
-----------
 bar desc
(1 row)

Upvotes: 1

Related Questions