This is a simple trick yet quite useful when you can just copy and paste. Here, "This" means when you have a column that store some integer values and you want these values to start at 1 and have no gaps. Here is an example, the goal is to convert the data:
Please note that this is not about introducing a candidate key column (a potential primary key column). As you can see in the above example, we just replace the integer values in the integer column so that rows having the equal values still get the equal but different values. For example, all two rows (Data 1 and Data 2) had equal values (3) in the old (from) table. In the new (to) table, they still have the equal values (1) but 1 is different from 3.
In PostgreSQL, the trick is to use a Window Function (note: it is not the Windows Operating System) called row_number(). In fact, row_number() is much more powerful than this (it can assign unique values to each row in each user-defined data partition in the query result). However, for the purpose of this task, its powerful feature is not necessary. Here is the function that serves the purpose:
The outer FOR loop traverses each unique pair of old and new values of IntegerColumn {(3, 1), (7, 2), (11, 3)}. For each of the pair, we run an update query, simple? Yes, but it is useful (handy) to have a function for when necessary.
Logged on Doughnut I/O. U.E. 1340372751.
Please note that this is not about introducing a candidate key column (a potential primary key column). As you can see in the above example, we just replace the integer values in the integer column so that rows having the equal values still get the equal but different values. For example, all two rows (Data 1 and Data 2) had equal values (3) in the old (from) table. In the new (to) table, they still have the equal values (1) but 1 is different from 3.
In PostgreSQL, the trick is to use a Window Function (note: it is not the Windows Operating System) called row_number(). In fact, row_number() is much more powerful than this (it can assign unique values to each row in each user-defined data partition in the query result). However, for the purpose of this task, its powerful feature is not necessary. Here is the function that serves the purpose:
Logged on Doughnut I/O. U.E. 1340372751.
Comments
Post a Comment