Close modal

Blog Post

Postgresql Complex (Composite) Types

Development
Tue 17 May 2016
0 Comments


This won't be the longest blog post in the word - I wish to explain the why and how of using composite/array types in postgresql - and the syntax may get tricky sometimes so I'd like to try and keep it as canonical as possible, both for your referencen and my own.

Postgresql Complex (Composite) Types

I like Postgresql, and I think it's awesome - that's no secret.

Let me show you some of the awesome features that can make our lives easier, by writing more elegant Code/Schemas that quite possibly may be more performant for alot of scenarios.

In this scenario we imagine we wish to store a list of clients identified by a unique client Id, and who also have a name. These clients will have 0 or more mobile devices which we must record to store their push tokens in order to send them messages. This example is trivial and there are no passwords or other objects of interest - so use your imagination.

Ordinarily to store a 1-many relationship you would create a table, perhaps client_device_details that has its own primary key and a foreign key into client, indicating the client (1) to device_details (many) relationship. If we frequently want to fetch this, it could slow down queries and grow the database, and of course you might be tempted to store it as an embedded string or some such if you had a simpler RDBMS however that loses all benefits of type safety and searching. Let's use postgresql to create a custom column type for this.

First we must create the custom type in postgresql, with syntax as such:
CREATE TYPE device_details AS
(
     type VARCHAR(16),
     token VARCHAR(256)
);

As you can see it looks similar to how one would create a table normally.

With that type created, let's use it inside a 'clients' structure:
 CREATE TABLE clients
(
   id BIGINT PRIMARY KEY,
   name VARCHAR(50),
   devices _device_details
);

Notice that to refer to the user type we use _device_details which means an array of elements device_details, this is true for user types and built in types.

An example of how to insert into this would be as follows, inserting a client row with one device_details assocaited with them.

  INSERT INTO clients VALUES (1,'user 1', ARRAY[('APPLE-APN', 'sdfdsfsdsdfsd')::device_details]);

As you can see that whole lot for a client object was inserted in one row, including an array container one client device - but we could have as many here as we wanted, and update this later.

General Notes

While this is a very powerful feature that can be incredibly useful under the right circumstances, I'm not advocating you go out and add it everywhere. It is true that Postgresql will allow you to search these fields using array/collection operators and it works nicely, you need to bare in mind that using it in the wrong circumstance may severely degrade performance if you load up your table's row space with alot of extra data that won't be fetched most of the time.

That being said, for data that is almost or always included as a part of a query, or where the expected set of data is very small for the custom type, it can simplify the table design (requiring less tables) and also improve performance by not having to perform joins that could otherwise just be embedded using the method advocated above.


Comments !