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.