5 Handy Dandy Postgres Features

Published in

Engineering at Earnest

4 min readAug 8, 2018

Postgres — because an elephant never forgets.

At Earnest, we use Postgres a lot. The original codebase we lovingly call the “Monolith” was built with it, new microservices use it, and some internal services have been migrating to it. This is largely due to the variety of features Postgres offers to organize, validate, and query our data. In this post, we’ll go through some of our favorite Postgres features.

Enum Types

Enum Types are a really underrated feature which allows you to list all the permissible values a column can contain.

CREATE TYPE product_category AS ENUM (‘jeans’, ‘shirt’, ‘scarf’);CREATE TABLE products (
  id uuid PRIMARY KEY,
  category product_category
);

The previous queries create a new type called `product_category` and a table `products` which has an `id` and `category` column.

Using the `product_category` enum instead of a text type informs any readers of the database with certainty that there are only three possible values present in the category column. Also, building services on top of the database is much simpler as there are less possible states for which the service needs to account.

Common Table Expressions

Common Table Expressions (or CTE’s) are an abstraction allowing you to bind the results of a query to a labeled temporary result set. This is really useful when building out a larger query as an alternative to subqueries:

CREATE TYPE product_category AS ENUM (‘jeans’, ‘shirt’, ‘scarf’);CREATE TABLE products (
  id uuid PRIMARY KEY,
  category product_category,
  price integer
);WITH avg_price_by_category AS (
  SELECT category, AVG(price) as price FROM products GROUP BY category
);SELECT p.id, p.category, p.price, a.price FROM products p LEFT JOIN avg_price_by_category a ON p.category = a.category;

The CTE statement above (the part starting with the `WITH` keyword) creates a temporary `avg_price_by_category` result set. This named result set is then referenced again when joining the average with each record from products with a matching category.

Common Table Expressions are an abstraction to help organize the query into named sections. This allows you to reason about different parts of the query independently.

Window Functions

Window Functions are a neat way to perform aggregations over a dataset without changing the number of rows in the dataset. This is performed by creating a window over which the aggregation is applicable and including the aggregated value for each record in the window. The following is a refactoring of the query from Common Table Expressions:

CREATE TYPE product_category AS ENUM (‘jeans’, ‘shirt’, ‘scarf’);CREATE TABLE products (
  id uuid PRIMARY KEY,
  category product_category,
  price integer
);SELECT id, category, price, AVG(price) OVER (PARTITION BY category) AS category_avg_price FROM products;

The above queries create a products table where each product has a category and price. The SELECT query uses a Window Function to construct the column `category_avg_price`, in this example the window is being created around each category and the average price is taken from the window and added to each record.

Window Functions allow you to write queries which associate aggregations constructed with any of Postgres aggregation functions to individual records in a performant manner.

Check Constraints

Check Constraints allow you to encode restrictions or validations on your data in the database. These restrictions can drastically reduce the allowable states permitted by the database, which reduces the problem space programs using the database have to deal with.

Common check constraints could be:

Ensure a number is always positive
Ensure a text field is non-empty
Ensure a column value is present if another value is defined

CREATE TABLE products (
  id uuid PRIMARY KEY,
  name text,
  price integer CONSTRAINT positive_integer_check CHECK (price >= 0)
);

The above query creates a products table where the product price must always be positive. If any query tried to insert a record less than zero, the insert would fail. This provides a guarantee for any program working with the table that it will always receive a positive product price.

Domains

Domains are in some ways very similar to Check Constraints, but instead of attaching the constraint to a table, a new type is created with the constraint applied implicitly. Using domains avoids the problem of having to define the same constraint logic on every table a particular type of data will reside. The following is a refactoring of the example previously used above for Check Constraints:

CREATE DOMAIN money AS integer
CHECK (
  VALUE >= 0
);CREATE TABLE products (
  id uuid PRIMARY KEY,
  name text,
  price money
);

Then we could use this type in any table requiring a money column, for example:

CREATE TABLE accounts (
  id uuid PRIMARY KEY,
  balance money
);

The use of domains in this case allows us to abstract the constraint logic away behind a type, both making our intent clearer and consolidating the logic to a single place.

Conclusion

Postgres has an extensive list of features and in this post we’ve gone through just a few we have come to use on a regular basis. At Earnest, we are constantly learning new ways to get the most out of our database and are always down to share the knowledge, whether it be through our internal #sql slack channel, pairing, code review, or blogging.

If you’d like to grow or share your Postgres knowledge, there are a many Meetups globally, the documentation is dense but excellent, or you could come join us. Earnest is hiring!