Postgres Migrations

(Updated: )

When aerich or alembic auto-generated migration files aren’t enough.

Summary of the basic DDL/DML statements used to keep track of the Postrges DB migrations, followed by a couple of use cases.

General DDL / DML

CREATE / INSERT

1CREATE EXTENSION IF NOT EXISTS postgis;
2CREATE INDEX location_gidx ON product USING gist (location);
3CREATE UNIQUE INDEX product_name_supplier_uidx ON product (name, supplier);
4CREATE TYPE orderstatus AS ENUM('PROCESSED', 'SHIPPED', 'DELIVERED', 'CANCELLED');
5-- upsert
6INSERT INTO product (name, supplier_id, location, status) 
7    VALUES ('NOTEBOOK-A6', 'Muji', ST_GeomFromText('POINT(12.3 4.56)', 4326), 'SHIPPED')
8    ON CONFLICT ON CONSTRAINT product_name_supplier_uc DO 
9    UPDATE SET location=EXCLUDED.location, status=EXCLUDED.status;

ALTER / UPDATE

1ALTER SEQUENCE product_id_seq RESTART WITH 1453;
2ALTER TABLE product ADD CONSTRAINT product_name_supplier_uc UNIQUE USING INDEX product_name_supplier_uidx;
3ALTER TABLE product ADD CONSTRAINT product_supplier_fk FOREIGN KEY (supplier_id) REFERENCES supplier (id) ON DELETE CASCADE;
4ALTER TABLE product ALTER COLUMN description TYPE VARCHAR(2000) USING description::VARCHAR(2000); --longer varchar
5ALTER TABLE product ALTER COLUMN status TYPE orderstatus USING status::text::orderstatus;
6ALTER TYPE orderstatus ADD VALUE 'OUT FOR DELIVERY';
7ALTER TYPE orderstatus RENAME TO orderstatus_tmp;
8UPDATE product SET orderstatus = NULL WHERE status = 'OUT FOR DELIVERY';

DROP / DELETE

1DROP DATABASE my_db;
2DROP INDEX product_name_supplier_uid;
3ALTER TABLE product DROP CONSTRAINT product_name_supplier_uc;
4DROP TYPE orderstatus_tmp;
5DELETE FROM product WHERE id=1234;

Use cases

Add / delete column

1--upgrade
2ALTER TABLE product ADD COLUMN price NUMERIC NOT NULL CONSTRAINT positive_price CHECK (price > 0);
3--downgrade
4ALTER TABLE product DROP COLUMN price;

Rename column

1--upgrade
2ALTER TABLE product RENAME COLUMN description TO details;
3--downgrade
4ALTER TABLE product RENAME COLUMN details TO description;

Change column type

Character length

1--upgrade - add characters to description column
2ALTER TABLE product ALTER COLUMN description TYPE VARCHAR(2000) USING description::VARCHAR(2000);
3--downgrade - remove characters from description column
4UPDATE product SET description = LEFT(description, 200) WHERE CHAR_LENGTH(description) > 200;
5ALTER TABLE product ALTER COLUMN description TYPE VARCHAR(200) USING description::VARCHAR(200);

Enum

1--upgrade - add a value to orderstatus type
2ALTER TYPE orderstatus ADD VALUE 'OUT FOR DELIVERY';
3--downgrade - remove a value from orderstatus type
4CREATE TYPE old_orderstatus AS ENUM('PROCESSED', 'SHIPPED', 'DELIVERED', 'CANCELLED');
5UPDATE product SET status = NULL WHERE status = 'OUT FOR DELIVERY';
6ALTER TABLE product ALTER COLUMN status TYPE old_orderstatus USING status::text::old_orderstatus;
7DROP TYPE orderstatus;
8ALTER TYPE old_orderstatus RENAME TO orderstatus;

Add / delete index

1--upgrade
2CREATE INDEX location_gidx ON product USING gist (location);
3CREATE UNIQUE INDEX product_name_supplier_uidx ON product (name, supplier);
4--downgrade
5DROP INDEX location_gidx;
6DROP INDEX product_name_supplier_uidproduct_name_supplier_uidx

Add / delete constraint

1--upgrade - add a unique and foreign key constraint
2ALTER TABLE product ADD CONSTRAINT product_name_supplier_uc UNIQUE USING INDEX product_name_supplier_uidx;
3ALTER TABLE product ADD CONSTRAINT product_supplier_fk FOREIGN KEY (supplier_id) REFERENCES supplier (id) ON DELETE CASCADE;
4--downgrade - remove the unique and foreign key constraint
5ALTER TABLE product DROP CONSTRAINT product_name_supplier_uc;
6ALTER TABLE product DROP CONSTRAINT product_supplier_fk;

Create / delete table

 1--upgrade
 2CREATE TABLE IF NOT EXISTS "product" (
 3    "time_created" TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
 4    "time_updated" TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
 5    "id" SERIAL NOT NULL PRIMARY KEY,
 6    "name" VARCHAR(64) NOT NULL,
 7    "supplier_id" INT REFERENCES "supplier" ("id") ON DELETE CASCADE,
 8    "location" GEOMETRY(POINT,4326) NOT NULL,
 9    "properties" JSONB,
10    "status" orderstatus NOT NULL DEFAULT 'processed',
11    CONSTRAINT "uc_product_name_supplier" UNIQUE ("name", "supplier_id")
12);
13COMMENT ON COLUMN product.status IS 'PROCESSED: PROCESSED\nSHIPPED: SHIPPED\nDELIVERED: DELIVERED\nCANCELLED: CANCELLED';
14--downgrade
15DROP TABLE product;

Misc. Postgres commands

Unnesting an array column

 1-- Generate the table for testing
 2
 3-- CREATE TABLE IF NOT EXISTS "intervals" (
 4--     "id" SERIAL NOT NULL PRIMARY KEY,
 5--     "cutoff" decimal[] NOT NULL
 6-- );
 7-- INSERT INTO intervals (id, cutoff) VALUES (1, '{0.0, 1.0, 2.5, 5.0, 10.0}');
 8-- INSERT INTO intervals (id, cutoff) VALUES (2, '{0.0, 10.0, 20.0, 50.0, 100.0, 250.0}');
 9
10SELECT
11    id AS interval_id,
12    GENERATE_SERIES(1, ARRAY_LENGTH(cutoff, 1)-1) AS range_id,
13    UNNEST(cutoff[1:ARRAY_LENGTH(cutoff, 1)-1]) AS low,
14    UNNEST(cutoff[2:ARRAY_LENGTH(cutoff, 1)]) AS high
15FROM intervals

Looping through list of ids

This is useful to update a table with a foreign key.
As an example below we assume every user has ordered a specific product.

 1DO
 2$$
 3DECLARE
 4    f record;
 5BEGIN
 6    FOR f IN SELECT id FROM public.users
 7    LOOP 
 8	INSERT INTO order (orderstatus, product_id, user_id)
 9	VALUE ('PURCHASED', 123456, f.id);
10    END LOOP;
11END;
12$$