Arrays are an essential part of software development. Among modern databases, however, only PostgreSQL has dedicated datatypes, functions, and indexes built to efficiently work with arrays. In fact, I think arrays are a hidden superpower of PostgreSQL, fully utilized as a data type, usable in pl/pgSQL, and essential for a myriad of built-in functions to process data more efficiently.

In this session I’ll discuss the array datatype in PostgreSQL along with things to consider before using it in a schema model. After laying the foundation for how arrays can be created, queried, and modified, I’ll demonstrate a handful of functions that can be used to process strings, JSON, numerics… and even Wordle emoji’s! The session will finish by demonstrating how using arrays in dynamic SQL can create more efficient multi-value inserts in some circumstances.

By the end of this session, you will understand how arrays work in PostgreSQL and how powerful they can be as a datatype, logic variable, and in processing other data.