PostgreSQL MERGE TO Reconcile Cash Register Sales Data Part 1

 I hoped to present the following at the PostgreSQL Conference 2025 in March. But I can not make it to Orlando for this wonderful event (Please attend if you can! It is a fantastic event). Cash Register information is vital to retail businesses and it is often processed one line item at a time (pronounced 'S-L-O-W'). MERGE was added a few years ago to PostgreSQL's arsenal. It is powerful and easy to use.

The Cash Register

The next time you are handed a receipt from a cash register, take the time to examine all the information on it. Time, date, location, total, line items, and possibly other information essential to the vendor's operations are on that slip of paper. There may be information not on the sales receipt collected for processing. Now imagine you are a major fast-food restaurant with tens of thousands of cash registers or more. All that data needs to be collected, transmitted, and processed.  It is easy to see how efficiency is important. 

MERGE

The manual page describes the function of MERGE distinctly as MERGE performs actions that modify rows in the target table identified as target_table_name, using the data_source. MERGE provides a single SQL statement that can conditionally INSERT, UPDATE or DELETE rows, a task that would otherwise require multiple procedural language statements. In the examples below the data source is the data from the cash register and the target table is the sales table. 

Our simple example uses the following table for cash register data. We will assume that each cash register in our imaginary fast-food chain has a unique identification number.

demo=# create table register (id int, tx_id int, amount money);
CREATE TABLE
demo=# insert into register values (1,101,19.99);
INSERT 0 1
demo=# 

A table to record all the transactions is created.  Not that this ledger table is empty and our first sale is ready to be processed.

demo=# create table ledger (id int, tx_id int, total_register money, misc int);
CREATE TABLE
demo=# select * from register;
 id | tx_id | amount 
----+-------+--------
  1 |   101 | $19.99
(1 row)

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

demo=# 

First Merge


The first time we merge, the code is simple. We compare the entries in the register table to see if there are matches in the ledger table.  There are no entries in the leger table. A clause in the code moves the data from the register table into the ledger table if there are NO MATCHES.    We end with the RETURNING line to report what has been performed (the INSERT) and the new values in the ledger table.

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)
RETURNING merge_action(), l.*;
 merge_action | id | tx_id | total_register | misc 
--------------+----+-------+----------------+------
 INSERT       |  1 |   101 |         $19.99 |    0
(1 row)

MERGE 1
demo=# 

Second Merge

If we run the same SQL code again, the results look a little different even if the data in the ledger tables does not change. This time there IS a match between the two tables. Later you will see how to right WHEN MATCH clauses.

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)
RETURNING merge_action(), l.*;
 merge_action | id | tx_id | total_register | misc 
--------------+----+-------+----------------+------
(0 rows)
MERGE 0
demo=# select * from ledger;
 id | tx_id | total_register | misc 
----+-------+----------------+------
  1 |   101 |         $19.99 |    0
(1 row)
demo=# 

Sometimes the same data is applied twice which is a disaster in many cases. But in this case, there was no action taken.

What is needed is to compare the transaction IDs along with the register IDs to make sure we do not record twice the same line of data.  heck, we probably in real life want a unique key made with the store number and the register id. 

Next Time

I am trying to keep these blogs short, if not sweet. And this one went on a bit longer than I thought.  Next time we will look at more MERGE() functionality and explore the logic of making matches to optimize the performance of the query.


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