This post will demonstrate mock asset tracking with PostgreSQL for a pipe fabrication setting. The typical data one would track and record in this setting are bends, weld id’s, and joints of pipe. We will see powerful queries from multiple tables, return informative and useful data with the JOIN command.
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, along with the Linux command line.
Objectives…
- Describe and explore the table structure for all tables in the database.
- Query and discover any data present within the tables.
- Use JOINS to obtain meaningful query results from multiple tables.
The database presented in this post is a mock example of a Pipeline Pipe/Bend fabrication weld-mapping/tracking system.
Let’s discover the tables and data being used in the fab_tracking example database.
Using the psql ‘\d’ command joined with the table name (‘describe tables’), we can see the table structure and data types:
fab_tracking=> \d bend_assets
Table "public.bend_assets"
Column | Type | Modifiers
----------------+-----------------------+---------------
custom_id | character varying(20) | not null
bnd_pipe_id | character varying(20) |
bnd_heat | character varying(20) |
bnd_length | numeric(3,1) |
degree | numeric(3,1) |
wall_thickness | numeric(4,3) | default 0.000
Indexes:
"bend_assets_pkey" PRIMARY KEY, btree (custom_id)
Check constraints:
"bnd_len_chk" CHECK (bnd_length > 0.0)
Referenced by:
TABLE "weld_log" CONSTRAINT "weld_log_bnd_connect_fkey" FOREIGN KEY (bnd_connect) REFERENCES bend_assets(custom_id)
The bend_assets table contains 6 columns made up of 3 character varying (character) and 3 numeric (number) data types. The custom_id column is the PRIMARY KEY for this table and is referenced by the not-yet-seen weld_log table. Also, a CHECK constraint on the bnd_length column ensures a length of greater than 0.0 must be entered into this column on INSERT and UPDATE operations to prohibit storing negative length values.
The wall_thickness column has the ability to store a default value of 0.000 should no value be entered during INSERT and UPDATE operations likewise.
Now querying the bend_assets table returns:
fab_tracking=> SELECT * FROM bend_assets; custom_id | bnd_pipe_id | bnd_heat | bnd_length | degree | wall_thickness ------------+-------------+----------+------------+--------+---------------- Joint-9167 | 7777T | 89A-11 | 7.2 | 45.2 | 0.530 Joint-2982 | 8989A | 59595 | 4.5 | 22.2 | 0.530 Joint-2991 | 7777B | 292A | 9.5 | 25.5 | 0.630 Joint-4982 | 8989A | 595 | 6.2 | 12.2 | 0.570 Joint-3987 | 8989A | 595AZ | 14.5 | 22.2 | 0.590 Joint-1188 | 8331T | 898888 | 9.2 | 45.2 | 0.630 Joint-1987 | 8989 | 49565 | 4.5 | 6.2 | 0.630 Joint-2940 | 7767 | 292 | 9.5 | 5.5 | 0.630 (8 rows)
As shown above, the bend_assets table has 8 rows, which represents 8 ‘factory’ bends of pipe with various bend degrees.
The next table structure we will look at is the pup_assets table:
fab_tracking=> \d pup_assets
Table "public.pup_assets"
Column | Type | Modifiers
--------------------+-----------------------+---------------
custom_pup_id | character varying(20) | not null
pup_pipe_id | character varying(20) |
pup_heat | character varying(20) |
pup_length | numeric(3,1) |
pup_wall_thickness | numeric(4,3) | default 0.000
Indexes:
"pup_assets_pkey" PRIMARY KEY, btree (custom_pup_id)
Check constraints:
"pup_len_chk" CHECK (pup_length > 0.0)
Referenced by:
TABLE "weld_log" CONSTRAINT "weld_log_pup2_connect_fkey" FOREIGN KEY (pup2_connect) REFERENCES pup_assets(custom_pup_id)
TABLE "weld_log" CONSTRAINT "weld_log_pup_connect_fkey" FOREIGN KEY (pup1_connect) REFERENCES pup_assets(custom_pup_id)
Sharing many similarities with the bend_assets table, except containing 1 less numeric column, the pup_assets table incorporates the same CHECK constraint for its pup_length column and default value constraint for its pup_wall_thickness column. The custom_pup_id column is this table’s PRIMARY KEY and is referenced by 2 columns in the weld_log table. Let’s also see the data in this table:
fab_tracking=> SELECT * FROM pup_assets; custom_pup_id | pup_pipe_id | pup_heat | pup_length | pup_wall_thickness ---------------+-------------+----------+------------+-------------------- Joint-1982 | 49022 | 494A-22 | 14.5 | 0.515 Joint-11 | 4902271 | 494A-22 | 15.0 | 0.515 Joint-12 | 490228 | 494A-52 | 11.3 | 0.515 Joint-13 | 39022 | 494A-66 | 12.7 | 0.515 Joint-14 | 89022 | 494T-22 | 17.9 | 0.515 Joint-15 | 29011 | 494u-22 | 10.9 | 0.515 Joint-33 | 49009 | 494A-1V | 9.1 | 0.515 Joint-34 | 19444 | 494A | 18.2 | 0.515 Joint-35A | 59022 | 49488 | 14.5 | 0.515 Joint-35 | 99022 | 555511 | 10.5 | 0.515 Joint-1182 | 47042 | 494B429 | 16.5 | 0.515 Joint-21 | 49027 | 454A-22 | 13.0 | 0.515 Joint-32 | 44022 | 497A-52 | 10.3 | 0.515 Joint-43 | 33322 | 194A-66 | 18.7 | 0.515 Joint-54 | 77022 | 294T-22 | 12.9 | 0.515 Joint-65 | 29077 | 394U-22 | 16.9 | 0.515 (16 rows)
This table contains 16 rows which represent 16 short joints of pipe.
Together these tables can be thought of as a ‘master’ pipe tally of bends and shorter pieces of pipe to be welded together.
Let’s do that now.
fab_tracking=> \d weld_log
Table "public.weld_log"
Column | Type | Modifiers
--------------+-----------------------+-----------
xray_id1 | character varying(20) | not null
bnd_connect | character varying(20) |
pup1_connect | character varying(20) |
xray_id2 | character varying(20) |
pup2_connect | character varying(20) |
Indexes:
"weld_log_pkey" PRIMARY KEY, btree (xray_id1)
Foreign-key constraints:
"weld_log_bnd_connect_fkey" FOREIGN KEY (bnd_connect) REFERENCES bend_assets(custom_id)
"weld_log_pup2_connect_fkey" FOREIGN KEY (pup2_connect) REFERENCES pup_assets(custom_pup_id)
"weld_log_pup_connect_fkey" FOREIGN KEY (pup1_connect) REFERENCES pup_assets(custom_pup_id)
The weld_log table contains all character varying data types for its 5 columns. The multiple FOREIGN KEY constraints are worth glancing over. The bnd_connect column references the custom_id column in the bend_assets table. Meaning this column(bnd_connect) can only contain values found in that column(the custom_id column from the bend_assets table), thus ensuring referential integrity. Both pup1_connect and the pup2_connect columns reference the custom_pup_id column found in the pup_assets table, also granting the same referential integrity.
This FOREIGN KEY relationship with column(s) from other tables will come into play whenever we are ready to query multiple tables using a JOIN.
Looking at the data in the weld_log table:
fab_tracking=> SELECT * FROM weld_log; xray_id1 | bnd_connect | pup1_connect | xray_id2 | pup2_connect -------------+-------------+--------------+-------------+-------------- Xray-22CLI | Joint-9167 | Joint-1982 | Xray-23ACT | Joint-11 Xray-12FB | Joint-2982 | Joint-12 | Xray-211FB | Joint-13 Xray-152ML | Joint-2991 | Joint-14 | Xray-55TR | Joint-15 Xray-552CTI | Joint-4982 | Joint-34 | Xray-955ML | Joint-33 Xray-512RB | Joint-3987 | Joint-35A | Xray-155TI | Joint-35 Xray-957MLB | Joint-1188 | Joint-1182 | Xray-705MLR | Joint-21 Xray-457TI | Joint-1987 | Joint-32 | Xray-775ML | Joint-43 Xray-457RB | Joint-2940 | Joint-65 | Xray-415ML | Joint-65 (8 rows)
This last example table contains a basic ‘weld map’ consisting of two welds labeled xray_id1 and xray_id2 respectively, joining one end of a bend, represented by the bnd_connect column with a short pup of pipe, represented by either the pup1_connect or pup2_connect. Since a bend needs to be welded on both ends to a pipe (the pup), the xray_id2 column represents that second weld. The image below depicts a rough representation of this weld map
I need more info…
While the weld_log table is certainly helpful and informative, often times more information from the attributes are needed for the bends and pups that make up a ‘fabrication section’.
I can re-query each needed table with the previous SELECT statements and ‘sort of’ put it all together myself. However, that is not productive.
With PostgreSQL (and SQL in general), there is a better way.
JOINS
Suppose you need all the information from the weld_log table, in addition to, all attributes for asset Joint-3987 from the bend_assets table. How can you do that?
With a JOIN.
A JOIN is a query that accesses multiple rows from either the same table or multiple tables at once.
The below INNER JOIN query will retrieve and return the exact information needed.
fab_tracking=> SELECT w.xray_id1, w.bnd_connect, b.bnd_pipe_id, b.bnd_heat, b.bnd_length,
b.degree, b.wall_thickness, w.pup1_connect, w.xray_id2, w.pup2_connect
FROM weld_log AS w
INNER JOIN bend_assets AS b
ON w.bnd_connect = b.custom_id
WHERE w.bnd_connect = 'Joint-3987';
xray_id1 | bnd_connect | bnd_pipe_id | bnd_heat | bnd_length | degree | wall_thickness | pup1_connect | xray_id2 | pup2_connect
------------+-------------+-------------+----------+------------+--------+----------------+--------------+------------+--------------
Xray-512RB | Joint-3987 | 8989A | 595AZ | 14.5 | 22.2 | 0.590 | Joint-35A | Xray-155TI | Joint-35
(1 row)
There is a lot to digest in this query so I will highlight what is taking place within it.
The SELECT clause specifies all the columns you want to be returned in the query results. Touching on the odd-looking prefixes as shown below on the lines,
weld_log AS w
and
bend_assets AS b
These are aliases for the weld_log and bend_assets tables that are prefixed on the different column names in the SELECT statement, depending on which table the column value needs to be returned from. Using this ‘table_name_alias.column’ notation is known as qualifying the columns.
Note: It is perfectly valid for columns to be qualified by prefixing them with the full table name (i.e., table_name.column_name) in lieu of an alias.
In other words, if multiple tables share the same column name, you must be explicit and instruct PostgreSQL exactly which column from which table you want to be returned. At this point, we do not have any columns that share the same name, however, it is good programming practice to always qualify the column name in your SELECT clause when performing JOINS on multiple tables. Should the database and tables be updated in the future, and identical column names are added into any of the tables, your queries or program will not crash or throw errors since you are qualifying all columns anyway.
FROM weld_log AS w
This FROM clause is instructing PostgreSQL to return columns from the weld_log table.
INNER JOIN bend_assets AS b
The INNER JOIN command is specifying the table (bend_assets) to JOIN with the weld_log table in the FROM clause. This line:
ON w.bnd_connect = b.custom_id
is where the FOREIGN KEY(s) from each table, are utilized. Basically, that line is saying, ‘Return all the specified columns from the specified tables where the weld_log table’s bnd_connect column value is exactly the same in the bend_assets table’s custom_id column.
We then filter the entire query with the WHERE clause by specifying that we only want the bnd_connect column value Joint-3987, from the weld_log table.
Note: There is also a LEFT OUTER JOIN, a RIGHT OUTER JOIN, and a FULL OUTER JOIN, however, they are not discussed or used here.
Retrieving all attributes.
Taking it a step further, a JOIN also allows you to query columns from more than 2 tables. The query below will return all the attributes for the bend, along with all of those pertaining to each pup of pipe welded to the bend.
fab_tracking=> SELECT w.xray_id1, w.bnd_connect, b.bnd_pipe_id, b.bnd_heat, b.bnd_length, b.degree, b.wall_thickness, w.pup1_connect, p1.pup_pipe_id, p1.pup_heat, p1.pup_length, p1.pup_wall_thickness, w.xray_id2, w.pup2_connect, p2.pup_pipe_id, p2.pup_heat, p2.pup_length, p2.pup_wall_thickness FROM weld_log AS w INNER JOIN bend_assets AS b ON w.bnd_connect = b.custom_id INNER JOIN pup_assets p1 ON w.pup1_connect = p1.custom_pup_id INNER JOIN pup_assets p2 ON w.pup2_connect = p2.custom_pup_id WHERE w.bnd_connect = 'Joint-3987';
One obvious difference between this JOIN and the previous one are more columns being specified in the SELECT clause. Another important difference is found in these lines:
INNER JOIN pup_assets p1
INNER JOIN pup_assets p2
Aren’t both of those commands referring to the pup_assets table?
Yes, in fact, they are.
This is known as a self join. Notice here I am qualifying the specific columns with either p1 or p2 to explicitly tell PostgreSQL which custom_pup_id column value in the pup_assets table to match up with the pup1_connect and the pup2_connect columns from the weld_log table. Since we are referring to that column (custom_pup_id) twice, this clears up any potential issues with ambiguity. Again, it is mandatory to be explicit with the column names.
Running the above JOIN query returns all attributes from the bnd_connect asset, both pup1_connect and pup2_connect assets, along with the welds that tie them all together, for the same bend asset as the previous example above.
xray_id1 | bnd_connect | bnd_pipe_id | bnd_heat | bnd_length | degree | wall_thickness | pup1_connect | pup_pipe_id | pup_heat | pup_length | pup_wall_thickness | xray_id2 | pup2_connect | pup_pipe_id | pup_heat | pup_length | pup_wall_thickness ------------+-------------+-------------+----------+------------+--------+----------------+--------------+-------------+----------+------------+--------------------+------------+--------------+-------------+----------+------------+-------------------- Xray-512RB | Joint-3987 | 8989A | 595AZ | 14.5 | 22.2 | 0.590 | Joint-35A | 59022 | 49488 | 14.5 | 0.515 | Xray-155TI | Joint-35 | 99022 | 555511 | 10.5 | 0.515 (1 row)
A JOIN is a powerful feature of PostgreSQL. In setups similar to the mock Fabrication Weld Tracking example, where multiple tables hold column values pertaining to each other in a significant manner, using a JOIN is a surefire way to retrieve all needed column values from the necessary tables.
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 this blog via email, please subscribe by clicking the ‘Follow Digital Owl’s Prose’ 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.