Last updated: 2026-05-20

Enum Columns

Declare a column as an enum and Jam SQL Studio adds a dedicated enum filter operator backed by a dropdown of known values — sourced from MySQL ENUM / Postgres pg_enum, a column-level CHECK constraint, or a sampled SELECT DISTINCT capped at 200 values. Works on MSSQL, PostgreSQL, MySQL, Oracle, and SQLite, with the same UX on every engine.

What is an enum column?

An enum column is any column whose values come from a small, finite set the user knows about — status, kind, category, role, severity, tier. The database may or may not enforce that constraint. Once you declare the column as an enum in Jam SQL Studio, the filter chip on that column gets an enum operator — selected by default — whose value input is a dropdown of the known values instead of a plain text box.

Declarations are per-database and stored locally (alongside loose foreign keys and JSON column declarations). They don't change the schema and they don't affect anyone else who connects to the same database.

Where the dropdown values come from

Jam SQL Studio tries three sources in priority order:

1. Native enum type. MySQL ENUM(...) / SET(...) column types and PostgreSQL user-defined pg_enum types. Read once from information_schema / pg_catalog.
2. Column-level CHECK constraint. CHECK (status IN ('draft','published','archived')) and its ANY(ARRAY[...]) variant on Postgres. Read from sys.check_constraints (MSSQL), pg_get_constraintdef (PG), INFORMATION_SCHEMA.CHECK_CONSTRAINTS (MySQL 8.0.16+), ALL_CONSTRAINTS (Oracle), or sqlite_master.sql (SQLite).
3. Sampled distinct values. SELECT col FROM tbl GROUP BY col capped at 200 distinct values and 100,000 scanned rows. If the column has more than 200 distinct values, the picker shows a warning — that's a strong signal the column isn't actually enum-shaped and you probably want a free-text filter instead.

The orchestrator hard-fails any extraction that runs longer than 5 seconds, so you'll never see a frozen UI when sampling a huge table.

Declaring an enum column

Three entry points open the same declaration dialog:

• Header glyph or right-click menu in the result grid. Native enum columns always show a gray ▾; columns whose name looks categorical (status, type, role, …) show a gray ▾?. Click either to open the dialog. Right-clicking the header of any short-text or integer column offers Declare as enum (or Configure enum column for native types) — you don't need a categorical-looking name to use it.
• MetaInfo manager. Open it from the Table Explorer toolbar or right-click a database in the Object Explorer and pick Manage meta info…. The Enum columns section lists what's declared, with a Details button and a Remove action.
• The enum filter operator. On an eligible column the operator dropdown lists enum at the bottom; selecting it opens the values picker right away (and you can declare the column from there to keep it).

The dialog shows a preview of the values it will use (native / check / sampled) and an optional description field, then writes the declaration to MetaInfo on confirm. Once declared, the enum operator becomes the default selection for that column's filter chip on every surface.

Hiding a false-positive hint

The gray ▾? glyph appears on columns whose name looks categorical — status, type, role, and similar. Occasionally it fires on a column that isn't actually an enum. You can permanently silence the hint without declaring the column:

1. Click the gray ▾? glyph to open the declaration dialog.
2. Choose “Mark <column> as not an enum” (the smaller link below the main declaration action).
3. The ▾? glyph disappears in both the Table Explorer and Query Editor result grids.

Filtering still works. The enum operator remains available in the filter chip's operator dropdown — the dismissal only hides the visual hint on the column header. You can still declare the column as an enum at any time using the right-click context menu or the enum operator in the filter chip.

All enum dialogs have a documentation link button in the title bar that opens this page.

Restoring a hidden hint

If you change your mind, open the MetaInfo manager (Table Explorer toolbar or right-click a database in the Object Explorer and pick Manage meta info…). In the Enum columns section, look for the Hidden enum hints subsection, which lists every dismissed column. Click Restore hint to bring the ▾? glyph back.

Filtering with the picker

Enum filtering is its own operator — = and != stay free-text so you can always type an ad-hoc value:

