Tips and tricks with the Postgres INSERT statement
Inserting a single row into a table is what comes to mind when you think of
the INSERT statement in PostgreSQL. It does, however, have a few more tricks up
it’s sleeve! Read on to discover some of the more interesting things you can
do with INSERT.
Copying in Bulk
Let’s say you want to periodically capture snapshots of a table – all rows in
the table should be copied to another table, with an additional timestamp column
denoting when the snapshot was taken. Here’s how you can create and populate the
table the first time around:
And from then on, you can use the INSERT..SELECT form of INSERT statement to
copy rows from one table and insert into another. You can fill in extra values
into the destination table row too.
In PostgreSQL 9.5, the ON CONFLICT clause was added to INSERT. This lets
application developers write less code and do more work in SQL.
Here is a table of key, value pairs:
A common use case is to insert a row only if it does not exist – and if it
does, do not overwrite. This is done with the ON CONFLICT..DO NOTHING clause
of the INSERT statement:
Another common usage is to insert a row if it does not exist, and update the
value, if it does. This can be done with the ON CONFLICT..DO UPDATE clause.
In the first case above the value of ‘host’ was overwritten with the new value,
and in the second case the value of ‘ssl’ was inserted as the third row.
Even more sophisticated use cases can be realized with DO UPDATE. Consider the
table below, where in addition to key and value, there is a column called
“accumulate”. For rows where accumulate is true, the values are meant to be
accumulated as a comma-separated string. For other rows, values are single-valued.
The WHERE clause can be used to either overwrite the “value” column, or
append into it, depending on the value of “accumulate”, like this:
The first statement did not accumulate the value of ‘3306’ into ‘port’ because
‘accumulate’ was off for that row. The next two statements added the values
‘127.0.0.1’ and ‘10.0.10.1’ into the value of ‘listen’, because ‘accumulate’
Returning Generated Values
Values generated by PostgreSQL during insert, like default values or
autoincremented SERIAL values can be returned using the RETURNING clause of
the INSERT statement.
Assume you need to generate random UUIDs as keys for rows in a table. You can
let PostgreSQL do the work of generating the UUIDs and have it return the
generated value to you like this:
Moving Rows with CTE Clauses
You can even move rows between tables with INSERT, using the WITH clause.
Here are two tables with todo lists for different years.
To move the todo items that are not yet completed in 2018 to 2019, you can
basically delete such rows from the 2018 table and insert them into the 2019
table in one shot:
To learn more about the smart little INSERT statement, check out the
pgDash is a modern, in-depth monitoring solution designed
specifically for PostgreSQL deployments. pgDash shows you information and
metrics about every aspect of your PostgreSQL database server, collected using
the open-source tool pgmetrics.
pgDash provides core reporting and visualization
functionality, including collecting and displaying PostgreSQL information and
providing time-series graphs, detailed reports, alerting, teams and more.
The Diagnostics feature in pgDash examines your PostgreSQL server and databases
scanning for potential issues that can impact the health and performance of the
deployment. No additional setup is required for Diagnostics - you just need to
be actively sending in data to pgDash. Learn more
here or signup today
for a free trial.