In Databases By Leigh Halliday January 23, 2015 Leigh Halliday

Copying Postgres Table with its Attributes

Why do this?

Recently at work we had 1 data model that was getting a bit out of control. When we first modelled the data it made sense, but over time business requirements change and we discovered that the model made more sense to be split in half to satisfy 2 unique models instead of 1.

The data was stored in a merchants table, but as we thought about it, it made sense to split it in half and create a sites table. Here are the characteristics of what a Merchant is and what a Site is:

Merchant characteristics

  • Has products to sell
  • Controls where it's products are sold
  • Has integrations with e-commerce stores
  • Has giftcards

Site characteristics

  • Products are sold on a site
  • Chooses which products to sell
  • Has urls mapped to it
  • Has theme settings
  • Has a lot of SEO data

Copying the table with its attributes

We wanted to essentially create a copy of the table and then subsequently remove the columns from where they don't belong. This will keep all default values, data-types, and not-null settings in check. We also created a new primary id sequence, which starts where the old one left off.

LOCK TABLE merchants;
INSERT INTO sites SELECT * FROM merchants;
CREATE SEQUENCE sites_id_seq;
SELECT setval('sites_id_seq', (SELECT max(id) FROM merchants)+1);
ALTER TABLE sites ALTER COLUMN id SET DEFAULT nextval('sites_id_seq');

The next step - not included below - is to simply remove the columns that are no longer needed from each of the 2 tables.