{"kind": "type", "slug": "enums", "name": "Enumerated types", "major": "18", "snapshot": {"casts": [], "facts": [{"label": "Object boundary", "value": "User-defined type family; not a finite list of user objects"}], "ranges": [], "aliases": [], "catalog": {}, "related": [], "release": {"ref": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "label": "18.6", "major": "18", "channel": "stable", "revision": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f", "source_files": {"doc/src/sgml/datatype.sgml": "86328daa77e20d81d222376ec0306d9841a17104e436c08d138eb763aefb3700", "src/include/catalog/pg_am.h": "3426799df799f32163fcc0765f43dbdb5e88e626acf31284fef9fc6014b69d8e", "src/include/catalog/pg_am.dat": "b3cb86b102a42fb0024cbd99779c9897afb40d12a71f5cc5a0e5aee7ff2f9969", "src/include/catalog/pg_cast.h": "de585c7df687d698e7d1d100dce8a794ac9da52414ea48169191019178c8f053", "src/include/catalog/pg_proc.h": "f7d59f07c5b95e2f3c7141762a0f576e8a3af7581ba54c27537f4d500472bea5", "src/include/catalog/pg_type.h": "8fb198749fd82b6c1818a3c18455f226f66116fcfeb3d4959c65ce3fda22802e", "src/include/catalog/pg_range.h": "45546d952b5181f989bd9234721897fc2b37005c8e49ce2ddc156f26974c3ca4", "src/include/catalog/pg_cast.dat": "97911281ca2c81917394ccb2ec13367e37c6801fd461d5e1a3d2f28b911b46b2", "src/include/catalog/pg_proc.dat": "1f934ce80d460159dda137714374a35f9896315ea2476709cbb4da13b38ef95a", "src/include/catalog/pg_type.dat": "5f5887b75677cba2d4a1a0cfeb355df5ed91f85d385fac88bd8d7c605b3578f9", "src/include/catalog/pg_opclass.h": "9c218537806c8ef0398302faaca7f91315dfc1198c48706cebffb479b68defaf", "src/include/catalog/pg_range.dat": "5c2271f8e89e9378d1887204e5785060b77405b5f0ff3e6c719250c9c1dcfcc4", "src/include/catalog/pg_operator.h": "621b18cfffe102413d79b746ce7a10f77fc60fdd097bac34c22b31070d3ee3c4", "src/include/catalog/pg_opfamily.h": "e1f5fc8aebd3042df847455bf66bf755f768c2aa8a9b03d22f7ec8563eba59c0", "src/include/catalog/pg_opclass.dat": "4ee7d3619a6aa106e1c0db55de903931c7d11b1c60ba053a022519c55f8ee668", "src/include/catalog/pg_operator.dat": "5d35b9b2ef5f9797cc815263927ef8a12367fedace53e828b4e8b320e2a7f703", "src/include/catalog/pg_opfamily.dat": "3b694879027b858f2ecf1fa1152b7e30c6e15a216ba99c2384291c30e9d52311"}, "manual_sha256": {"arrays.html": "0e1d5c5a7b4a949d4439995f44f74ad4264e050d16eb9ad2a850fbaee04032df", "domains.html": "82d486973ccc35d14276627a67ac76b434452f23f9ed4d8aa3fbb01fdf285fc2", "datatype.html": "e581f67c74e42006289638c8659e9b338adbfd7f068bd8bf2b3be3880fcd2254", "rowtypes.html": "74f99e029c3a9edfd3e18810ba3c8cb66d3aada91eb67b86476560871e6617e9", "rangetypes.html": "e4960abc7ce8e51d794f94f1e57b099d30b7f75dd7f2b23a960eef91f6291c33", "datatype-bit.html": "c49124aad561636c18080b9e09572fde7785a6b9eae86f5a33283f0fd6774c37", "datatype-oid.html": "8b3360302225f08ae72716ce1804596de95b568e5d31f788cb4597427b28cc38", "datatype-xml.html": "061fc2295b7b69be704f0a741b8decd10ad721059f871fb0243c669bb54d7822", "datatype-enum.html": "cab38e53222e30cc94b24de4b33634fd22d7c93f58ebcf35f646c9b8da78fae8", "datatype-json.html": "650162a05b660146b500a205d523374d00aee117a6a6240d30262be51b6e33e5", "datatype-uuid.html": "292b307b3223c6182bdba687e7f69e6dca9038430143ad79d56327f6ed4c3246", "datatype-money.html": "8b29c14b90679683ac5c472b59a0a7e2853f32cc7db43fab417b2697a6879964", "functions-info.html": "78ac80bf81da2e4f4a33f3b850faa86f0f58ec54a083d6c31da557907de0df87", "catalog-pg-type.html": "ea9c9313bab8e92a7da18ebc50c7ff1f908dd80999a7e0ca3bbc82ba626c58d4", "datatype-binary.html": "0c03ec47b76e37888a3da7f5dcfd340128a5d356642a85185cc9ebe17856a904", "datatype-pg-lsn.html": "339172f5107cc7eb7139bec911277647219e92a532cebd0f3da0a5dacf8195af", "datatype-pseudo.html": "c7cf0b8214304bd8702c95f2142671ae5f0d631f83717f9c07d9e1f2579961b4", "datatype-boolean.html": "b633f663e6c8276e7056287640c413aa79dbc15d5353cee0d41653d58d376956", "datatype-numeric.html": "b74619f7ac2f1ca9f85c48073df78c33dd30e464e61baba6008634534edcb500", "sql-createdomain.html": "e94f927196de1b0ea1ce35cd4603c1f6f544439adcc0edf1c4222358e832245b", "datatype-datetime.html": "e366275d8b13845bfa4ef0d68faa093e5f3ecb708a1ec870cbcbf7e425b85699", "datatype-character.html": "c73eebe413ea709a7792e4cfc3cfe9fb68cb496ebe83c3ebaa9d09f8c40d0f73", "datatype-geometric.html": "0eb3053cfe7d6b0a4b5592c5d7af8d97cae527e87f063c5793413bcba5c14cd9", "datatype-net-types.html": "96846e641cc37727ddcd8b8a1aa0dbe33cd9787760a2b4efb0dcb9403d5be63f", "datatype-textsearch.html": "4b15e05be8b49a71b28aea22f2c46d0dbf639a91e7b4fe129a4c737c6daad47c"}, "source_sha256": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}, "sources": [{"url": "/docs/18/datatype-enum.html", "path": "datatype-enum.html", "label": "PostgreSQL 18 English manual", "sha256": "cab38e53222e30cc94b24de4b33634fd22d7c93f58ebcf35f646c9b8da78fae8"}], "coverage": "documented type family or SQL syntax; not a catalog object", "sections": [], "operators": [], "signature": "Enumerated types", "description": ["Enumerated (enum) types are data types that comprise a static, ordered set of values. They are equivalent to the enum types supported in a number of programming languages. An example of an enum type might be the days of the week, or a set of status values for a piece of data."], "manual_html": "
\n
\n
\n
\n

8.7. Enumerated Types

\n
\n
\n
\n\n

Enumerated (enum) types are data types that comprise a static, ordered set of values. They are equivalent to the enum types supported in a number of programming languages. An example of an enum type might be the days of the week, or a set of status values for a piece of data.

\n
\n
\n
\n
\n

8.7.1. Declaration of Enumerated Types

\n
\n
\n
\n

Enum types are created using the CREATE TYPE command, for example:

\n
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');\n
\n

Once created, the enum type can be used in table and function definitions much like any other type:

\n
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');\nCREATE TABLE person (\n    name text,\n    current_mood mood\n);\nINSERT INTO person VALUES ('Moe', 'happy');\nSELECT * FROM person WHERE current_mood = 'happy';\n name | current_mood\n------+--------------\n Moe  | happy\n(1 row)\n
\n
\n
\n
\n
\n
\n

8.7.2. Ordering

\n
\n
\n
\n

The ordering of the values in an enum type is the order in which the values were listed when the type was created. All standard comparison operators and related aggregate functions are supported for enums. For example:

\n
INSERT INTO person VALUES ('Larry', 'sad');\nINSERT INTO person VALUES ('Curly', 'ok');\nSELECT * FROM person WHERE current_mood > 'sad';\n name  | current_mood\n-------+--------------\n Moe   | happy\n Curly | ok\n(2 rows)\n\nSELECT * FROM person WHERE current_mood > 'sad' ORDER BY current_mood;\n name  | current_mood\n-------+--------------\n Curly | ok\n Moe   | happy\n(2 rows)\n\nSELECT name\nFROM person\nWHERE current_mood = (SELECT MIN(current_mood) FROM person);\n name\n-------\n Larry\n(1 row)\n
\n
\n
\n
\n
\n
\n

8.7.3. Type Safety

\n
\n
\n
\n

Each enumerated data type is separate and cannot be compared with other enumerated types. See this example:

\n
CREATE TYPE happiness AS ENUM ('happy', 'very happy', 'ecstatic');\nCREATE TABLE holidays (\n    num_weeks integer,\n    happiness happiness\n);\nINSERT INTO holidays(num_weeks,happiness) VALUES (4, 'happy');\nINSERT INTO holidays(num_weeks,happiness) VALUES (6, 'very happy');\nINSERT INTO holidays(num_weeks,happiness) VALUES (8, 'ecstatic');\nINSERT INTO holidays(num_weeks,happiness) VALUES (2, 'sad');\nERROR:  invalid input value for enum happiness: \"sad\"\nSELECT person.name, holidays.num_weeks FROM person, holidays\n  WHERE person.current_mood = holidays.happiness;\nERROR:  operator does not exist: mood = happiness\n
\n

If you really need to do something like that, you can either write a custom operator or add explicit casts to your query:

\n
SELECT person.name, holidays.num_weeks FROM person, holidays\n  WHERE person.current_mood::text = holidays.happiness::text;\n name | num_weeks\n------+-----------\n Moe  |         4\n(1 row)\n\n
\n
\n
\n
\n
\n
\n

8.7.4. Implementation Details

\n
\n
\n
\n

Enum labels are case sensitive, so 'happy' is not the same as 'HAPPY'. White space in the labels is significant too.

\n

Although enum types are primarily intended for static sets of values, there is support for adding new values to an existing enum type, and for renaming values (see ALTER TYPE). Existing values cannot be removed from an enum type, nor can the sort ordering of such values be changed, short of dropping and re-creating the enum type.

\n

An enum value occupies four bytes on disk. The length of an enum value's textual label is limited by the NAMEDATALEN setting compiled into PostgreSQL; in standard builds this means at most 63 bytes.

\n

The translations from internal enum values to textual labels are kept in the system catalog pg_enum. Querying this catalog directly can be useful.

\n
\n
", "manual_path": "datatype-enum.html", "operator_classes": []}, "from": "17", "comparison": {"available": true, "changes": [], "prose_changed": true}}