ALTER TABLE "Vessel" ADD COLUMN "code" TEXT;

WITH numbered AS (
  SELECT id, ROW_NUMBER() OVER (ORDER BY id) AS n
  FROM "Vessel"
)
UPDATE "Vessel" SET "code" = 'SITE-' || LPAD(n::text, 3, '0')
FROM numbered
WHERE "Vessel".id = numbered.id;

ALTER TABLE "Vessel" ALTER COLUMN "code" SET NOT NULL;
ALTER TABLE "Vessel" ADD CONSTRAINT "Vessel_code_key" UNIQUE ("code");
