Postgres Migrations

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

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

DROP DATABASE my_db;
DROP INDEX product_name_supplier_uid;
ALTER TABLE product DROP CONSTRAINT product_name_supplier_uc;
DROP TYPE orderstatus_tmp;
DELETE FROM product WHERE id=1234;

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

--upgrade
ALTER TABLE product RENAME COLUMN description TO details;
--downgrade
ALTER TABLE product RENAME COLUMN details TO description;

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

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

--upgrade
CREATE INDEX location_gidx ON product USING gist (location);
CREATE UNIQUE INDEX product_name_supplier_uidx ON product (name, supplier);
--downgrade
DROP INDEX location_gidx;
DROP INDEX product_name_supplier_uidproduct_name_supplier_uidx

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

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

-- Generate the table for testing

-- CREATE TABLE IF NOT EXISTS "intervals" (
--     "id" SERIAL NOT NULL PRIMARY KEY,
--     "cutoff" decimal[] NOT NULL
-- );
-- INSERT INTO intervals (id, cutoff) VALUES (1, '{0.0, 1.0, 2.5, 5.0, 10.0}');
-- INSERT INTO intervals (id, cutoff) VALUES (2, '{0.0, 10.0, 20.0, 50.0, 100.0, 250.0}');

SELECT
    id AS interval_id,
    GENERATE_SERIES(1, ARRAY_LENGTH(cutoff, 1)-1) AS range_id,
    UNNEST(cutoff[1:ARRAY_LENGTH(cutoff, 1)-1]) AS low,
    UNNEST(cutoff[2:ARRAY_LENGTH(cutoff, 1)]) AS high
FROM intervals

DO
$$
DECLARE
    f record;
BEGIN
    FOR f IN SELECT id FROM public.users
    LOOP 
	INSERT INTO order (orderstatus, product_id, user_id)
	VALUE ('PURCHASED', 123456, f.id);
    END LOOP;
END;
$$