Direct Postgres patterns

postgresql://<ref>:<password>@db.p.powabase.ai:5432/<ref>

psql "postgresql://<ref>:<password>@db.p.powabase.ai:5432/<ref>"

-- See your schemas
\dn

-- See your tables in public
\dt public.*

-- See the AI schema (platform-managed)
\dt ai.*

-- See your extensions
\dx

-- Check the role you're connected as
SELECT current_user, session_user;
-- → supabase_admin, supabase_admin

-- WORKS — single transaction
BEGIN;
SET LOCAL statement_timeout = '5s';
CREATE TEMP TABLE staged_orders AS SELECT * FROM raw_imports;
DELETE FROM staged_orders WHERE total < 0;
INSERT INTO orders SELECT * FROM staged_orders;
COMMIT;

-- BROKEN — statements may land on different server connections
SET statement_timeout = '5s';  -- might apply to one server connection
SELECT * FROM big_table;       -- might run on a different connection (no timeout)

BEGIN;
CREATE TEMP TABLE staged_users (
  email text NOT NULL,
  display_name text,
  created_at timestamptz DEFAULT now()
) ON COMMIT DROP;

\copy staged_users (email, display_name) FROM 'users.csv' DELIMITER ',' CSV HEADER;

INSERT INTO public.profiles (id, email, display_name)
SELECT gen_random_uuid(), email, display_name
FROM staged_users
ON CONFLICT (email) DO NOTHING;

COMMIT;

BEGIN;
ALTER TABLE public.orders RENAME COLUMN status TO order_status;
-- PostgREST hot-reloads its schema cache via a NOTIFY (Powabase configures
-- this automatically); clients querying the old column name will start
-- getting 400s. For a zero-downtime rename, add the new column as a
-- generated column first, switch clients, then drop the old.
COMMIT;

\timing on
\set FETCH_COUNT 1000
SELECT * FROM events WHERE created_at > now() - interval '30 days';

-- Find your query
SELECT pid, query, state, query_start
FROM pg_stat_activity
WHERE state = 'active'
  AND usename = 'supabase_admin'
ORDER BY query_start;

-- Cancel it (gentle — asks the backend to stop)
SELECT pg_cancel_backend(<pid>);

-- Or kill it (hard — terminates the connection)
SELECT pg_terminate_backend(<pid>);

import psycopg
from psycopg import errors

try:
    cur.execute("INSERT INTO users (email) VALUES (%s)", ["alice@example.com"])
except errors.UniqueViolation:
    # Handle duplicate — typically "treat as success" or "update instead"
    pass
except psycopg.OperationalError as e:
    if e.sqlstate in ("40001", "40P01"):
        # Serialization / deadlock — retry the transaction
        pass