In our previous Fun with SQL post on the Citus Data blog, we covered window functions. Window functions are a special class of function that allow you to grab values across rows and then perform some logic. By jumping ahead to window functions, we missed so many of the other handy functions that exist within Postgres natively. There are, in fact, several hundred built-in functions, and when needed, you can also create your own user defined functions (UDFs) if you need something custom. Today, we’re going to walk through just a small sampling of SQL functions that can be extremely handy in PostgreSQL.
ArraysFirst, arrays are a first-class datatype within Postgres. You can have an array of text or an array of numbers. Personally, I love using arrays when dealing with category tags. You can also index arrays, which can make querying extremely fast, but even if you’re not putting arrays directly into your database, you may want to build up arrays within your query.
