jackc
support copyfrom with WHERE condition
I would like to use the copyfrom feature with a WHERE condition to avoid duplicate records in my destination table.
Here is the WHERE condition i would like to use (taken from a working rule for INSERT):
WHERE (EXISTS ( SELECT *
FROM asns
WHERE merchantId IS NOT DISTINCT FROM NEW.merchantId
AND return_provider IS NOT DISTINCT FROM NEW.return_provider
AND barcode IS NOT DISTINCT FROM NEW.barcode
AND carrier IS NOT DISTINCT FROM NEW.carrier
AND tracking_number IS NOT DISTINCT FROM NEW.tracking_number
AND customer_email IS NOT DISTINCT FROM NEW.customer_email
AND order_id IS NOT DISTINCT FROM NEW.order_id
AND order_name IS NOT DISTINCT FROM NEW.order_name
AND order_number IS NOT DISTINCT FROM NEW.order_number
AND return_line_item_id IS NOT DISTINCT FROM NEW.return_line_item_id
AND rma IS NOT DISTINCT FROM NEW.rma
AND sku IS NOT DISTINCT FROM NEW.sku)) DO INSTEAD NOTHING;
Thank you.
That would be a interesting feature, but unfortunately, the underlying PostgreSQL copy protocol does not allow for a where clause.
There's nothing that can be done at the driver layer. This change would have to be made at the PostgreSQL server layer.
@jackc Actually it does support WHERE https://www.postgresql.org/docs/current/sql-copy.html
Oh, wow you're right. It was added in PG 12 and I never noticed the new feature. That said, your particular example wouldn't be possible as subqueries are not allowed, but it seems like a worthwhile feature nonetheless.
This functionality can be accessed now at the pgconn layer, but it's not clear what would be the best interface would be at the pgx level.
- A variadic options argument could be added CopyFrom. This would avoid adding a new function or breaking compatibility.
- A new function could take a options struct.
- A new function could take the COPY FROM SQL string like the underlying pgconn function does.
- A new function or struct could expose building the copy stream directly. This might allow alternate ways of program composition.
I've just discovered this while thinking about opening an issue myself. I'd love to have a version of CopyFrom which allows custom SQL but still handles the encoding of rows into binary format etc. Currently I'm using the underlying pgconn function and csv format, but it's not ideal.
In my specific case my SQL first does CREATE TEMPORARY TABLE, then COPY into that table, followed by an INSERT from the temporary table. I'm aware I could decompose this client-side into the three steps, and use the current CopyFrom implementation for this middle step, but it's nice to be able to put the whole thing in a single SQL block and just fire the data at it.
