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.
Table of Contents
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:
|
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.
CREATE DOMAIN is exactly what we need here. The syntax is actually quite simple:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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:
|
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:
|
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.
In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Facebook or LinkedIn.
You need to load content from reCAPTCHA to submit the form. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from Facebook. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from X. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More Information
Leave a Reply