Airtable

1
create extension if not exists wrappers with schema extensions;

1
create foreign data wrapper airtable_wrapper
2
  handler airtable_fdw_handler
3
  validator airtable_fdw_validator;

1
-- Save your Airtable API key in Vault and retrieve the created `key_id`
2
select vault.create_secret(
3
  '<Airtable API Key or PAT>', -- Airtable API key or Personal Access Token (PAT)
4
  'airtable',
5
  'Airtable API key for Wrappers'
6
);

1
create schema if not exists airtable;

1
create foreign table airtable.my_foreign_table (
2
  message text
3
  -- other fields
4
)
5
server airtable_server
6
options (
7
  base_id 'appXXXX',
8
  table_id 'tblXXXX'
9
);

1
create foreign table airtable.customers (
2
  -- Simple lowercase match
3
  id text,
4
5
  -- Airtable field has a space: "First Name"
6
  "First Name" text,
7
8
  -- Airtable field is mixed case: "LastName"
9
  "LastName" text,
10
11
  -- Airtable field has special characters: "Price ($)"
12
  "Price ($)" numeric,
13
14
  -- Airtable field is all caps: "SKU"
15
  "SKU" text
16
)
17
server airtable_server
18
options (
19
  base_id 'appXXXX',
20
  table_id 'tblXXXX'
21
);

1
-- Create foreign table with multiple select field
2
create foreign table airtable.products (
3
  id text,
4
  name text,
5
  "Tags" jsonb
6
)
7
server airtable_server
8
options (
9
  base_id 'appXXXX',
10
  table_id 'tblXXXX'
11
);
12
13
-- Example data in tags: ["Electronics", "Sale", "Featured"]
14
15
-- Query: Find products with a specific tag
16
select name, "Tags"
17
from airtable.products
18
where "Tags" ? 'Sale';
19
20
-- Query: Find products with any of several tags
21
select name, "Tags"
22
from airtable.products
23
where "Tags" ?| array['Electronics', 'Furniture'];
24
25
-- Query: Extract tags as text array for processing
26
select name, jsonb_array_elements_text("Tags") as tag
27
from airtable.products;

1
-- Create foreign table with linked records
2
create foreign table airtable.orders (
3
  id text,
4
  "Order Number" text,
5
  "Customer" jsonb
6
)
7
server airtable_server
8
options (
9
  base_id 'appXXXX',
10
  table_id 'tblXXXX'
11
);
12
13
-- Example data in customer ids: ["recABC123", "recDEF456"]
14
15
-- Query: Find orders linked to a specific customer
16
select order_number, "Customer"
17
from airtable.orders
18
where "Customer" ? 'recABC123';
19
20
-- Query: Count linked records
21
select order_number, jsonb_array_length("Customer") as customer_count
22
from airtable.orders;

1
-- Create foreign table with attachments
2
create foreign table airtable.documents (
3
  id text,
4
  title text,
5
  "Attachments" jsonb
6
)
7
server airtable_server
8
options (
9
  base_id 'appXXXX',
10
  table_id 'tblXXXX'
11
);
12
13
-- Example data in attachments:
14
-- [{"id": "attXXX", "url": "https://...", "filename": "doc.pdf", "size": 12345, "type": "application/pdf"}]
15
16
-- Query: Get first attachment URL
17
select title, "Attachments"->0->>'url' as first_file_url
18
from airtable.documents;
19
20
-- Query: Get all attachment filenames
21
select title, jsonb_path_query("Attachments", '$[*].filename') as filename
22
from airtable.documents;
23
24
-- Query: Find documents with PDF attachments
25
select title
26
from airtable.documents
27
where exists (
28
  select 1 from jsonb_array_elements("Attachments") as f
29
  where f->>'type' = 'application/pdf'
30
);

1
-- Create foreign table with user fields
2
create foreign table airtable.tasks (
3
  id text,
4
  "Task Name" text,
5
  "Assigned To" jsonb,
6
  "Created By" jsonb
7
)
8
server airtable_server
9
options (
10
  base_id 'appXXXX',
11
  table_id 'tblXXXX'
12
);
13
14
-- Example data in assigned_to: {"id": "usrXXX", "email": "user@example.com", "name": "John Doe"}
15
16
-- Query: Get assignee email
17
select "Task Name", "Assigned To"->>'email' as assignee_email
18
from airtable.tasks;
19
20
-- Query: Find tasks assigned to specific user
21
select "Task Name"
22
from airtable.tasks
23
where "Assigned To"->>'email' = 'john@example.com';

1
-- Before (returns NULL because Airtable field is "Full Name"):
2
full_name text
3
4
-- After (correctly maps to Airtable field):
5
"Full Name" text

1
-- Airtable field is "ProductID" not "productid"
2
"ProductID" text

1
-- Multiple select returns JSONB, not TEXT
2
tags jsonb  -- Correct
3
tags text   -- Incorrect: will cause errors

1
-- Check what the JSONB actually contains
2
select tags from airtable.products limit 1;
3
4
-- Then build your query based on actual structure

1
create foreign table airtable.filtered_products (
2
  -- columns...
3
)
4
server airtable_server
5
options (
6
  base_id 'appXXXX',
7
  table_id 'tblXXXX',
8
  view_id 'viwYYYY'  -- Pre-filtered view
9
);

1
create foreign table airtable.products (
2
  id text,
3
  name text,
4
  description text,
5
  price numeric,
6
  in_stock boolean,
7
  created_at timestamp
8
)
9
server airtable_server
10
options (
11
  base_id 'appTc3yI68KN6ukZc',
12
  table_id 'tbltiLinE56l3YKfn'
13
);
14
15
-- Query all products
16
select * from airtable.products;
17
18
-- Query with filter (applied locally)
19
select name, price
20
from airtable.products
21
where in_stock = true
22
  and price < 100
23
order by price;

1
create foreign table airtable.active_products (
2
  name text,
3
  price numeric
4
)
5
server airtable_server
6
options (
7
  base_id 'appTc3yI68KN6ukZc',
8
  table_id 'tbltiLinE56l3YKfn',
9
  view_id 'viwY8si0zcEzw3ntZ'
10
);
11
12
select * from airtable.active_products;

1
create foreign table airtable.tagged_products (
2
  id text,
3
  name text,
4
  "Tags" jsonb,
5
  category text
6
)
7
server airtable_server
8
options (
9
  base_id 'appXXXX',
10
  table_id 'tblXXXX'
11
);
12
13
-- Find all products tagged as "Featured"
14
select name, "Tags"
15
from airtable.tagged_products
16
where "Tags" ? 'Featured';
17
18
-- Find products with both "Sale" and "Electronics" tags
19
select name, "Tags"
20
from airtable.tagged_products
21
where "Tags" ?& array['Sale', 'Electronics'];

1
create foreign table airtable.project_tasks (
2
  id text,
3
  task_name text,
4
  status text,
5
  priority text,
6
  due_date date,
7
  assigned_to jsonb,
8
  attachments jsonb,
9
  tags jsonb,
10
  created_at timestamp
11
)
12
server airtable_server
13
options (
14
  base_id 'appXXXX',
15
  table_id 'tblXXXX'
16
);
17
18
-- Find high-priority tasks due this week with assignee details
19
select
20
  task_name,
21
  status,
22
  due_date,
23
  assigned_to->>'name' as assignee,
24
  assigned_to->>'email' as assignee_email,
25
  jsonb_array_length(coalesce(attachments, '[]'::jsonb)) as attachment_count
26
from airtable.project_tasks
27
where priority = 'High'
28
  and due_date between current_date and current_date + interval '7 days'
29
order by due_date;