Gmail Contacts (Outlook CSV format) in Postgres, Import, Sampling and Export

1. Gmail Contacts (Outlook CSV format) as FDW table in Postgres, Import

create extension file_fdw;
create server csvfile foreign data wrapper file_fdw;

create foreign table contacts_exported(
  "First Name" text not null default '',
  "Middle Name" text not null default '',
  "Last Name" text not null default '',
  "Title" text not null default '',
  "Suffix" text not null default '',
  "Initials" text not null default '',
  "Web Page" text not null default '',
  "Gender" text not null default '',
  "Birthday" text not null default '',
  "Anniversary" text not null default '',
  "Location" text not null default '',
  "Language" text not null default '',
  "Internet Free Busy" text not null default '',
  "Notes" text not null default '',
  "E-mail Address" text not null default '',
  "E-mail 2 Address" text not null default '',
  "E-mail 3 Address" text not null default '',
  "Primary Phone" text not null default '',
  "Home Phone" text not null default '',
  "Home Phone 2" text not null default '',
  "Mobile Phone" text not null default '',
  "Pager" text not null default '',
  "Home Fax" text not null default '',
  "Home Address" text not null default '',
  "Home Street" text not null default '',
  "Home Street 2" text not null default '',
  "Home Street 3" text not null default '',
  "Home Address PO Box" text not null default '',
  "Home City" text not null default '',
  "Home State" text not null default '',
  "Home Postal Code" text not null default '',
  "Home Country" text not null default '',
  "Spouse" text not null default '',
  "Children" text not null default '',
  "Manager's Name" text not null default '',
  "Assistant's Name" text not null default '',
  "Referred By" text not null default '',
  "Company Main Phone" text not null default '',
  "Business Phone" text not null default '',
  "Business Phone 2" text not null default '',
  "Business Fax" text not null default '',
  "Assistant's Phone" text not null default '',
  "Company" text not null default '',
  "Job Title" text not null default '',
  "Department" text not null default '',
  "Office Location" text not null default '',
  "Organizational ID Number" text not null default '',
  "Profession" text not null default '',
  "Account" text not null default '',
  "Business Address" text not null default '',
  "Business Street" text not null default '',
  "Business Street 2" text not null default '',
  "Business Street 3" text not null default '',
  "Business Address PO Box" text not null default '',
  "Business City" text not null default '',
  "Business State" text not null default '',
  "Business Postal Code" text not null default '',
  "Business Country" text not null default '',
  "Other Phone" text not null default '',
  "Other Fax" text not null default '',
  "Other Address" text not null default '',
  "Other Street" text not null default '',
  "Other Street 2" text not null default '',
  "Other Street 3" text not null default '',
  "Other Address PO Box" text not null default '',
  "Other City" text not null default '',
  "Other State" text not null default '',
  "Other Postal Code" text not null default '',
  "Other Country" text not null default '',
  "Callback" text not null default '',
  "Car Phone" text not null default '',
  "ISDN" text not null default '',
  "Radio Phone" text not null default '',
  "TTY/TDD Phone" text not null default '',
  "Telex" text not null default '',
  "User 1" text not null default '',
  "User 2" text not null default '',
  "User 3" text not null default '',
  "User 4" text not null default '',
  "Keywords" text not null default '',
  "Mileage" text not null default '',
  "Hobby" text not null default '',
  "Billing Information" text not null default '',
  "Directory Server" text not null default '',
  "Sensitivity" text not null default '',
  "Priority" text not null default '',
  "Private" text not null default '',
  "Categories" text not null default ''
) server csvfile options ( filename '/home/nikolay/contacts.csv', format 'csv' );

2. Generate Gmail Sample Data

create table gmail_sample(
  "First Name" text not null default '',
  "Middle Name" text not null default '',
  "Last Name" text not null default '',
  "Title" text not null default '',
  "Suffix" text not null default '',
  "Initials" text not null default '',
  "Web Page" text not null default '',
  "Gender" text not null default '',
  "Birthday" text not null default '',
  "Anniversary" text not null default '',
  "Location" text not null default '',
  "Language" text not null default '',
  "Internet Free Busy" text not null default '',
  "Notes" text not null default '',
  "E-mail Address" text not null default '',
  "E-mail 2 Address" text not null default '',
  "E-mail 3 Address" text not null default '',
  "Primary Phone" text not null default '',
  "Home Phone" text not null default '',
  "Home Phone 2" text not null default '',
  "Mobile Phone" text not null default '',
  "Pager" text not null default '',
  "Home Fax" text not null default '',
  "Home Address" text not null default '',
  "Home Street" text not null default '',
  "Home Street 2" text not null default '',
  "Home Street 3" text not null default '',
  "Home Address PO Box" text not null default '',
  "Home City" text not null default '',
  "Home State" text not null default '',
  "Home Postal Code" text not null default '',
  "Home Country" text not null default '',
  "Spouse" text not null default '',
  "Children" text not null default '',
  "Manager's Name" text not null default '',
  "Assistant's Name" text not null default '',
  "Referred By" text not null default '',
  "Company Main Phone" text not null default '',
  "Business Phone" text not null default '',
  "Business Phone 2" text not null default '',
  "Business Fax" text not null default '',
  "Assistant's Phone" text not null default '',
  "Company" text not null default '',
  "Job Title" text not null default '',
  "Department" text not null default '',
  "Office Location" text not null default '',
  "Organizational ID Number" text not null default '',
  "Profession" text not null default '',
  "Account" text not null default '',
  "Business Address" text not null default '',
  "Business Street" text not null default '',
  "Business Street 2" text not null default '',
  "Business Street 3" text not null default '',
  "Business Address PO Box" text not null default '',
  "Business City" text not null default '',
  "Business State" text not null default '',
  "Business Postal Code" text not null default '',
  "Business Country" text not null default '',
  "Other Phone" text not null default '',
  "Other Fax" text not null default '',
  "Other Address" text not null default '',
  "Other Street" text not null default '',
  "Other Street 2" text not null default '',
  "Other Street 3" text not null default '',
  "Other Address PO Box" text not null default '',
  "Other City" text not null default '',
  "Other State" text not null default '',
  "Other Postal Code" text not null default '',
  "Other Country" text not null default '',
  "Callback" text not null default '',
  "Car Phone" text not null default '',
  "ISDN" text not null default '',
  "Radio Phone" text not null default '',
  "TTY/TDD Phone" text not null default '',
  "Telex" text not null default '',
  "User 1" text not null default '',
  "User 2" text not null default '',
  "User 3" text not null default '',
  "User 4" text not null default '',
  "Keywords" text not null default '',
  "Mileage" text not null default '',
  "Hobby" text not null default '',
  "Billing Information" text not null default '',
  "Directory Server" text not null default '',
  "Sensitivity" text not null default '',
  "Priority" text not null default '',
  "Private" text not null default '',
  "Categories" text not null default ''
);

with gen as (
  select generate_series as num
  from generate_series(1, 10000)
)
insert into gmail_sample("First Name", "Last Name", "E-mail Address")
select
  'Test',
  'Number' || num::text,
  'myaccount+' || num::text || '@my_gmail_backed_domain.com' -- all emails to such addresses will go to 'myaccount@' mailbox at GMail
from gen
;

3. Export to CSV

COPY gmail_sample to '/tmp/gmail_sample.csv' delimiters','; -- lacks header! copy it manually