Two handy examples of the psql \copy meta-command.This post will focus on the regexp portion of the query and future post(s) will cover the actual UPDATE and logic.ĭo you need to load CSV data into your PostgreSQL database tables? Have a look at these blog posts I wrote using both \ copy and COPY with simple examples: So, a successful UPDATE must: SET the ‘has_parent’ column to TRUE along with the ‘parent_id’ column to the name of the original parent pipe, all based off of the ‘asset_name’ values ending letter. Imagine records are loaded into this table from a CSV file and my daily requirement is to update this table reflecting a TRUE value for column ‘has_parent’ along with the ‘parent_id’ for those pipe assets that have been cut. Therefore, table ‘pipe_asset’ is implemented for this purpose, storing cut pipe assets with the corresponding origin pipe name from which is was cut. Typically pipe assets are labeled with some form of identification (I’ve mostly been exposed to numerical naming conventions in my career) and when a piece is cut, it is typically renamed with an Alphabet character tacked on the end of the original name. When these pipe assets are cut, that essentially creates a new piece of pipe – also called a PUP for ‘Partial Unit of Pipe’ – that must be tracked and accounted for. In the world of Energy pipelines, the pieces of pipe used to construct the line, are often cut for any number of reasons. Let’s take a moment and discuss the significance of the table columns. The table structure I am using is relatively simple, having 3 columns: ‘asset_name’, ‘has_parent’, and ‘parent_id’.Īt this point, columns ‘has_parent’ and ‘parent_id’ have their default values: FALSE and the NULL marker respectively. Now that we have an idea of what a regexp is, let’s see the table and data for this example. “A regular expression, regex or regexp (sometimes called a rational expression) is a sequence of characters that define a search pattern.” Here is a portion of the Wikipedia definition: What exactly is a regular expression (or regexp as it is also called)? Since coffee is my favorite drink, you can even buy me one if you would like! If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Xubuntu Linux 18.04.2 LTS (Bionic Beaver).It by no means depicts actual data belonging to or being used by any party or organization. Note: All data, names or naming found within the database presented in this post, are strictly used for practice, learning, instruction, and testing purposes.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |