Re: DELETE vs TRUNCATE explanation
От
Craig James
Тема
Re: DELETE vs TRUNCATE explanation
Дата
Msg-id
CAFwQ8rdZWMHvmEEPyOKCgC0bMtPhaRGiY1nSVLA_tORAk1SVPg@mail.gmail.com
Ответ на
Re: DELETE vs TRUNCATE explanation (Mark Thornton)
Список
Дерево обсуждения
DELETE vs TRUNCATE explanation Craig Ringer <ringerc@ringerc.id.au>
Re: DELETE vs TRUNCATE explanation Daniel Farina <daniel@heroku.com>
Re: DELETE vs TRUNCATE explanation Tom Lane <tgl@sss.pgh.pa.us>
Re: DELETE vs TRUNCATE explanation "ktm@rice.edu" <ktm@rice.edu>
Re: DELETE vs TRUNCATE explanation Matthew Woodcraft <matthew@woodcraft.me.uk>
Re: DELETE vs TRUNCATE explanation Craig Ringer <ringerc@ringerc.id.au>
Re: DELETE vs TRUNCATE explanation Daniel Farina <daniel@heroku.com>
Re: DELETE vs TRUNCATE explanation Craig Ringer <ringerc@ringerc.id.au>
Re: DELETE vs TRUNCATE explanation Daniel Farina <daniel@heroku.com>
Re: DELETE vs TRUNCATE explanation Craig Ringer <ringerc@ringerc.id.au>
Re: DELETE vs TRUNCATE explanation Jeff Janes <jeff.janes@gmail.com>
Re: DELETE vs TRUNCATE explanation "Harold A. Giménez" <harold.gimenez@gmail.com>
Re: DELETE vs TRUNCATE explanation Jeff Janes <jeff.janes@gmail.com>
Re: DELETE vs TRUNCATE explanation Jeff Janes <jeff.janes@gmail.com>
Re: DELETE vs TRUNCATE explanation Craig James <cjames@emolecules.com>
Re: DELETE vs TRUNCATE explanation Shaun Thomas <sthomas@optionshouse.com>
Re: DELETE vs TRUNCATE explanation Andrew Dunstan <andrew@dunslane.net>
Re: DELETE vs TRUNCATE explanation Mark Thornton <mthornton@optrak.com>
Re: DELETE vs TRUNCATE explanation Craig James <cjames@emolecules.com>
Re: DELETE vs TRUNCATE explanation Craig Ringer <ringerc@ringerc.id.au>
On Wed, Jul 11, 2012 at 2:32 PM, Mark Thornton <mthornton@optrak.com> wrote:
If it's a single session, use a temporary table. It is faster to start with (temp tables aren't logged), and it's automatically dropped at the end of the session (or at the end of the transaction if that's what you specified when you created it). This doesn't work if your insert/update spans more than one session.
Another trick that works (depending on how big your tables are) is to scan the primary key before you start, and build a hash table of the keys. That instantly tells you whether each record should be an insert or update.
Craig
On 11/07/12 21:18, Craig James wrote:If I have a lot of data which updates/inserts an existing table but I don't know if a given record will be an update or an insert, then I write all the 'new' data to a temporary table and then use sql statements to achieve the updates and inserts on the existing table.
It strikes me as a contrived case rather than a use case. What sort of app repeatedly fills and truncates a small table thousands of times ... other than a test app to see whether you can do it or not?
Is there a better way of doing this in standard SQL?
If it's a single session, use a temporary table. It is faster to start with (temp tables aren't logged), and it's automatically dropped at the end of the session (or at the end of the transaction if that's what you specified when you created it). This doesn't work if your insert/update spans more than one session.
Another trick that works (depending on how big your tables are) is to scan the primary key before you start, and build a hash table of the keys. That instantly tells you whether each record should be an insert or update.
Craig
Mark
В списке pgsql-performance по дате отправления
