Re: INSERT Trigger to check for existing records

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.