What is the best way to separate out a few column from an existing table into a new table while maintaining relationship?

user_12687019 0
Viewed 58

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.

spring-data-jpa spring-boot postgresql hibernate sql
2 Answers

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;
user_106767160

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

user_77724830
Related