• Pick the enum operator from the chip's operator dropdown. On declared or native enum columns it's already selected by default; on eligible columns it sits at the bottom of the list.
• Click the trigger to open the popover with a search input and the list of values.
• Type to filter; click to select. Selection commits the filter immediately (no extra Apply step).
• The list is a hint, not a constraint. Type a value that isn't in the list and a Use “your value” option appears — pick it to filter by that value anyway.
• The footer has a Refresh values from data button that re-runs the extraction. Useful when the underlying data gains a new value the cache hasn't seen yet.

The picker is cache-first: the first time you open it, Jam SQL Studio reads the cached values from MetaInfo and renders the list instantly. If no cache entry exists yet (just-declared column), the popover shows a brief loading spinner while the extraction runs in the background.

Filtering by multiple values at once

The default single-click UX stays the same — pick one value, filter commits, popover closes. When you need to filter by several values at once, use multi-select mode:

1. Open the enum picker and click the Select multiple… link at the bottom of the popover.
2. The list switches to checkbox mode. Tick the values you want, or use Select all / Select none to toggle everything in the current search.
3. Click Apply (n) (where n is the number of checked values) to commit the filter. Closing the popover without clicking Apply discards the pending selection.

A multi-value filter emits column IN ('v1', 'v2', …) and the compact filter chip reads "in N values" (for example, "in 3 values"). Multi-value filtering works on all five engines (MSSQL, PostgreSQL, MySQL, Oracle, SQLite). The = / != free-text operators are always available alongside the enum picker.

Reviewing the cached values

Open the Enum values details dialog two ways:

• Click the blue ▾ glyph on the declared column's header in the result grid.
• Open the MetaInfo manager and click Details on the enum column row.

The dialog lists the cached values as small chips, shows the source (native / check / sampled), the time of the last scan, and a Refresh from data action. From the MetaInfo manager, the dialog also exposes Remove declaration so you can undo the declaration in one click.

The 200-value cap (and the truncated banner)

Sampled extractions stop after 200 distinct values. If your column has more, the picker shows an amber banner:

More than 200 distinct values — this column probably isn't an enum. Use a free-text filter instead.

That's intentional. Two hundred is more than enough for any genuinely categorical field, and it caps the cost of the lookup so the dropdown is always usable. If the column is honestly a free-text field that happens to share many repeated values (city names, free-form tags), undeclare it and use the regular text filter.

MetaInfo: where declarations live, and how to share them

Enum declarations are persisted in the same per-database MetaInfo file as loose foreign keys and JSON column declarations:

• macOS: ~/Library/Application Support/jam-sql-studio/metainfo/<connection>/<database>.json
• Windows: %APPDATA%\jam-sql-studio\metainfo\<connection>\<database>.json
• Linux: ~/.config/jam-sql-studio/metainfo/<connection>/<database>.json

The same file is read by every workspace tab that needs MetaInfo (Table Explorer, Query Editor, Schema Overview, etc.). Declare an enum once and every surface picks it up.

Exporting and sharing

Open the MetaInfo manager (Table Explorer toolbar or Object Explorer right-click) and use Export to write the full DatabaseMetaInfo document as JSON. Send it to teammates, commit it next to your database migrations, or attach it to a ticket.

One thing to flag: unlike the JSON structure cache (which stores path skeletons only), the enum value cache stores actual data values. That's necessary for the picker to render without round-tripping, but it does mean an exported MetaInfo file contains the distinct values for every declared enum column. Strip the enumValueCache array if your exported declarations should not include any production data values.

Importing

The MetaInfo importer offers two modes:

• Merge (recommended for sharing) — appends every imported enum declaration whose (schema, table, column) isn't already declared locally. Local declarations always win on conflict. Imported enumValueCache entries replace local entries per column — freshest sample wins.
• Replace — overwrites the entire local MetaInfo file with the imported document.

Engine matrix

Removing a declaration

Two ways:

• From the details dialog. Open the dialog from the blue ▾ glyph or from the MetaInfo manager, and click Remove declaration.
• From the MetaInfo manager. Find the enum column row and click the trash icon.

After removal, the column drops back to enum-eligible (if its type/name still qualify) or loses the enum operator entirely — = / != free-text filtering is unaffected throughout. The cached values are left in place — re-declaring uses them without another scan.

Enums on JSON properties

