Dave's PostgreSQL Stuff

 The November 2026 DB-Engines rankings are out. PostgreSQL is the only database in the top four whose line is going up and to the right. Oracle, MySQL and Microsoft SQL Server all lost points this month, and all three are down from a year ago. PostgreSQL gained points on both counts. It now sits just 3.84 points behind SQL Server for third place. On the past year's trend, PostgreSQL could move ahead of SQL Server as early as next month. So it's time to celebrate, right? Maybe not yet. Before anyone orders the cake, let's look at what the numbers say, why people are moving, and how hard the next step up will be. The numbers Only PostgreSQL gained points this month, and only PostgreSQL is up from a year ago. Source: DB-Engines Ranking of Relational DBMS , November 2026. A little napkin math shows when the order could change: SQL Server: The gap is 3.84 points. At this month's pace, it closes in about two months. At the past year's pace, it closes in less than one. S...

 Structured Query Language 101 -15:00–16:20 Nov. 6, 2026 · 15:00 - 6:20 I will be teaching the basics of SQL at the 2026 Texas Linuxfest. The session is listed for only 100 minutes, but I wrote the materials for a 3-hour course. We will cover as much of those three hours as the audience can stand (sit?), or they send us off to Sixth Street. Tickets are available, and this event is great for networking.  Ping me if you have questions about this session.  Description: SQL is a powerful language for working with relational databases such as MySQL, PostgreSQL, SQL Server, and Oracle. This is an 80-minute introduction to writing SQL database queries. Please load a copy of DBeaver Community Edition (free, open-source) from https://dbeaver.io/download/ on your Mac, Windows, or Linux laptop to work along with the presentation. We will use the sample database that is included with DBeaver. We will start with simple SELECT statements to retrieve data, INSERT to add data, use...

 Note: I am presenting a tutorial for those who want to learn SQL at this year's Texas Linuxfest (https://pretalx.com/txlf2026/talk/DQ3XW7/) on November 6th.  This is a great opportunity at a fantastic community event and I encourage you to attend. Demo databases are good way to develop skills with Structured Query Language. Some are associate with one data store more than another, like Sakila and World with MySQL, or DVD with PostgreSQL. The Northwind database originated in the Microsoft sphere of influence but it is easy to obtain for PostgreSQL. Step 1 Go to https://github.com/pthom/northwind_psql and download https://github.com/pthom/northwind_psql/blob/master/northwind.sql This file has all you need. Step 2 I am using DBeaver Enterprise Edition 26.1.0 and open the northwind.sql file. DBeaver is an amazing data tool and makes this type of project simple.  Opening the northwind.sql file Step 3 This is that northwind.sql file in all its glory. If you are new to SQL, tak...

  DBeaver recently introduced interactive chat capabilities into the free, open-source Community Edition. What does that mean?  It means some of your database tasks can be accomplished significantly faster !  First , you can ask for information without coding SQL. I love Structured Query Language, but it is much faster to write this prompt: What are the top ten most popular rentals and how much revenue did they generate? And faster than I look at the schema, the AI answers: -- This query finds the top 10 most rented films and the total revenue they generated. select f . film_id , f . title , count ( r . rental_id ) as rental_count , coalesce ( sum ( p . amount ), 0 ) as total_revenue from film f join inventory i on f . film_id = i . film_id join rental r on i . inventory_id = r . inventory_id left join payment p on r . rental_id = p . rental_id group by f . film_id , f . title order by rental_count desc , total_revenue desc limi...

 Ever have a query 'tossed over the fence' that you find incomprehensible but still have to support it? A few years ago, you would have needed to triage the query. Obfuscated queries can be tough to decipher.  Sometimes, the query is due to someone or an ORM being clever. Many times the query is touch to read because the  DBeaver recently added its AI Chat to the free, open-source DBeaver Community Edition. And you will find it very at determining what a query does. Let's start with a simple query. Open AI Assistant Where to find the AI Assistant DBeaver 26.1.2's AI Assistant can be found on the main menu under 'AI'. Input Prompt Once you have the AI Assistant open, ask 'what does this do'? Prompt with a query Explanation The AI will examine what you gave it and report back. I am using OpenAI with the gpt-4o engine.  The Query Explained Hopefully, you were able to recognize this simple query, which reports the number of dead tuples. Okay, let us try some...

Time zones are an unfortunately complex subject when dealing with PostgreSQL. You may be running your local time zone on your on-premises server or on your own laptop. Or you may be using the time zone of your server’s physical location. And you may have set all your servers to UTC. And all are valid approaches, depending on your circumstances. DBeaver users know it is a very advanced tool for database work. But it is easy to get into time zone issues, as the default time zone for your session is taken from your client machine. But this can be adjusted. UTC? UTC or Universal Time Coordinated is a time zone standard used as a basis for all time zones worldwide. It is a constant time scale and does not change for Daylight Saving Time (DST). The benefits include streamlined cross-region data synchronization, easier debugging, accurate time-based transaction ordering, and scalability for global applications. In distributed systems, storing data in UTC ensures that logs and transactions ar...