Summary: this tutorial shows you how to create PostgreSQL user-defined data type using CREATE DOMAIN and CREATE TYPE statements.
Besides the built-in data types, PostgreSQL allows you to create user-defined data types through the following statements:
CREATE DOMAINcreates a user-defined data type with constraints such asNOT NULL,CHECK, etc.CREATE TYPEis often used to create a composite type used in the stored procedures as the return data type.
PostgreSQL CREATE DOMAIN
In PostgreSQL, a domain is a data type with optional constraints e.g., NOT NULL, CHECK etc. A domain has a unique name within the schema scope.
Domains are useful for centralizing management of fields with the common constraints. For example, some tables might contain the text columns that require a CHECK constraint to ensure values are not null and also do not contain space.
The following statement create a table named mail_list:
1 2 3 4 5 6 7 8 9 10 | CREATE TABLE mail_list ( ID SERIAL PRIMARY KEY, first_name VARCHAR NOT NULL, last_name VARCHAR NOT NULL, email VARCHAR NOT NULL, CHECK ( first_name !~ '\s' AND last_name !~ '\s' ) ); |
In this table, both first_name and last_name columns are not null and should not contain spaces. To make it easier for management, you can create a contact_name domain as follows:
1 2 | CREATE DOMAIN contact_name AS VARCHAR NOT NULL CHECK (value !~ '\s'); |
And use the contact_name as the data type of the first_name and last_name columns:
1 2 3 4 5 6 | CREATE TABLE mail_list ( id serial PRIMARY KEY, first_name contact_name, last_name contact_name, email VARCHAR NOT NULL ); |
Let’s insert a new row into the mail_list table:
1 2 3 4 5 6 7 | INSERT INTO mail_list (first_name, last_name, email) VALUES ( 'Jame V', 'Doe', ); |
PostgreSQL issued the following error:
1 | ERROR: value for domain contact_name violates check constraint "contact_name_check" |
As you can see, it worked as expected.
To change or remove a domain, you use the ALTER DOMAIN or DROP DOMAIN respectively.
To view all domains in the current database, you use the \dD command as follows:
1 2 3 4 5 6 | test=#\dD List of domains Schema | Name | Type | Modifier | Check --------+--------------+-------------------+----------+----------------------------------- public | contact_name | character varying | not null | CHECK (VALUE::text !~ '\s'::text) (1 row) |
PostgreSQL CREATE TYPE
The CREATE TYPE statement allows you to create a composite type, which can be use as the return type of a function.
Suppose you want to have a function that returns several values: film_id, title, and release_year. The first step is to create a type e.g., film_summary as follows:
1 2 3 4 5 | CREATE TYPE film_summary AS ( film_id INT, title VARCHAR, release_year YEAR ); |
Second, use the film_summary data type as the return type of a function:
1 2 3 4 5 6 7 8 9 10 11 12 13 | CREATE OR REPLACE FUNCTION get_film_summary (f_id INT) RETURNS film_summary AS $$ SELECT film_id, title, release_year FROM film WHERE film_id = f_id ; $$ LANGUAGE SQL; |
Third, call the get_film_summary() function:
1 2 3 4 | SELECT * FROM get_film_summary (40); |
To change a type or remove a type, you use the ALTER TYPE or DROP TYPE statement respectively.
The command for listing all user-defined types in the current database is \dT or \dT+:
1 2 3 4 5 6 7 8 | dvdrental=# \dT List of data types Schema | Name | Description --------+--------------+------------- public | film_summary | public | mpaa_rating | public | year | (3 rows) |
In this tutorial, you have learned how to create PostgreSQL user-defined types using the CREATE DOMAIN and CREATE TYPE statements.