Pierrick Brun Pierrick Brun - 1 month ago 4
Node.js Question

COPY FROM CSV with static fields on Postgres

I'd like to switch an actual system importing data into a PostgreSQL 9.5 database from CSV files to a more efficient system.

I'd like to use the COPY statement because of its good performance. The problem is that I need to have one field populated that is not in the CSV file.

Is there a way to have the COPY statement add a static field to all the rows inserted ?
The perfect solution would have looked like that :

COPY data(field1, field2, field3='Account-005')
FROM '/tmp/Account-005.csv'
WITH DELIMITER ',' CSV HEADER;


Do you know a way to have that field populated in every row ?

My server is running node.js so I'm open to any cost-efficient solution to complete the files using node before COPYing it.

Answer

Use a temp table to import into. This allows you to:

  • add/remove/update columns
  • add extra literal data
  • delete or ignore records (such as duplicates)

, before inserting the new records into the actual table.


       -- target table
CREATE TABLE data
    ( id SERIAL PRIMARY KEY
    , batch_name varchar NOT NULL
    , remote_key varchar NOT NULL
    , payload varchar
        , UNIQUE (batch_name, remote_key)
        -- or::
        -- , UNIQUE (remote_key)
    );
        -- temp table
CREATE TEMP TABLE temp_data
    ( remote_key varchar -- PRIMARY KEY
    , payload varchar
    );

COPY temp_data(remote_key,payload)
FROM '/tmp/Account-005'
    ;

        -- The actual insert
        -- (you could also filter out or handle duplicates here)
INSERT INTO data(batch_name, remote_key, payload)
SELECT 'Account-005', t.remote_key, t.payload
FROM temp_data t
    ;

BTW It is possible to automate the above: put it into a function (or maybe a prepared statement), using the filename/literal as argument.