CREATE DOMAIN: Data type abstraction

If a good programmer is facing a technical problem, he will always try to abstract the problem. In a procedural language such as C people will create typedefs, functions and so on – in object oriented programming people will simply create objects. But what about data type abstraction in SQL? Most people seem to miss this possibility completely.

CREATE DOMAIN – an approach

PostgreSQL (and most other relational databases I am aware of) provide an instruction called CREATE DOMAIN. The idea is to have a mechanism capable of providing data type abstraction.

Let us assume you are writing an application, which has to store information about a simple shop. You might have sales prices, discounts, taxes, and so on. In short: There will be many places in the data structure where you actually want to store a price or some sort of financial information.

Here is an example:

CREATE TABLE t_product (
      id               serial,
      name             text,
      normal_price     numeric(10, 4),
      discount_price   numeric(10, 4)
);

1

2

3

4

5

6

CREATE TABLE t_product (

id serial,

name text,

normal_price numeric(10, 4),

discount_price numeric(10, 4)

);

In this case we are storing two prices and we have to repeat the same thing over and over again. The danger is that if you store currency in ten different tables, there is always a danger that one of your programmers will have a different view of currency – this will definitely lead to inconsistencies in your models.

Data type abstraction

CREATE DOMAIN is exactly what we need here. The syntax is actually quite simple:

test=# h CREATE DOMAIN
Command: CREATE DOMAIN
Description: define a new domain

Syntax:
CREATE DOMAIN name [ AS ] data_type
       [ COLLATE collation ]
       [ DEFAULT expression ]
       [ constraint [ ... ] ]
where constraint is:
       [ CONSTRAINT constraint_name ]
       { NOT NULL | NULL | CHECK (expression) }

1

2

3

4

5

6

7

8

9

10

11

12

test=# h CREATE DOMAIN

Command: CREATE DOMAIN

Description: define a new domain

Syntax:

CREATE DOMAIN name [ AS ] data_type

[ COLLATE collation ]

[ DEFAULT expression ]

[ constraint [ ... ] ]

where constraint is:

[ CONSTRAINT constraint_name ]

{ NOT NULL | NULL | CHECK (expression) }

In our case we want to make sure that our column has exactly 10 digits (overall), it must not be NULL and the value inserted must be higher than zero. Here is the command to achieve exactly that:

test=# CREATE DOMAIN currency AS numeric(10, 4) NOT NULL CHECK (VALUE > 0);
CREATE DOMAIN

1 2	test=# CREATE DOMAIN currency AS numeric(10, 4) NOT NULL CHECK (VALUE > 0); CREATE DOMAIN

Once we have created the domain we can use it just like any other data type:

test=# CREATE TABLE t_product (id serial, name text, normal_price currency, discount_price currency);
CREATE TABLE

test=# d t_product
 Table "public.t_product"
    Column      |   Type   |   Modifiers
----------------+----------+--------------------------------------------------------
 id             | integer  | not null default nextval('t_product_id_seq'::regclass)
 name           | text     |
 normal_price   | currency |
 discount_price | currency |

1

2

3

4

5

6

7

8

9

10

11

test=# CREATE TABLE t_product (id serial, name text, normal_price currency, discount_price currency);

CREATE TABLE

test=# d t_product

Table "public.t_product"

Column | Type | Modifiers

----------------+----------+--------------------------------------------------------

id | integer | not null default nextval('t_product_id_seq'::regclass)

name | text |

normal_price | currency |

discount_price | currency |

The main advantage is that those checks we have assigned to the domain will now be used by all columns using the data type. It is a consistent way to store data.

If you want to modify things later on you can still use ALTER DOMAIN so you are not stuck with a mistake.

Visit us on facebook: www.fb.com/cybertec.postgresql