In PostgreSQL, composite types allow you to definecustom data types with multiple fields. These fields can be any built-in or user-defined types, including other composite types.

Defining PostgreSQL composite types

To define a composite type, use theCREATE TYPE statement followed by the type name and a list of fields with their corresponding data types.

Here’s the basic syntax for defining a composite type:

CREATE TYPE type_name AS (    field1 data_type1,    field2 data_type2,    ...);

In this syntax:

• First, specify the name of the composite type (type_name) after theCREATE TYPE keywords.
• Second, define a list of fields of the composite type along with their respective data types.

For example, the following statement defines the address type that stores address information including street, city, state, zip code, and country:

CREATE TYPE address_type AS(   streettext,   citytext,   state text,   zip_codeinteger,   countrytext);

After defining a composite type, you can use it as a data type of a table column.

For example, the following statementcreates a table calledcontacts whose type ofaddress column is theaddress_type:

CREATE TABLE contacts(    idINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,    name VARCHAR(255)NOT NULL,    address address_type);

Please note that when creating a table, PostgreSQL implicitly creates a corresponding composite type. In this example, PostgreSQL automatically creates thecontacts composite type.

Inserting values into a composite column

To construct a composite value, you use theROW expression syntax:

ROW(value1, value2, ...)

In this syntax, theROW keyword is optional when you have multiple fields in the expression. Therefore, you can simplify the composite value as follows:

(value1, value2, ...)

To indicate aNULL, you can use theNULL keyword:

(value1,NULL, value3, ...)

Alternatively, you can omit it in the expression:

(value1,, value3, ...)

For example, the following statement inserts a new row into thecontacts table:

INSERT INTO contacts (name,address)VALUES (    'John Smith',    ROW('123 St','Houston','TX',77001,'USA'));

In this example, we use the following composite value to insert into theaddress column:

ROW('123 St','Houston','TX',77001,'USA')

The following statement inserts a value into individual fields of theaddress column:

INSERT INTO contacts (    name,    address.street,    address.city,    address.state,    address.zip_code  )VALUES  ('Jane Doe','4000 N. 1st Street','San Jose','CA',95134);

In this statement, we use the column name, followed by a dot, and field name to indicate the field of a composite type.

Querying composite values

The following statement retrieves the rows from thecontacts table:

SELECT * FROM contacts;

Output:

id |    name    |                   address----+------------+---------------------------------------------  1 | John Smith | ("123 St",Houston,TX,77001,USA)  2 | Jane Doe   | ("4000 N. 1st Street","San Jose",CA,95134,)(2 rows)

To query individual fields of a composite type, you use the following syntax:

(column_name).field_name

For example, the following statement retrieves the id, name, city, state, and zip code of contacts:

SELECT  id,  name,  (address).city,  (address).state,  (address).zip_codeFROM  contacts;

Output:

id |    name    |   city   | state | zip_code----+------------+----------+-------+----------  1 | John Smith | Houston  | TX    |    77001  2 | Jane Doe   | San Jose | CA    |    95134(2 rows)

If you retrieve all fields from a composite value, you can use the asterisk (*) shorthand:

SELECT  id,  name,  (address).*FROM  contacts;

Output:

id |    name    |       street       |   city   | state | zip_code | country----+------------+--------------------+----------+-------+----------+---------  1 | John Smith | 123 St             | Houston  | TX    |    77001 | USA  2 | Jane Doe   | 4000 N. 1st Street | San Jose | CA    |    95134 | null(2 rows)

Updating composite values

The following exampleupdates the country of the contact id 2 toUSA:

UPDATE contactsSET  address.country= 'USA'WHERE  id= 2RETURNING*;

Output:

id |   name   |                    address----+----------+------------------------------------------------  2 | Jane Doe | ("4000 N. 1st Street","San Jose",CA,95134,USA)(1 row)

In this example, you cannot put the parentheses around the column name of the composite type after theSET keyword.

Summary

• Composite types allow you to define custom data types that include multiple fields.

Last updated onApril 21, 2024

On this page

• Defining PostgreSQL composite types
• Inserting values into a composite column
• Querying composite values
• Updating composite values
• Summary