How to Use Composite Type to Create New Table in PostgreSQL

composite-typesdatabase-designddlpostgresql

In ORDBMS databases I have seen that complex types can be used:

create type name as( ...)

And I can references those types when creating a new table, like:

create table example (row_name ref(name))

How can I achieve the same in PostgreSQL?

Best Answer

You can use a typed table:

CREATE TYPE mytype AS (some_id int, some_col text);

CREATE TABLE example OF mytype (PRIMARY KEY (some_id));

I added a PK constraint (which you did not ask for).

The syntax is documented as second variant in the manual for CREATE TABLE, which also explains further down:

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.

There is also a code example at the end of the examples section.

Related Solutions

Should I create a new table for store information of an associated object with object need to create

If the number of questions for each topic on each course varies from one General Exam to another, then yes, you want a structure like this (which is pretty much what you have):

NumberQuestions:
GE_ID int FK
Course_ID int FK
Topic_ID int FK
MaxQuestions int

PostgreSQL – Table-Based Custom Type Not Verifying Check Constraints

A record will only be null if all of its fields are null or if the record itself is null. If you want to check if any of the fields is null then check one by one:

create table final
(
    name character varying(20) not null,
    data source check (
        (data).id is not null and (data).weight is not null
    ),
    constraint final_pkey primary key (name)
);

insert into final (name, data) values ('a', null);
ERROR:  new row for relation "final" violates check constraint "final_data_check"
DETAIL:  Failing row contains (a, null).

insert into final (name, data) values ('a', (null, null));
ERROR:  new row for relation "final" violates check constraint "final_data_check"
DETAIL:  Failing row contains (a, (,)).

insert into final (name, data) values ('a', ('1', null));
ERROR:  new row for relation "final" violates check constraint "final_data_check"
DETAIL:  Failing row contains (a, (1,)).

insert into final (name, data) values ('a', ('1', 1));
INSERT 0 1

Best Answer

Related Solutions

Should I create a new table for store information of an associated object with object need to create

PostgreSQL – Table-Based Custom Type Not Verifying Check Constraints

Related Question