Postgres ENUM Datentyp oder CHECK CONSTRAINT?

Basierend auf die Kommentare und Antworten hier, und einige rudimentäre Forschung, habe ich die folgende Zusammenfassung zu bieten für die Kommentare aus der Postgres-erati. Wirklich zu schätzen Ihre Eingabe.

Gibt es drei Möglichkeiten zum einschränken der Einträge in einer Postgres-Datenbank-Tabelle-Spalte. Betrachten Sie eine Tabelle zum speichern von "Farben", wo Sie wollen, nur 'rot', 'grün' oder 'blau' werden gültige Einträge.

1. Aufgelisteten Datentyp

   CREATE TYPE valid_colors AS ENUM ('red', 'green', 'blue');
   
   CREATE TABLE t (
       color VALID_COLORS
   );

   Vorteile sind, dass der Typ kann einmal definiert werden und dann wieder in so viele Tabellen wie erforderlich. Eine standard-Abfrage kann eine Liste aller Werte für einen ENUM-Typ, und kann verwendet werden, um Anwendung, form-widgets.

   SELECT  n.nspname AS enum_schema,  
           t.typname AS enum_name,  
           e.enumlabel AS enum_value
   FROM    pg_type t JOIN 
           pg_enum e ON t.oid = e.enumtypid JOIN 
           pg_catalog.pg_namespace n ON n.oid = t.typnamespace
   WHERE   t.typname = 'valid_colors'
   
    enum_schema | enum_name     | enum_value 
   -------------+---------------+------------
    public      | valid_colors  | red
    public      | valid_colors  | green
    public      | valid_colors  | blue

   Nachteile sind, der ENUM-Typ ist gespeichert in der system-Kataloge, so eine Abfrage wie oben erforderlich ist, um die Ansicht seiner definition. Diese Werte sind nicht sichtbar, wenn die Anzeige der Tabellendefinition. Und, da ein ENUM-Typ ist eigentlich ein Datentyp getrennt von den eingebauten NUMERISCHEN und TEXT-Datentypen, die regelmäßige numerische und string-Operatoren und-Funktionen arbeiten nicht auf. So kann man nicht eine Abfrage wie

   SELECT FROM t WHERE color LIKE 'bl%'; 

2. Check-Einschränkungen

   CREATE TABLE t (
       colors TEXT CHECK (colors IN ('red', 'green', 'blue'))
   );

   Zwei Vorteile sind, dass man, "was Sie sehen ist was Sie erhalten," das heißt, die gültigen Werte für die Spalte aufgezeichnet werden direkt in der Tabelle definiert, und zwei, alle nativen string-oder numerischen Operatoren arbeiten.

3. Fremdschlüssel

   CREATE TABLE valid_colors (
       id SERIAL PRIMARY KEY NOT NULL,
       color TEXT
   );
   
   INSERT INTO valid_colors (color) VALUES 
       ('red'),
       ('green'),
       ('blue');
   
   CREATE TABLE t (
       color_id INTEGER REFERENCES valid_colors (id)
   );

   Im wesentlichen das gleiche wie das erstellen eines ENUM-Typ, außer die native numerische oder string-Operatoren funktionieren, und einen nicht-Abfrage-system-Kataloge entdecken die gültigen Werte. Ein join ist erforderlich, um die Verknüpfung der color_id den gewünschten text Wert.

InformationsquelleAutor der Antwort punkish

PostgreSQL hat enum-Typenfunktioniert wie es sollte. Ich weiß nicht, ob ein enum ist "besser" als eine Einschränkung, dass Sie eben beide arbeiten.

InformationsquelleAutor der Antwort Frank Heikens

Ich hoffe jemand Glockenspiel mit eine gute Antwort aus dem PostgreSQL-Datenbank-Seite, warum man vielleicht besser als die anderen.

Vom software-Entwickler-Sicht habe ich eine leichte Präferenz für die Verwendung von check-Einschränkungen, da PostgreSQL enum erfordern eine Besetzung in der SQL ein update/insert, wie:

INSERT INTO table1 (colA, colB) VALUES('foo', 'bar'::myenum)

wo "myenum" ist der enum-Typ angegeben werden in PostgreSQL.

Diese sicherlich macht das SQL nicht tragbar (die nicht eine große Sache für die meisten Menschen), aber auch nur noch ein weiteres Sache, die Sie zu bewältigen haben, während die Entwicklung von Anwendungen, so dass ich lieber mit VARCHARs (oder andere typische "primitive") mit check-Einschränkungen.

Als seitliche Anmerkung, ich habe bemerkt, dass die MySQL-Enumerationen, die nicht benötigen diese Art der Besetzung, so ist dies etwas, insbesondere PostgreSQL in meiner Erfahrung.

InformationsquelleAutor der Antwort quux00