Is there a way to define a PostgreSQL column as an Enum?-CodePudding

In a PostgreSQL DB, imagine you want to add a column called vehicle_type and you only want one of the following 3 entries to ever possibly appear: car, truck, or motorcycle. I thought there would be a way to define this somehow but try a I might, and reading lots of articles, I couldn't figure out how to do it.

Note: I'm working with the DBeaver client but also can run any SQL commands too.

Is there a way?

CodePudding user response：

CREATE TYPE vehicle_type AS ENUM ('car', 'truck', 'motorcycle');
-- add column with default
ALTER TABLE tbl ADD COLUMN v_type vehicle_type NOT NULL DEFAULT 'car';

Read the manual here.

Since " you only want one of the following 3 entries to ever possibly appear", also make it NOT NULL.

An enum occupies 4 bytes. To keep the disk footprint to a minimum, you could alternatively use a "char" field instead which occupies 1 byte;

ALTER TABLE tbl
  ADD column v_type2 "char" NOT NULL DEFAULT 'c'
, ADD CONSTRAINT v_type_allowed CHECK (v_type2 IN ('c', 't', 'm'));

Upsides: small, fast.
Downsides: Postgres-specific, less readable.

db<>fiddle here

See:

How exactly does the one-byte "char" type work in PostgreSQL?