3

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?

  • The question is why would you do this? – dezso Feb 26 '18 at 10:11
4

Is this bad practice?

No, not at all, modifying the catalog is bad practice but there is no reason not to query it.

But in your query…

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

…there isn't any need to join pg_enum and use distinct:

create type mood as enum ('sad', 'ok', 'happy');
select * from pg_enum;
enumtypid | enumsortorder | enumlabel
:-------- | :------------ | :--------
53430     | 1             | sad      
53430     | 2             | ok       
53430     | 3             | happy    
select typname from pg_type where typtype='e';
| typname |
| :------ |
| mood    |

dbfiddle here

5

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.