Copy selected columns from a csv file into a postgres table

Question

In postgres - I currently create a table and copy data from a csv file (in the following I create in postgres the exact table, which corresponds to my csv file's table - so the same number of columns) - as follows:

CREATE TABLE SupEnh_AGK50kb_K27ac (
    EnhancerID_AGK50kb_K27ac character(80) NOT NULL,
    Status_AGK50kb_K27ac character(15) NOT NULL,
    Enrich_D_AGK50kb_K27ac float,
    Enrich_R_AGK50kb_K27ac float,
    Enrich_LR_AGK50kb_K27ac float,
    Span_D_AGK50kb_K27ac float,
    Span_R_AGK50kb_K27ac float,
    Span_LR_AGK50kb_K27ac float,
    Multiplication_D_AGK50kb_K27ac float,
    Multiplication_R_AGK50kb_K27ac float,
    Multiplication_LR_AGK50kb_K27ac float,
    NumPeaks_D_AGK50kb_K27ac float,
    NumPeaks_R_AGK50kb_K27ac float,
    NumPeaks_LR_AGK50kb_K27ac float,
    PVal_D_AGK50kb_K27ac float,
    PVal_R_AGK50kb_K27ac float,
    Pval_lr_AGK50kb_K27ac float,

    CONSTRAINT AGK50kb_27ac_Key PRIMARY KEY (EnhancerID_AGK50kb_K27ac)
);

COPY SupEnh_AGK50kb_K27ac 
FROM 'G:\CarrollLab\EnhancerAnalysis\AGK_K27ac.KeyFile'
WITH (FORMAT 'csv', DELIMITER E'\t', NULL 'NULL',HEADER);

This procedure works like a charm, but I would like to amend it slightly, so I'll be able to create first a small version of my postgres table - one the holds only the first 5 columns. I then want to load directly from my csv file the first 5 columns. Is there a way to change slightly the code that I provided here so it'll allow me to do what I'm after (and without using a temporary table that would be created on postgres and will hold all columns) ? Thanks a lot, Roy

It might be easier to remove columns from the file with some kind of shell script before COPY — Ihor Romanchenko
– Ihor Romanchenko, Commented Oct 13, 2014 at 19:33
Thanks Igor, So there is no easy solution in postgres for what I'm after?... — Roy
– Roy, Commented Oct 13, 2014 at 19:36

harmic · Accepted Answer · 2014-10-13 23:40:07Z

You could use the file_fdw foreign data wrapper to access the CSV file, which would allow you to use SELECT INTO to query directly from the CSV file into the table. You can then specify exactly the columns you want to keep.

Example: Suppose I have this text file:

1,"apple","banana"
2,"pear","orange"

and I only want to include the first two columns in my table:

-- Do this once only per database, to create the extension and define the fdw server
CREATE EXTENSION file_fdw;
CREATE SERVER csvfile FOREIGN DATA WRAPPER file_fdw;

-- File must be accessible to the backend
-- All columns existing in the file must be present in the FDW table definition
CREATE FOREIGN TABLE mycsv(a integer, b text, c text)
    SERVER csvfile OPTIONS ( filename '/tmp/test.csv', format 'csv');

-- Now we can copy into our actual table
-- ignoring column c
SELECT a,b INTO testtable FROM mycsv;

Using SELECT INTO creates the new table for you. An alternative is to create the table beforehand and use INSERT INTO {table} SELECT {query}.

Collectives™ on Stack Overflow

Copy selected columns from a csv file into a postgres table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related