Enum types

PostgreSQL enum types are custom data types that define a fixed, ordered set of values. They are useful for small and stable fields such as order status, ticket priority, workflow stage, or user role.

Enum values are validated by the database. After a column uses an enum type, it can only store values declared by that type, which helps avoid inconsistent spelling across clients and services.

When to use enums

Enum types are suitable when:

• The value set is small and rarely changes.
• Values do not need extra fields such as display name, sort weight, enabled status, or color.
• You want the database to reject invalid states directly.

If values change frequently, need localized display names, require extra attributes, or must be maintained by operators in an admin system, use a lookup table or relation table instead of an enum type.

Modeling option	Use case
Enum type	Small and stable values, such as pending, paid, and cancelled
check constraint	Simple column validation that does not need to be reused as a type
Lookup table / relation table	Values need extra attributes, permissions, ordering, enable/disable state, or operational maintenance

Create an enum type

You can create enum types in the console or with SQL statements. The CloudBase PostgreSQL management UI is currently consistent with the Supabase console, so it is suitable for directly managing enum types and enum values.

Console

1. Go to the CloudBase Console / PostgreSQL Database management page.
2. Select the target environment and open the database management UI.
3. Open the Enum Types management page.
4. Click Create Enum Type.
5. Enter the enum type name, such as order_status.
6. Add enum values one by one, such as pending, paid, shipped, and cancelled.
7. Confirm and submit the form. Wait until the enum type is created.

The console creates the corresponding PostgreSQL enum type from the values you provide, which is useful for team members who are less familiar with SQL.

SQL

Use CREATE TYPE ... AS ENUM in the DMC SQL window or SQL editor to create an enum type.

create type public.order_status as enum (
  'pending',
  'paid',
  'shipped',
  'cancelled'
);

Enum labels are case-sensitive. Prefer lowercase English identifiers and convert them to user-facing text in the application layer.

Use enums in tables

When creating a table, declare the column type as the enum type.

create table public.orders (
  id bigint generated by default as identity primary key,
  user_id varchar(64) not null default auth.uid(),
  status public.order_status not null default 'pending',
  title text not null,
  created_at timestamptz not null default now()
);

For an existing table, add an enum column with ALTER TABLE.

alter table public.orders
add column status public.order_status not null default 'pending';

Insert and query

Insert or update enum columns by writing the corresponding enum label.

insert into public.orders (title, status)
values ('Sample order', 'paid');

update public.orders
set status = 'shipped'
where id = 1;

Query enum columns like normal fields.

select id, title, status, created_at
from public.orders
where status = 'paid'
order by created_at desc;

Enum values have declaration order. order by status sorts by the order declared when the enum type was created, not alphabetically. If that order represents business priority, you can use it directly. If the sort order changes often, use a lookup table with an explicit sort weight.

Query through HTTP API

When accessing enum fields through the HTTP API, use normal field filters.

curl -X GET 'https://<envId>.api.tcloudbasegateway.com/v1/rdb/rest/orders?status=eq.paid' \
  -H 'Authorization: Bearer <access_token>'

Use Query data and the HTTP API reference as the source of truth for supported operators.

Manage enum values

Use ALTER TYPE ... ADD VALUE to append a new enum value.

alter type public.order_status add value if not exists 'refunded';

You can also specify the position of the new value in the enum order.

alter type public.order_status add value if not exists 'processing' after 'paid';

Use ALTER TYPE ... RENAME VALUE to rename an enum value.

alter type public.order_status rename value 'shipped' to 'delivered';

Notes:

• Adding enum values is a schema change. Put it in migration scripts and verify it in a staging environment first.
• PostgreSQL does not support directly deleting enum values. Deletion usually requires creating a new type, migrating column data, and replacing the old type.
• If ALTER TYPE ... ADD VALUE runs inside a transaction, the new value usually cannot be used by later statements until the transaction is committed.
• If tables, functions, views, or policies depend on an enum type, evaluate the dependency impact before renaming or replacing it.

List enum values

Use enum_range to list all values of an enum type.

select enum_range(null::public.order_status);

You can also query system catalogs to inspect multiple enum types.

select
  t.typname as enum_name,
  e.enumlabel as enum_value,
  e.enumsortorder as sort_order
from pg_type t
join pg_enum e on t.oid = e.enumtypid
join pg_namespace n on n.oid = t.typnamespace
where n.nspname = 'public'
order by t.typname, e.enumsortorder;

Modeling recommendations

• Use enums for stable states, not frequently changing business configuration.
• Appending enum values in production is safer than deleting them. Design state transitions with future changes in mind.
• Across clients, services, and migration scripts, reuse the same enum definitions to avoid unsynchronized states.
• If RLS policies depend on enum columns, review those policies after adding enum values so new states remain readable and cannot be modified without authorization.