{"id":108481,"date":"2026-03-27T13:21:12","date_gmt":"2026-03-27T13:21:12","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=108481"},"modified":"2026-04-17T09:46:45","modified_gmt":"2026-04-17T09:46:45","slug":"learning-postgresql-with-grant-user-defined-types","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/postgresql\/learning-postgresql-with-grant-user-defined-types\/","title":{"rendered":"How User-Defined Types work in PostgreSQL: a complete guide"},"content":{"rendered":"\n
\n

Learn how to work with user-defined types in PostgreSQL, including how to define, use, and manage them effectively. This guide explores how custom types can improve schema design, enforce data integrity, and support cleaner, more maintainable database structures in real-world applications.<\/strong><\/p>\n\n\n\n

I\u2019m sure I\u2019m not alone when I say, sometimes I get sidetracked. In this particular instance, I hadn\u2019t intended to start learning about User-Defined Types (UDT)<\/a> in PostgreSQL<\/a> – I just wanted to test a behavior that involved creating a UDT. But, once I started reading, I was hooked. I mean, four distinct UDTs with different behaviors? That\u2019s pretty cool. Let\u2019s get into it.<\/p>\n<\/blockquote>\n\n\n\n

What is a User-Defined Type (UDT), and why do we have them?<\/h2>\n\n\n\n

First, it\u2019s PostgreSQL, so I have to address the true User-Defined Type, not simply the derived types that we\u2019re going to talk about in the rest of the article. See, in PostgreSQL, you can quite literally build your own data type<\/a>. You\u2019ve got to break out the C compiler and write a bunch of code to input and output constructors and more. That is far beyond my capacity to describe. If you\u2019re interested in this, I\u2019d suggest starting with their excellent documentation<\/a>.<\/p>\n\n\n\n

Outside Postgres, a standard UDT is usually just an existing data type, or data types, that can be referenced as if it was a standard data type when you add a column to a table. Further, we can add rules around the data type so that we can readily enforce exactly what gets stored. It\u2019s used for constructs that have specific use cases and rules that we want to define once, and then reuse without worrying ever again.<\/p>\n\n\n\n

An example of such a set of rules would be for amateur radio call signs in the United States. We have a relatively precise system. It\u2019s a string, up to six characters long that consists of one to two letters, a number, and then one to three letters. For example, my call sign is KC1KCE, all six characters. But my local club\u2019s call sign is W5IAS, only five. Another example is W1AW, four characters, and the call sign for the ARRL headquarters. You get the idea. Other countries have other rules.<\/p>\n\n\n\n

Now, let\u2019s say I want to use this call sign value in multiple places in my database. I can easily create a column with a variable length limit of six characters. I can create a regular expression<\/a> in a constraint to enforce the naming standard too: [A-Z]{1,2}[0-9][A-Z]{1,3}. If I decide to use call signs in multiple places in my database, I\u2019ll be creating that column and that constraint over and over. Or, I can define it as a User-Defined Type and only define the code once. That\u2019s the beauty of the UDT.<\/p>\n\n\n\n

What User-Defined Types are available in PostgreSQL?<\/h2>\n\n\n\n

PostgreSQL provides four different types of UDT:<\/p>\n\n\n

\n