Domain is a data type with optional constraints, and as with other database objects it should have a unique name within the schema scope.

The first use case of domains is to use it for common patterns. For example, a text type that does not allow null values and does not contain spaces is a common pattern. In the web car portal, the first_name and the last_name columns in the account table are not null. They should also not contain spaces, and are defined as follows:

CREATE TABLE account (
...
..first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
..CHECK(first_name !~ 's' AND last_name !~ 's'),
...
);

One can replace the text data type and the constraints by creating a domain and using it for defining the first_name and the last_name data type, as follows:

CREATE DOMAIN text_without_space_and_null AS TEXT NOT NULL CHECK (value !~ 's');

In order to test the text_without_space_and_null domain, let us use it in a table definition, and execute several INSERT statements, as follows:

CREATE TABLE test_domain (
  test_att text_without_space_and_null
);
INSERT INTO test_domain values ('hello');
INSERT INTO test_domain values ('hello  with space');
-- This error is raised: ERROR: value for domain text_without_space_and_null violates check constraint "text_without_space_and_null_check"

INSERT INTO test_domain values (NULL);
-- This error is raised: ERROR:  domain text_without_space_and_null does not allow null values

Another good use case for creating domains is to create distinct identifiers across several tables, since some people tend to use numbers instead of names to retrieve information.

One can do that by creating a sequence and wrapping it with a domain:

CREATE SEQUENCE global_id_seq;
CREATE DOMAIN global_id INT DEFAULT NEXTVAL('global_id_seq') NOT NULL;

Finally, one can alter the domain using the ALTER DOMAIN command. If a new constraint is added to the domain, it will cause all the attributes using this domain to be validated against the new constraint. One can control this by suppressing the constraint validation on old values and then cleaning up the tables individually. For example, let us assume we would like to have a constraint on the text length of the text_without_space_and_null domain; this can be done as follows:

ALTER DOMAIN text_without_space_and_null ADD CONSTRAINT text_without_space_and_null_length_chk check (length(value)<=15);

The preceding SQL statement will fail due to data violation if an attribute is using this domain, and the attribute value length is more than 15 characters. So, to force the newly created data to adhere to the domain constraints and to leave the old data without validation, one can still create it as follows:

ALTER DOMAIN text_without_space_and_null ADD CONSTRAINT text_without_space_and_null_length_chk check (length(value)<=15) NOT VALID;

After data clean up, one can also validate the constraint for old data by the ALTER DOMAIN ... VALIDATE CONSTRAINT option.

Finally, the dD+ Psql meta command can be used for describing the domain, as follows:

dD+ text_without_space_and_null

Composite data types are very useful in creating functions, especially when the return type is a row of several values. For example, let us assume that we would like to have a function that returns the seller_id, seller_name, number of advertisements, and the total rank for a certain customer account. The first step is to create a type, as follows:

CREATE TYPE seller_information AS (seller_id INT, seller_name TEXT, number_of_advertisements BIGINT, total_rank float);

Then we can use the newly created data type as the return type of the function, as follows:

CREATE OR REPLACE FUNCTION seller_information (account_id INT ) RETURNS seller_information AS
$$
  SELECT
    seller_id,
    first_name || last_name as seller_name,
    count(*),
    sum(rank)::float/count(*)
  FROM
    account INNER JOIN
    seller_account ON (account.account_id = seller_account.account_id) LEFT JOIN
    advertisement ON (advertisement.seller_account_id = seller_account.seller_account_id)LEFT JOIN
    advertisement_rating ON (advertisement.advertisement_id = advertisement_rating.advertisement_id)
  WHERE
    account.account_id = $1
  GROUP BY
    seller_id,
    first_name,
    last_name
$$
LANGUAGE SQL;

CREATE TYPE could be also used to define enums; an enum type is a special data type that enables an attribute to be assigned one of the predefined constants. The usage of the enum data types reduces the number of joins needed to create some queries; thus, it makes the SQL code more compact and easier to understand. In the advertisement_rating table, we have a column with the rank name, which is defined as follows:

CREATE TABLE advertisement_rating (
  ...
rank INT NOT NULL,
CHECK (rank IN (1,2,3,4,5))
  ...
);

In the preceding example, the given code is not semantically clear. For example, some people might consider 1 as the highest rank, while others might consider 5 as the highest rank. To solve this, one could use the lookup table, as follows:

CREATE TABLE rank (
  rank_id SERIAL PRIMARY KEY,
  rank_name TEXT NOT NULL
);
INSERT INTO rank VALUES (1, 'poor') , (2, 'fair'), (3, 'good') , (4, 'very good') ,( 5, 'excellent');

CREATE TABLE advertisement_rating (
  ...
rank INT NOT NULL REFERENCES rank(rank_id),
);

In this preceding approach, the user can explicitly see the rank table entries. Moreover, the rank table entries can be changed to reflect new business needs, such as to make ranking from 1 to 10. Additionally, in this approach, changing the rank table entries will not lock the "advertisement rating" table, since the ALTER TABLE command will not be needed to change the check constraint CHECK (rank IN (1, 2, 3, 4, 5)). The disadvantage of this approach lies in retrieving the information of a certain table that is linked to several lookup tables, since the tables need to be joined together. In our example, we need to join advertisement_rating and the rank table to get the semantic of rank_id in the advertisement_rating table, as follows:

SELECT
  advertisement_rating_id,
  ...,
  rank_id,
  rank_name
FROM
  advertisement_rating INNER JOIN
  rank ON (advertisement_rating.rank_id = rnk.rank_id);

Another approach to model the rank is to use the enum data types, as follows:

CREATE TYPE rank AS ENUM ('poor', 'fair', 'good', 'very good', 'excellent');

The psql dT meta command is used to describe the enum data type. One could also use the function enum_range, as follows:

car_portal=# SELECT enum_range(null::rank);
               enum_range
----------------------------------------
 {poor,fair,good,"very good",excellent}
(1 row)

The enum data type order is determined by the order of the values in the enum at the time of its creation. So in our example, poor always comes first, as shown in the following example:

CREATE TABLE rank_type_test (
  id SERIAL PRIMARY KEY,
  rank rank
);
INSERT into rank_type_test(rank) VALUES ('poor') , ('fair'), ('very good') ,( 'excellent'), ('good'), ('poor') ;

SELECT * FROM rank_type_test ORDER BY rank ASC;
 id |   rank
----+-----------
 17 | poor
 22 | poor
 18 | fair
 21 | good
 19 | very good
 20 | excellent
(6 rows)

The enum PostgreSQL data types are type safe, and the different enum data types cannot be compared with each other. Moreover, the enum data types can be altered, and new values can be added. Unfortunately, it is not possible to take out the old values.