How to create custom type in postgresql

PostgresqlHow-ToBeginner · 3 min read

How to Create Custom Types in PostgreSQL: Syntax and Examples

In PostgreSQL, you create a custom type using the CREATE TYPE statement. You can define composite types with multiple fields or enumerated types with a list of values to suit your data needs.

📐

Syntax

The CREATE TYPE command lets you define a new data type. You can create a composite type with named fields or an enum type with a fixed set of values.

CREATE TYPE type_name AS (field_name data_type, ...); defines a composite type.
CREATE TYPE type_name AS ENUM ('value1', 'value2', ...); defines an enum type.

Composite types group multiple fields like a small table row. Enum types restrict values to a list.

sql

CREATE TYPE type_name AS (
  field_name1 data_type1,
  field_name2 data_type2
);

-- Or for enum type:
CREATE TYPE type_name AS ENUM ('value1', 'value2', 'value3');

💻

Example

This example shows how to create a composite type address and an enum type mood. Then it uses them in a table.

sql

CREATE TYPE address AS (
  street TEXT,
  city TEXT,
  zip_code TEXT
);

CREATE TYPE mood AS ENUM ('happy', 'sad', 'neutral');

CREATE TABLE person (
  id SERIAL PRIMARY KEY,
  name TEXT,
  home_address address,
  current_mood mood
);

INSERT INTO person (name, home_address, current_mood) VALUES
('Alice', ROW('123 Main St', 'Springfield', '12345'), 'happy');

SELECT name, home_address.city, current_mood FROM person;

Output

name | city | current_mood -------+------------+-------------- Alice | Springfield | happy (1 row)

⚠️

Common Pitfalls

Common mistakes include:

Trying to use composite types without ROW() when inserting data.
Forgetting to use single quotes around enum values.
Attempting to alter enum types by dropping values (PostgreSQL only allows adding new enum values).

Always use ROW() to insert composite type values and single quotes for enum literals.

sql

/* Wrong: missing ROW() for composite type */
INSERT INTO person (name, home_address, current_mood) VALUES
('Bob', ('456 Elm St', 'Shelbyville', '54321'), 'sad');

/* Right: use ROW() */
INSERT INTO person (name, home_address, current_mood) VALUES
('Bob', ROW('456 Elm St', 'Shelbyville', '54321'), 'sad');

📊

Quick Reference

Command	Description
CREATE TYPE type_name AS (field_name data_type, ...);	Create a composite type with multiple fields.
CREATE TYPE type_name AS ENUM ('val1', 'val2', ...);	Create an enum type with fixed values.
Use ROW(...) to insert composite type values.	Wrap composite values with ROW() when inserting.
ALTER TYPE type_name ADD VALUE 'new_val';	Add a new value to an enum type (cannot remove).

✅

Key Takeaways

Use CREATE TYPE to define custom composite or enum types in PostgreSQL.

Composite types group multiple fields; enum types restrict values to a list.

Use ROW() to insert composite type values correctly.

Enum values must be single-quoted strings.

You can add but not remove values from enum types after creation.