how can I overwrite unique rows with COPY, in Postgres?

user_695498 0
Viewed 232

I have this object:

CREATE TABLE IF NOT EXISTS {exchangeName}.{tableName}
(
    instrument     VARCHAR                     NOT NULL,
    ts             TIMESTAMP WITHOUT TIME ZONE NOT NULL,
    interval       VARCHAR                     NOT NULL,
    price          FLOAT8                      NOT NULL,
    UNIQUE (instrument, ts, interval)
);

and I want to have a unique row per (instrument, ts, interval) combination.

However, I'm inserting a lot of data like this (F# code):

use writer = connection.BeginBinaryImport($"COPY {exchangeName}.{tableName} (instrument, ts, interval, price) FROM STDIN (FORMAT BINARY)")

for k in kvwap do
    do! writer.StartRowAsync()                                          |> Async.AwaitTask
    do! writer.WriteAsync(k.Instrument.Ticker,  NpgsqlDbType.Varchar)   |> Async.AwaitTask
    do! writer.WriteAsync(k.Timestamp,          NpgsqlDbType.Timestamp) |> Async.AwaitTask
    do! writer.WriteAsync(k.Interval.Text,      NpgsqlDbType.Varchar)   |> Async.AwaitTask
    do! writer.WriteAsync(double k.Price,       NpgsqlDbType.Double)    |> Async.AwaitTask

let! c = writer.CompleteAsync()

and I'm getting errors:

One or more errors occurred. (23505: duplicate key value violates unique constraint "kvwap_instrument_ts_interval_key")

How can I get the COPY instruction overwrite the existing rows? I can't do a regular insert with ON CONFLICT, there are too many rows and it is too slow.

npgsql postgresql
1 Answers

No, you cannot do that with COPY.

But you can use COPY to copy the data into a temporary table and then merge with something like

INSERT INTO tab
SELECT * FROM temp_tab
ON CONFLICT (...)
DO UPDATE ...;
user_64643080
Related