· 6 years ago · Aug 15, 2019, 01:02 PM
1--
2-- PostgreSQL database cluster dump
3--
4
5-- Started on 2019-08-15 09:58:49
6
7SET default_transaction_read_only = off;
8
9SET client_encoding = 'UTF8';
10SET standard_conforming_strings = on;
11
12--
13-- Roles
14--
15
16CREATE ROLE postgres;
17ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS;
18
19
20
21
22
23
24--
25-- Database creation
26--
27
28REVOKE CONNECT,TEMPORARY ON DATABASE template1 FROM PUBLIC;
29GRANT CONNECT ON DATABASE template1 TO PUBLIC;
30
31
32\connect postgres
33
34SET default_transaction_read_only = off;
35
36--
37-- PostgreSQL database dump
38--
39
40-- Dumped from database version 9.6.15
41-- Dumped by pg_dump version 9.6.15
42
43-- Started on 2019-08-15 09:58:49
44
45SET statement_timeout = 0;
46SET lock_timeout = 0;
47SET idle_in_transaction_session_timeout = 0;
48SET client_encoding = 'UTF8';
49SET standard_conforming_strings = on;
50SELECT pg_catalog.set_config('search_path', '', false);
51SET check_function_bodies = false;
52SET xmloption = content;
53SET client_min_messages = warning;
54SET row_security = off;
55
56--
57-- TOC entry 2186 (class 0 OID 0)
58-- Dependencies: 2185
59-- Name: DATABASE postgres; Type: COMMENT; Schema: -; Owner: postgres
60--
61
62COMMENT ON DATABASE postgres IS 'default administrative connection database';
63
64
65--
66-- TOC entry 1 (class 3079 OID 12387)
67-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner:
68--
69
70CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
71
72
73--
74-- TOC entry 2188 (class 0 OID 0)
75-- Dependencies: 1
76-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner:
77--
78
79COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
80
81
82SET default_tablespace = '';
83
84SET default_with_oids = false;
85
86--
87-- TOC entry 185 (class 1259 OID 16384)
88-- Name: fabricante; Type: TABLE; Schema: public; Owner: postgres
89--
90
91CREATE TABLE public.fabricante (
92 cnpj bigint NOT NULL,
93 nome character varying(255),
94 endereco character varying(255)
95);
96
97
98ALTER TABLE public.fabricante OWNER TO postgres;
99
100--
101-- TOC entry 188 (class 1259 OID 16426)
102-- Name: medicamento; Type: TABLE; Schema: public; Owner: postgres
103--
104
105CREATE TABLE public.medicamento (
106 numeroproduto bigint NOT NULL,
107 tarja character varying(255),
108 formula character varying(1023)
109);
110
111
112ALTER TABLE public.medicamento OWNER TO postgres;
113
114--
115-- TOC entry 189 (class 1259 OID 16439)
116-- Name: perfumaria; Type: TABLE; Schema: public; Owner: postgres
117--
118
119CREATE TABLE public.perfumaria (
120 numeroproduto bigint NOT NULL,
121 tipo character varying(255)
122);
123
124
125ALTER TABLE public.perfumaria OWNER TO postgres;
126
127--
128-- TOC entry 187 (class 1259 OID 16405)
129-- Name: produto; Type: TABLE; Schema: public; Owner: postgres
130--
131
132CREATE TABLE public.produto (
133 numero bigint NOT NULL,
134 nomecomercial character varying(255),
135 tipoembalagem character varying(255),
136 quantidade bigint,
137 precounitario real,
138 cnpj bigint
139);
140
141
142ALTER TABLE public.produto OWNER TO postgres;
143
144--
145-- TOC entry 191 (class 1259 OID 16481)
146-- Name: receitamedica; Type: TABLE; Schema: public; Owner: postgres
147--
148
149CREATE TABLE public.receitamedica (
150 crm bigint NOT NULL,
151 numero bigint NOT NULL,
152 dataatendimento timestamp without time zone
153);
154
155
156ALTER TABLE public.receitamedica OWNER TO postgres;
157
158--
159-- TOC entry 186 (class 1259 OID 16397)
160-- Name: venda; Type: TABLE; Schema: public; Owner: postgres
161--
162
163CREATE TABLE public.venda (
164 datavenda timestamp without time zone,
165 numeronota bigint NOT NULL,
166 nomecliente character varying(255),
167 cidadecliente character varying(255)
168);
169
170
171ALTER TABLE public.venda OWNER TO postgres;
172
173--
174-- TOC entry 192 (class 1259 OID 16546)
175-- Name: vendamedicamento; Type: TABLE; Schema: public; Owner: postgres
176--
177
178CREATE TABLE public.vendamedicamento (
179 numeronotavenda bigint NOT NULL,
180 numeroproduto bigint NOT NULL,
181 crm bigint NOT NULL,
182 numero bigint NOT NULL
183);
184
185
186ALTER TABLE public.vendamedicamento OWNER TO postgres;
187
188--
189-- TOC entry 190 (class 1259 OID 16449)
190-- Name: vendaperfumaria; Type: TABLE; Schema: public; Owner: postgres
191--
192
193CREATE TABLE public.vendaperfumaria (
194 numeronotavenda bigint NOT NULL,
195 numeroproduto bigint NOT NULL
196);
197
198
199ALTER TABLE public.vendaperfumaria OWNER TO postgres;
200
201--
202-- TOC entry 2172 (class 0 OID 16384)
203-- Dependencies: 185
204-- Data for Name: fabricante; Type: TABLE DATA; Schema: public; Owner: postgres
205--
206
207COPY public.fabricante (cnpj, nome, endereco) FROM stdin;
2081234567891011 Pablo Suria LTDA Rua dos Escoteiros, 1313
209\.
210
211
212--
213-- TOC entry 2175 (class 0 OID 16426)
214-- Dependencies: 188
215-- Data for Name: medicamento; Type: TABLE DATA; Schema: public; Owner: postgres
216--
217
218COPY public.medicamento (numeroproduto, tarja, formula) FROM stdin;
219\.
220
221
222--
223-- TOC entry 2176 (class 0 OID 16439)
224-- Dependencies: 189
225-- Data for Name: perfumaria; Type: TABLE DATA; Schema: public; Owner: postgres
226--
227
228COPY public.perfumaria (numeroproduto, tipo) FROM stdin;
229\.
230
231
232--
233-- TOC entry 2174 (class 0 OID 16405)
234-- Dependencies: 187
235-- Data for Name: produto; Type: TABLE DATA; Schema: public; Owner: postgres
236--
237
238COPY public.produto (numero, nomecomercial, tipoembalagem, quantidade, precounitario, cnpj) FROM stdin;
239\.
240
241
242--
243-- TOC entry 2178 (class 0 OID 16481)
244-- Dependencies: 191
245-- Data for Name: receitamedica; Type: TABLE DATA; Schema: public; Owner: postgres
246--
247
248COPY public.receitamedica (crm, numero, dataatendimento) FROM stdin;
249\.
250
251
252--
253-- TOC entry 2173 (class 0 OID 16397)
254-- Dependencies: 186
255-- Data for Name: venda; Type: TABLE DATA; Schema: public; Owner: postgres
256--
257
258COPY public.venda (datavenda, numeronota, nomecliente, cidadecliente) FROM stdin;
259\.
260
261
262--
263-- TOC entry 2179 (class 0 OID 16546)
264-- Dependencies: 192
265-- Data for Name: vendamedicamento; Type: TABLE DATA; Schema: public; Owner: postgres
266--
267
268COPY public.vendamedicamento (numeronotavenda, numeroproduto, crm, numero) FROM stdin;
269\.
270
271
272--
273-- TOC entry 2177 (class 0 OID 16449)
274-- Dependencies: 190
275-- Data for Name: vendaperfumaria; Type: TABLE DATA; Schema: public; Owner: postgres
276--
277
278COPY public.vendaperfumaria (numeronotavenda, numeroproduto) FROM stdin;
279\.
280
281
282--
283-- TOC entry 2032 (class 2606 OID 16391)
284-- Name: fabricante fabricante_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
285--
286
287ALTER TABLE ONLY public.fabricante
288 ADD CONSTRAINT fabricante_pkey PRIMARY KEY (cnpj);
289
290
291--
292-- TOC entry 2038 (class 2606 OID 16433)
293-- Name: medicamento medicamento_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
294--
295
296ALTER TABLE ONLY public.medicamento
297 ADD CONSTRAINT medicamento_pkey PRIMARY KEY (numeroproduto);
298
299
300--
301-- TOC entry 2040 (class 2606 OID 16443)
302-- Name: perfumaria perfumaria_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
303--
304
305ALTER TABLE ONLY public.perfumaria
306 ADD CONSTRAINT perfumaria_pkey PRIMARY KEY (numeroproduto);
307
308
309--
310-- TOC entry 2036 (class 2606 OID 16412)
311-- Name: produto produto_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
312--
313
314ALTER TABLE ONLY public.produto
315 ADD CONSTRAINT produto_pkey PRIMARY KEY (numero);
316
317
318--
319-- TOC entry 2044 (class 2606 OID 16485)
320-- Name: receitamedica receitamedica_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
321--
322
323ALTER TABLE ONLY public.receitamedica
324 ADD CONSTRAINT receitamedica_pkey PRIMARY KEY (crm, numero);
325
326
327--
328-- TOC entry 2034 (class 2606 OID 16404)
329-- Name: venda venda_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
330--
331
332ALTER TABLE ONLY public.venda
333 ADD CONSTRAINT venda_pkey PRIMARY KEY (numeronota);
334
335
336--
337-- TOC entry 2046 (class 2606 OID 16550)
338-- Name: vendamedicamento vendamedicamento_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
339--
340
341ALTER TABLE ONLY public.vendamedicamento
342 ADD CONSTRAINT vendamedicamento_pkey PRIMARY KEY (numeronotavenda, numeroproduto, crm, numero);
343
344
345--
346-- TOC entry 2042 (class 2606 OID 16453)
347-- Name: vendaperfumaria vendaperfumaria_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
348--
349
350ALTER TABLE ONLY public.vendaperfumaria
351 ADD CONSTRAINT vendaperfumaria_pkey PRIMARY KEY (numeronotavenda, numeroproduto);
352
353
354--
355-- TOC entry 2048 (class 2606 OID 16434)
356-- Name: medicamento medicamento_numeroproduto_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
357--
358
359ALTER TABLE ONLY public.medicamento
360 ADD CONSTRAINT medicamento_numeroproduto_fkey FOREIGN KEY (numeroproduto) REFERENCES public.produto(numero);
361
362
363--
364-- TOC entry 2049 (class 2606 OID 16444)
365-- Name: perfumaria perfumaria_numeroproduto_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
366--
367
368ALTER TABLE ONLY public.perfumaria
369 ADD CONSTRAINT perfumaria_numeroproduto_fkey FOREIGN KEY (numeroproduto) REFERENCES public.produto(numero);
370
371
372--
373-- TOC entry 2047 (class 2606 OID 16413)
374-- Name: produto produto_cnpj_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
375--
376
377ALTER TABLE ONLY public.produto
378 ADD CONSTRAINT produto_cnpj_fkey FOREIGN KEY (cnpj) REFERENCES public.fabricante(cnpj);
379
380
381--
382-- TOC entry 2054 (class 2606 OID 16561)
383-- Name: vendamedicamento vendamedicamento_crm_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
384--
385
386ALTER TABLE ONLY public.vendamedicamento
387 ADD CONSTRAINT vendamedicamento_crm_fkey FOREIGN KEY (crm, numero) REFERENCES public.receitamedica(crm, numero);
388
389
390--
391-- TOC entry 2052 (class 2606 OID 16551)
392-- Name: vendamedicamento vendamedicamento_numeronotavenda_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
393--
394
395ALTER TABLE ONLY public.vendamedicamento
396 ADD CONSTRAINT vendamedicamento_numeronotavenda_fkey FOREIGN KEY (numeronotavenda) REFERENCES public.venda(numeronota);
397
398
399--
400-- TOC entry 2053 (class 2606 OID 16556)
401-- Name: vendamedicamento vendamedicamento_numeroproduto_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
402--
403
404ALTER TABLE ONLY public.vendamedicamento
405 ADD CONSTRAINT vendamedicamento_numeroproduto_fkey FOREIGN KEY (numeroproduto) REFERENCES public.medicamento(numeroproduto);
406
407
408--
409-- TOC entry 2050 (class 2606 OID 16454)
410-- Name: vendaperfumaria vendaperfumaria_numeronotavenda_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
411--
412
413ALTER TABLE ONLY public.vendaperfumaria
414 ADD CONSTRAINT vendaperfumaria_numeronotavenda_fkey FOREIGN KEY (numeronotavenda) REFERENCES public.venda(numeronota);
415
416
417--
418-- TOC entry 2051 (class 2606 OID 16459)
419-- Name: vendaperfumaria vendaperfumaria_numeroproduto_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
420--
421
422ALTER TABLE ONLY public.vendaperfumaria
423 ADD CONSTRAINT vendaperfumaria_numeroproduto_fkey FOREIGN KEY (numeroproduto) REFERENCES public.perfumaria(numeroproduto);
424
425
426-- Completed on 2019-08-15 09:58:50
427
428--
429-- PostgreSQL database dump complete
430--
431
432\connect template1
433
434SET default_transaction_read_only = off;
435
436--
437-- PostgreSQL database dump
438--
439
440-- Dumped from database version 9.6.15
441-- Dumped by pg_dump version 9.6.15
442
443-- Started on 2019-08-15 09:58:50
444
445SET statement_timeout = 0;
446SET lock_timeout = 0;
447SET idle_in_transaction_session_timeout = 0;
448SET client_encoding = 'UTF8';
449SET standard_conforming_strings = on;
450SELECT pg_catalog.set_config('search_path', '', false);
451SET check_function_bodies = false;
452SET xmloption = content;
453SET client_min_messages = warning;
454SET row_security = off;
455
456--
457-- TOC entry 2118 (class 0 OID 0)
458-- Dependencies: 2117
459-- Name: DATABASE template1; Type: COMMENT; Schema: -; Owner: postgres
460--
461
462COMMENT ON DATABASE template1 IS 'default template for new databases';
463
464
465--
466-- TOC entry 1 (class 3079 OID 12387)
467-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner:
468--
469
470CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
471
472
473--
474-- TOC entry 2120 (class 0 OID 0)
475-- Dependencies: 1
476-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner:
477--
478
479COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
480
481
482-- Completed on 2019-08-15 09:58:50
483
484--
485-- PostgreSQL database dump complete
486--
487
488-- Completed on 2019-08-15 09:58:50
489
490--
491-- PostgreSQL database cluster dump complete
492--