github.com/pf-qiu/concourse/v6@v6.7.3-0.20201207032516-1f455d73275f/atc/db/migration/migrations/1548349925_separate_pins_from_resources.up.sql (about)

     1  BEGIN;
     2    CREATE TABLE resource_pins (
     3      resource_id INTEGER NOT NULL PRIMARY KEY
     4        REFERENCES resources(id) ON DELETE CASCADE,
     5      version jsonb NOT NULL,
     6      comment_text text NOT NULL
     7    );
     8  
     9    INSERT INTO resource_pins (
    10      SELECT
    11        id AS resource_id,
    12        api_pinned_version AS version,
    13        COALESCE (pin_comment, '') AS comment_text
    14      FROM resources
    15      WHERE api_pinned_version IS NOT NULL
    16    );
    17  
    18    ALTER TABLE resources
    19      DROP COLUMN api_pinned_version,
    20      DROP COLUMN pin_comment;
    21  COMMIT;