Driving home from a long day of work, I see the beautiful sky and so to not miss the beauty, I decided to pull over to take this picture. But then coming home, I was reminded of today’s challenges and I felt that it’s best to document my findings for posterity and to share it with the rest of the world in hopes that someone running into the same challenge can benefit from this knowledge. If you find this article useful, drop me a line. 😉
This article shows you how to efficiently alter column datatypes that leverages PostgreSQL TimeScale hypertables.
Problem Statement
When you work with PostgreSQL tables with time-series data, you may end up choosing TimeScale as a product for storing time-series data. And as your schema design evolves, you will need to modify the table-columns to support the changes. TimeScale uses hypertables to shard/chunk the time-series data into smaller tables that uses your time column as an index. There may be cases where you created one of the time columns as type TIMESTAMP and then realize later on that you need to convert it to TIMESTAMPZ (timestamp with timezone). Here is a clean way you can make that modification.
Step-1: Create a temporary table that will hold the real table data
1 2 3 4 5 | CREATE TABLE tmp_SMART_DISK_METRICS AS SELECT * FROM SMART_DISK_METRICS;# This creates a tmp_* table with no constraints or foreign key maps # tmp table will be owned by the user you are currently logged in into psql |
Step-2: Truncate table that requires column change
1 2 3 4 | TRUNCATE TABLE SMART_DISK_METRICS;# This will be free of any constraints# When you run "\d+ SMART_DISK_METRICS", your table will not have any Child table references to hypertables |
Step-3: Convert table column
1 2 3 4 5 | ALTER TABLE SMART_DISK_METRICS ALTER COLUMN time TYPE timestamp with time zone;ALTER TABLE SMART_DISK_METRICS ALTER COLUMN time SET DEFAULT now();ALTER TABLE SMART_DISK_METRICS ALTER COLUMN time SET NOT NULL;# When you run "\d+ SMART_DISK_METRICS" now, your table schema will have "timestamp with time zone", not-null, now() |
Step-4: Move back the data from tmp_ table to the real table
1 2 3 4 | INSERT INTO SMART_DISK_METRICS SELECT * FROM tmp_SMART_DISK_METRICS;# When you run "\d+ SMART_DISK_METRICS" now, your table ** will have ** Child table with references pointing to hypertables # like _timescaledb_internal._hyper_1_245_chunk |
That’s it! You have now modified a PostgreSQL table with time-series data backed by TimeScale hypertable.
If you find this article useful, consider bookmarking, subscribing and/or sharing it on your favorite social channels so that others can also find and read these articles. I do this out of love and passion to share my ideas, thoughts, findings and knowledge with others. So the more you help me share the more my content can reach others. Thank you for helping spread the word!
Now for Today’s Inspiration:
Three Steps In Cultivating Willpower:
- Finish what you begin.
- Do a little bit more than you think you can.
- Do a little bit better than you think you can.
Cheers!
Published on System Code Geeks with permission by Venkatt Guhesan, partner at our SCG program. See the original article here: Handling PostgreSQL TimeScale Hypertable Column Changes Efficiently Opinions expressed by System Code Geeks contributors are their own. |
