postgresqlsqitch

Altering Table with Sqitch Rework command

I've tried several times to follow Sqitch's 'postgres tutorial', just except instead of altering a function (which uses CREATE OR REPLACE FUNCTION), I'm changing a field name in a table to see how it'll workout after a deployment. But it ends up with below error. Could someone please point me to the right direction?

$ sqitch verify
Verifying sqtest_db
  * appschema .... ok
  * contact ...... ok
Undeployed change:
  * contact
Verify successful


$ sqitch deploy
Deploying changes to sqtest_db
  + contact .. psql:deploy/contact.sql:10: ERROR:  relation "contact" already exists
not ok
"/usr/local/bin/psql" unexpectedly returned exit value 3

Deploy failed

This is my before tagged and after tagged deploy query

BEFORE tagging the DB

BEGIN;
CREATE TABLE sq_schema.contact
  (
    log_date DATE NOT NULL,
    emp_name CHARACTER VARYING(100) DEFAULT ''
  );
COMMIT;

Tagging DB with

sqitch rework contact --requires appschema -n 'Added CONTACT table'

AFTER tagging

BEGIN;

CREATE TABLE sq_schema.contact
  (
    log_date DATE NOT NULL,

    -- Change field name,
    employee_name CHARACTER VARYING(100) DEFAULT ''
  );

COMMIT;
Solution

  • Rework is intended for making idempotent changes, such as CREATE OR REPLACE FUNCTION. The CREATE TABLE statement is not idempotent. If you want to add a column to a table, I suggest either:

    1. If you have not released your database, just modify the CREATE TABLE statement in the original change and sqitch rebase to revert all changes and redeploy with the updated table. This is ideal when doing development.

    2. Otherwise, add a new change, named $table_$column or some such, and use an ALTER TABLE statement to add the new column. This is the approach to take if you have released the database already, though you can also do it before release if you like.