I'm looking at a code base that I have inherited. I'm new to Postgres (but have an Oracle background), and I think this in one of the queries is an example of an enum. Can someone confirm if I'm correct in this thinking, and if not then what this is, and how I find out more about it.

SELECT fp.id AS fund_part_id, fp.fund_id, fp.principle_amount, fp.detail
FROM finance.fund_part fp
WHERE fp.status = 'live'::finance.fund_part_status;

I can see if the pg_enum catalog (table) that there are a series of records that belong to one enumtypid oid. Some of the entries in the enumlabel name column are ['live', 'for sale', 'pre-agrement']


Yes, it looks like finance.fund_part_status is an enum type.

Assuming you're using the psql command-line interpreter, try this command:

\dT+ finance.fund_part_status;

You can find out for sure with this query to the system catalog table pg_type:

SELECT typtype
FROM   pg_type
WHERE  oid = 'finance.fund_part_status'::regtype

I quote the manual about pg_type:

typtype is b for a base type, c for a composite type (e.g., a table's row type), d for a domain, e for an enum type, p for a pseudo-type, or r for a range type. See also typrelid and typbasetype.

Bold emphasis mine.
More about the cast to regtype in the chapter about object identifier types.

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.