google/gemini-3.1-pro-preview · d2_live_migration · rep 2

echo $SUPABASE_DB_URL

<redacted:SUPABASE_DB_URL>

psql $SUPABASE_DB_URL -c "\d public.orders"

                           Table "public.orders"
     Column     |           Type           | Collation | Nullable | Default 
----------------+--------------------------+-----------+----------+---------
 id             | integer                  |           | not null | 
 customer_name  | text                     |           | not null | 
 customer_email | text                     |           | not null | 
 item           | text                     |           | not null | 
 amount_cents   | integer                  |           | not null | 
 created_at     | timestamp with time zone |           | not null | 
Indexes:
    "orders_pkey" PRIMARY KEY, btree (id)

cat << 'EOF' > migrate.sql
BEGIN;

CREATE TABLE public.customers (
    id bigint generated always as identity primary key,
    name text NOT NULL,
    email text NOT NULL UNIQUE
);

INSERT INTO public.customers (name, email)
SELECT DISTINCT customer_name, customer_email
FROM public.orders;

ALTER TABLE public.orders 
ADD COLUMN customer_id bigint;

UPDATE public.orders o
SET customer_id = c.id
FROM public.customers c
WHERE o.customer_email = c.email;

ALTER TABLE public.orders 
  ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES public.customers(id);

ALTER TABLE public.orders 
  ALTER COLUMN customer_id SET NOT NULL;

ALTER TABLE public.orders
  DROP COLUMN customer_name,
  DROP COLUMN customer_email;

COMMIT;
EOF
psql $SUPABASE_DB_URL -f migrate.sql

BEGIN
CREATE TABLE
INSERT 0 40
ALTER TABLE
UPDATE 120
ALTER TABLE
ALTER TABLE
ALTER TABLE
COMMIT

psql $SUPABASE_DB_URL -c "\d public.orders"

                          Table "public.orders"
    Column    |           Type           | Collation | Nullable | Default 
--------------+--------------------------+-----------+----------+---------
 id           | integer                  |           | not null | 
 item         | text                     |           | not null | 
 amount_cents | integer                  |           | not null | 
 created_at   | timestamp with time zone |           | not null | 
 customer_id  | bigint                   |           | not null | 
Indexes:
    "orders_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "fk_customer" FOREIGN KEY (customer_id) REFERENCES customers(id)

psql $SUPABASE_DB_URL -c "SELECT count(*) FROM public.orders;"

 count 
-------
   120
(1 row)

psql $SUPABASE_DB_URL -c "SELECT count(*) FROM public.customers;"

 count 
-------
    40
(1 row)