Postgres Pearl: Discovering the Treasured and Precious Features of PostgreSQL (part-1)

Topic 1: Returning Clause: Retrieving Inserted Data

When performing an INSERT operation in PostgreSQL, the default behavior is to insert the data into the table without returning any information about the inserted row. However, there are situations where we may need to retrieve the inserted data immediately after the INSERT operation. This is where the RETURNING clause comes into play.

Example: Retrieving Inserted Data

Consider the following scenario where we have a table called customers with columns customer_id and first_name.

-- Inserting a new customer and retrieving the inserted data
INSERT INTO customers (first_name)
VALUES ('Adams')
RETURNING *;

The RETURNING * clause allows us to fetch the data of the inserted row. This query will return the inserted row(s) as a result set, displaying all columns and their corresponding values.

Output:

We can also specify a specific column to retrieve its value. For example:

-- Inserting a new customer and retrieving the inserted customer_id
INSERT INTO customers (first_name)
VALUES ('Smith')
RETURNING customer_id;

Output:

In this case, only the customer_id column is returned, providing us with the value of the inserted customer_id.

Topic 2: UPSERT: Handling Conflicts during Insertion

In PostgreSQL, the UPSERT operation allows us to handle conflicts that may arise when inserting data. If the data we are inserting conflicts with an existing row in the table, we can choose to either update the existing row or take no action.

Example: UPSERTing Data

Consider the following scenario where we have a table called customers with columns customer_id, first_name, and last_name.

-- UPSERT: Update or insert a new customer based on customer_id
INSERT INTO customers (customer_id, first_name)
VALUES (2, 'Black-Smith')
ON CONFLICT (customer_id)
DO
UPDATE SET first_name = 'Mr.', last_name = EXCLUDED.first_name;

In this example, we are inserting a new customer with customer_id = 2 and first_name = 'Black-Smith'. If a conflict occurs on the customer_id column, we use the ON CONFLICT clause to specify the desired action. Here, we choose to update the existing row with the new values specified in the UPDATE SET clause.

Data (Before UPSERT):

Data (After UPSERT):

As seen in the output, the existing row with customer_id = 2 is updated with the new values of first_name = 'Mr.' and last_name = 'Black-Smith'.

We can also choose to take no action when a conflict occurs by using the DO NOTHING clause.

-- UPSERT: Insert a new customer if the customer_id does not exist
INSERT INTO customers (customer_id, first_name)
VALUES (2, 'Jacks')
ON CONFLICT (customer_id)
DO NOTHING;

Data (Before UPSERT):

Data (After UPSERT):

As shown in the output, since a row with customer_id = 2 already exists, the DO NOTHING clause prevents any modification to the existing row, leaving it unchanged.

By utilizing the Returning Clause and UPSERT functionality, PostgreSQL empowers us with greater control and flexibility during data insertion and retrieval, making it an invaluable tool for data management and manipulation.

Stay tuned for more exciting features and insights in our "Postgres Piquancy" series as we delve deeper into the world of PostgreSQL.