Using Composite Types within Postgres

At a company where most all people have some Postgres expertise you can easily learn something new from your coworkers every day about Postgres. In my first week I saw a question in our internal slack that I could guess an answer to, but it wasn't definitive.

It was "Why have composite types? Why would you use them?". I threw in an answer a few others did as well, but collectively we didn't have anything definitive but all these seemed like valid cases.

But first, what are composite types?

Composite types) are a custom type that is composed of other already existing Postgres data types. You can essentially think of them as a defined struct. Let's take a very basic example... I want to create a grid data type with letters across the top of my grid, and numbers that go vertically:

So for my datatype:

CREATE TYPE grid_spot AS (
  x varchar(1),
  y integer
);

Now if I were to use this in my gridding exercise for prioritizing effort vs. impact I can include activities:

sql
CREATE TABLE grid AS (
  idea text,
  grid_value grid_spot
);

Now I can insert a value directly into the grid_spot data type:

INSERT INTO grid VALUES ('Boot crunch operator to provision database', ROW('C', 1));

But why composite types?

In our internal chat as soon as we had exhausted some of the various cases where you might use composite types, the real fun began... The debate about should you use composite types...

Composite types similar to enums can be useful for enforcing certain constraints on your data. Sure you could use constraints, but composite types give you another mechanism for this. They also can be useful as function return types so you have a very defined struct coming back out of your function.

How far can you go with composite types? Well, you can actually have an entire table essentially as a composite type but that feels a bit overkill to me personally.

When and what to use them for?

So there are some cases on why, but practically when do you want to use them?

As we mentioned above returning data from functions can be useful, composite types are especially handy here when dealing with spatial data. You could have a full address which is ready for you to ship some package to.

You can also leverage them for more creative activities such as modeling complex numbers.

Can you, but should you?

In the end we collectively felt while a fun feature, there wasn't a home run use case. The usage of them is likely more dependent on tying to some defined structure your application needs. But, I'm curious to hear from all of you, are you using composite types? Let us know what for @crunchydata