Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Improve Airtable docs #170

Open
pangolingo opened this issue Oct 10, 2023 · 6 comments · May be fixed by #388
Open

Improve Airtable docs #170

pangolingo opened this issue Oct 10, 2023 · 6 comments · May be fixed by #388
Labels
documentation Improvements or additions to documentation

Comments

@pangolingo
Copy link

pangolingo commented Oct 10, 2023

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:

Screen Shot 2023-10-10 at 1 19 28 PM

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
@pangolingo pangolingo added the documentation Improvements or additions to documentation label Oct 10, 2023
@imor
Copy link
Contributor

imor commented Oct 11, 2023

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
Copy link
Author

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

@jacobwilson41
Copy link

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

@lij55
Copy link

lij55 commented Jan 19, 2024

  • 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
Copy link

lij55 commented Jan 19, 2024

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

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

@datoiashvili713
Copy link

so also image/attachments are not supported

devin-ai-integration bot added a commit that referenced this issue Dec 16, 2024
- Add supported PostgreSQL data types and their Airtable mappings
- Document column naming rules and case sensitivity
- Add examples for handling arrays and linked records
- Clarify null value handling

Fixes #170

Co-Authored-By: [email protected] <[email protected]>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
documentation Improvements or additions to documentation
Projects
None yet
Development

Successfully merging a pull request may close this issue.

5 participants