patternsqlMinor
Is it a bad practice to query pg_type for enums on a regular basis?
Viewed 0 times
basispracticequerypg_typebadregularforenums
Problem
In Postgres, I'm thinking of query
or
Is this bad practice?
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:
That's simpler and resilient against (unlikely) changes in future major Postgres versions that might break your query.
dbfiddle here
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.