Tag: development
Get the best PostgreSQL development tips and tricks from CYBERTEC’s blog posts – our experts develop and test new tools and code constantly, so their blogs will give you insider knowledge you can’t get anywhere else.
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
© Laurenz Albe 2023 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 […]
Indexing “LIKE” in PostgreSQL and Oracle
© Laurenz Albe 2023 Unless you use the binary collation, creating a b-tree index to support a LIKE condition in PostgreSQL is not straightforward. This keeps surprising Oracle users, who claim that a regular b-tree index will of course always support LIKE. I decided to explore the differences between Oracle and PostgreSQL when it comes […]
Subqueries and performance in PostgreSQL
© Laurenz Albe 2023 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 […]
Unexpected downsides of UUID keys in PostgreSQL
There are various compelling reasons to use universally unique identifiers (UUID) as primary keys. Two examples are: To be able to generate keys independently of the database To move sets of related records between different databases without having to deal with renumbering everything However, like everything good in life, UUID’s come with their own downsides. […]
What is a schema in PostgreSQL?
One way to organize data in PostgreSQL is to make use of schemas. What is a schema in PostgreSQL? And more importantly: What is the purpose of a schema and how can schemas be used to make your life easier? Let’s dive in and find out. The purpose of a schema Before you figure out […]
PostgreSQL ALTER DEFAULT PRIVILEGES – permissions explained
© 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 […]
EXPLAIN (GENERIC_PLAN): New in PostgreSQL 16
© 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 […]
Parallel aggregate – PostgreSQL 16 – better performance
What is a parallel aggregate? In PostgreSQL, a parallel aggregate refers to a way of processing aggregate functions (such as SUM, AVG, MAX, MIN, etc.) on large amounts of data in a parallel and distributed manner, thereby making the query execution faster. When executing an aggregate query, the database system automatically breaks up the result […]
Breaking your PostgreSQL database with bad CHECK constraints
© Laurenz Albe 2023 I am on a spree! After writing about breaking your database and transaction ID wraparound, here I am writing about damage caused by CHECK constraints! Trust me, I don’t want to destroy PostgreSQL databases. It’s just that this Twitter message was brought to my attention and triggered memories of other reports […]
PostgREST: Get started with automated PostgreSQL APIs
This blog introduces you to PostgREST and helps you to get started with it quickly. What is PostgREST? First think about this: a modern database application usually doesn’t connect to the database directly and issue arbitrary SQL commands. In general, there is always some kind of abstraction layer in between. This layer could be Hibernate […]
Stored procedures in PostgreSQL: getting started
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 […]