0

i used postgresql copy to store my csv file to my table. But the thing is on my csv table i have 4field

f2 f5 f6 f13

and on my postgresql table have 8 table

f1 f2 f3 f4 f5 f6 f7 f8

and i want to make f1 the sequence i make, and f3 is current_date and the other is null

this is similiar to my question, but that question is asked on 2013 (maybe now there is other solution?)

The point is i want to insert from specific column from csv to specific column postgresql, if possible i dont want to make temporary table

Community
  • 1
  • 1
Alexander Chandra
  • 587
  • 2
  • 9
  • 23

1 Answers1

0

You can selectively declare target columns with COPY:

COPY tbl(f2, f5, f6, f7) FROM '/path/to/file.csv'

If the source file has columns you do not want to insert into the table at all, you need another (temporary) table as stepping stone - COPY to it and INSERT from there. Or you edit the input file to remove the excess column.

Another option would be to add a column to the target table temporarily. Adding a NULL column is cheap and does not trigger a table rewrite. You can COPY to the table and then drop the column again. This, too, does not trigger a table rewrite and is also very cheap: the dropped column is still physically there, but is marked dead in the system catalog pg_attribute. Any UPDATE on a row or VACUUM FULL on the table or a backup/restore cycle will remove the dead column physically.

ALTER TABLE tbl ADD COLUMN f13 text;
COPY tbl(f2, f5, f6, f13) FROM '/path/to/file.csv'
ALTER TABLE tbl DROP COLUMN f13;

If you do it all within a single transaction, the column remains invisible to the rest of the world.

However:

  • only the owner of the table (and superusers) can use ALTER TABLE.

  • dropped columns are currently (Postgres 12) kept as invisible zombis in the system catalogs, which can have some minor side effects. See:

Related:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228