Dave's PostgreSQL Stuff

Using PostgreSQL will definitely spoil you. I recently had to use another database and found, to my chagrin, that aggregate filters were not included in their implementation of Structured Query Language. Specifically, there was no FILTER clause available. FILTER? To illustrate my dependency, let me start with some simple data. demo=# create table z (a int, b int, c int); CREATE TABLE demo=# insert into z values (1,10,100),(2,20,200),(3,30,300),(4,40,400); INSERT 0 4 demo=# select a,b,c from z;  a | b  |  c   ---+----+-----  1 | 10 | 100  2 | 20 | 200  3 | 30 | 300  4 | 40 | 400 (4 rows) demo=#  If I wanted the overall number of rows, I could use a simple COUNT(*). If I wanted an overall number of rows where one of the columns was of a certain value, I could add the logic for getting those rows to the WHERE clause. What if I wanted both?  Do I need two queries to do the work separately? Maybe a materialized view or a CTE (Common Tabl...

 It is much easier to keep bad data out of your database than it is to have to correct it later. Those costs are measured in time, money, and sanity. One of the easier ways of protecting your data is with a constraint check .  I was recently approached by someone who urgently needed a constraint check and could afford to 'fix' the bad data after the constraint was in place. They had to quickly keep new 'bad' data out and could afford to correct the old 'bad' data at leisure.  The Overly Simplified Example To illustrate how to do just that, here is an example of a table and some simple data. demo=# create table x1 (a int); CREATE TABLE demo=# insert into x1 (a) values (10),(20),(30),(-5),(1); INSERT 0 5 demo=# Sometime later, it is discovered that any value of a needs to be greater than five. If we add the constraint, the system will balk and refuse the request. demo=# alter table x1 add constraint x_id_gt_5 check (a > 5); ERROR:  check constraint "x_id_...

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 |  ...

 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 pa...

In the  last  blog, you saw how PostgreSQL keeps old versions of rows around within a transaction just in case there is a need to unwind or roll back the transaction. So, what happens to those rows that have been replaced after the transaction is complete? The answer is nothing. They sit there, taking up disk space.  How Do You Get That Disk Space Back? PostgreSQL's design keeps the old rows around. In the early days of the PostgreSQL project, the idea was that those old rows could be rolled off to a WORM drive (write-once read many), or you could 'time travel' through the data. Those were grand goals, but sadly, practicality and the cost of disk space set them aside. Our first example shows the creation of a table and the addition of one row of data. demo=# create table v (a int); CREATE TABLE demo=# insert into v (a) values (1); INSERT 0 1 demo=#  The first row of data is stored at CTID(0,1) demo=# select a, ctid from v;  a | ctid   ---+------- ...

 We started looking at PostgreSQL transactions last time , and now we can plunge onward.  Populating a new table is an intense process. Besides the columns we declare, there are others created for us by the server. Please read the documentation on System Columns.  First Example We start by creating a simple two-column table. demo=# create table foo (a int, b int); CREATE TABLE demo=# insert into foo (a,b) values (1,2),(3,4),(5,6); INSERT 0 3 demo=# select a,b from foo;  a | b  ---+---  1 | 2  3 | 4  5 | 6 (3 rows) The server adds the system columns that can be seen if we explicitly select them. demo=# select a, b, tableoid, xmin, cmin, xmax, cmax, ctid from foo;  a | b | tableoid | xmin | cmin | xmax | cmax | ctid   ---+---+----------+------+------+------+------+-------  1 | 2 |    16401 |  776 |    0 |    0 |    0 | (0,1)  3 | 4 |    16401 |  776 |  ...