Inserting rows using Select query results in PostgreSQL.

This blog post will cover using the query results of a SELECT statement from one table, as the values to INSERT, into another table.

Note: All data, names or naming found within the database presented in this post, are strictly used for practice, learning, instruction, and testing purposes. It by no means depicts actual data belonging to or being used by any party or organization.

I will be using Xubuntu Linux 16.04.2 LTS (Xenial Xerus) and PostgreSQL 9.6.4 for these exercises.

Continuing to work with the fab_tracking database, I will populate both the pup_assets and bend_assets tables with records from the staging_assets table depending upon the type of asset.

Objectives…

• Verify the total count of existing records in both the pup_assets and bend_assets tables.
• Verify the count of each kind column value (BEND or PIPE_JOINT) of record in the staging_assets table.
• INSERT records into to the pup_assets and bend_assets tables using a SELECT query from the staging_assets table, dependent upon the kind column value.

To start, I will verify the count in both of the target tables with the following SELECT statements:

The count of each of the kind columns in the staging_assets table is shown as well:

Share me…

Now that we know exactly how many kinds of assets are stored in the staging_assets table, let’s INSERT them into their respective tables.

The INSERT command creates new rows in a database table by populating the table columns with values. One of the numerous available options is the ability to use the results of a SELECT query, as the input values for the table columns.

First, we can SELECT all assets with the kind column value of ‘BEND’ to INSERT into the bend_assets table using the following command:

Note: A successful INSERT returns a command tag of the number of rows inserted or updated.

Now lets’ verify the count of records in the bend_assets table and confirm the INSERT was successful:

The bend_assets table now has a total of 31 records, confirming this INSERT was successful.

We need to SELECT and INSERT all relevant records with the PIPE_JOINT kind column value into the pup_assets table as well. That command is shown below:

And checking the count of the pup_assets table verifies the operation was also successful:

Closing.

Using INSERT in conjunction with SELECT command query results, allows us to take full advantage of the staging_assets table. By first housing our data in this temporary table, any data cleaning tasks can be completed, prior to then using these rows, for inserting to their common storage table. Feel free to peruse the detailed documentation concerning the INSERT statement for all options.

Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.

To receive notifications for the latest post from “Digital Owl’s Prose” via email, please subscribe by clicking the ‘Click To Subscribe’ button in the sidebar!

Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided are performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.