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

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)

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

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

psql $SUPABASE_DB_URL -c "SELECT customer_email, COUNT(DISTINCT customer_name) FROM public.orders GROUP BY customer_email HAVING COUNT(DISTINCT customer_name) > 1;"

 customer_email | count 
----------------+-------
(0 rows)

cat << 'EOF' > migration.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 REFERENCES public.customers(id);

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

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 migration.sql

BEGIN
CREATE TABLE
INSERT 0 40
ALTER TABLE
UPDATE 120
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:
    "orders_customer_id_fkey" 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)