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')
WITH DELIMITER ',' CSV HEADER;
Use a temp table to import into. This allows you to:
, 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.