The UPSERT statement is a way of performing an UPDATE over an INSERT, allowing an INSERT statement to be automatically rewritten as an UPDATE statement under certain conditions. In particular, if the INSERT statement cannot be performed due to a conflict between data that already exists and data that is going to be inserted, the UPSERT statement enables you to override this conflict.

This technique is similar to what many Object Relational Mappers (ORMs) do when a generic persistence action is performed. The ORM examines the object it has to persist (the data), and if it finds out that an object has never been seen on the database, it executes an INSERT; otherwise, it performs an UPDATE. In this way, the client code (and therefore the developer) never has to consider which database actions have to be performed to persist the data. This is also the...

Each write-statement (INSERT, UPDATE, or DELETE) supports an optional RETURNING predicate that makes the statement return a results set with the manipulated tuples. From a conceptual point of view, it is as if these INSERT, UPDATE, and DELETE commands are immediately followed by an automatic SELECT statement.

This feature is very useful. It allows us to get back the exact values of the manipulated tuples, such as an automatically generated key, a computed timestamp, or other non-predictable values. It also allows us to pipeline write statements one after another, as you will see later in this chapter.

Let's take a look at the RETURNING function in action. Imagine that we need to insert some random data into the files table, as follows:

testdb=> INSERT INTO files( f_name, f_hash, f_size ) 
SELECT 'file_' || v || '...

Common Table Expressions (CTEs), also known as queries, take advantage of a particular syntax when writing sub-queries that make statements a lot more readable. Readability, however, is not the only reason to use CTEs: their structure also allows for a statement pipeline of sorts, and in their recursive version, they can be used to loop through joins.

Any DML statement (such as SELECT, INSERT, UPDATE, and DELETE) can be enhanced by placing a WITH predicate before the query. Any sub-query appearing in the WITH clause can be thought of as a materialized result set over the data, or as a temporary table that exists throughout the duration of a statement. CTEs have many applications...

PostgreSQL provides advanced features that allow you to avoid writing a program to solve common problems when dealing with data structures and SQL statements. Thanks to the RETURNING predicate, it is possible for you to get back automatically generated data, such as an incremental key. Conflict resolution allows us to perform a kind of 'tuple merging' when dealing with INSERT and UPDATE statements against the same tuples, but one of its most powerful features is surely the Common Table Expression, which not only allows us to re-write complex queries by splitting them into more readable pieces, but also allows us to perform statement pipelining and recurse over a result set.

Knowing these features will allow you to interact with a PostgreSQL cluster efficiently and solve common problems in your day-to-day use quickly and easily.

In Chapter 3, The PL/pgSQL Language...

• INSERT and ON CONFLICT—Official PostgreSQL 11 documentation can be found at: https://www.postgresql.org/docs/11/static/sql-insert.html
• Common Table Expression—Official PostgreSQL 11 documentation can be found at: https://www.postgresql.org/docs/11/static/queries-with.html