2 Ways of Creating a Composite Type in PostgreSQL

In PostgreSQL, a composite type is a data type that represents the structure of a row or record. It’s basically a list of field names and their data types.

We can use composite types in many of the same ways we can use simple types, such as in table columns.

Below are two ways of creating composite types in PostgreSQL.

Option 1: The `CREATE TYPE` Statement

The CREATE TYPE statement is designed specifically for creating a user-defined data type. It registers the data type for use in the current database.

When we use the CREATE TYPE statement to create a composite type, it’s almost like creating a table.

Here’s an example:

CREATE TYPE address AS(
   street text, 
   city varchar(200),
   state varchar(50),
   postcode varchar(12),
   country_code char(3)
);

Running that code will create a composite type called address. It consists of five fields and their respective data types.

Once created, the composite type can be used in table definitions, function definitions, etc.

Option 2: Create a Table

Another way to create a composite type is to simply create a table.

When we create a table, PostgreSQL automatically creates a composite type behind the scenes with the same name as the table. Each column name becomes a field name in the composite type.

Example:

CREATE TABLE person (
    person_id serial primary key,
    first_name varchar(100),
    last_name varchar(100),
    address_details address
    );

Running the above code will create a table called person.

Behind the scenes, it will also create a composite type called person. Its field names will correspond with the column names defined for the table. We could go ahead and use the person composite type as if it was one that we created with the CREATE TYPE statement.

For example:

CREATE TABLE person2 (
    details person
    );

You may have noticed that the person table uses the composite type (called address) that we created in the previous example. Well that composite type is included in the new composite type, effectively giving us a nested composite type, or a composite type inside another composite type. Therefore, when we create the person2 table, its details column uses the person composite type, which itself also has the address composite type.

One thing to be aware of is that, if the table has any constraints, none of those constraints are copied to the composite type. Composite types don’t support constraints (at least not as of PostgreSQL 16). It is possible to create a domain over the composite type, and then create any constraints as CHECK constraints of the domain.

Option 1: The CREATE TYPE Statement

Option 2: Create a Table

Option 1: The `CREATE TYPE` Statement