PostgreSQL MERGE To Reconcile Cash Register Sales Data Part 2

Part one of this series covered a simple use of MERGE and can be found here.  In Part Two, we get more complicated logically. Previously, we only had a WHEN NOT MATCHED logical leg in the code. Now, we add a WHEN MATCHED logic.

WHEN MATCHED and WHEN NOT MATCHED

The query now has two logical legs - when there are matching rows and when there are no matching rows. I have color-coded the query to illustrate the separate paths.

demo=# merge into ledger l
using register r
on l.id = r.id
WHEN NOT MATCHED THEN
 insert (id, tx_id, total_register, misc)
 values (r.id, r.tx_id, r.amount, 0)
WHEN MATCHED THEN
 update set misc = misc + 1
RETURNING merge_action(), l.*;
~                                  
 merge_action | id | tx_id | total_register | misc 
--------------+----+-------+----------------+------
 UPDATE       |  1 |   101 |         $19.99 |    1
(1 row)
MERGE 1
demo-# 

The ledger table already has a match in the above example, so we increment the misc column when the query runs. If we rerun the query, the same column gets incremented again.

demo=# merge into ledger l
using register r
on l.id = r.id
WHEN NOT MATCHED THEN
 insert (id, tx_id, total_register, misc)
 values (r.id, r.tx_id, r.amount, 0)
WHEN MATCHED THEN
 update set misc = misc + 1
RETURNING merge_action(), l.*;
~                               
 merge_action | id | tx_id | total_register | misc 
--------------+----+-------+----------------+------
 UPDATE       |  1 |   101 |         $19.99 |    2
(1 row)

MERGE 1
demo-# 

Delete

Another option with Merge is to DELETE the row. The NOT MATCHED logic does not change but there is now a WHEN MATCHED logical leg.

demo=# merge into ledger l
using register r
on l.id = r.id
WHEN NOT MATCHED THEN
 insert (id, tx_id, total_register, misc)
 values (r.id, r.tx_id, r.amount, 0)
WHEN MATCHED THEN
 DELETE
RETURNING merge_action(), l.*;
 merge_action | id | tx_id | total_register | misc 
--------------+----+-------+----------------+------
 DELETE       |  1 |   101 |         $19.99 |    2
(1 row)

MERGE 1
demo=# select * from ledger;
 id | tx_id | total_register | misc 
----+-------+----------------+------
(0 rows)

demo=# 

Doing Nothing


There is also an option to DO NOTHING.  Yes, you can get paid to write code that does nothing.

Remember that the ledger table is empty after the last example. We have to run the code one to insert a row into this table. Then, we run it again to test the WHEN MATCHED logic leg with the DO NOTHING option.

demo=# merge into ledger l
using register r
on l.id = r.id
WHEN NOT MATCHED THEN
 insert (id, tx_id, total_register, misc)
 values (r.id, r.tx_id, r.amount, 0)
WHEN MATCHED THEN
 DO NOTHING
RETURNING merge_action(), l.*;
 merge_action | id | tx_id | total_register | misc 
--------------+----+-------+----------------+------
 INSERT       |  1 |   101 |         $19.99 |    0
(1 row)

MERGE 1
demo=# merge into ledger l
using register r
on l.id = r.id
WHEN NOT MATCHED THEN
 insert (id, tx_id, total_register, misc)
 values (r.id, r.tx_id, r.amount, 0)
WHEN MATCHED THEN
 DO NOTHING
RETURNING merge_action(), l.*;
 merge_action | id | tx_id | total_register | misc 
--------------+----+-------+----------------+------
(0 rows)

MERGE 0
demo=# 

Note that the merge_action() function does not say 'Nothing'.  

Checking the ledger table shows us one row.

demo=# select * from ledger;
 id | tx_id | total_register | misc 
----+-------+----------------+------
  1 |   101 |         $19.99 |    0
(1 row)

demo=# 

More Complicated Logic


We can add complications to the logic to make the query more useful. We will add a status column to the ledger table to clarify the illustration. 

demo=# alter table ledger add column status text;
ALTER TABLE
demo=# truncate ledger;
TRUNCATE TABLE
demo=# 

The new logic for the query is just a little more complicated.
  • No Match
    • Insert data
  • There is a match, but it is the first time we match
    • Update the status field
  • There is a match, and it is the second time we match
    • Delete the row
The first run puts 'inserted' into the status column.

demo=# merge into ledger l
using register r
on l.id = r.id
WHEN NOT MATCHED THEN
insert (id, tx_id, total_register, misc, status)
 values (r.id, r.tx_id, r.amount, 0, 'inserted')
WHEN MATCHED and l.status = 'inserted' THEN
 update set misc = misc + 1, status = 'already seen'
WHEN MATCHED and l.status = 'already seen' THEN
 DELETE
RETURNING merge_action(), l.*;
~                                 
 merge_action | id | tx_id | total_register | misc |  status  
