Tag: sql
PostgreSQL 15: Using MERGE in SQL
It’s been many years since the first ideas to implement the MERGE command surfaced on the PostgreSQL mailing list. Many years later, this important feature has finally made it into PostgreSQL core, and will (as of today) make it into PostgreSQL 15. To show people how this vital command works, I have decided to come […]
PostgreSQL: mapping timestamps (date_bin)
date_bin and timestamps in PostgreSQL Date and time are relevant to pretty much every PostgreSQL application. A new function was added to PostgreSQL 14 to solve a problem which has caused challenges for many users out there over the years: How can we map timestamps to time bins? The function is called date_bin. What people […]
Primary Keys vs. UNIQUE Constraints in PostgreSQL
Most of my readers will know about primary keys and all kinds of table constraints. However, only a few of you may have ever thought about the difference between a primary key and a UNIQUE constraint. Isn’t it all just the same? In both cases, PostgreSQL will create an index that avoids duplicate entries. So […]
SHOW TABLES in PostgreSQL: what’s wrong with it?
In this article, I will answer the questions: why isn’t there a SHOW TABLES command in PostgreSQL, when will SHOW TABLES in PostgreSQL be available, and how do I list tables with native PostgreSQL methods? Why isn’t there a SHOW TABLES command in PostgreSQL? People who come from MySQL are always asking the same question: […]
PostgreSQL: LIMIT vs FETCH FIRST ROWS … WITH TIES
Most people in the SQL and in the PostgreSQL community have used the LIMIT clause provided by many database engines. However, what many do not know is that LIMIT / OFFSET are off standard and are thus not portable. The proper way to handle LIMIT is basically to use SELECT … FETCH FIRST ROWS. However, […]
About cross join in PostgreSQL
© Laurenz Albe 2021 For many people, “cross join” is something to be afraid of. They remember the time when they forgot the join condition and the DBA was angry, because the query hogged the CPU and filled the disk. However, there are valid use cases for cross joins which I want to explore in […]
Tips and tricks to kick-start the Postgres year 2021
I decided to start out this year by looking into my notes from last year and extracting from them a small set of Postgres tips for you. This might not be, strictly speaking, fresh news… but I guess the average RAM of tech workers is about 64KB or so nowadays, so some repetition might not […]
Updates for the Postgres Showcase project
In order to prepare for the start of training season (you can see our “stock” training offerings here by the way), I’ve updated our small “Postgres features showcase” project, and thought I’d echo it out too. Main changes – coverage on some features of the current v11 release and also from the upcoming v12. Short […]
Avoiding “OR” for better query performance
PostgreSQL query tuning is our daily bread at CYBERTEC, and once you have done some of that, you’ll start bristling whenever you see an OR in a query, because they are usually the cause for bad query performance. Of course there is a reason why there is an OR in SQL, and if you […]
Flexible grouping: Some dirty SQL trickery
While doing PostgreSQL consulting for a German client, I stumbled over an interesting issue this week, which might be worth sharing with some folks out on the Internet, it’s all about grouping. Suppose you are measuring the same thing various times on different sensors every, say, 15 minutes. Maybe some temperature, some air pressure or […]