I have a pipe delimited data file with no headers. I need to import data into a PostgreSQL table starting with the data from second column in the file i.e skip the data before the first '|' for each line. How do I achieve this using the COPY command?
2 Answers
Use the cut command to remove the first column and then import.
cut -d "|" -f 2- file1.csv > file2.csv
psql -d test -h localhost -c "\copy table(f1,f2,f3) from 'file2.csv' delimiter '|' csv header;"
Not an answer as such related to postgresql but more about command line tools.
Comments
I had a similar issue recently. I solved it with following code:
begin;
-- create temporary table, its columns NEED to match source file
-- you can also specify all columns manually, they just need to match file.
create temporary table tmp_table as select * from source_table where false;
-- either from file
copy tmp_table ('list of columns IN THE FILE' ) from '/data/table.csv' WITH (FORMAT csv, HEADER false);
-- or from gzip
copy tmp_table ('list of columns IN THE FILE' ) from program 'zcat /data/table.csv.gz' WITH (FORMAT csv, HEADER false);
-- you can add, drop, compute additional columns if needed
alter table tmp_table ADD COLUMN IF NOT EXISTS new_column date default NULL;
insert into source_table (columns, in, the, target, table) select columns, in, the, temp, table from tmp_table where optional=conditions on conflict do nothing ;
drop table if exists tmp_table;
commit;
This creates a temporary table that columns needs to match columns in a file. Then it loads data into that table. Once you have data in the database you can change them as you wish.
This approach allows you to modify data, rearrange columns and add or remove them. You can also translate data using other tables in the db - do id lookups etc. You can also handle conflicts using on conflict cause. Be aware that depending on the way you create temp table it can be removed once commit/rollback is issued.
COPYby itself will not do what you want as it works on assumption all the data in file is being imported. To achieve your goal you will need to pre-process the file to get rid of the first column of data and then feed the rest toCOPY. I should add bothCOPYand its client form\copydo allow for including an externalPROGRAMin their commands. Still it would be another program that does the file pruning.