Statement Tricks: UPSERTs, RETURNING, and CTEs

Before taking a closer look at server-side programming, it is worth exploring some of the cool features PostgreSQL provides that allow us to enhance statements. Sometimes, developers start working in a rush to solve a specific problem, which often means writing a function, a routine, or a whole program just to inspect some data. The features provided by PostgreSQL alleviate the need to do this.

This chapter will look at some of these features, and will offer hints and tricks so that you can gain as much as possible from ordinary statements. This will not only make any problems simpler to address, but will also improve your database experience.

In particular, in day-to-day database activity, there is often the need to get back auto-generate values (for example, keys, dates, timestamps, and so on), fix insertion conflicts, and even recursing on a flat data set.

So, after reading this chapter, we will have learned the following:

• How to solve tuple insertion conflicts
• How to get back automatically generated tuple data, such as incremental keys or timestamps
• How to write better statements with Common Table Expressions
• How to perform recursion at a statement level using Recursive Common Table Expressions