From: | Kevin Duffy <kevind0718(at)gmail(dot)com> |
---|---|
To: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: avoid update - insert churn |
Date: | 2017-03-05 20:07:19 |
Message-ID: | CAHCyeW30Lgd8kMkG7t7K0rk4p0xhDTi8QE=CT2z5VWZn2feDYA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
"PostgreSQL 9.5.4, compiled by Visual C++ build 1800, 64-bit"
some examples here: http://www.postgresqltutorial.com/postgresql-upsert/
many thanks for your swift reply.
KD
On Sun, Mar 5, 2017 at 2:56 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:
> 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(dot)klaver(at)aklaver(dot)com
>
From | Date | Subject | |
---|---|---|---|
Next Message | Steve Midgley | 2017-03-06 03:55:24 | Re: avoid update - insert churn |
Previous Message | Adrian Klaver | 2017-03-05 19:56:03 | Re: avoid update - insert churn |