API tools faq
paste
Advertisement
Guest User

mmMMMMmmm touch postgre

a guest
Mar 18th, 2019
70
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PostgreSQL 10.34 KB | None | 0 0
raw download clone embed print report
  1. create table customers
  2. (
  3.   idcustomer  serial not null
  4.     constraint customers_pk
  5.       primary key,
  6.   companyname text,
  7.   lastname    text   not null,
  8.   firstname   text   not null,
  9.   adress      text,
  10.   city        text,
  11.   indexcode   text
  12.     constraint customers_indexcode_check
  13.       check (indexcode ~ similar_escape('[0-9]{6}'::text, NULL::text)),
  14.   phone       text,
  15.   email       text,
  16.   constraint customers_lastname_firstname_key
  17.     unique (lastname, firstname)
  18. );
  19.  
  20. alter table customers
  21.   owner to postgres;
  22.  
  23. INSERT INTO public.customers (idcustomer, companyname, lastname, firstname, adress, city, indexcode, phone, email) VALUES (7, null, 'Дятленко', 'Елена', null, null, '321313', null, null);
  24. INSERT INTO public.customers (idcustomer, companyname, lastname, firstname, adress, city, indexcode, phone, email) VALUES (8, null, 'Сысоев', 'Антон', null, null, '123123', null, null);
  25. INSERT INTO public.customers (idcustomer, companyname, lastname, firstname, adress, city, indexcode, phone, email) VALUES (9, null, 'Храмцов', 'Денис', null, null, '534345', null, null);
  26. INSERT INTO public.customers (idcustomer, companyname, lastname, firstname, adress, city, indexcode, phone, email) VALUES (10, null, 'Карягин', 'Александр', null, null, '989845', null, null);
  27. INSERT INTO public.customers (idcustomer, companyname, lastname, firstname, adress, city, indexcode, phone, email) VALUES (11, null, 'Федюкин', 'Данила', null, null, '347587', null, null);
  28. INSERT INTO public.customers (idcustomer, companyname, lastname, firstname, adress, city, indexcode, phone, email) VALUES (12, null, 'Кудрявцев', 'Сергей', null, null, '253462', null, null);
  29. INSERT INTO public.customers (idcustomer, companyname, lastname, firstname, adress, city, indexcode, phone, email) VALUES (13, null, 'Михеев', 'Никита', null, null, '454538', null, null);
  30. INSERT INTO public.customers (idcustomer, companyname, lastname, firstname, adress, city, indexcode, phone, email) VALUES (14, null, 'Ким', 'Радмир', null, null, '959885', null, null);
  31. INSERT INTO public.customers (idcustomer, companyname, lastname, firstname, adress, city, indexcode, phone, email) VALUES (15, null, 'Брусникина', 'Мария', null, null, '939765', null, null);
  32. INSERT INTO public.customers (idcustomer, companyname, lastname, firstname, adress, city, indexcode, phone, email) VALUES (16, null, 'Белова', 'Елена', null, null, '587489', null, null);
  33. INSERT INTO public.customers (idcustomer, companyname, lastname, firstname, adress, city, indexcode, phone, email) VALUES (17, null, 'Чернышев', 'Павел', null, null, '626235', null, null);
  34. INSERT INTO public.customers (idcustomer, companyname, lastname, firstname, adress, city, indexcode, phone, email) VALUES (18, null, 'Андреев', 'Илья', null, null, '874872', null, null);
  35. INSERT INTO public.customers (idcustomer, companyname, lastname, firstname, adress, city, indexcode, phone, email) VALUES (19, null, 'Эдилов', 'Адам', null, null, '235683', null, null);
  36. INSERT INTO public.customers (idcustomer, companyname, lastname, firstname, adress, city, indexcode, phone, email) VALUES (20, null, 'Грачев', 'Ярослав', null, null, '987673', null, null);
  37. INSERT INTO public.customers (idcustomer, companyname, lastname, firstname, adress, city, indexcode, phone, email) VALUES (6, null, 'Селедкина', 'Алиса', null, null, '123123', null, null);
  38.  
  39. create table orders
  40. (
  41.   idorder    serial             not null
  42.     constraint orders_pk
  43.       primary key,
  44.   idcustomer integer            not null
  45.     constraint orders_customers_idcustomer_fk
  46.       references customers
  47.       on delete restrict,
  48.   orderdate  date default now() not null,
  49.   shipdate   date,
  50.   paiddate   date,
  51.   status     char
  52.     constraint valid_status
  53.       check ((status = 'C'::bpchar) OR (status = 'P'::bpchar))
  54. );
  55.  
  56. alter table orders
  57.   owner to postgres;
  58.  
  59. INSERT INTO public.orders (idorder, idcustomer, orderdate, shipdate, paiddate, status) VALUES (2, 6, '2019-03-18', null, null, 'P');
  60. INSERT INTO public.orders (idorder, idcustomer, orderdate, shipdate, paiddate, status) VALUES (3, 7, '2019-03-18', null, null, 'C');
  61. INSERT INTO public.orders (idorder, idcustomer, orderdate, shipdate, paiddate, status) VALUES (4, 8, '2019-03-18', null, null, 'P');
  62. INSERT INTO public.orders (idorder, idcustomer, orderdate, shipdate, paiddate, status) VALUES (5, 9, '2019-03-18', null, null, 'C');
  63. INSERT INTO public.orders (idorder, idcustomer, orderdate, shipdate, paiddate, status) VALUES (6, 10, '2019-03-18', null, null, 'P');
  64. INSERT INTO public.orders (idorder, idcustomer, orderdate, shipdate, paiddate, status) VALUES (7, 11, '2019-03-18', null, null, 'C');
  65. INSERT INTO public.orders (idorder, idcustomer, orderdate, shipdate, paiddate, status) VALUES (8, 12, '2019-03-18', null, null, 'P');
  66. INSERT INTO public.orders (idorder, idcustomer, orderdate, shipdate, paiddate, status) VALUES (9, 13, '2019-03-18', null, null, 'P');
  67. INSERT INTO public.orders (idorder, idcustomer, orderdate, shipdate, paiddate, status) VALUES (10, 14, '2019-03-18', null, null, 'C');
  68. INSERT INTO public.orders (idorder, idcustomer, orderdate, shipdate, paiddate, status) VALUES (11, 15, '2019-03-18', null, null, 'C');
  69. INSERT INTO public.orders (idorder, idcustomer, orderdate, shipdate, paiddate, status) VALUES (12, 16, '2019-03-18', null, null, 'P');
  70. INSERT INTO public.orders (idorder, idcustomer, orderdate, shipdate, paiddate, status) VALUES (13, 17, '2019-03-18', null, null, 'P');
  71. INSERT INTO public.orders (idorder, idcustomer, orderdate, shipdate, paiddate, status) VALUES (14, 18, '2019-03-18', null, null, 'C');
  72. INSERT INTO public.orders (idorder, idcustomer, orderdate, shipdate, paiddate, status) VALUES (15, 19, '2019-03-18', null, null, 'C');
  73. INSERT INTO public.orders (idorder, idcustomer, orderdate, shipdate, paiddate, status) VALUES (16, 20, '2019-03-18', null, null, 'P');
  74. create table products
  75. (
  76.   idproduct   serial not null
  77.     constraint products_pk
  78.       primary key,
  79.   prname      text   not null,
  80.   prprice     integer,
  81.   instock     integer,
  82.   reorder     boolean,
  83.   description text
  84. );
  85.  
  86. alter table products
  87.   owner to postgres;
  88.  
  89. INSERT INTO public.products (idproduct, prname, prprice, instock, reorder, description) VALUES (1, 'Нурофен', 200, 200, null, null);
  90. INSERT INTO public.products (idproduct, prname, prprice, instock, reorder, description) VALUES (2, 'Анальгин', 40, 200, null, null);
  91. INSERT INTO public.products (idproduct, prname, prprice, instock, reorder, description) VALUES (3, 'Новомегин', 300, 200, null, null);
  92. INSERT INTO public.products (idproduct, prname, prprice, instock, reorder, description) VALUES (4, 'Новопассит', 600, 200, null, null);
  93. INSERT INTO public.products (idproduct, prname, prprice, instock, reorder, description) VALUES (5, 'Пенталгин', 156, 150, null, null);
  94. INSERT INTO public.products (idproduct, prname, prprice, instock, reorder, description) VALUES (6, 'Анвимакс', 90, 150, null, null);
  95. INSERT INTO public.products (idproduct, prname, prprice, instock, reorder, description) VALUES (8, 'Валерьянка', 150, 150, null, null);
  96. INSERT INTO public.products (idproduct, prname, prprice, instock, reorder, description) VALUES (9, 'Спирт медицинский', 400, 100, null, null);
  97. INSERT INTO public.products (idproduct, prname, prprice, instock, reorder, description) VALUES (12, 'Глицин', 99, 100, null, null);
  98. INSERT INTO public.products (idproduct, prname, prprice, instock, reorder, description) VALUES (13, 'Пустырника настойка', 126, 100, null, null);
  99. INSERT INTO public.products (idproduct, prname, prprice, instock, reorder, description) VALUES (14, 'Гематоген', 45, 150, null, null);
  100. INSERT INTO public.products (idproduct, prname, prprice, instock, reorder, description) VALUES (15, 'Ингалятор', 100, 100, null, null);
  101. INSERT INTO public.products (idproduct, prname, prprice, instock, reorder, description) VALUES (10, 'Сиалекс', 187, 100, true, null);
  102. INSERT INTO public.products (idproduct, prname, prprice, instock, reorder, description) VALUES (7, 'Аскорбиновая кислота в гранулах', 140, 150, true, null);
  103. INSERT INTO public.products (idproduct, prname, prprice, instock, reorder, description) VALUES (11, 'Настойка боярышника', 55, 100, true, null);
  104. create table items
  105. (
  106.   iditem    serial  not null
  107.     constraint items_pk
  108.       primary key,
  109.   idorder   integer not null
  110.     constraint items_orders_idorder_fk
  111.       references orders
  112.       on delete restrict,
  113.   idproduct integer not null
  114.     constraint items_products_idproduct_fk
  115.       references products
  116.       on delete restrict,
  117.   quantity  integer not null,
  118.   total     integer
  119. );
  120.  
  121. alter table items
  122.   owner to postgres;
  123.  
  124. INSERT INTO public.items (iditem, idorder, idproduct, quantity, total) VALUES (1, 2, 7, 100, null);
  125. INSERT INTO public.items (iditem, idorder, idproduct, quantity, total) VALUES (2, 3, 15, 1, null);
  126. INSERT INTO public.items (iditem, idorder, idproduct, quantity, total) VALUES (3, 4, 14, 1, null);
  127. INSERT INTO public.items (iditem, idorder, idproduct, quantity, total) VALUES (4, 5, 1, 1, null);
  128. INSERT INTO public.items (iditem, idorder, idproduct, quantity, total) VALUES (5, 6, 2, 1, null);
  129. INSERT INTO public.items (iditem, idorder, idproduct, quantity, total) VALUES (6, 7, 3, 1, null);
  130. INSERT INTO public.items (iditem, idorder, idproduct, quantity, total) VALUES (7, 8, 4, 1, null);
  131. INSERT INTO public.items (iditem, idorder, idproduct, quantity, total) VALUES (8, 9, 5, 1, null);
  132. INSERT INTO public.items (iditem, idorder, idproduct, quantity, total) VALUES (9, 10, 6, 1, null);
  133. INSERT INTO public.items (iditem, idorder, idproduct, quantity, total) VALUES (10, 11, 8, 1, null);
  134. INSERT INTO public.items (iditem, idorder, idproduct, quantity, total) VALUES (11, 12, 9, 1, null);
  135. INSERT INTO public.items (iditem, idorder, idproduct, quantity, total) VALUES (12, 13, 10, 1, null);
  136. INSERT INTO public.items (iditem, idorder, idproduct, quantity, total) VALUES (13, 14, 11, 1, null);
  137. INSERT INTO public.items (iditem, idorder, idproduct, quantity, total) VALUES (14, 15, 12, 1, null);
  138. INSERT INTO public.items (iditem, idorder, idproduct, quantity, total) VALUES (15, 16, 13, 1, null);
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!