From: | Dominique Devienne <ddevienne(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | COPY RETURNING? |
Date: | 2023-04-19 09:55:08 |
Message-ID: | CAFCRh-9nKHCG3OQQ=_Rbm3CW+NqBVZZM5AX0hh8SraixF7xTuA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi.
We are switching a schema type's surrogate/primary key, from `uuid` to
`int`.
That schema has parent-child relationships enforced with foreign-keys.
Our ingestion/schema-loading code uses COPY FROM STDIN BINARY.
Before, the SK/PK was generated client-side, as random uuid.
The ingestion code maintained client-side maps from NKs to uuids,
to be used for FK columns in child-tables COPY'd later.
But now that the SK/PK is an integer identity column generated server-side,
thanks to an implicitly-created sequence, we need that generated per-row
`int` PK.
With a normal prepared statement, we'd use a RETURNING clause, to avoid
a separate round-trip to the server. Can the same somehow be achieved with
COPY?
I'm afraid the answer is no, but I want to ask anyway, maybe there's a way
or work-around?
At the end of the COPY, we do get a ResultSet, so API-wise it was be
possible for it
to contain some rows, I guess, but I'm not sure the COPY protocol supports
returning
rows, nor what the syntax would be to have a COPY RETURNING form of COPY.
Thanks for any insights. --DD
From | Date | Subject | |
---|---|---|---|
Next Message | shaurya jain | 2023-04-19 11:56:06 | Re: Logical replication failed with SSL SYSCALL error |
Previous Message | Peter Smith | 2023-04-19 08:27:15 | Re: Support logical replication of DDLs |