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.
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?
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,
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
- We calculate two different intervals, to dissect the first:
- We get the start of the month
lower(period)and increase it by 1 month so it's the exact end of the month
- Then we get the timestamp of how much time has elapsed into the month
- Then we subtract the two, leaving us with something similar to:
14 days 3 hrs 5 minutes
- The second interval we get as the full length of the month, so February
28 daysor January
- We extract the epoch of the time interval
EXTRACT ( epoch FROM intervalhere )
- 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.
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 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:
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
If you want a quick place to play around with the funtions above, check out our Postgres Playgroud.
September 21, 2022 •More by this author