Go further with Airtable using Postgres Arrays

Airtable is a bit of a tease.

While it looks and feels like a database, you can't query it like a database.

That's why I'm helping build Sequin.

In this guide, you'll first connect Airtable to Postgres using Sequin. Then, you'll unlock your Airtable data with some killer PostgreSQL array methods.

Airtable and Postgres Arrays

Airtable's power lies in its flexibility. Thanks to Airtable's friendly UI, I can add new tables, new columns, or change the format of a column at will. It's great.

For example, let's say I have a Products table with a column that contains the product's image. But what happens later when I decide to keep more than one image per product? I just drag another image into the row! No need to migrate the column or my previous images.

To reflect this flexibility, when Airtable data is pulled into Postgres via Sequin, that data is often represented as Postgres Arrays. So when a new image pops into the Products table, your Postgres table can support it immediately, without migrations or changes to existing queries. (The new image is simply another value in the array.)

Many field types in Airtable can contain multiple values: attachments, multiple select, lookups, and collaborators. With Sequin, these field types will all end up in Postgres as arrays. These might not be as familiar to you, but arrays are first-class in Postgres.

In a couple minutes you'll get the hang of Postgres Arrays and never look at Airtable the same way.

Wait...a growing small business needs your help?

To make this guide more interesting, imagine you are helping a small business owner grow their business.

They've been using Airtable to catalog products, purchase orders, and sales orders. The business owner wants their Airtable base to remain simple and easy to use - but she needs more insight to grow.

To play along with this story, take a look at the small business's inventory tracker base and add it to your workspace by clicking Copy base in the top right corner:

I’m going to assume that if you’ve made it this far, you already know Airtable is the bees knees and know how to set it up.

Connect Airtable to Postgres

Now, let’s get your Airtable base into Postgres using Sequin:

1. Go to https://app.sequin.io/signup and create an account.
2. Connect your base to Sequin using the tutorial or check out the Quickstart guide.
3. In a couple seconds, you’ll be provisioned a Postgres database and Sequin will begin filling it with all your Airtable data.
4. Now connect to your new Postgres database with any standard SQL client. Tools like TablePlus and PopSQL are great options (I’ll be using TablePlus).

How Airtable looks in Postgres

Take a look at your Postgres schema.

First, you’ll see that all your Airtable table names have been converted to snake_case:

Second, you’ll notice that any field type in Airtable that could hold multiple values (like lookup, multiple select , and attachment) have been stored in columns of type text[]. text[] is an array of text fields:

Arrays in Postgres are delimited with curly braces {}, instead of the common square bracket [] you are probably familiar with. (It will keep you on your toes.)

Airtable and Postgres ARRAY

Before you start solving real problems for the small business you are assisting, let's just get comfortable with Postgres arrays.

For example, in your Inventory Tracker base, you can see that the Warehouse Locations table contains a lookup field where you can select which products are associated to a given warehouse location:

You can select multiple products for each location. So in your synced Postgres database, the warehouse_locations table shows the product_inventory column as an array type containing multiple record_ids representing each selected product.

Let's play with the product_inventory arrays.

Selecting the product_inventory column from the warehouse_locations table is simple:

SELECT product_inventory FROM warehouse_locations;

This will return all the product_inventory as a column full of arrays:

This is okay, but let’s say you want to select the first product listed on every warehouse shelf.

To do so, you need to access the values in the array. Postgres arrays are base-1 indexed (another fun way to keep you on your toes) - so to access the first value in the array we use product_inventory[1] with square brackets. Like so:

SELECT product_inventory[1] FROM warehouse_locations;

Now you'll see the record_id for the first product on each shelf.

Again, when using Postgres with Airtable, any field that can contain multiple values will be an array. So this simple approach of extracting the first value of the array comes in handy.

As a practical example, in the Product Inventory table in your Airtable base, you can see that the Product Name column is a multi-select field. This is probably better represented as a text field - but then you don't get the pretty colors 😌:

To query for just the text of the product name, you'll use the array syntax again:

SELECT product_name[1] FROM product_inventory;

This will return a clean list of product names:

unnest()

With a grasp of how Postgres arrays work, let's begin to use SQL to extract some insights.

For example, let’s say you want to see which product colors are most appealing to customers. So you want to pull the list of colors for products that sell well. Let's say "sell well" for this small business means more than 5 sales.

To get this data, you need to get a list of colors from the product_inventory table where the units sold are greater than 5:

