PostgreSQL: What is a Domain?

Leave a reply

After running PgAdmin III and opening up a database schema, you may notice an item listed as ‘Domain’.  Think of domains as a custom data type that can be put together using data types already found in PostgreSQL along with the ability to set rules or constraints on this data type.

Domains are used to define a commonly used data object found through out a database. For example – A phone number object can be defined as a domain and reused through out a schema. You can define this phone number domain as 13 characters and include a constraint to match a regular expression that checks for a specific format. Once the domain has been defined, you can reuse the same domain in different tables to define the data type used for a specific column.

Launch the Bash shell and open up a sandbox database using psql.

I typed ‘$ psql -d <database_name>’

Next I created a domain for a phone number by typing the following:

CREATE DOMAIN phone_number as CHAR(13)
CHECK( VALUE ~ '((\(\d{3}\) ?)|(\d{3}-))?\d{3}-\d{4}' );

Now, let’s add this domain to a new table.

CREATE TABLE customers 
(
	id Serial Unique Not Null,
	name varchar,
	phone phone_number
);

We can now test to see if the constraints are enforced for the phone number.

INSERT INTO CUSTOMERS(name, phone) VALUES('Alex', '(123)123-1234');

INSERT INTO CUSTOMERS(name, phone) VALUES('Bob', '3213231313');

The second insert statement for bob should fail because it does not match the regular expression check for the phone_number data type or domain.  The convenience of creating the phone_number data type is that we can now reuse the same data type in other tables that may need to store phone numbers, giving us a central point of maintenance for adding or removing constraints and checks for this type of data.

If you would like more information on this topic, please check out the following links:

Leave a Reply