What are typed tables in PostgreSQL?

While reading the PostgreSQL documentation for “CREATE TABLE” I came across this:

“OF type_name: Creates a typed table, which takes its structure from the specified composite type (name optionally schema-qualified). A typed table is tied to its type; for example the table will be dropped if the type is dropped (with DROP TYPE … CASCADE).”

Sounds interesting, lets have a look.

Obviously we’ll need a composite type to make use of the feature described above:

postgres=# create type ctyp1 as ( a int, b varchar(10), c date );
CREATE TYPE
postgres=# d ctyp1
       Composite type "public.ctyp1"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 a      | integer               | 
 b      | character varying(10) | 
 c      | date                  | 

Using the “CREATE TABLE” statement we can now create a table which is based on that type:

postgres=# create table t1 of ctyp1;
CREATE TABLE
postgres=# d t1
             Table "public.t1"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 a      | integer               | 
 b      | character varying(10) | 
 c      | date                  | 
Typed table of type: ctyp1

But is that useful? First I thought no, but there is at least one use case for which this is a great help. Image you need another table with the same structure, maybe in another schema. All you need to do is:

postgres=# create table t2 of ctyp1;
CREATE TABLE
postgres=# d t2
             Table "public.t2"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 a      | integer               | 
 b      | character varying(10) | 
 c      | date                  | 
Typed table of type: ctyp1

Not much of a help you might think as you can just create another table the usual way by specifying all the columns once more, but wait: How would you manage changes to the table structure then? Having both tables based on the same type gives you this:

postgres=# alter type ctyp1 add attribute d numeric cascade;
ALTER TYPE
postgres=# d ctyp1
       Composite type "public.ctyp1"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 a      | integer               | 
 b      | character varying(10) | 
 c      | date                  | 
 d      | numeric               | 

The magic is in the keyword “cascade”. What happened is that both our tables now look like this:

postgres=# d t1
             Table "public.t1"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 a      | integer               | 
 b      | character varying(10) | 
 c      | date                  | 
 d      | numeric               | 
Typed table of type: ctyp1

postgres=# d t2
             Table "public.t2"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 a      | integer               | 
 b      | character varying(10) | 
 c      | date                  | 
 d      | numeric               | 
Typed table of type: ctyp1

With one command we changed the structure of all the tables which are based on the composite type. Btw: When you skip the “cascade” keyword you’ll get an error:

postgres=# alter type ctyp1 add attribute e numeric;
ERROR:  cannot alter type "ctyp1" because it is the type of a typed table
HINT:  Use ALTER ... CASCADE to alter the typed tables too.

Can be quite useful…Good to know that this is possible.