The Art of PostgreSQL/

...

/

Composite Types

Composite Types

Learn about composite types in PostgreSQL.

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:

Press + to interact
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.

Press + to interact
table rate limit 10;

We’ll get the kind of result we expect:

 ...