Fun With Postgres Functions

Postgres is an awesome database for a lot of reasons, put simply it's a really feature rich, powerful, and reliable database. One of those rich areas is the many built-in functions in Postgres.

Let's take a look at a few of my favorites.

Date functions

First, let's start with one of the most simple built-in ones but when doing things with time is quite handy.

Want to know what time it is according to your database?

SELECT now();

Want to know the biggest date in your data?

select max(day)
from births;

Now let's look at one that is a little bit more interesting. If you want to do a rollup by month, by week, by day, really any time interval you could do comparison of greater than or less than various months, or you could truncate the date. A simple example of this could be calculating your signups per week, where day is the data you have per row.

SELECT date_trunc('week', day),
       count(*)
FROM users
GROUP BY 1
ORDER BY 1;

Shifting a little bit, let’s take a look at something even more complicated. Earlier this week I wanted to calculate what percentage of time remained in the month. For example if it was the very start of the day on the 15th of the month, in February we'd be 50% of the way through the month, but in January we'd only be 45% of the way through the month.

My table events has a column period which is a range type (a single datatype that has a from and a to with two timestamp values). On the query below we do a few things:

1. We calculate two different intervals, to dissect the first:
2. We get the start of the month lower(period) and increase it by 1 month so it's the exact end of the month
3. Then we get the timestamp of how much time has elapsed into the month
4. Then we subtract the two, leaving us with something similar to: 14 days 3 hrs 5 minutes
5. The second interval we get as the full length of the month, so February 28 days or January 31 days
6. We extract the epoch of the time interval EXTRACT ( epoch FROM intervalhere )
7. And finally we do the math against the two number values to compute the percentage of month left.

SELECT
  extract (
    epoch FROM
      max(lower(period)) + '1 month'::interval - max(upper(period))
  ) /
  extract (
    epoch FROM max(lower(period)) + '1 month'::interval - max(lower(period))
  ) perc
FROM events

So there is a more advanced example above, but in total there is a wealth of functions for all different use cases. Let’s continue with a very quick cursory look at some categories of functions and examples within each.

Text Functions

Another common category of functions is text manipulation ranging from formatting to parsing. You can even do something like excel concatenation using a text format to combine two fields:

SELECT FORMAT('%s, %s',last_name, first_name) full_name
FROM customer
ORDER BY full_name;

Or here we can capitalize the first letter and lower case email for consistent reporting of names and emails:

SELECT INITCAP(first_name),
       INITCAP(last_name),
       LOWER(email)
FROM customer;

And as we saw in large initial computation above, you can combine functions together. Here we’ll get a formatted name with the first letter capitalized.

SELECT FORMAT('%s, %s',INITCAP(last_name), INITCAP(first_name)),
       full_name
FROM customer
ORDER BY full_name;

You can find a full set of string functions and operators within the Postgres docs, but for now lets keep going on more more categories.

Aggregate functions

Aggregate functions combine groups of rows in some way. There are many built-in ones available, such as if you want to combine a list of strings split by comma, or to sum an array of numbers. You can also use aggregate functions to transform results into JSON. Here’s a collection of a number of aggregate functions I tend to find handy:

-- Math functions

-- Get total number of customers
SELECT count(*)
FROM customer;

-- Find when the first customer was created
SELECT min(created_at)
FROM customer;

-- Get array of all customer ids created this week
SELECT array_agg(id)
FROM customer
WHERE created_at >= now() - '1 week'::interval;

-- String aggregate functions

-- Get email addresses of all customers signed up this week
SELECT string_agg(email)
FROM customer
WHERE created_at >= now() - '1 week'::interval;

-- JSON aggregate functions

-- Get id and email addresses in JSON format of signups this week
SELECT json_agg(id, email)
FROM customer
WHERE created_at >= now() - '1 week'::interval;

Math functions in Postgres

Of course you can do basic addition, subtraction, multiplication, etc. But surprise, surprise that isn't all you can do in Postgres. Let's do a really quick rundown of some of the ones that exist for you:

-- floor for providing nearest integer
SELECT floor(89.6);

90

-- trunc to truncate down to nearest integer
SELECT trunc(89.6);

89

-- abs for absolute value
SELECT abs(-10);

10

--log for base10 log

SELECT log(100);

2

--pi for pi

SELECT pi();
3.14159265358979

A final collection of useful Postgres functions

While we’ve tried to categorized some clear groups above there is a mix of other functions that deserve some special mention. One of these is coalesce which is essentially replace nulls with some value.

SELECT coalesce(name, 'default team')
FROM team;

Another one that can be indispensable is when working with some of the internal Postgres catalog tables. If you want to check for something like how much bloat you get back a raw number of bytes. You may be used to looking at those numbers and intuiting things about them, but for the rest of us getting something like 16 MB can be much more useful. For this you can feed any “size” into this function and get what it seems like a prettier version of it:

select pg_size_pretty(pg_relation_size('customer'));

The definitive guide to Postgres functions

If you want to manipulate your data in basic ways chances are Postgres can do it for you, and it doesn't require plpgsql. So next time you're wondering "Can I do regular expressions directly on my data with a function?" and you know the answer is probably yes, best to check the PostgreSQL docs.

If you want a quick place to play around with the funtions above, check out our Postgres Playgroud.