Ever have the need to create a holding table say spreadsheet data with say 100 columns. You need to create a table to hold this stuff. Or perhaps you were feeling in a sadist mood and wanted to abuse your PostgreSQL database to see how many columns you can create in a table of a specific data type.
Here is a quick script to do it:
One of the main features I love about PostgreSQL is its array support. This is a
feature you won't find in most relational databases, and even databases that support some variant
of it, don't allow you to use it as easily. It is one of the features that makes building
aggregate functions wicked easy in PostgreSQL with no messy compiling required.
Aside from building aggregate functions, it has some other common day uses.
In this article, I'll cover two common ways we use them which I will refer to as the ANY
and Contains tricks.
I like to think of this approach as YeSQL programming style: how SQL can be augmented by more complex data types and index retrieval mechanisms.
Arrays and many other data types (spatial types, keyvalue (hstore), ltree etc) are far from relational structures, yet we can query them easily with SQL and can even relate them.