Dropdown Options from Retool DB Enum column

[Andrew_Hogue]

Hi, I'm new to Retool.
I have a DB (Retool Database) with a column "Status".
Status has a field type of "Enum" with a number of options.

In my GUI, I have a table view pulling data from this DB data and I've set the Status column to be editable and I want the users to be able to select one of the pre-defined enum options from a drop down.

I have set the Status column type to be a Tag (Dropdown).

I can't figure out how to get the enum values defined in the DB to populate the Option List.

Is there a way to do this?

Thanks,
-- Andrew

[lamh_bytecode.io]

Hello,

You can run this query below using your Retool DB resource.

Quick note, "severity" is my enum column, so replace with your column name since the enum type will be dynamic - see second column in image. From here it should be easy to populate in the correct format for the options property

SELECT
  enum.enumsortorder,
  type.typname,
  enum.enumlabel AS value
FROM pg_enum AS enum
JOIN pg_type AS type
  ON (type.oid = enum.enumtypid)
WHERE type.typname like 'severity%'
GROUP BY enum.enumsortorder,
         enum.enumlabel,
         type.typname
ORDER BY enumsortorder

source: https://sadique.io/blog/2019/05/09/looking-up-enum-types-and-values-in-postgres/

[Andrew_Hogue]

Awesome! Thanks so much! Worked like a charm.

Just for posterity so others can find the solution.

• create Retool DB table with Columns that have Enum values
• Create a query like the above, for debugging purposes, I made a general query for all enum labels to ensure I can easily look for the type names I want

SELECT
  type.typname,
  enum.enumlabel AS value
FROM pg_enum AS enum
JOIN pg_type AS type
  ON (type.oid = enum.enumtypid)
GROUP BY enum.enumsortorder, 
         enum.enumlabel,
         type.typname 
ORDER BY enumsortorder

• Create another specific query to get just the status like:

SELECT
  type.typname,
  enum.enumlabel AS value
FROM pg_enum AS enum
JOIN pg_type AS type
  ON (type.oid = enum.enumtypid)
WHERE type.typname like 'Status%'
GROUP BY enum.enumsortorder, 
         enum.enumlabel,
         type.typname 
ORDER BY enumsortorder

*Then on the Table GUI element, make the Status column editable, set it to be a Tag (Dropdown) type and select the query for those labels.