copy csv into partitioned table with unique index

From: Mariel Cherkassky <mariel(dot)cherkassky(at)gmail(dot)com>
To: PostgreSQL mailing lists <pgsql-performance(at)postgresql(dot)org>
Subject: copy csv into partitioned table with unique index
Date: 2018-01-28 09:11:33
Message-ID: CA+t6e1=QCgqDbgU86pmsQAVWEgH08TvOeVukK9CfKfa53V7L2g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hi,
I configured a master table that is called "year_2018" :
create table year_2018(a int,b int c date);

The master table has a unique constraint on those 3 columns so that I wont
have any duplicated rows. Moreover, I configured a before insert trigger on
that table that creates a child table for each day in the year. The child
should include all the data related to that specific day.

Now, every day I got a csv file that I need to load to the year table. I
must load the data as fast as possible but I have 2 problems :
1)I must load the data as a bulk - via the copy command. However, the copy
command fails because sometimes I have duplicated rows.
2)I tried to use the pgloader extension but it fails because I have a
trigger before each insert.

-I cant load all the data into a temp table and then run insert into
year_2018 select * from temp because it takes too much time.

Any idea ?

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message legrand legrand 2018-01-28 10:55:38 Re: copy csv into partitioned table with unique index
Previous Message Rick Otten 2018-01-26 10:58:18 PG 10 hash index create times