SELECT colors FROM product_inventory WHERE units_sold[1]::integer > 5;

Tip: The double colon :: next to WHERE units_sold:: is short-hand for CAST. Casting allows you to change the data type of a field - in this case from text to integer - so that you can evaluate the > 5 statement. You'll use CAST often with your Airtable data as Sequin works to support more data types beyond text, text[], and bool;

The results from this query are a little convoluted because all the colors return in arrays. Some of the colors are duplicated across rows so you can't really tell what the most popular colors are:

It would be nice to see each unique color in a clear list.

unnest() allows you to un-nest each item of an array onto its own row:

SELECT
    DISTINCT UNNEST(colors) AS "Color",
    count(colors) AS "Frequency"
FROM product_inventory
WHERE units_sold[1]::integer > 5
GROUP BY "Color"
ORDER BY "Frequency" DESC;

Here, you use DISTINCT in conjunction with unnest() to get a clean list of the most popular colors. You are then using AS to give the column an easy to understand name - in this case "Color".

Then you use count(colors) (again, using AS to alias the column to the name "Frequency") to calculate how frequently each unique color is appearing. count(colors) works with GROUP BY so that count(colors) is tabulated against each unique Color.

Lastly, you use ORDER BY to sort the table so that the most popular color is at the top.

It looks like "Desert Brown" is a common color amongst the most popular products:

ANY

Building on your new insights around the popularity of "Desert Brown", you now want to understand which products are available in this desirable color.

We can use ANY to quickly check if a product's color array contains our desired color - in this case "Desert Brown"

SELECT product_name[1]
FROM product_inventory
WHERE 'Desert Brown' = ANY(colors);

Okay, some ideas are beginning to come together. Let’s unpack (dare I say unnest) this query.

First, because product_name is represented in your database as an array (you'll recall that product name is a multiple select in our Airtable base) we use product_name[1] to select the first item in the array. In this case, the name of the product.

Then, the WHERE clause is checking that the product colors array (colors) contains a string matching Desert Brown.

This produces a list of all the products with a "Desert Brown" color option:

Airtable and Postgres JOIN

Now, you're ready for some heavy lifting. Warehouse optimization.

It would be nice to see how much inventory is on each shelf in the warehouse. This might help the business owners use their inventory space more efficiently.

The first tool you might reach for is an Airtable lookup. But it won't render exactly what you want:

In Airtable, you'd need to resort to writing complex functions. But now that your data is in Postgres, you can just use SQL to express what you want in a few lines.

To do so, you can use ANY with a JOIN to see how much inventory is on each shelf:

SELECT
    warehouse_locations.name AS "Shelf",
    SUM((product_inventory.units_ordered[1]::INTEGER) - (product_inventory.units_sold[1]::INTEGER)) AS "Inventory"
FROM warehouse_locations
JOIN product_inventory
    ON product_inventory.id = ANY (warehouse_locations.product_inventory)
GROUP BY warehouse_locations.name
ORDER BY warehouse_locations.name ASC;

You've already worked with most of the elements in this query, so let's highlight how this is all working together.

First, you use CAST to turn units_ordered and units_sold into INTEGERS so you can calculate the inventory of each product. You use AS to give each returned column a nice name.

Then, you use ANY to JOIN the available product inventory to each shelf.

Lastly, as with the prior query, you use GROUP BY and ORDER BY to organize and sort the output.

Airtable and Postgres UNION

There's one more SQL operator I find myself reaching for all the time: UNION. (It's not strictly related to Postgres arrays, so let's call this a bonus.)

UNION is a particularly helpful tool when working with Airtable because it can help you pull together similar data from across different tables.

As an example, perhaps you want to see all the store's product images in one place to prepare for a new marketing campaign. To do so, you'll need to pull product images from both the Product Inventory table as well as from the Purchase Orders table.

In SQL this is no big deal:

SELECT product_inventory.images[1] FROM product_inventory
UNION
SELECT purchase_orders.image[1] FROM purchase_orders;

Now, in one list you have access to all the product images appearing across the system. To do this natively in Airtable you would have needed to create a unique Images table with lookups...and restructure the entire base.

Best of both worlds

When you combine Airtable with a Postgres database, you get all the amazing capabilities of Airtable plus the flexibility and reliability of PostgreSQL.

Now you can query, integrate and build on your data with the ease of Airtable and the power of SQL.