Creating Your Own Data Types
PostgreSQL allows you to create your own data types. This is not unique among relational database systems, but PostgreSQL's depth of support is unique. In other RDBMSs, you can define one data type in terms of another (predefined) data type. For example, you might create a new numeric data type to hold an employee's age, with valid values between 18 and 100. This is still a numeric data type—you must define the new type as a subset of an existing type. PostgreSQL calls such a “refined” data type a domain. Starting with PostgreSQL version 8.0, you can also create composite data types. A composite type is a single data type made up of multiple fields. For example, you might define a composite type named address that contains a street number, city, state/province, and postal code. When you define a composite type, each component has a separate name and data type.
With PostgreSQL, you can create entirely new types that have no relationship to existing types. When you define a custom data type (in PostgreSQL), you determine the syntax required for literal values, the format for internal data storage, the set of operators supported for the new type, and the set of (predefined) functions that can operate on values of that type.
There are a number of contributed packages that add new data types to the standard PostgreSQL distribution. For example, the PostGIS project (http://postgis.refractions.net) adds geographic data types based on specifications produced by the Open GIS Consortium. The /contrib directory of a standard PostgreSQL distribution contains a cube data type as well as an implementation of ISBN/ISSN (International Standard Book Number/International Standard Serial Number) data types.
Creating a new data type is too advanced for this chapter. If you are interested in defining a new data type, see Chapter 6, “Extending PostgreSQL.” In the next two sections, we'll show you how to create and work with domains and composite data types.
Refining Data Types with CREATE DOMAIN
A domain is a user-defined data type that refines an existing data type. You typically create a domain when you need to store the same kind of data in many tables (or many times within the same table). For example, if you create a phone_number domain, you can store a phone number in a customer table (in fact, you may store many phone numbers per customer), in a salesman table, a vendor table, and so on. At first glance, you may think that you could simply add a CHARACTER(13) column to each table, but a phone number isn't simply a collection of 13 characters—it has a specific format. Here in the U.S., a phone number is often written as
(800)555-1212
The three-digit area code is surrounded by parentheses, then you see the prefix, a dash, and the last four digits of the phone number. To create a phone_number domain that enforces these constraints, you could execute the command
CREATE DOMAIN phone_number AS CHAR(13)
CHECK( VALUE ~ '\([[:digit:]]{3}\)[[:digit:]]{3}-[[:digit:]]{4}' );
The first part of this command is straightforward—you're creating a domain named phone_number as a constrained version of a 13-character CHAR field. The second part of the command (the CHECK() clause) is a constraint. In this case, you're telling PostgreSQL that the VALUE stored in a phone_number field must match the given regular expression (if you're not accustomed to reading complex regular expressions, this one specifies that VALUE must be an open parenthesis followed by three digits, followed by a close parenthesis, followed by three digits, a dash, and then four digits).
Once you've created a domain, you can define columns of that type. For example, to add a home phone number to the customers table, use the command:
ALTER TABLE customers ADD COLUMN home_phone phone_number;
Now here's the payoff. Once you've defined a domain (and all of the constraints you want to apply to the domain), you can use the domain in multiple tables, or many times in the same table. To add two more phone numbers to the customers table, use this command:
ALTER TABLE customers ADD COLUMN cell_phone phone_number, work_phone phone_number;
You've defined the constraints once, but you've created three columns that enforce those constraints. If you don't define a phone_number domain, you'll have to specify the constraints every time you add a phone number to a table.
When should you define a domain? Any time you store the same kind of object in multiple tables (or many times in the same table). You should also define a domain for any column that participates in a PRIMARY/FOREIGNKEY relationship. For example, the rentalstable contains two foreign keys: The rentals.customer_id column refers to customer.customer_id and rentals.tape_id refers to tapes.tape_id (for the sake of simplicity, we haven't actually defined PRIMARY/FOREIGN KEY constraints in the sample data for this book). Given these relationships, it's clear that the data type of rentals.customer_id must be identical to the data type of customer.customer_id (and that rentals.tape_id and tapes.tape_id must have the same type). The safest way to ensure that the data types match is to create a customer_id domain and a tape_id domain and define the key columns using those types, as shown in the following sequence of commands:
movies=# CREATE DOMAIN tape_id AS CHARACTER(8); CREATE DOMAIN movies=# CREATE DOMAIN customer_id AS INTEGER; CREATE DOMAIN movies=# ALTER TABLE customers ALTER COLUMN customer_id TYPE customer_id; ALTER TABLE movies=# ALTER TABLE rentals ALTER COLUMN customer_id TYPE customer_id; ALTER TABLE movies=# ALTER TABLE tapes ALTER COLUMN tape_id TYPE tape_id; ALTER TABLE movies=# ALTER TABLE rentals ALTER COLUMN tape_id TYPE tape_id; ALTER TABLE
Notice that the tape_id and customer_id domains are unconstrained. You don't have to attach constraints to a domain—an unconstrained domain is still useful because it defines a logical data type. In fact, you can attach new constraints to a domain later (or change existing constraints) using the ALTER DOMAIN command and PostgreSQL will ensure that existing data conforms to the new constraints (you'll be rewarded with an error message if you have any data that fails to satisfy the new constraints).
The complete syntax for CREATE DOMAIN is shown here:
CREATE DOMAIN name [AS] data_type [ DEFAULT expression ] [ constraint [ ... ] ] where constraint is one or more of the following: [ CONSTRAINT constraint_name ] NULL [ CONSTRAINT constraint_name ] NOT NULL [ CONSTRAINT constraint_name ] CHECK( expression )
If you include a DEFAULT expression clause, the given value becomes the default for any columns of type name. In other words, if you omit a column of type name in an INSERT command, PostgreSQL inserts expression instead of the usual NULL value. The default value should satisfy any constraints that you attach to the domain.
Once you've created a column whose type is defined by a domain, you can treat that column in the same way you would treat any other column of the base data type. For example, if you define a domain whose base type is CHARACTER, you can insert string values using the same syntax you would use for values of type CHARACTER. You can also create indexes that include domain values. A domain is a refinement of some other data type.
Creating and Using Composite Types
One of the new and powerful features introduced in PostgreSQL version 8.0 is the composite data type. A composite type is a data type composed of one or more named fields. For example, you might want to create a composite type named address composed of a street number, city, state/province, and postal code. The following command will do just that:
movies=# CREATE TYPE address AS movies-# ( movies(# street_number VARCHAR, movies(# city VARCHAR, movies(# state CHAR(2), movies(# postal_code VARCHAR movies(# );
Once you've defined a composite type, you can create columns based on the new type. When you add a column of composite type, you're adding a single field that happens to be composed of multiple fields. For example, to add an address to the customers table, execute the following command:
movies=# ALTER TABLE customers ADD COLUMN home_address address; ALTER TABLE
Now take a look at the definition of the customers table:
movies=# d customers;
Table "public.customers"
Column | Type | Modifiers
---------------+-----------------------+-----------
customer_id | integer | not null
customer_name | character varying(50) | not null
phone | character(8) |
birth_date | date |
balance | numeric(7,2) |
home_address | address |
Indexes:
"customers_customer_id_key" UNIQUE, btree (customer_id)
The ALTER TABLE command added a single field named home_address. So what happened to the street_number, city, state, and postal_code fields? They're inside the home_address column. Let's fill in the home_address for one of your customers :
movies=# UPDATE customers movies-# SET home_address = ( '200 Main Street', 'Springfield', 'CA', '90210' ) movies-# WHERE customer_id = 3;
Notice that the composite value that you're inserting is enclosed in parentheses. There are three ways that you can write a composite literal. The easiest method is the one you've just seen—simply enclose the component values in a set of parentheses. If you only have a single component to INSERT (that is, you're inserting default values for the other components), you must use the row constructor form instead:
ROW( '200 Main Street' )
You can also write the composite value as a string:
'(200 Main Street,Springfield,CA,90210)'
The row constructor form is typically the easiest way to build a composite literal because you don't have to worry about doubling-up any embedded quotes.
Now take a look at what PostgreSQL stored in the row you just added:
movies=# SELECT customer_name, home_address FROM customers;
customer_name | home_address
----------------------+------------------------------------------
Jones, Henry |
Wonderland, Alice N. |
Rubin, William |
Panky, Henry | ("200 Main Street",Springfield,CA,90210)
(4 rows)
You see a single column (home_address) with four values in it. How do you get to the individual components in the home_address column? Simply refer to ( columnName) .fieldname, like this:
movies=# SELECT customer_name, (home_address).postal_code FROM customers;
customer_name | postal_code
----------------------+-------------
Jones, Henry |
Wonderland, Alice N. |
Rubin, William |
Panky, Henry | 90210
(4 rows)
That command retrieved the postal_code component of the home_address column. The parentheses are required because the PostgreSQL parser can't tell if home_address.postal_code refers to a column (postal_code) within a table (home_address) or a field (postal_code) within a composite column (home_address).
Of course you can UPDATE a single component in a composite column as well:
movies=# UPDATE customers movies-# SET home_address.postal_code = '94404' movies-# WHERE customer_id = 3;
Notice that you can't include the parentheses around home_address in this case. Why? Because the parser would never expect to see a table name following the word SET and therefore can't mistake home_address as the name of a table.
You can't easily[20] create an index on a composite column, but you can create an index on an individual component (or on multiple components) even though the syntax is a bit mysterious. To create an index on home_address.city plus home_address.state, use the following command:
[20] You can create an index on a composite column, but you'll have to define an index operator class for each composite type—it's much easier to create an index on each component instead.
movies=# CREATE INDEX customer_location ON customers movies-# ( movies(# (( home_address ).city ), movies(# (( home_address ).state ) movies(# );
Take careful note of the parentheses—when you create an index on a field within a composite column, you must use the syntax '(( columnName ). fieldname ) '.
If you ask psql to display the layout of a table that contains a composite column, you won't see the component fields listed:
movies=# d customers;
Table "public.customers"
Column | Type | Modifiers
---------------+-----------------------+-----------
customer_id | integer | not null
customer_name | character varying(50) | not null
phone | character(8) |
birth_date | date |
balance | numeric(7,2) |
home_address | address |
Indexes:
"customers_customer_id_key" UNIQUE, btree (customer_id)
"customer_location" btree (((home_address).city), ((home_address).state))
To see the definition of a composite type, use the command d typename:
movies=# d address
Composite type "public.address"
Column | Type
---------------+-------------------
street_number | character varying
city | character varying
state | character(2)
postal_code | character varying
There are a few restrictions on composite types in PostgreSQL version 8.0. You can't attach constraints to a composite type. That's really not a problem because you can attach constraints to a domain and define a composite type that uses the domain. You can't create a domain whose base type is a composite type, but you can create a composite type that includes a domain. You can create a composite type that contains fields of composite type (meaning that you can nest one composite type within another). Nesting composite types can cause some confusion when you need to create a literal value of the outermost type (you need a lot of parentheses).
Overall, composite types take you one step closer to modeling complex real-world objects inside of a PostgreSQL database. When you combine composite types and domains, you have a powerful mechanism for enforcing constraints on complex objects.