postgresql - 在 Postgresql 中，有没有办法将列的值限制为枚举？

Question

在 postgresql 中，我可以创建一个表来记录人们拥有的车辆类型。

CREATE TABLE IF NOT EXISTS person_vehicle_type
( id SERIAL NOT NULL PRIMARY KEY
, name TEXT NOT NULL
, vehicle_type TEXT
);

这个表可能有这样的值

 id | name    | vehicle_type
----+---------+---------
  1 | Joe     | sedan
  2 | Sue     | truck
  3 | Larry   | motorcycle
  4 | Mary    | sedan
  5 | John    | truck
  6 | Crystal | motorcycle
  7 | Matt    | sedan

car_type 列中的值仅限于集合 {sedan, truck, motorcycle}。

有没有办法在 postgresql 中形式化此限制？

Answer 1

我个人会使用外键和查找表。

无论如何你都可以使用枚举。我建议阅读文章 PostgreSQL Domain Integrity In Depth :

A few RDBMSes (PostgreSQL and MySQL) have a special enum type thatensures a variable or column must be one of a certain list of values.This is also enforcible with custom domains.

However the problem is technically best thought of as referentialintegrity rather than domain integrity, and usually best enforced withforeign keys and a reference table. Putting values in a regularreference table rather than storing them in the schema treats thosevalues as first-class data. Modifying the set of possible values canthen be performed with DML (data manipulation language) rather thanDDL (data definition language)....

However when the possible enumerated values are very unlikely tochange, then using the enum type provides a few minor advantages.

1. Enums values have human-readable names but internally they are simple integers. They don’t take much storage space. To compete withthis efficiency using a reference table would require using anartificial integer key, rather than a natural primary key of the valuedescription. Even then the enum does not require any foreign keyvalidation or join query overhead.

2. Enums and domains are enforced everywhere, even in stored procedure arguments, whereas lookup table values are not. Referencetable enumerations are enforced with foreign keys, which apply only torows in a table.

3. The enum type defines an automatic (but customizable) order relation:

 CREATE TYPE log_level AS ENUM ('notice', 'warning', 'error', 'severe');
 CREATE TABLE log(i SERIAL, level log_level);
 INSERT INTO log(level) 
 VALUES ('notice'::log_level), ('error'::log_level), ('severe'::log_level);

 SELECT * FROM log WHERE level >= 'warning';

DBFiddle Demo

Drawback:

Unlike a restriction of values enforced by foreign key, there is no way to delete a value from an existing enum type. The only workarounds are messing with system tables or renaming the enum, recreating it with the desired values, then altering tables to use the replacement enum. Not pretty.