Change PRIMARY KEY CONSTRAINT in PostgreSql using Play Framework Evolutions

Question

I need to change a PRIMARY KEY CONSTRAINT in PostgreSql from a single to a composite key, using Scala's Play Framework Evolutions. This Change Primary Key post gives me a good head start, so I will adapt their example; suppose my db evolutions 1.sql file is:

-- !Ups
CREATE TABLE city (
    city_id BIGSERIAL PRIMARY KEY,
    group_id int,
    "version" int
);

CREATE INDEX city__version ON city("version");
CREATE INDEX city__group_id ON city(group_id);

-- !Downs
DROP TABLE city;

I want to change the PK to include 2 more columns, like:

CREATE TABLE city (
    city_id BIGSERIAL,
    group_id int,
    "version" int,
    CONSTRAINT city_pk PRIMARY KEY (city_id, group_id, "version");
);

-- ...

I think the 2.sql file should look like:

-- !Ups
ALTER TABLE city 
  DROP COLUMN city_id, 
  ADD COLUMN city_id BIGSERIAL,
  ADD CONSTRAINT city_pk PRIMARY KEY (city_id, group_id, "version");

-- !Downs
ALTER TABLE city 
  DROP CONSTRAINT city_pk,
  DROP COLUMN city_id,
  ADD COLUMN city_id BIGSERIAL PRIMARY KEY;

But I wonder if I need to truncate the table first*, since I'm dropping an essential column... If so, should I include a TRUNCATE command to the evolution file before the ALTER TABLE command? Otherwise, is there another way to change the PRIMARY KEY w/o DROP + ADD COLUMN?

* it's OK to do it on my use case.

score 0 · Answer 1 · answered Dec 19 '22 at 18:29

Just tested my suggested changes and it worked. The 2.sql file is the same as suggested; there was no need to truncate the table first, and the original data was preserved, including the city_id:

-- !Ups
ALTER TABLE city 
  DROP COLUMN city_id, 
  ADD COLUMN city_id BIGSERIAL,
  ADD CONSTRAINT city_pk PRIMARY KEY (city_id, group_id, "version");

-- !Downs
ALTER TABLE city 
  DROP CONSTRAINT city_pk,
  DROP COLUMN city_id,
  ADD COLUMN city_id BIGSERIAL PRIMARY KEY;

Change PRIMARY KEY CONSTRAINT in PostgreSql using Play Framework Evolutions

1 Answers1