Hello Michael,
Thanks so much for this advice. As I mentioned previously, I'm not very good at this yet, so forgive me if my response is obtuse. I really love databases but my sentiments may be unrequited.
In reality my table has lots of columns (~30) including a report date (repdate) and each week's pull has a new repdate ( in this case 2020-11-02 and 2020-11-09) which could function as a "created on" field.
To clarify, I would create an unique index on all the columns in the old report records (2020-11-02) or just the three I am comparing (dealid,stage and revenue)?
In either case, so far in my efforts it looks like the create index fails because there are lots of rows with the same stage value, and a few with the same revenue value.
Create UNIQUE INDEX idx_sfdc
ON sfdc(ndealid, stage, revusd);
ERROR: could not create unique index "idx_sfdc" DETAIL: Key (ndealid, stage, revusd)=(19743576, Commit - 90% , 22072.37) is duplicated. SQL state: 23505
I probably could create an unique index on the dealid column as that should be unique. Would that be enough? It seems like that would insert ONLY the new records with a new (unique) dealid and that would definitely by an important step forward.
I hesitate to admit I have no idea how I would code the "call insert on conflict (unique index) do nothing" syntax, but I would be excited to learn.
Best,
Hagen
On 11/22/20 8:54 AM, Michael Lewis wrote:
If you can modify your insert statement, and live with an extra column in the data, no trigger is needed as best I can figure.
Create a unique index over the existing columns, add a "created_on" field and call insert on conflict (unique index) do nothing.
This should give the behavior you want.