Today I Learned

hashrocket A Hashrocket project

Getting A Slice Of An Array In PostgreSQL

Postgres has a very natural syntax for grabbing a slice of an array. You simply add brackets after the array declaring the lower and upper bounds of the slice separated by a colon.

> select (array[4,5,6,7,8,9])[2:4];

Notice that the bounds are inclusive, the array index is 1-based, and the array declaration above needs to be wrapped in parentheses in order to not trip up the array slice syntax.

You can also select rectangular slices from two dimensional arrays like so:

> select (array[[1,2,3],[4,5,6],[7,8,9]])[2:3][1:2];
See More #sql TILs
Looking for help? Hashrocket developers believe that data quality is as important as code quality. We enjoy all the challenges of relational databases, from finding the fastest index, to structuring data to fit the needs of an application. We're eager to share our experiences; check out PG Casts, our series of free PostgreSQL screencasts.