Difficulty: beginner
Estimated Time: 15 minutes

This is a continuation of the preceding course, Intro to Data Types. We've looked at a few basic data types, learned some of the nuances of numeric, character, date/time and boolean types, and started considering the implications of database schema choices. If you haven't yet, or feel you could use a refresher, head over to Intro to Data Types before proceeding with this course.

We'll be tackling a few more data types that are used in PostgreSQL:

  1. Arrays
  2. Range types
  3. Enumerated types
  4. XML

We've grouped these more "advanced" types together in this course because it helps to have started with a good foundation of the data types covered in the previous course.

We'll log you in to a workshop database using the following credentials:

  • username: groot
  • password: password

While we'll also introduce some related functions and operators, we won't be going in depth since this course is only meant to get your feet wet with these data types. At the end of the course, we'll mention a few other types that might be of interest, as well as some further resources you can look at if you want to continue learning.

Part of what makes PostgreSQL powerful (and fun to use) is the ability to use these advanced data types. This course serves as just an introduction; here are some related resources we recommend for further learning:

More resources

  • Dimitri Fontaine's blog has several entries about the data types we covered here, and more
  • This Citus Data blog post also gives a quick rundown on why you might use these data types
  • Our Crunchy Data blog features a more in-depth and advanced use case for range types
  • We have another Crunchy blog post that discusses how your choice of data structure and types may impact storage

Enjoy learning about PostgreSQL? Sign up for our newsletter and get the latest tips from us each month.

Advanced Data Types

Step 1 of 8

Before We Begin

Just like in our Create Table course, we'll consider a particular business scenario, build out our data structure, and use array, range, and enumerated (aka enum) types in our examples.

Scenario: Career counseling agency

Extra Mile Counseling offers career counseling and coaching services to young professionals. They have two counselors onsite in their Portland, Oregon headquarters, and four other remote counselors across the United States and Canada.

Many of Extra Mile's clients work individually with a career counselor over a certain time period, but Extra Mile also hosts other events that are open to the public for registration. A few of their most popular events are: 30-minute one-on-one consultations, résumé and cover letter group workshops, and job search strategy workshops.

Extra Mile Counseling needs a way to track their events, event attendance, and clients. Any one-on-one meeting with a counselor is considered an "event," as are group meetings and workshops. Anyone who attends an event is considered a "client," regardless of whether they work with an individual career counselor on a regular basis, or have only ever attended one event.

Data structure

Since we're focusing on a few specific data types, we'll work with an "incomplete" data structure, i.e. we'll only concern ourselves with a few tables, relationships, and columns that serve the purpose of this course.

Extra Mile Counseling entity relationship diagram

PK and FK indicate primary keys and foreign keys respectively. The attendance table has a composite primary key comprised of its two columns.

The main concepts to derive from this data model are:

  • All clients have attended at least one event.
  • Each instance of attendance is associated with exactly one event and one client only.
  • An event can have zero (for example, if registration hasn't been opened yet), one, or many attendees.

Again, we'll keep it to a very simple data structure for this course, but you may already realize some of the things it lacks. For example, we haven't included a table for career counselors. Also, in the real world, these three tables will likely need more fields. Planning and implementing a database design is typically a rather involved exercise, taking into account many business rules and processes.

What we'll focus on

These are the fields we'll use to learn about array, range, and enum types:

a. client table: career_interests - array

b. event table: event_dt - range

c. event table: mode - enum

For the purposes of this course, we won't actually need the attendance table, so we won't build it this time. With that said, this kind of "intersection" table is common in relational database design.

Note:

You may encounter entity relationship diagrams (ERD) or data models that look different from the one we're using here. There are many ways to illustrate how objects in a database are related to each other. (Here's a quick two-part video series on ERDs if you'd like to learn more about creating them.)