Search This Blog

Monday, January 13, 2014

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...


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');

No comments:

Post a Comment