Repository navigation
Expand file tree
/
Copy pathschema.sql
More file actions
46 lines (46 loc) · 1.69 KB
/
Copy pathschema.sql
File metadata and controls
46 lines (46 loc) · 1.69 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
CREATE TABLE public.customers (
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
email text NOT NULL UNIQUE,
name text NOT NULL,
updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE public.addresses (
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
customer_id bigint NOT NULL REFERENCES public.customers(id),
city text NOT NULL
);
CREATE TABLE public.products (
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
sku text UNIQUE NOT NULL,
price numeric(12,2) NOT NULL CHECK (price >= 0)
);
CREATE TABLE public.orders (
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
customer_id bigint NOT NULL REFERENCES public.customers(id),
total_amount numeric(12,2) NOT NULL CHECK (total_amount >= 0),
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE public.order_items (
order_id bigint REFERENCES public.orders(id),
product_id bigint REFERENCES public.products(id),
quantity integer NOT NULL CHECK (quantity > 0),
PRIMARY KEY (order_id, product_id)
);
CREATE TABLE public.payments (
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
order_id bigint NOT NULL REFERENCES public.orders(id),
amount numeric(12,2) NOT NULL CHECK (amount >= 0)
);
CREATE INDEX orders_customer_idx ON public.orders(customer_id);
CREATE VIEW public.customer_summary AS
SELECT c.id, c.email, count(o.id) AS order_count
FROM public.customers c LEFT JOIN public.orders o ON o.customer_id = c.id
GROUP BY c.id, c.email;
CREATE FUNCTION public.touch_customer() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
NEW.updated_at = now();
RETURN NEW;
END
$$;
CREATE TRIGGER customers_touch BEFORE UPDATE ON public.customers
FOR EACH ROW EXECUTE FUNCTION public.touch_customer();