Go further with Airtable using Postgres Arrays
Tags: postgres, airtable, sql, array
Published: 2020-12-16 02:09:24

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](https://sequin.io/).

In this guide, you'll first connect Airtable to Postgres using [Sequin](https://sequin.io/). 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](https://www.postgresql.org/docs/9.1/arrays.html). 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](https://airtable.com/shrV2tLjq2FbrRtO2) and add it to your workspace by clicking **Copy base** in the top right corner:

![Copy base](https://dev-to-uploads.s3.amazonaws.com/i/kxrppizncs51zvxer09o.png)

> 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](https://sequin.io/):

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](https://docs.sequin.io/get-started).
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](https://docs.sequin.io/reference#tools-and-applications) with any standard SQL client. Tools like [TablePlus](https://tableplus.com/) and [PopSQL](https://popsql.com/) 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:

![Schema](https://dev-to-uploads.s3.amazonaws.com/i/jbhe7abo1qv16z2ss39q.png)

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](https://dev-to-uploads.s3.amazonaws.com/i/o66hrs1d4ig9da3vakb4.png)

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:

![Product Inventory](https://dev-to-uploads.s3.amazonaws.com/i/c5h7bfvjq1ox80w0goc7.png)

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_id`s representing each selected product.

![Tableplus](https://dev-to-uploads.s3.amazonaws.com/i/m0gp5tg3w42wwdndliwq.png)

Let's play with the `product_inventory` arrays.

Selecting the `product_inventory` column from the `warehouse_locations` table is simple:

```sql
SELECT product_inventory FROM warehouse_locations;
```

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

![Product Inventory Arrays](https://dev-to-uploads.s3.amazonaws.com/i/nqx49bry989auu5eefq1.png)

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:

```sql
SELECT product_inventory[1] FROM warehouse_locations;
```

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

![Product inventory](https://dev-to-uploads.s3.amazonaws.com/i/k1m0zz9rmtpzfgruzw5g.png)

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 😌:

![Product Name](https://dev-to-uploads.s3.amazonaws.com/i/weuypju51f0f3kkkvig5.png)

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

```SQL
SELECT product_name[1] FROM product_inventory;
```

This will return a clean list of product names:

![Product Name Output](https://dev-to-uploads.s3.amazonaws.com/i/ghbi9riq6h906rcxa710.png)

## `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:

```sql
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:

![Color arrays](https://dev-to-uploads.s3.amazonaws.com/i/oquj3c8jgup08tvvwtth.png)

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:

```sql
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:

![colors](https://dev-to-uploads.s3.amazonaws.com/i/nzgxk08utnfz2wnkr4a4.png)

## `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"

```sql
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:

![Desert Brown Colors](https://dev-to-uploads.s3.amazonaws.com/i/pvw7uzohn6wcovtza736.png)

## 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:

![Airtable mess](https://dev-to-uploads.s3.amazonaws.com/i/gmol2ltpli33g5tyg7hj.png)

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:

```sql
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.

![Shelf inventory](https://dev-to-uploads.s3.amazonaws.com/i/7bsef3mzpky8bdm6z2oz.png)

## 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:

```sql
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.

![All the images](https://dev-to-uploads.s3.amazonaws.com/i/7f5kswfo7x35r8b4diwk.png)

## 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.