agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Adrian Klaver <adrian.klaver@aklaver.com>
To: Kevin Duffy <kevind0718@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: avoid update - insert churn
Date: Sun, 5 Mar 2017 11:56:03 -0800
Message-ID: <4d61f745-7fba-d768-5bbf-5fdbf2b89569@aklaver.com> (raw)
In-Reply-To: <CAHCyeW1ErcVHaiRReSwBDBr6mcZ4D+6ZzZjvV9g_K9ohjo2p3Q@mail.gmail.com>
References: <CAHCyeW1ErcVHaiRReSwBDBr6mcZ4D+6ZzZjvV9g_K9ohjo2p3Q@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
view thread (4+ messages) latest in thread
Message-ID: <4d61f745-7fba-d768-5bbf-5fdbf2b89569@aklaver.com>
Permalink: ../4d61f745-7fba-d768-5bbf-5fdbf2b89569@aklaver.com/
Also on: postgresql.org/message-id/4d61f745-7fba-d768-5bbf-5fdbf2b89569@aklaver.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: adrian.klaver@aklaver.com, kevind0718@gmail.com
Subject: Re: avoid update - insert churn
In-Reply-To: <4d61f745-7fba-d768-5bbf-5fdbf2b89569@aklaver.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox