Postgres: CSV file as a table using FDW

file_fdw__csv.sql

-- Installs "file_fdw" extension and creates foreign table to work with data from CSV file.
-- See also the comment below which helps to automate the process for Google Spreadsheets
-- Another option would be using Multicorn for Google Spreadsheets, but it requires additional steps  
-- (see https://wiki.postgresql.org/wiki/Foreign_data_wrappers).

create extension file_fdw;
create server "import" foreign data wrapper file_fdw;

create foreign table "table1" (
  col1 text,
  col2 text,
  ...
) server "import" options (
  filename '/path/to/file.csv',
  format 'csv',
  header 'on'
);

--

The full procedure for a public Google Spreadsheet (list of Pokemons as an example):

0. Install file_fdw to your database (run in psql):

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

1. Download as csv (run in bash):

wget -O pokemons.csv\
  "https://docs.google.com/spreadsheets/d/14mIpk_ceBWVnjc1cPAD7AWeXkE8-t729pcLqwcX_Iik/export?format=csv"

2. Get rid of \r:

sed -n '2,$p' pokemons.csv

3. Take the first line of csv file and use it as the list of columns for FDW table (run in bash, and don't forget to change the path to csv file!):

psql -c "create foreign table pokemon_imported($(
  head -n 1 pokemons.csv  | tr '[:upper:]' '[:lower:]' | sed -e 's/[^,a-zA-Z0-9\r]/_/g' | sed -e 's/,/ text, /g'
) text) server import options (filename '$(pwd)/pokemons.csv', format 'csv', header 'on')"

4. Now it should work, test it (in psql):

select *
from pokemon_imported
order by max_hp::int desc
limit 5;