If a column stores JSON and one of its properties always comes from a known set of values, you can declare that specific property as an enum without declaring anything at the column level. The result is a dedicated value picker inside the JSON filter — the same searchable dropdown you get for column-level enums, but scoped to a single path inside the JSON.

How to declare a JSON property as an enum

Three entry points, in increasing breadth:

• Filter chip on a JSON column. Select the json operator, then pick one of the three enum sub-operators from the second dropdown:
  • Property enum — filters on a scalar path (e.g. $.status). A single value commit, emitting = 'value'.
  • Any element enum — filters on an array path (e.g. $.tags[*]). At least one array element must match.
  • All elements enum — same array path, but every element must match.
  Enter a path in the path field. If that path isn't declared yet, a "Declare … as enum" prompt appears. Click it and confirm in the dialog to declare the path and load the picker immediately.
• JSON Peek popover. Open the peek popover from the eye-icon next to the path field. Each discovered path shows an inline glyph next to its property name: a blue ▾ on paths that are already declared (clicking opens the values inspector) and a muted ▾? on paths whose name suggests an enum — status, kind, type, priority, severity, and similar. Clicking ▾? opens the declaration dialog pre-filled with that path and the detected kind. The peek footer also shows a small gear icon next to "Filtering on the whole column" that opens the JSON structure details dialog.
• JSON structure details dialog. Open it from the eye-icon's gear or from any "Details" affordance on a declared JSON column. The shape preview shows the same blue ▾ / muted ▾? glyphs next to property names. To declare a property whose name does not hint at an enum (for example a colour code or a custom internal id), click the "Declare property as enum…" link at the bottom of the dialog — an amber banner appears, and clicking any property in the preview opens the declaration dialog pre-filled for that path.

All three entry points open the same declaration dialog. When declaring a JSON property, the dialog swaps to JSON-property-aware copy — title "This JSON property looks like an enum" (or "Declare JSON property as enum" when starting from a non-hinted path), description references the property/array path, and the "Mark column as not an enum" affordance is hidden (it applies only to column-level enums). Values are always sourced from a SELECT DISTINCT at the JSON path — native and CHECK-constraint sources don't apply to JSON properties.

Path sampling works correctly even when the host JSON column has a plain text type (PostgreSQL text/varchar, MSSQL nvarchar, MySQL text, Oracle VARCHAR2, SQLite TEXT): non-JSON rows are skipped at sample time, so a few malformed rows can't break the picker.

Filtering with a JSON-path enum

Once a JSON property is declared as an enum, the filter chip for that path renders the same searchable dropdown as a column-level enum picker:

• For property enum (scalar path), single-click commits the filter as JSON_VALUE(col, '$.p') = 'value' (or the engine-equivalent).
• For any / all element enum (array path), the picker opens in multi-select mode — tick the values you want and click Apply. The filter commits as an IN check against the array elements.
• Typing a value that isn't in the list shows a Use “your value” option, so an ad-hoc value is always reachable.

JSON-path enum filtering works on all five engines (MSSQL, PostgreSQL, MySQL, Oracle, SQLite).

Managing JSON-path enum declarations

Each path-scoped declaration appears as its own row in the Enum columns section of the MetaInfo manager, displayed as schema.table.column · $.path (for example, dbo.Orders.metadata · $.status). The Details and Remove actions work exactly as they do for column-level enums.

Removing a JSON-path enum declaration automatically clears any open filter conditions that were using that path's enum sub-operator, and shows a brief notification confirming how many filters were removed. The cached values for that path are also cleaned up. Declarations export and import alongside the rest of MetaInfo.

When NOT to declare a column as an enum

• Free-text fields with many repeated values (city names, tags, comments). The picker will show a truncated banner and the dropdown won't be useful.
• Columns that change values frequently. The cached values get stale — you can refresh manually, but the picker is most useful when the value set is stable.
• Multi-value columns. A column storing a comma-separated list of statuses isn't an enum (each row has many values). Use a JSON column or a many-to-many table instead.

Related

• Loose Foreign Keys — the first MetaInfo concern, similar lifecycle.
• JSON Columns — JSON filter operators, path discovery, and the peek popover where you can declare a path as an enum.
• Table Explorer — primary surface for filter chips.
• Query Editor — results-grid column header shows the same enum glyph trio and right-click menu as the Table Explorer.