I was trying to wrap my head around how to go about doing an update to a table if an updated list of the same table type does not have one or more of its original rows. The list of services comes from an API and is usually the same set of services, however every now and again the list may be updated and services become inactive and no longer show up in the results from the API call so these services should be updated in our database to reflect active as FALSE. Likewise is a new service comes onboard it should be added to the current list of available services. I had in mind to just drop all records and add the new ones fetched from the API however considering I have used the Ids for the existing services in other tables and I would still need to reference them I threw that idea out the window and now I am in a bit of a bind.
Services - (Currently in DB)
Name | Id | Active
Test1 3 true
Test2 4 true
Test3 5 true
I wanted to have a query or trigger of some sort to run when trying to insert duplicate data to table Services where constrained by the following:
- If an existing 'Name' is found skip the insert and move on to the next item in the array
- If a new 'Name' comes up not found in the DB add it as a new row. Example [Test4 5 true]
- If the newly fetched list from an API does not have one of the existing 'Name's, that is Test1,Test2 or Test3 update that existing row to set the Active column to false. So if the new list does not have Test3 the existing Test3 row would be updated to show Active as false.
Best Answer
I think you will want something like the following - see the fiddle
here
. It's based onCommon Table Expressions
(CTE
s) and that fact that with PostgreSQL, you can perform not onlySELECT
s, but alsoINSERT
s,UPDATE
s andDELETE
s (seehere
also).First, your
service
table:populate it with your data:
Now, you receive your data from your API - I'll assume that you put that into some sort of temporary table - the keyword
TEMPORARY
just means that the table will be dropped at the end of your session - I've tested with bothTEMPORARY
and normal tables with the fiddle and the results are the same, so we'll go withTEMPORARY
:I've assumed that your API doesn't know the status of the service, so it only has two fields - the name and the
name
and theid
.Populate it:
Notice that service
Test1
is missing and that serviceTest4
is an additional service.So now, because of PostgreSQL's ability to perform
INSERT
s andUPDATE
s withinCTE
s, we can now do the following:The first
CTE
inserts new services from the
api
table into theservice
table and the second:sets the service status =
false
where a service in theservice
table isn't present in theapi
table.Now, the result of the
SELECT * FROM service
at the end of this query is:So, you might think "Drat, it hasn't worked!" - but in fact, it has worked!
In the next section, you rerun
and you get:
So, we can see that service
Test1
'sactive
field has been set tofalse
and that serviceTest4
has been added. The reason it doesn't show up in theSELECT
immediately after theCTE
s has to do with the scope of the transaction - the firstSELECT
shows theservice
table as it was at the beginning of the transaction - the second shows the state of the table after the transaction.