Querying data using PostgreSQL

PostgreSQL comes with so much power which is still unexplored by the developer community. There are so many ways using which you can perform multiple type of operations which are easier and can be very beneficial. Let’s see a few practical examples of them and explore the real power of PostgreSQL.

Inserting Multiple-Row Data Using a JSON Array

Let’s see how to insert some rows into, say, a users table that contains fields like name, email, mobile, username, etc.

INSERT INTO users(name, email, mobile, username) SELECT name, email, mobile, username FROM 
json_populate_recordset(null::users, '<usersJsonArrayData>');

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. And, of course, the <> operators needs 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 string using JSON.stringify (usersJsonArrayData) to pass it into the SQL query. The json_populate_recordset receives 2 parameters: one being the JSON data type, users in this case, and secondly, the JSON array data in string format. This is how, with the help of json_populate_recordset, you can insert multiple rows in your table with JSON data.

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:

UPDATE table SET column_1 = temp.column_1 FROM (SELECT column_1, column_2 FROM json_populate_recordset 
(null::table, '<json_array>')) 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.

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, in case if you wish to return the inserted data or the inserted id in the API response without running 2 different queries, this can be very beneficial for you. You just have to put RETURNING * at the end where * denotes returning all the columns.

INSERT INTO table (column_1, column_2) VALUES (value_1, value2) RETURNING *;

This is not only applicable in case of an INSERT query but you can also apply the same in the UPDATE as well as DELETE queries. Also, 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 comma. Let’s see how can we do that:

UPDATE table SET column_1 = value_1 WHERE column_2 = value_2 RETURNING column_1, column_2 AS new_column_2;

Upserting data in a table

You might have faced many situations where you need to run 2 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 code. Here, PostgreSQL has made your life very easy with it’s ON CONFLICT clause to upsert (update data if already present, else insert) the data directly. Check out the following query:

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;

Here, as you can see we have updated the row whenever there is a conflict on column_3 which must be unique. A unique column like primary key, for eg. will always have unique values and if duplicate values occur, conflict will come 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 both the values of 2 different columns namely column_1 and column_2. So, here we only need to update the different column when there is a case of constraint violation.

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, in case when you only need to insert the rows and there can be rows which can cause conflict on unique columns, then you can simply don’t update the row if there is a violation, else insert the row. Let’s see how to achieve this using DO NOTHING :

INSERT INTO table(column_1, column_2) VALUES (value_1, value_2) ON CONFLICT (column_3) DO NOTHING;


Conclusion

These functions and ways will enable you query data very fast, creating/modifying hundreds of rows together even though it is working on JSON data. I hope this article makes your life easier and makes you confident enough to understand and apply the real power of PostgreSQL.