Search Postgresql Archives

Re: INSERT Trigger to check for existing records

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



David,

That's an interesting idea. I WOULD like  to retain the OLD records that are the same and only INSERT new or changed records. Is there a way to compare the old and the new records without a trigger?

Hagen

On 11/21/20 9:15 AM, David G. Johnston wrote:


On Saturday, November 21, 2020, Hagen Finley <hagen@xxxxxxxxxxxxxx> wrote:


I pull a new forecast spreadsheet each Monday. 80% of the records are the same as the existing records from the week before.

Here’s what I (REALLY) want:

Trigger looks at three fields prior to new insert: Deal ID (numeric), revenue (numeric), stage(char)     Example: 19743576    22072.37    Commit - 90%

  1. If the NEW dealid doesn't match any of the OLD dealids, insert the new row
  2. if the NEW dealid, revenue and stage fields ALL match the OLD dealid, revenue and stage, skip (don't insert the NEW row)
  3. If the NEW dealid matches an OLD dealid but either the NEW revenue OR the stage fields have changed (don't match OLD record) insert new row (I'll review both rows manually)


Am I anywhere close (same county) to the right code?



IMO, don’t use triggers.  Load the data into a temporary, or unlogged table, and then run commands to do what you want against the live tables.  Truncate/drop before doing that again the following week.

David J.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux