Tag: sql help
If you have SQL questions, we have answers – get step-by-step instructions for how to fix what ails you right here in CYBERTEC’s SQL help blog posts.
TDE: A dive into encrypted data
How to check if TDE works? Some days ago a customer asked me how to check if TDE works and files on disk are encrypted. So I installed a new virtual machine with Ubuntu and installed our PGEE-Demo on it. After installation, the new created cluster is unencrypted. So we can try to create a […]
ERROR: current transaction is aborted in PostgreSQL
Whenever you actively use transaction blocks in PostgreSQL “ERROR: current transaction is aborted, commands ignored until end of transaction block” is by far most often seen error message of them all. However, this does not imply that it is also the most widely understood error message of them all. This blog will hopefully provide some […]
Kill long running queries in PostgreSQL
How to terminate long running queries in PostgreSQL: People might be fans of SQL, people might like PostgreSQL on Kubernetes or people might even love database ORMs. But have you ever heard of a single person who loves slow queries? In 20+ years of professional database engineering, I have not seen a single fan club […]
Citus: 7 commonly used advanced SQL tools
When you run advanced SQL in Citus, what’s possible? Which SQL statements work, and which ones don’t? Citus is a PostgreSQL extension that adds powerful sharding capabilities to PostgreSQL. However, every solution does have limitations. Therefore, it makes sense to take a look at the latest version of Citus and learn how to properly use […]
“hired” vs. “fired” – fuzzy search in PostgreSQL
When dealing with data (and life in general) small things can have a major impact. The difference between “hired” and “fired” is just one simple character, but in many cases it does have real world implications. The question is: How can we use good old community Open Source PostgreSQL to do a fuzzy search, in […]
Exclusion constraints in PostgreSQL and a tricky problem
Exclusion constraints are a feature that is not very well known, but can be used to implement highly sophisticated constraints. A few years ago, Hans wrote his blog post about EXCLUDE USING GIST… WITH. Recently we received a note from someone dealing with a very tricky problem concerning exclusion constraints: (many thanks to @necrotikS at […]
Conditional foreign keys and polymorphism in SQL: 4 Methods
This article is about the notoriously difficult problem of “conditional foreign keys”. In object-oriented programming languages, polymorphism is a natural concept. However, it does not translate well to SQL. As a consequence, many people whose data models are driven by an object-oriented application design end up trying to implement conditional foreign keys. In this […]
Killing performance with PostgreSQL partitioning
PostgreSQL is a powerful database which supports partitioning. In contrast to Oracle partitioning is part of the PostgreSQL core engine and does not need any additional licensing or extensions. If you migrate from Oracle to PostgreSQL this will be a major advantage. However, just like any other technology, partitioning is not without risk. While it […]
Reproducible builds: a PostgreSQL query optimization example
This post shows how to optimize a slow query that came out of the Reproducible Builds project. The Reproducible Builds initiative aims to make software compilation entirely deterministic, with no variation in the output when the build is run again. This makes software supply chain attacks much harder, but has also advantages for software quality […]
Subqueries and performance in PostgreSQL
SQL allows you to use subqueries almost anywhere where you could have a table or column name. All you have to do is surround the query with parentheses, like (SELECT …), and you can use it in arbitrary expressions. This makes SQL a powerful language – and one that can be hard to read. […]
ERROR: invalid byte sequence – Fix bad encoding in PostgreSQL
(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 […]
PostgreSQL ALTER DEFAULT PRIVILEGES – permissions explained
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 privileges You cannot […]