Improve Airtable docs

[pangolingo]

Improve documentation

Link

I referenced these docs: https://supabase.github.io/wrappers/airtable/
(The same as the Supabase.com ones I think: https://supabase.com/docs/guides/database/extensions/wrappers/airtable)

Describe the problem

Here's the trouble I ran into:

• there was no list of supported data types. I had to hunt for that (can't recall where I found it)
• it wasn't clear whether arrays are a supported data type, but trying to use them indicates that they are not. Airtable uses arrays of strings for linked records, and that was important to me
• there was no documentation for how to update a foreign wrapper table
• documentation was unclear about how column names are associated with Airtable's column names. I experimented and found that it accepted "Whatever" as a column name even though that column was not in my Airtable base. I used quoted column names for my Airtable columns with spaces: "Business Name". I'm not sure if that was correct.
• I managed to do a successful query but it returned null values for all the columns in my table (however it did return the correct number of rows). The documentation did not point me to a solution or a way to debug this. At this point I gave up.

Describe the improvement

Add documentation to address my confusion above.

Additional context

Airtable API description of my table:

Some commands I ran (secrets removed):

create extension if not exists wrappers;

create foreign data wrapper airtable_wrapper
  handler airtable_fdw_handler
  validator airtable_fdw_validator;

-- Save your Airtable API key in Vault and retrieve the `key_id`
insert into vault.secrets (name, secret)
values (
  'airtable',
  '<Airtable API Key or PAT>' -- Airtable API key or Personal Access Token (PAT)
)
returning key_id;

create server airtable_server
  foreign data wrapper airtable_wrapper
  options (
    api_key_id '<key_ID>' -- The Key ID from above.
  );

create foreign table my_foreign_table (
  "Business Name" text,
  "City" text,
  "State" text
  --"Whatever" text
  --"Industry" text[],
  -- other fields
)
server airtable_server
options (
  base_id 'appxxxx',
  table_id 'tblxxxx'
);

SELECT 
*
 FROM my_foreign_table;

Result from that SELECT *

Business Name	City	State
null	null	null
null	null	null

[imor]

Thank you @pangolingo for your feedback. We will work on improving the docs. For now I'll share what I know here:

• I need to find out which Airtable fields types are supported.
• Arrays are not supported.
• Updating an Airtable base from the FDW is not yet supported. It is documented in the Create Foreign Table section.

You have also uncovered a bug which I have reported as a separate issue. To workaround this bug, for now do not quote your column names in the create foreign table command. Unfortunately even with this workaround any columns with a space in them will not be usable from the FDW.

[pangolingo]

Thanks for sharing @imor, and for filing that bug!

[jacobwilson41]

Hello! Is there an update on additional supported field types? Or at least a list of the current supported field types?

[lij55]

• Arrays are not supported.

Hi @imor ,
pgrx seems support RawArray type and some array format.
is there any plan for array support for this fdw wrappers? some applications save the embeddings as array of real/double.

[lij55]

impl<T: IntoDatum + FromDatum> IntoDatum for Array<'_, T> 

it seems Array of pgrx support both into and from Datum.

[datoiashvili713]

so also image/attachments are not supported