PostgreSQL dealing with changing column data types on old data

Atomixx 0
Viewed 15

I am a new PostgreSQL user who is migrating some old mySQL data to PostgreSQL. With this migration, I am also considering changing the way my data is stored. Specifically, I need some insight on how to approach changing the data type for one of my columns from a number to an array of three numbers. I have a database that records American Football stats for my game. I have a column that is labeled "receiving_yards" that is of type Int. Recently I have made improvements to the stats for my game and want to essentially divide the "receiving_yards" column from one integer to three integers, with each integer corresponding to a part of the field, like so: [deep, middle, sideline]. Therefore, the "receiving_yards" column would be of type [Int, Int, Int]. The issue is how do I deal with all my old data, as it can't be transferred to this format, and all the new data will use this new array format.

I still want to be able to aggregate the total of the "receiving_yards" column, including the contents of the array, but I want to be able to differentiate the stats for each part of the field, corresponding to the index of the array.

My initial solution was to simply update all the old data to array format, such as [Int], and then have exceptions in my aggregations. Is that the solution? Or should I rethink the way I am storing the data? Thanks in advance.

How old data is stored

receiving_yards
100
25
30

How new data will be stored

receiving_yards
[25, 25, 50]
[5, 5, 15]
[0, 0, 30]
postgresql
1 Answers

Recently I have made improvements to the stats for my game and want to essentially divide the "receiving_yards" column from one integer to three integers, with each integer corresponding to a part of the field, like so: [deep, middle, sideline]

I would simply make three columns. PostgreSQL arrays and JSON are mostly useful when you have arbitrary data to store. In this case you have three fixed values, traditional tables and columns work better.

Add them to get the total receiving yards. You can even retain receiving_yards by making it a generated column.

receiving_yards integer generated always as (deep + middle + sideline) stored

Or you can make a view.

create view view_name as
select *, (deep + middle + sideline) as receiving_yards
from the_table
Schwern0
Related