(Inscription seen on a T-shirt) It’s annoying to get error messages caused by encoding problems. But it is more annoying not to get error messages and end up with bad data in the database. I’ll show you how to fix bad encoding. Some basic facts about encoding in PostgreSQL Each PostgreSQL database has a server […]

© Laurenz Albe 2023 Many people have heard that ALTER DEFAULT PRIVILEGES makes it easy to allow other users access to tables. But then, many people don’t understand the command in depth, and I hear frequent complaints that ALTER DEFAULT PRIVILEGES does not work as expected. Read on if you want to know better! Default […]

© Laurenz Albe 2023 Different from many other database systems, PostgreSQL does not support query hints. That makes it difficult to force the hand of the query planner when it comes to a certain join order that you know to be good. This article explains how you can influence execution plans in PostgreSQL. Why no […]

© Laurenz Albe 2023 A while ago, I wrote about how difficult it is to get an execution plan for a parameterized query. The method suggested in that article works, but is still somewhat complicated. So I wrote a patch to support an EXPLAIN option GENERIC_PLAN, which provides native support for that. My patch got […]

© Laurenz Albe 2023 The PostgreSQL documentation has some information about row locks. But typically, you cannot see them in pg_locks, and not everybody knows how they work and how to track and debug row locks. This article intends to give some insight into how PostgreSQL row locks work “under the hood”. Why are there […]

Data isn’t only about storage and accumulation – sometimes it’s also about deletion, cleanup and archiving. In SQL there’s more than one way to empty a table. Two essential methods are available: DELETE TRUNCATE DELETE vs. TRUNCATE Both commands serve totally different purposes, which are sometimes not fully understood. The key difference is that DELETE […]

Stored procedures are a core concept which can be found in most relational database systems. They have proven to be useful in many areas and have been widely adopted by developers and DBA’s alike. Stored procedures basics In PostgreSQL stored procedures have been around for a number of years. The syntax of this important command […]

PostgreSQL provides all kinds of time-related functions. But time is not as easy as it seems. One question many people keep asking: How can I find the current timestamp? What time is it? While this seems like an easy question the answer isn’t as clear-cut as you might expect. Basically, there are three functions we […]

Analyzing time series often comes with challenges. This is especially true if you want to do a bit more than just run simple counts on values in a column. More often than not you’ll want to create a month’s report, a weekly overview and so on. This requires you to group on expressions rather than […]

What is LISTEN and NOTIFY? LISTEN / NOTIFY is a feature that enables users to listen to what goes on in the database. It is one of the oldest functionalities in PostgreSQL and is still widely used. The main question is: What is the purpose of the asynchronous query interface (LISTEN / NOTIFY), and what […]

GROUP BY is nothing new and is available in any relational database I am aware of. It is an integral part of SQL and PostgreSQL but what many people might not know is the fact that the GROUP BY expression can do more than just group by simple fields. You can use expressions to group […]

© Laurenz Albe 2020 Many people consider recursive queries a difficult topic. Still, they enable you to do things that would otherwise be impossible in SQL. This articles gives a simple introduction with examples and shows the differences to Oracle’s implementation of recursive queries. Common table expressions (WITH clauses) A common table expression (CTE) can […]