Re: [SQL] avoid update - insert churn
От | Adrian Klaver |
---|---|
Тема | Re: [SQL] avoid update - insert churn |
Дата | |
Msg-id | 4d61f745-7fba-d768-5bbf-5fdbf2b89569@aklaver.com обсуждение исходный текст |
Ответ на | [SQL] avoid update - insert churn (Kevin Duffy <kevind0718@gmail.com>) |
Ответы |
Re: [SQL] avoid update - insert churn
|
Список | pgsql-sql |
On 03/05/2017 10:20 AM, Kevin Duffy wrote: > > Hello All: > > I am looking for suggestions on how to optimize a relatively simple problem. > > Say I have a group of shoe stores. The store report sales daily and > daily I > calc stats off on the sales numbers. Ie rolling averages & volatility > numbers, with a couple of different look backs. > > I am storing the raw sales figures in one table and the calc'ed stats in > another. > > Here is the interesting part, it is possible and it does happen that a > store will not > report sales for a day or two or prior figures reported will get revised. > There is at max a five day window on this. > > For the prior dates there are two possibilities, I will be updating > a record or inserting a new record in sales table. For the current > date's sales it will be all inserts. > Currently what I do is loop through the data by store and shoe style > attempt an update and if that fails > do an insert. > > Same deal for the stat's table except there are multiple records for > each Store/Shoe style pair. > > > So here is my question/issue: Is this there a better design pattern for > doing this? > I could gather up the data for the prior dates and do one "bulk" > update. But that would > fail for sales that have not been reported todate. > The current date's sales are of course an insert. And I could fix this > to be one "bulk" insert. > > Was just chewing this over in my heading wondering if there is a more > efficient way to this. > Other than attempt update if fail insert. Seems to be a bit of churn to me. You do not say what version of Postgres you are using, but if 9.5+ https://www.postgresql.org/docs/9.5/static/sql-insert.html "ON CONFLICT Clause The optional ON CONFLICT clause specifies an alternative action to raising a unique violation or exclusion constraint violation error. For each individual row proposed for insertion, either the insertion proceeds, or, if an arbiter constraint or index specified by conflict_target is violated, the alternative conflict_action is taken. ON CONFLICT DO NOTHING simply avoids inserting a row as its alternative action. ON CONFLICT DO UPDATE updates the existing row that conflicts with the row proposed for insertion as its alternative action." It wraps the INSERT/UPDATE as an UPSERT in one command. > > But at the end of the day, it do believe Keep It Simple. > > Many thanks for considering this problem. > > KD > > -- Adrian Klaver adrian.klaver@aklaver.com
В списке pgsql-sql по дате отправления: