PostgreSQL – ENUM Data Type

ByAdmin

The enum data type is used to define a user-defined enumeration type. An enumeration type consists of a static, ordered set of values, each of which can represent a specific category or state. Enumerations provide a way to ensure that only valid values are stored in a column, enhancing data integrity. Here are some key points about the enum data type:

  1. Creating Enum Types: You can create an enumeration type using the CREATE TYPE statement. This statement defines the set of allowed values for the enum and assigns each value a label.sqlCopy codeCREATE TYPE status AS ENUM ('active', 'inactive', 'pending');
  2. Enum Functions: PostgreSQL provides various functions for working with enum values, such as extracting labels, comparing values, and checking whether a value is part of an enum type.
  3. Casting and Converting: Enum values can be cast to strings and vice versa, but there is no implicit casting between enums and other data types.

Here’s an example of using the enum data type:

CREATE TYPE day_of_week AS ENUM ('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday'); 
CREATE TABLE weekly_schedule ( schedule_id serial PRIMARY KEY, day day_of_week, task varchar(200) ); 
INSERT INTO weekly_schedule (day, task) VALUES ('Monday', 'Team meeting'), ('Wednesday', 'Client presentation'); 
SELECT * FROM weekly_schedule WHERE day = 'Monday';

In this example, an enum type named day_of_week is defined to represent days of the week. A day column of this enum type is used in the weekly_schedule table to store scheduled tasks for specific days. Enums are useful when you want to enforce a predefined set of values and improve data consistency.

Alter Enum Types: You can use the ALTER TYPE statement to add new values to an existing enum type.

ALTER TYPE status ADD VALUE 'resigned';

Similar Posts