--------------+----+-------+----------------+------+----------
 INSERT       |  1 |   101 |         $19.99 |    0 | inserted
(1 row)

MERGE 1
demo-# 

The second pass updates the status column to 'already seen'.

demo=# merge into ledger l
using register r
on l.id = r.id
WHEN NOT MATCHED THEN
 insert (id, tx_id, total_register, misc, status)
 values (r.id, r.tx_id, r.amount, 0, 'inserted')
WHEN MATCHED and l.status = 'inserted' THEN
 update set misc = misc + 1, status = 'already seen'
WHEN MATCHED and l.status = 'already seen' THEN
 DELETE
RETURNING merge_action(), l.*;
 merge_action | id | tx_id | total_register | misc |    status    
--------------+----+-------+----------------+------+--------------
 UPDATE       |  1 |   101 |         $19.99 |    1 | already seen
(1 row)

MERGE 1

The third pass of the query where status equals 'already seen' will DELETE the row.

demo=# merge into ledger l
using register r
on l.id = r.id
WHEN NOT MATCHED THEN
 insert (id, tx_id, total_register, misc, status)
 values (r.id, r.tx_id, r.amount, 0, 'inserted')
WHEN MATCHED and l.status = 'inserted' THEN
 update set misc = misc + 1, status = 'already seen'
WHEN MATCHED and l.status = 'already seen' THEN
 DELETE
RETURNING merge_action(), l.*;
 merge_action | id | tx_id | total_register | misc |    status    
--------------+----+-------+----------------+------+--------------
 DELETE       |  1 |   101 |         $19.99 |    1 | already seen
(1 row)

MERGE 1
demo=# select * from ledger;
 id | tx_id | total_register | misc | status 
----+-------+----------------+------+--------
(0 rows)

demo=# 


Summary


Merge is very handy for tasks like cash register sales reconciliation. You can make the WHEn MATCHED or WHEN NOT MATCHED logic as complicated as needed. Please peruse the manual page as it provides more details on its use.

In 'real life,' you probably want a primary key that is complex enough to be unique, like a store number, register number, and transaction number to ensure uniqueness. Make sure you use techniques like constraint checks on the data, as it is easier and cheaper to out than try to go back later to correct it. 

If you have an interesting use for Merger and can share it, please let me know.



Comments

Post a Comment

Popular posts from this blog

How Does PostgreSQL Store Your Data?

PostgreSQL is an amazing database. And some of us wonder how it takes what we enter on a keyboard and stores it. The following is peeking into the 'clockworks' to see how things are done. Step 1 On a fresh install of Ubuntu with PostgreSQL 17, let us create a database. stoker@ThinkPad:~$ createdb demo Password:  stoker@ThinkPad:~$ psql demo Password for user stoker:  psql (17.2 (Ubuntu 17.2-1.pgdg24.04+1)) Type "help" for help. Now that we are in this new database, we can ask where PostgreSQL stores the data by looking for the data directory. demo=# show data_directory;        data_directory         -----------------------------  /var/lib/postgresql/17/main (1 row) This informs us that our data will be somewhere under the /var/lib/postgresql17/main directory.  Step 2 Issuing a command to create a table starts of series of events that we will study in later posts. Right now, we want to see where the stuff goes. Here a simp...
Read more

Now That We Know Where PostgreSQL Stores Data, We Can Look At How

 Last time , we started looking at how PostgreSQL stores data by discovering where the data is stored and had a quick peek at that data. There is only one record at this point. demo=# SELECT * FROM ex01;  a | b  ---+---  1 | 1 (1 row) And the contents of that file are not precisely illuminating at first glance. For now, just trust me that our row is in there, and you will see where in just a little bit. root@ThinkPad:/var/lib/postgresql/17/main/base/16389# od -c 16390 0000000  \0  \0  \0  \0   (   z 227 001  \0  \0  \0  \0 034  \0 340 037 0000020  \0     004      \0  \0  \0  \0 340 237   @  \0  \0  \0  \0  \0 0000040  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0 * 0017740 362 002  \0  \0  \0  \0  \...
Read more

Beginning PostgreSQL Transactions

Transactions can be a rough concept for those new to databases. Those new to PostgreSQL will find the implementation much different than those in other databases.  The default isolation level in PostgreSQL is READ COMMITTED so that you only see data that has been committed. If someone else works on the same table, you will not see those changes until they are committed. Start with an elementary table: demo=# create table ex03 (a int); CREATE TABLE demo=# insert into ex03 (a) values (1); INSERT 0 1 Behind the scenes, the system sets up some information to track your data, as we learned here and here .  demo=# select a, ctid, xmin, xmax, cmin, cmax from ex03;  a | ctid  | xmin | xmax | cmin | cmax  ---+-------+------+------+------+------  1 | (0,1) |  768 |    0 |    0 |    0 (1 row) The CTID was introduced last time , and the other columns may be new. The documentation details their definitions. For this blog, we are goin...
Read more