CYBERTEC PostgreSQL Logo

CREATE DOMAIN: Data type abstraction

10.2013 / Category: / Tags: |

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:

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:

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:

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

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.

In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Facebook or LinkedIn.

Leave a Reply

Your email address will not be published. Required fields are marked *

CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf
Austria

+43 (0) 2622 93022-0
office@cybertec.at

Get the newest PostgreSQL Info & Tools


    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    ©
    2024
    CYBERTEC PostgreSQL International GmbH
    phone-handsetmagnifiercrosscross-circle
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram