Today’s Tip

In PostgreSQL, import tables to a temp table then transfer to the final destination table

Discussion

Much of the data that I work with, comes from somewhere else. If there is a reasonable assumption that I may want to access it again, I will bring it into a PostgreSQL database. The final table that this data will be loaded into is set up to match the naming and attribute type conventions of my database. This is not, however, the format I usually receive the data in. This creates a need for reformatting or modifying the data in some way. One way of doing this is by setting up a more complex import query that takes each attribute and at the time of insertion, reformats or modifies to the final form required. The other way of doing this is to import the data into a temporary table. From this temporary table, you then query the data and insert into the final table. There are a number of benefits to this method which I will discuss below.

1. Requires less complex query upon import: The most common method of importing data into PostgreSQL is using some form of the Copy function. Another way is to use a function like OGR2OGR to pull it in separately. As part of each of these, you can create an SQL function to format the data for the target as well. This can be a challenge as the type recognition is very basic, whether for text or numeric data. Bringing the data into a temp table allows a single OGR2OGR command string, like the one below.
2. Allows you time to review data for anomalies prior to final import: If you try to parse data on import, you have to be aware of data type anomalies in the process. If you have data that has errors, this may cause your import process to fail when it encounters something unexpected. If you import to a temp table, any anomalies would cause the field type to be set to text. Reviewing the temp table can alert you to these issues so you may address the problems. When you transfer to the final table, you can cast your data to the correct data type.
3. Prior planning of attributes, spatial references, and primary and foreign keys: In some cases, when you import data, you may specify a fid or other unique ID field, but it doesn’t necessarily specify this field as a Primary Key. Having a primary key is critical for many database functions as I discussed in this Tip: https://getspatial.com/gisblog/tip-of-the-day-ensure-unique-id-each-table-in-database/
4. Having separate steps from import to table loading makes it easier to handle errors: As mentioned above, the data you are importing may not be as clean or concise as you may wish it were. If you import directly into your final table location, it may be more difficult to handle errors that occur. You may not be able to tell if you are having an error with the import function itself, or if there is a data error causing the process to fail. Keeping them separate allows you one more chance to catch errors before they make it to your final database.
5. Keeps integer count of primary key field low for final table. It is assumed that you will have a primary key field for each table. This is critical for correct operation of many functions in the database, and to be able to interact with the table using outside software. The easiest form of primary key field is a serial field, which designates a field as an autonumber field and creates the appropriate sequence to fill the field. When you import data into a table, this field will be filled with sequential numbers for each record. If something happens with your import, and you have to delete records and start again, the new keys created will start the sequence as the next number in line of your prior record count. For example, if you imported a 500 record table, you will end up with primary keys of 1-500. If you have to delete these records and import again, the new numbers assigned will be 501 – 1000. If you have to do this multiple times, or have a table with a lot of records, this numeric range is going to get very large, very quickly.

So, to recap. Bring your data in to the database into a temp table. Create the permanent table with all of your attributes and data types defined. Query data from the temp table to the permanent and use that query to do any conversion necessary, whether data type, text extraction, dates, etc. This will leave you with a clean permanent table when you are finished. Another benefit is after you query the data to the permanent table, if you see something wrong, you can simply drop that table, recreate it as a new, clean, empty table, and redo the query with any necessary revisions. This may take slightly longer to do, but the additional control you gain over the data and the process, will benefit you in the long run.