Elizabeth Garrett Christensen: Intro to Postgres Custom Data Types

Custom data types is one of those many features that makes PostgreSQL flexible for development of a huge variety of business and application use cases. Data types will help you primarily for data integrity, ensuring your data is stored in the database as you want it to be. A common surprise gift of using strict data types is that you can end up reducing your long term data maintenance. There’s two main ways to customize data types in Postgres: Create DOMAINs, which are value constraints added to built-in data types Creating user-defined custom data types Before you go crazy with custom data types, make sure that one of the 43 existing data types won’t work for you ;). There’s so many data types and existing extensions, most people will never even need to touch custom data types. That doesn't mean it's not fun to learn about! Hands on tutorials My wonderful colleague, Jean-Paul Agudo wrote two hands-on tutorials for these topics, one for DOMAINS and the other for User Defined Custom Data Types. Using CREATE DOMAIN DOMAINs let you create a specific value check. For example if I want to make sure my birthdays are all greater than Jan 1st, 1930 and my emails are valid, I could create this: CREATE DOMAIN date_of_birth AS date CHECK (value > '1930-01-01'::date) ; CREATE DOMAIN valid_email AS text NOT NULL CHECK (value ~* '^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+[.][A-Za-z]+$') ; When creating the table, assign the date_of_birth and valid_email data types to their respective columns. Now, if a database has multiple fields representing data of birth or valid email, the logic for those fields is portable to those additional columns. CREATE TABLE person_using_domains ( id INTEGER GENERATED always AS IDENTITY PRIMARY KEY, firstname TEXT NOT NULL, lastname TEXT NOT NULL, birth_date DATE_OF_BIRTH, email VALID_EMAIL ); When using psql, all domains will be listed by running \dD. DOMAIN vs CHECK CONSTRAINT So you[...]