Here is a short reference of some powerful queries you now have at your fingertips.
Tip | Use |
---|---|
CAST | Change the data type for different values stored in your database. Airtable Formulas and Lookup Values can be numbers, strings, dates or timestamps. So we store the values as text or text[], respectively. You can use CAST to convert text to INTEGER or DECIMAL or any other PostgreSQL type. |
TO_DATE() | Change the data type from text to Date so that you can easily play with time. |
ARRAY[1] | Airtable fields that can contain multiple items (like multi-select, attachments, and lookup) will be stored as arrays in your database. Postgres uses a base-1 index here. |
unnest() | Use unnest() to put every item in an array onto its own row. |
ANY | Use ANY when doing JOINs on arrays in PostgreSQL. This is very common when joining two tables that were linked with a lookup field in Airtable. |
CAST
text
and text[]
columns, respectively.
Let’s say you want to calculate your total revenue using SQL. Yo do so, you’ll sum up the total_price
for all the purchase orders. Since total price is a formula in Airtable, we record it as type text
in your Sequin database. So to calculate your total revenue, you’ll need to cast the total_price
as an integer:
::
casts the total_price
column to an integer to then calculate the sum.
Answer: Total revenue is \$17,280.
JOIN
with ANY
JOIN
the warehouse_locations
table with the product_inventory
table and calculate the total inventory of all the products on each specific shelf:
ANY
. When performing the JOIN
, there are many products associated with each warehouse location. These values are stored in an array. With ANY
, you can JOIN
on each value in the array.
UNION
UNION
of the product_inventory
table and the purchase_orders
table: