3 ms·
The correct way to do this, I believe, is to `CREATE TEMP TABLE to_insert ON COMMIT DROP`, then `COPY INTO to_insert` and `INSERT INTO target SELECT * FROM to_i
by Smaug123 6y ago
The correct way to do this, I believe, is to `CREATE TEMP TABLE to_insert ON COMMIT DROP`, then `COPY INTO to_insert` and `INSERT INTO target SELECT * FROM to_insert RETURNING`. This gives you all the perf benefit of `COPY` while still giving you the ability to use `RETURNING`.
- thoraway1010 6y agoCopy is great! I do a few hundred million row DB reload in a script. No real time pressure, but I did wonder, couldn't this be faster? COPY is really much nicer, I'm pretty sure I'm down to essentially disk read / network / disk write limits vs processing limits.
- chinhodado 6y agoCan you explain more about COPY? I never used it before and would like to know what its benefits are.
- swaranga 6y agoCOPY api in Postgres avoids a lot of the query parsing and other stuff that takes up cpu time. I have used it in production and have seen great results. There are cases where the COPY api is not sufficient. For instance it cannot do the ON CONFLICT IGNORE thingy and instead fails the call. In that case we fallback to INSERT ON CONFLICT IGNORE - since in our application we do not expect too many duplicates this works well for us.