Let's say that I have a table Payment with the following columns
payment_id - UUID
amount - Numeric
order_id - Unique VARCHAR
psp_order_id - Third Party Unique Order Id created by Payment Service Provider
psp_payment_id -Third Party Unique Payment Id created by Payment Service Provider
created_at - payment created at
ended_at - payment ended at
Now there is a requirement for integrating a new PSP. But since the Payment table is tightly bound to one PSP, I would like to move psp_order_id, psp_payment_id to another table Eg: razorpay_payment_details.
So the resulting schema could be :
Payment Table
payment_id - UUID
amount - Numeric
order_id - Unique VARCHAR
created_at - payment created at
ended_at - payment ended at
razorpay_payment_details
id - UUID (Primary Key)
psp_order_id (Unique String)
psp_payment_id (Unique String)
payment_id (Foreign Key which relates Payment Entity to razorpay_payment_details entity)
How should one go about changing the schema and migrating the existing production data into the new table ?
Technologies used - Spring Boot, PostgreSQL PS: I do not manage Database Migrations using Flyway/Liquibase.
You would typically create the new table (razorpay_payment_details), and then copy the relevant rows/columns from the original table (payment) with an insert ... select query.
insert into razorpay_payment_details(psp_order_id, psp_payment_id, payment_id)
select psp_order_id, psp_payment_id, payment_id from payment;
The id column should an automatically generated column (such as a serial column), so I left it apart in the query.
You can then go ahead with the validation of the results. Finally, you can drop the columns from the original table.
alter table payment
drop column psp_order_id,
drop column psp_payment_id;
Your steps with query will be like this:
Create your table razorpay_payment_details first:
create table razorpay_payment_details(
id uuid DEFAULT uuid_generate_v4 (),
psp_order_id varchar(100),
psp_payment_id varchar(100),
payment_id uuid,
primary key(id),
constraint fk_paymentid
foreign key(payment_id)
references payment(payment_id)
)
Migrate your data from payment table to new table razorpay_payment_details using below query:
insert into razorpay_payment_details (psp_order_id, psp_payment_id, payment_id)
select psp_order_id, psp_payment_id, payment_id from payment
After completion of migration and verification of data you can run below query to alter he payment table:
alter table payment
drop column psp_order_id ,
drop column psp_payment_id;
Do'nt forget to take backup before this activity