Importing data with foreign data wrappers on PostgreSQL

Foreign data wrappers is a new feature of PostgreSQL 9.1. It serves to read data from external data sources. The data sources can be...

Oh! http://wiki.postgresql.org/wiki/Foreign_data_wrappers

The list is extense! but will need to compile the extension from source, in a Linux server is easy. In Windows you ´ll need MinGW.

There´s 2 FDW which comes with PostgreSQL 9.3 for Windows: the file_fdw and postgres_fdw.



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

create foreign table foo_csv (
field1 varchar(13),
field2 varchar(50),
...

) server files options(format 'csv', encoding 'windows-1252', header 'true', delimiter ';', quote '"');



For Postgres_fdw here a example:

create extension postgres_fdw;

create server pgserver foreign data wrapper postgres_fdw options( host 'myhost', port '5432', dbname 'mydb');

create user mapping for myuser server pgserver options(user 'postgres', password '##@%¨$&¨'¨);

create foreign table my_foreign_table (
  id int,
  datet timestamp,
  ...
) server pgserver;

 

For copying data to a real table you can give a simple command:
create table foo as (select * from foo_csv where field2='E467');