0

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?

user9057272
  • 367
  • 2
  • 5
  • 17
  • There are ample examples on how to import character-delimited files. Please perform a search first. – kometen Dec 02 '21 at 06:03
  • Yes. But I am hardly finding any resource that addresses my specific requirements for COPY Command. – user9057272 Dec 02 '21 at 08:09
  • Please edit your question and add what you have tried. – kometen Dec 02 '21 at 08:17
  • 1
    `COPY` by 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 to `COPY`. I should add both `COPY` and its client form `\copy` do allow for including an external `PROGRAM` in their commands. Still it would be another program that does the file pruning. – Adrian Klaver Dec 02 '21 at 16:19

2 Answers2

1

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.

kometen
  • 6,536
  • 6
  • 41
  • 51
0

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.

Greg
  • 698
  • 4
  • 11