Search⌘ K
Home
Courses
The Art of PostgreSQL

Composite Types

Explore how to build and manage composite types in PostgreSQL tables to handle denormalized data effectively. Understand practical applications including query execution and advanced use cases, enhancing your database management skills.

We'll cover the following...

PostgreSQL tables are made of tuples with a known type. It’s possible to manage that type separately from the main table, as in the following script:

PostgreSQL
begin;
drop type if exists rate_t cascade;
create type rate_t as
 (
   currency text,
   validity daterange,
   value    numeric
 );
create table rate of rate_t
 (
   exclude using gist (currency with =,
                       validity with &&)
 );
 
insert into rate(currency, validity, value)
     select currency, validity, rate
       from rates; 
commit;

Building a composite type

The rate table works exactly like the rates table that we defined earlier.

PostgreSQL
table rate limit 10;

We’ll get the kind of result we expect:

 ...