PostgreSQL CSV file foreign data wrapper

Published on:

Postgres foreign data wrappers allow you to create a table from a CSV file
without actually loading the CSV file into Postgres.

You can then query the data in Postgres but not update it.
Updates you make to the CSV file are immediately seen in Postgres.

Given this file . . .

/pipedream/out/account_contact.csv

"ID","NPE01__ONE2ONECONTACT__C"
"0014B000006TMDTQA4","asdf"
"asdf","0034B000005XhcOQAS"
"0014B000006TMDVQA4","8e8e8e"

You can use csvsql to generate your CREATE TABLE statement for you.

csvsql out/account_contact.csv
CREATE TABLE account_contact (
    "ID" VARCHAR(18) NOT NULL,
    "NPE01__ONE2ONECONTACT__C" VARCHAR(18) NOT NULL
);

Then just tweak it a bit to create your foreign table.

CREATE EXTENSION file_fdw;

CREATE SERVER file_server FOREIGN DATA WRAPPER file_fdw;

CREATE FOREIGN TABLE account_contact (
    "ID" VARCHAR(18) NOT NULL,
    "NPE01__ONE2ONECONTACT__C" VARCHAR(18) NOT NULL
)
SERVER file_server
OPTIONS (
    format 'csv',
    header 'true',
    delimiter ',',
    filename '/pipedream/out/account_contact.csv'
);

Now you can query the table.

select * from account_contact;
         ID         | NPE01__ONE2ONECONTACT__C
--------------------+--------------------------
 0014B000006TMDTQA4 | asdf
 asdf               | 0034B000005XhcOQAS
 0014B000006TMDVQA4 | 8e8e8e
(3 rows)