We're gonna talk about composite types, but I have to warn you, you have to issue this disclaimer. Composite types are probably not what you're looking for. They're fun, they're awesome, they're cool, but eh, you won't use them very often. There are a few cases where you might use them.

A composite type wraps up a bunch of normal Postgres types into one discrete type, one compressed unit of information, one inseparable thing. That's kind of useful sometimes. For example, in PostGIS, it will return an address type, and the address type has, you know, an 18 different fields. And that's kind of the example we're gonna be looking at here. It's gonna be vastly simplified, but most of the time, composite types aren't what you're looking for. You probably just want discrete columns or potentially a JSON object or an entirely separate table to hold all of those separate columns in the address case. But they exist. They exist, they have a use case. And if this lines up with your domain, or your business, or your application, I want you to be ready to use them. This is what it looks like to create a composite type.

You have create type address as, and this as is pretty important, 'cause if you don't have that as there, it will think you mean create a different kind of type. For a composite type, we need as open parentheses, and then what is in here looks a lot like a table constructor. Traditionally, we would call them columns. I guess, for a composite type, you could call them fields. And then you put the actual, you know, subtypes, the actual real types of those fields in there.

You cannot, however, have something like a not null, constraint, or something like that. You can't have constraints in a type. You still have to put those at the table level. So, if we create that type, then we can actually, I guess, instantiate a type here by doing this ROW syntax. We're gonna say the numbers 123, street is Maine, Anytown, ST, 12345 cast to an address. We get that back. If we were to run, goodness, I mistyped it, pg_typeof of all the way to the end. We get address.

Now, you can shorten this just a little bit by dropping the row identifier, as long as it still has parentheses around it. There is a way to create these straight out of a string. I find that to be cumbersome, because, typically, you are gonna have text fields in here. Then you get into this double quoting, nested quoting, escape kind of situation where you just, I don't recommend it. I would stick with either the row parentheses or just the shorthand parentheses style. Now, I don't want to spend too much time on these, because they're just so infrequently used, but lemme show you how to create a table, how to insert one of these composite types, and how to get these individual fields back out of it.

So, here we have a table called addresses. id is, that is just so many words for auto incrementing id. And then we have addr, and it is a type of address. If we run that, and then we can take this guy. We're gonna take this ROW syntax and we're gonna say insert into addresses, just the address field values of, and then we're gonna insert that as one discrete unit, and so, we can select * from addresses and we see it as one discrete unit.

Now, what if we want it as individual parts? Let me show you a little bit of a problem. If we do that, we get the address back, but then let's say we want the number, and it says, "Ugh, missing FROM for table 'addr'." So it thinks that this is a table, and who could blame it? Honestly, that is how you do that. When you have a composite type, you can wrap the type or the column that is the type in parentheses, and then run that again, and you'll be able to, yoink, grab those discrete fields out of your composite type.

That's kind of where I want to leave it with composite types. They're very cool, just very infrequently used. You might see them coming out of some extensions, and so I want you to be aware of their existence and how to access them.