Unlocking the Power of PostgreSQL with Practical Examples for Developers: A PostgreSQL Tutorial
Introduction
PostgreSQL comes with so much power that remains unexplored by the developer community. There are numerous ways to perform various operations that are easier and can be extremely beneficial. In this PostgreSQL Tutorial, let’s dive into a few practical examples and explore the real power of PostgreSQL.
1. Inserting Multiple-Row Data Using a JSON Array
Let’s see how to insert some rows into a users table that contains fields like name, email, mobile, username, etc.
“`sql
INSERT INTO users(name, email, mobile, username)
SELECT name, email, mobile, username
FROM json_populate_recordset(null::users, ”);
“`
Note: Whenever I am using `<…>`, that means either it’s some variable defined in the code or it’s some kind of custom statement (like, for example, a comparison statement) which needs to be modified as per your own use case. The `<…>` operators need to be removed from the final query.
In the above query, we are inserting a JSON array data stored in a variable called usersJsonArrayData. We are stringifying the JSON data to convert it into a string using `JSON.stringify(usersJsonArrayData)` to pass it into the SQL query. The `json_populate_recordset` receives two parameters: one being the JSON data type, users in this case, and secondly, the JSON array data in string format. This is how you can insert multiple rows in your table with JSON data.
2. Updating Multiple Rows Using a JSON Object Array
Yes, you heard it right. You can update multiple data at once using a JSON array in the following way:
“`sql
UPDATE table
SET column_1 = temp.column_1
FROM (SELECT column_1, column_2
FROM json_populate_recordset(null::table, ”)) AS temp
WHERE table.column_2 = temp.column_2;
“`
Here, in the above query, we are updating the table’s column_1 with data fetched/obtained from a JSON object array.
3. Returning Inserted Data After Insertion
PostgreSQL allows you to insert the data into the table and send back the rows inserted for your reference. So, if you wish to return the inserted data or the inserted ID in the API response without running two different queries, this can be very beneficial for you. You just have to put `RETURNING *` at the end where `*` denotes returning all the columns.
“`sql
INSERT INTO table (column_1, column_2)
VALUES (value_1, value_2)
RETURNING *;
“`
This is not only applicable in case of an INSERT query but can also be applied in the UPDATE as well as DELETE queries. If you wish to return only one or more than one columns rather than returning all the columns, instead of `*` you can mention the column names separated by commas. Let’s see how we can do that:
“`sql
UPDATE table
SET column_1 = value_1
WHERE column_2 = value_2
RETURNING column_1, column_2 AS new_column_2;
“`
4. Upsetting Data in a Table
You might have faced many situations where you need to run two different queries together in which you have to select all the data and then check if the data is present, then update; else, insert the row with conditional statements in the code. Here, PostgreSQL has simplified this process with its `ON CONFLICT` clause to upsert (update data if already present, else insert) the data directly. Check out the following query:
“`sql
INSERT INTO table(column_1, column_2)
VALUES (value_1, value_2)
ON CONFLICT (column_3)
DO UPDATE SET column_1 = EXCLUDED.column_1, column_2 = EXCLUDED.column_2;
“`
In the above query, we have updated the row whenever there is a conflict on column_3, which must be unique. A unique column, like a primary key, will always have unique values, and if duplicate values occur, a conflict arises while inserting, in which case you will update that specific row with the details excluded from the insert query. Instead of using a unique column, you can also use a unique constraint with the `ON CONSTRAINT` clause. Let’s see an example where `table_unique_key` is a unique constraint based on the values of two different columns, namely column_1 and column_2. In this situation, you only need to update the different column when there is a case of constraint violation.
“`sql
INSERT INTO table(column_1, column_2, column_3)
VALUES (value_1, value_2, value_3)
ON CONFLICT ON CONSTRAINT (table_unique_key)
DO UPDATE SET column_3 = EXCLUDED.column_3;
“`
Additionally, if you only need to insert the rows and there can be rows that might cause conflict on unique columns, you can simply choose not to update the row if there is a violation, else insert the row. Let’s see how to achieve this using `DO NOTHING`:
“`sql
INSERT INTO table(column_1, column_2)
VALUES (value_1, value_2)
ON CONFLICT (column_3)
DO NOTHING;
“`
Conclusion
These functions and methods will allow you to query data very quickly, creating/modifying hundreds of rows together, even when working with JSON data. I hope this PostgreSQL Tutorial makes your life easier and boosts your confidence to understand and apply the real power of PostgreSQL.
Do you like to read more educational content? Read our blogs at Cloudastra Technologies or contact us for business enquiry at Cloudastra Contact Us