What is a typed table in PostgreSQL? How can I make such table?

Question

While studying the information_schema views, I noticed the following properties for a table:

is_typed                  YES if the table is a typed table, NO if not
user_defined_type_catalog If the table is a typed table, the name of the database...
user_defined_type_schema  If the table is a typed table, the name of the schema...
user_defined_type_name    If the table is a typed table, the name of the data type...

See https://www.postgresql.org/docs/current/static/infoschema-tables.html.

I never heard of such concept. I have been looking in the documentation, but I cannot find more information.

What is a typed table? How can I create such table?

Answer 1

It's creating a table from a type. Per the documentation:

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).

When a typed table is created, then the data types of the columns are determined by the underlying composite type and are not specified by the CREATE TABLE command. But the CREATE TABLE command can add defaults and constraints to the table and can specify storage parameters.

Basically when you create a composite type (e.g., using the create type statement) you can create a table from that type. When you cascade changes of the type (altering columns or dropping the type), it affects all tables built with that type, which means you can have many tables that are structured the same way. This is helpful for logging, replication, an ETL process, etc.

---

Creating the table (SQLFiddle Example)

Step 1: Create the type

CREATE TYPE people_type AS ( age INT, name TEXT, dob DATE );

Step 2: Create the table

CREATE TABLE sales_staff   OF people_type;
CREATE TABLE service_staff OF people_type;

Step 3: Alter type

ALTER TYPE people_type ADD ATTRIBUTE gender CHAR CASCADE;

---

After altering the type you will be able to see that both tables have been affected. This can be done by using \d in psql, or querying the INFORMATION_SCHEMA.COLUMNS table in the database as in the example at the SQLFiddle link above.