PostgreSQL releases are incremental evolutions that improve the product. The beta version 17 was recently released, and there are two small items you may overlook unless you have them pointed out to you. So, “Point! Point!”
MERGE()
MERGE() was a new feature of PostgreSQL 15. It allows you to modify the contents of a target table based on data in another table. For example, cash register sales data can update a business’s general accounting ledger. It can perform these reconciliations as a batch instead of line-by-line from an application, significantly increasing throughput.
The new additions are RETURNING and MERGE_ACTION(), which lets you know what action MERGE performed for each row, such as an INSERT or UPDATE.
Example
Here are some test tables and data. Please note that the sales table is empty and has one row of data.
|
1 2 3 4 5 |
postgres=# create table sales (id int, location int, amount int); CREATE TABLE postgres=# create table ledger (id int, location int, total int, misc int); CREATE TABLE postgres=# insert into sales values (1,1,100) |
The first execution of our MERGE takes the WHEN NOT MATCHED logical leg and inserts data from the sales table into the ledger.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
postgres=# merge into ledger l using sales s on l.id = s.id when matched then update set misc = 99 when not matched then insert (id,location,total) values (s.id,s.location,s.amount) returning merge_action(), l.*; merge_action | id | location | total | misc --------------+----+----------+-------+------ INSERT | 1 | 1 | 100 | (1 row) MERGE 1 postgres=# |
MERGE_ACTION() informs us that, indeed, the INSERT was made. Rerunning the same MERGE statement returns a different result. Since there is a row in each table where the id columns match, the logical leg for the UPDATE is taken.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
postgres=# merge into ledger l using sales s on l.id = s.id when matched then update set misc = 99 when not matched then insert (id,location,total) values (s.id,s.location,s.amount) returning merge_action(), l.*; merge_action | id | location | total | misc --------------+----+----------+-------+------ UPDATE | 1 | 1 | 100 | 99 (1 row) MERGE 1 |
The logic can be as complex as you want in the WHEN MATCHED or WHEN NOT MATCHED as you desire. That is where the two new additions help as your logic gets more complicated. The MERGE_ACTION() combined with RETURNING can help you debug your query.
JSON_TABLE
The JSON_TABLE operator temporarily converts your JSON-formatted data into a relational table. This allows you to use Structured Query Language on the temporary table, such as aggregates, Window Functions, or CTEs.
Example
Here is a table and test data.
|
1 2 3 4 5 6 7 8 9 10 |
postgres=# create table t1 (json_col jsonb); CREATE TABLE postgres=# INSERT INTO t1 VALUES ( '{ "people": [ { "name":"John Smith", "address":"780 Mission St, San Francisco, CA 94103"}, { "name":"Sally Brown", "address":"75 37th Ave S, St Cloud, MN 94103"}, { "name":"John Johnson", "address":"1262 Roosevelt Trail, Raymond, ME 04071"} ] }' ); INSERT 0 1 |
JSON_TABLE will cast the JSON formatted data to the desired data type and assign a column name. In the following example, the JSONB column json_col is accessed. The keys matching $.name are cast to a VARCHAR(40), and the matched for $.address are cast to VARCHAR(100).
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
postgres=# SELECT people.* FROM t1, JSON_TABLE(json_col, '$.people[*]' COLUMNS ( name VARCHAR(40) PATH '$.name', address VARCHAR(100) PATH '$.address') ) people; name | address --------------+----------------------------------------- John Smith | 780 Mission St, San Francisco, CA 94103 Sally Brown | 75 37th Ave S, St Cloud, MN 94103 John Johnson | 1262 Roosevelt Trail, Raymond, ME 04071 (3 rows) postgres=# |
Now that there is a temporary table, we can feed the output to SQL commands for processing.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
postgres=# SELECT people.* FROM t1, JSON_TABLE(json_col, '$.people[*]' COLUMNS ( name VARCHAR(40) PATH '$.name', address VARCHAR(100) PATH '$.address') ) people WHERE people.name LIKE 'John%'; ~ name | address --------------+----------------------------------------- John Smith | 780 Mission St, San Francisco, CA 94103 John Johnson | 1262 Roosevelt Trail, Raymond, ME 04071 (2 rows) postgres-# |
This is an overly simple example, but it does demonstrate how to process the JSON data that is now temporarily relational with SQL.
Summary
These small but significant additions to PostgreSQL 17 will have an impact.
MERGE makes processing transaction logs much faster than spoon-feeding data. JSON_TABLE provides better tools for working with data stored in a JSON column. These two features have been in some other databases for a while and have proven handy. Those who want to transition from another database with these two operators have fewer hurdles to keep them from doing so. And PostgreSQL users now have two new ways to work with data.
You’ve chosen PostgreSQL for its flexibility, performance, and cost savings—but even experienced IT leaders can hit avoidable pitfalls along the way. Here’s what to look out for.
Enterprise PostgreSQL Buyer’s Guide
About the Author
David StokesDavid Stokes is a Technology Evangelist for Percona Corporation, is the author of MySQL & JSON - A Practical Programming Guide, and resides in Texas.
