3 Essential PostgreSQL Functions You Deserve To Know

Author:Murphy | View: 25772 | Time: 2025-03-23 18:34:49

PostgreSQL

Photo by Sunder Muthukumaran on Unsplash

Postgres is one of the most famous relational database management systems (RDBMS) out there. And for a good reason.

I won't even go over all the advantages of using it, but I'll instead use a really well-explained conclusion I've found on the Internet:

"PostgreSQL has gained a great reputation as a powerful, feature-rich choice for relational data. Valuing stability, functionality, and standards conformance, PostgreSQL checks all of the right boxes for many projects."[1]

Having been using it daily in my job, I've discovered nice and practical functions that aren't easily found on the web.

Previously, I would retrieve all the necessary data from Postgres and then manipulate it with Python and Pandas. But not anymore, not with the three functions I'm about to share with you.

Before getting into the coolest ones, let me share some more common ones. Just to put us all on the same level.

I'll separate the content of this post into three categories: Basic Functions, Alternative Functions (to the basic ones), and Cool Functions.

Basic Functions

The goal here is to touch a little bit on the basics. These aren't necessarily cool functions, but anything essential is rarely cool.

Sql">SELECT now();

This retrieves your system's time. No big deal.

SELECT name || ' ' || last_name AS full_name
FROM human

This is a simple concatenation of two strings. I'm not using any function here.

SELECT CONCAT(name, ' ', last_name) AS full_name
FROM human

This yields the exact same result, but this time using a Postgres function. Again, really simple and basic-level Postgres.

SELECT ARRAY_AGG(name) AS all_names
FROM human

This is my default go-to when trying to aggregate some data: it puts it all into an array. Simple right?

I think these are some of the must-know Postgres functions. I've obviously left a lot behind, like all the math functions like round, floor, abs, trunc, log; or aggregation functions like min, max, count…

Alternative Functions

While the previous functions are perfectly fine, having alternatives is always good.

For example, we take our concatenations to the next level by using FORMAT:

SELECT 
  FORMAT('%s %s has %s pets', 
         first_name, 
         last_name, 
         n_pets) AS human_pet_info
FROM human

Using the %-format can make our strings better. I've shown a simple example but it can get much more complex and better.

Moving on to the aggregation part, using arrays is fine but we'll sometimes prefer to use other methods. Maybe a string? Or a JSON? PostgreSQL has you covered:

SELECT 
  ARRAY_AGG(name) AS array_names,
  STRING_AGG(name) AS string_names,
  JSON_AGG(name) AS json_names
FROM human

Well, enough intro. Let's go over the cool stuff. The functions you probably never knew Postgres was able to perform.

Cool Functions

This is the important section within this story. What I'm sharing next are three less-known PostgreSQL functions I've been using as a data analyst.

String Similarity

If your job consists in working with data, such as a data scientist or analyst, you might have tried to study the similarity of two strings in the past. I have and Python was my go-to every time I had to do it.

I certainly didn't know better.

But a workmate shared the pg_trgm module with me, which provides functions for determining the similarity of alphanumeric text based on trigram matching[2].

A trigram is a group of three consecutive characters taken from a string. And we can find how similar two strings are by finding how many trigrams they share. Simple yet effective.

Here are some examples:

SELECT 
  similarity('Make', 'Take'),
  word_similarity('Make', 'Take'),
  strict_word_similarity('Make', 'Take');

similarity | word_similarity | strict_word_similarity
-----------+-----------------+------------------------
      0.25               0.4                    0.25

There are different functions and they compute things differently. It will depend on the case but I highly recommend you to read the documentation if you're someone who's into the NLP world, or work with text data.

Similarity Matches— Photo by Anne Nygård on Unsplash

String Matching

Another feature I want to share, not so advanced though, is related to the string matching features.

We all know the LIKE clause. However, what if we want to try to match against multiple patterns? If we use it, we'd have something like:

SELECT *
FROM human
WHERE 
   country LIKE 'United%' 
OR country LIKE '%Republic%'

But we can make it shorter. The best and most known alternative is using regexp:

SELECT *
FROM human
WHERE 
   country ~ '^United|Republic'

And there's the alternative for those that prefer the LIKE or don't want to learn to use regex:

SELECT *
FROM human
WHERE 
   country SIMILAR TO 'United%|'%Republic%'

A String of Matches (kind of)— Photo by Jamie Street on Unsplash

Pivot Tables

Back in my newbie days, Pandas or Excel would be my go-to when I wanted to generate a pivot table. But again, PostgreSQL has its own functionality implemented.

The CROSSTAB function from the tablefunc module[3] – and its derivates— literally generate Pivot tables in the PostgreSQL database. All it needs as an input is a SQL query as a text.

The SQL parameter is the statement that produces the source set of data. This statement has to have only 3 columns: one row_name column, one category column, and one value column.

Using the example from the official documentation, the output of the query could be something like this:

 row_name    cat    value
----------+-------+-------
  row1      cat1    val1
  row1      cat2    val2
  row1      cat3    val3
  row1      cat4    val4
  row2      cat1    val5
  row2      cat2    val6
  row2      cat3    val7
  row2      cat4    val8

And we would create the pivot table with:

SELECT 
  * 
FROM 
  crosstab(
    'SELECT 
        row_name, 
        cat, 
        value  
    FROM 
        sample_table'
  ) AS ct(row_name text, category_1 text, category_2 text);

And it would yield something like:

           <== value  columns  ==>
 row_name   category_1   category_2
----------+------------+------------
  row1        val1         val2
  row2        val5         val6

Useful, huh?

The data before being pivoted – Photo by Mika Baumeister on Unsplash

Conclusion

PostgreSQL is widely used and it comes as no surprise. However, only a few really tap into its hidden, less-known advantages. It has amazing functionalities that allow us to do most of our jobs on Postgres without the need of transitioning to Python or any other language if we don't want to.

We just have to find them.

While the ones I shared are overly specific, I believe they could be of great help to a lot of people out there because at some point we've all done a pivot table, for example, or any data scientist working with language models has had to work with string similarity.

And there are many more. This post aims to awaken the curiosity within you to seek other hidden gems of this amazing language.

Thanks for reading the post! 

I really hope you enjoyed it and found it insightful.
Follow me for more content like this one, it helps a lot!

@polmarin

If you'd like to support me further, consider subscribing to Medium's Membership through the link you find below: it won't cost you any extra penny but will help me through this process.