PostgreSQL comes with a lot of flexibility for defining new data types, defining tables and defining arrays over data types. You can make that as complex as you want, which does not mean that you should do it. Somehow you still need to manage and maintain what your settings up. The more complex your schema gets, the more time people will need to understand what is going on there, and why it is like it this.

Let’s start simple by creating a very tiny and basic table:

postgres=# create table t1 (a int );
CREATE TABLE
postgres=# \d t1
                 Table "public.t1"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          |

What PostgreSQL did internally is, that it created two types you can see in the pg_type table:

postgres=# select oid,typname,typlen,typtype,typcategory,typarray from pg_type where typname like '%t1%';
  oid  | typname | typlen | typtype | typcategory | typarray 
-------+---------+--------+---------+-------------+----------
 24578 | t1      |     -1 | c       | C           |    24577
 24577 | _t1     |     -1 | b       | A           |        0
(2 rows)

What that means:

  • The type “_t1” is a so called base type (typtype=b) while “t1” is a composite type (e.g., a table’s row type).
  • “typlen=-1” means it is a variable length type.
  • “typarray” means, that our table gets its definition from the other type (oid=24577)
  • “typcategory” means: A is an array type, C again is a composite type.

As everything here is a type, one type can reference another type, e.g.:

postgres=# create table t2 ( a int, b t1 );
CREATE TABLE
postgres=# \d t2
                 Table "public.t2"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 
 b      | t1      |           |          |

What happened here is, that we used the “t1” table as a data type for the second column of the “t2” table. Adding a row to the t1 table is straight forward, nothing special here:

postgres=# insert into t1 values(1);
INSERT 0 1

How does that work for the second table, which uses the first table/type as a data type for the second column? For this you can use a row constructor and cast that to the appropriate data type:

postgres=# insert into t2 values ( 1, row(1)::t1 );
INSERT 0 1
postgres=# select * from t2;
 a |  b  
---+-----
 1 | (1)
(1 row)

If you want, you can use both tables as data types for a third table:

postgres=# create table t3 ( a int, b t1, c t2 );
CREATE TABLE

Once more use the row constructor to add data:

postgres=# insert into t3 values ( 1, row(1)::t1, row(1,row(1)::t1)::t2 );
INSERT 0 1
postgres=# select * from t3;
 a |  b  |     c     
---+-----+-----------
 1 | (1) | (1,"(1)")
(1 row)

As you can see, this can become quite complex very fast. I am not saying there is no use case for this, but you should think carefully about what you really need. Let’s make another example and add arrays to the game. We again start with two little tables:

postgres=# create table x (a int, b int, c int, d int, e int, f int, g int, h int, j int );
CREATE TABLE
postgres=# create table xx ( a int, b x );
CREATE TABLE

In the same way as above we’ll add some data to the second table:

postgres=# insert into xx values ( 1, row(1,1,1,1,1,1,1,1,1)::x );
INSERT 0 1
postgres=# select * from xx;
 a |          b          
---+---------------------
 1 | (1,1,1,1,1,1,1,1,1)
(1 row)

On top of that we’ll create another tables which contains an array over the xx table as a column definition:

postgres=# create table xxx ( a int, b x, c xx[] );
CREATE TABLE

For getting data into this table you need to use the array constructor:

postgres=# insert into xxx values ( 1, row(1,1,1,1,1,1,1,1,1)::x, array[row(1,row(1,1,1,1,1,1,1,1,1)::x)::xx,row(1,row(1,1,1,1,1,1,1,1,1)::x)::xx] );
INSERT 0 1
postgres=# select * from xxx;
 a |          b          |                               c                               
---+---------------------+---------------------------------------------------------------
 1 | (1,1,1,1,1,1,1,1,1) | {"(1,\"(1,1,1,1,1,1,1,1,1)\")","(1,\"(1,1,1,1,1,1,1,1,1)\")"}
(1 row)

This is even more complex than the example above. What about querying data from that table? This can be done by using the same features as before:

postgres=# select * from xx where b = row(1,1,1,1,1,1,1,1,1)::x;
 a |          b          
---+---------------------
 1 | (1,1,1,1,1,1,1,1,1)
(1 row)

postgres=# select * from xxx where b = row(1,1,1,1,1,1,1,1,1)::x;
 a |          b          |                               c                               
---+---------------------+---------------------------------------------------------------
 1 | (1,1,1,1,1,1,1,1,1) | {"(1,\"(1,1,1,1,1,1,1,1,1)\")","(1,\"(1,1,1,1,1,1,1,1,1)\")"}
(1 row)

postgres=# select * from xxx where c = array[row(1,row(1,1,1,1,1,1,1,1,1)::x)::xx,row(1,row(1,1,1,1,1,1,1,1,1)::x)::xx];
 a |          b          |                               c                               
---+---------------------+---------------------------------------------------------------
 1 | (1,1,1,1,1,1,1,1,1) | {"(1,\"(1,1,1,1,1,1,1,1,1)\")","(1,\"(1,1,1,1,1,1,1,1,1)\")"}
(1 row)

Conclusion: By combining types and arrays you can do almost anything, but keep in mind that this needs to be managed and understood by other people. And even yourself, are you sure you understand what you did when you look at that a year later?

Post Views: 714
Thumbnail [60x60]
by
Daniel Westermann