Introduction
Working effectively with databases often means needing immediate feedback from operations. PostgreSQL’s ‘RETURNING’ clause with the ‘INSERT’ statement is a powerful feature that helps in retrieving values from the inserted row, enhancing both performance and code clarity.
Basic Usage of ‘RETURNING’
To start with the basics, the ‘RETURNING’ clause can be used to return column values of the newly inserted row. Here’s how this can be done in its simplest form:
INSERT INTO employees (name, position) VALUES ('John Doe', 'Software Engineer') RETURNING id;
We inserted a new employee and immediately got the id of the new record without needing to execute a separate SELECT query.
Returning Multiple Columns
In situations where you need more than one column’s value, the ‘RETURNING’ clause can include multiple columns:
INSERT INTO employees (name, position) VALUES ('Jane Doe', 'Project Manager') RETURNING id, name, start_date;
This inserts a new record and returns the ‘id’, ‘name’, and hypothetical ‘start_date’ of that new employee.
Combining with Data Modification Language (DML)
You can use ‘RETURNING’ to receive data from an insert operation that is performed by another DML statement like ‘WITH’. This could be used for complex workflows that include inserting into various tables at once:
WITH new_project AS (
INSERT INTO projects (name, deadline) VALUES ('New Website', '2023-12-31') RETURNING id
)
INSERT INTO tasks (project_id, description) VALUES ((SELECT id FROM new_project), 'Initial Planning') RETURNING id;
This creates a new project and a new task associated with that project in a single query while returning the id of the newly created task.
Conditional Insertions with ‘RETURNING’
The ‘RETURNING’ clause becomes even more potent when combined with conditional statements, such as ‘INSERT … ON CONFLICT’. This allows you to handle unique constraints and return either the inserted row or the previously existing data:
INSERT INTO employees (email, name, position) VALUES ('[email protected]', 'John Doe', 'Developer') ON CONFLICT (email) DO NOTHING RETURNING id, email;
Should the email already exist, the output would be empty; otherwise, you will get the id and email of the newly inserted employee.
Working with Joins
‘RETURNING’ can be used alongside ‘JOIN’ to provide even more contextual information about the inserted rows:
INSERT INTO employee_contacts (employee_id, contact_type, contact_value) VALUES (1, 'Phone', '+1234567890') RETURNING *, (SELECT department FROM employees WHERE employees.id = employee_contacts.employee_id) AS department;
This returns all columns from ’employee_contacts’ plus the department of the associated employee derived through a subquery.
Using ‘RETURNING’ in Functions
More advanced usage of the ‘RETURNING’ clause involves wrapping it inside custom SQL functions for consistent and reusable DML operations:
CREATE OR REPLACE FUNCTION insert_employee(full_name TEXT, job_position TEXT) RETURNS TABLE (employee_id INT, employee_email TEXT) AS $
BEGIN
INSERT INTO employees (name, position) VALUES (full_name, job_position) RETURNING id, email INTO employee_id, employee_email;
RETURN NEXT;
END;
$ LANGUAGE plpgsql;
Calling this function will execute the ‘INSERT’ statement and return the ‘id’ and ’email’ to the caller.
Handling with Arrays and ‘RETURNING’
PostgreSQL allows inserting multiple rows in a single ‘INSERT’ statement and using the ‘RETURNING’ clause to retrieve each new row’s data. Here’s how this could look with arrays:
INSERT INTO employees (name, position) VALUES ('Alice Blue', 'Designer'), ('Bob Grey', 'Analyst') RETURNING id;
This inserts multiple employees simultaneously and returns a list of the generated ‘id’ values for each new row.
Transaction Control with ‘RETURNING’
Last but not least, the ‘RETURNING’ clause also works seamlessly within transactions. This means that it honors the atomicity of transactions and will only return the values if the transaction is successfully committed:
BEGIN;
INSERT INTO employees (name, position) VALUES ('Carl White', 'Manager') RETURNING id;
-- More operations
COMMIT;
If the transaction fails and rolls back, no values will be returned, maintaining the data integrity at all times.
Conclusion
Through a variety of examples, we’ve seen the flexibility and power of the ‘RETURNING’ clause in PostgreSQL. By returning information after a row is inserted, we can write more efficient and robust applications. As seen, the ‘RETURNING’ clause can be a game-changer when it comes to database operations in PostgreSQL, significantly reducing the complexity and number of queries required.
