HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Is it a bad practice to query pg_type for enums on a regular basis?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
basispracticequerypg_typebadregularforenums

Problem

In Postgres, I'm thinking of query pg_type for an up-to-date list of enumerations I'm using on a regular basis. I'd be using something like this:

SELECT pg_type.typname AS enum_type, pg_enum.enumlabel AS enmu_label FROM 
pg_type JOIN pg_enum ON pg_enum.enumtypid = pg_type.oid;


or

SELECT distinct pg_type.typname AS enum_type FROM pg_type JOIN pg_enum ON 
pg_enum.enumtypid = pg_type.oid;


Is this bad practice?

Solution

What @Jack said. Plus, if all you need is the list of registered values for an enum type, there are some Enum Support Functions to do that. Based on Jack's example:

SELECT enum_range(null::mood);

enum_range
--------------
{sad,ok,happy}


That's simpler and resilient against (unlikely) changes in future major Postgres versions that might break your query.

dbfiddle here

Code Snippets

SELECT enum_range(null::mood);

enum_range
--------------
{sad,ok,happy}

Context

StackExchange Database Administrators Q#198789, answer score: 5

Revisions (0)

No revisions yet.