-- sample-pg-dump.sql: real pg_dump output (plain format), unedited below
-- the next blank line. Free sample from 99helpers.com/file-types/sql
--
-- It is the database that sample-schema.sql creates. Restore it into a
-- new, empty PostgreSQL database with psql (not with other tools: the
-- \restrict, \unrestrict and COPY ... FROM stdin parts are psql features):
--   createdb shop_copy
--   psql -X -v ON_ERROR_STOP=1 -d shop_copy -f sample-pg-dump.sql

--
-- PostgreSQL database dump
--

\restrict b4KzIorjhA1SgfBxcRrsZqpDyCviNXWyVLtFePpPgKb7xwEWbEboW8teUYAOlGB

-- Dumped from database version 16.15 (Ubuntu 16.15-0ubuntu0.24.04.1)
-- Dumped by pg_dump version 16.15 (Ubuntu 16.15-0ubuntu0.24.04.1)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: customers; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.customers (
    id integer NOT NULL,
    name character varying(100) NOT NULL,
    email character varying(255) NOT NULL,
    city character varying(100),
    active boolean DEFAULT true NOT NULL
);


--
-- Name: order_items; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.order_items (
    order_id integer NOT NULL,
    product_id integer NOT NULL,
    quantity integer NOT NULL,
    unit_price numeric(10,2) NOT NULL,
    CONSTRAINT order_items_quantity_check CHECK ((quantity > 0))
);


--
-- Name: orders; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.orders (
    id integer NOT NULL,
    customer_id integer NOT NULL,
    ordered_on date NOT NULL,
    status character varying(20) DEFAULT 'new'::character varying NOT NULL
);


--
-- Name: order_totals; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW public.order_totals AS
 SELECT o.id AS order_id,
    c.name AS customer,
    o.status,
    sum(((i.quantity)::numeric * i.unit_price)) AS total
   FROM ((public.orders o
     JOIN public.customers c ON ((c.id = o.customer_id)))
     JOIN public.order_items i ON ((i.order_id = o.id)))
  GROUP BY o.id, c.name, o.status;


--
-- Name: products; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.products (
    id integer NOT NULL,
    sku character varying(20) NOT NULL,
    title character varying(200) NOT NULL,
    price numeric(10,2) NOT NULL,
    CONSTRAINT products_price_check CHECK ((price >= (0)::numeric))
);


--
-- Data for Name: customers; Type: TABLE DATA; Schema: public; Owner: -
--

COPY public.customers (id, name, email, city, active) FROM stdin;
1	Ada Example	ada@example.com	Utrecht	t
2	Ben O'Example	ben@example.org	Leeds	t
3	Cleo Example	cleo@example.net	\N	f
\.


--
-- Data for Name: order_items; Type: TABLE DATA; Schema: public; Owner: -
--

COPY public.order_items (order_id, product_id, quantity, unit_price) FROM stdin;
101	1	2	8.50
101	3	4	4.25
102	2	1	19.00
103	3	10	4.00
\.


--
-- Data for Name: orders; Type: TABLE DATA; Schema: public; Owner: -
--

COPY public.orders (id, customer_id, ordered_on, status) FROM stdin;
101	1	2026-10-01	shipped
103	1	2026-10-20	new
102	2	2026-10-03	shipped
\.


--
-- Data for Name: products; Type: TABLE DATA; Schema: public; Owner: -
--

COPY public.products (id, sku, title, price) FROM stdin;
1	MUG-01	Coffee mug	8.50
2	TEE-M	T-shirt, size M	19.00
3	NOTE-A5	A5 notebook	4.25
\.


--
-- Name: customers customers_email_key; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.customers
    ADD CONSTRAINT customers_email_key UNIQUE (email);


--
-- Name: customers customers_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.customers
    ADD CONSTRAINT customers_pkey PRIMARY KEY (id);


--
-- Name: order_items order_items_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.order_items
    ADD CONSTRAINT order_items_pkey PRIMARY KEY (order_id, product_id);


--
-- Name: orders orders_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.orders
    ADD CONSTRAINT orders_pkey PRIMARY KEY (id);


--
-- Name: products products_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.products
    ADD CONSTRAINT products_pkey PRIMARY KEY (id);


--
-- Name: products products_sku_key; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.products
    ADD CONSTRAINT products_sku_key UNIQUE (sku);


--
-- Name: idx_orders_customer; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_orders_customer ON public.orders USING btree (customer_id);


--
-- Name: order_items order_items_order_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.order_items
    ADD CONSTRAINT order_items_order_id_fkey FOREIGN KEY (order_id) REFERENCES public.orders(id);


--
-- Name: order_items order_items_product_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.order_items
    ADD CONSTRAINT order_items_product_id_fkey FOREIGN KEY (product_id) REFERENCES public.products(id);


--
-- Name: orders orders_customer_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

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


--
-- PostgreSQL database dump complete
--

\unrestrict b4KzIorjhA1SgfBxcRrsZqpDyCviNXWyVLtFePpPgKb7xwEWbEboW8teUYAOlGB