· 5 years ago · Feb 05, 2020, 06:22 AM
1artem@artem-office:~$ pg_dump -U postgres example
2--
3-- PostgreSQL database dump
4--
5
6SET statement_timeout = 0;
7SET lock_timeout = 0;
8SET client_encoding = 'UTF8';
9SET standard_conforming_strings = on;
10SELECT pg_catalog.set_config('search_path', '', false);
11SET check_function_bodies = false;
12SET client_min_messages = warning;
13
14--
15-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner:
16--
17
18CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
19
20
21--
22-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner:
23--
24
25COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
26
27
28--
29-- Name: uuid-ossp; Type: EXTENSION; Schema: -; Owner:
30--
31
32CREATE EXTENSION IF NOT EXISTS "uuid-ossp" WITH SCHEMA public;
33
34
35--
36-- Name: EXTENSION "uuid-ossp"; Type: COMMENT; Schema: -; Owner:
37--
38
39COMMENT ON EXTENSION "uuid-ossp" IS 'generate universally unique identifiers (UUIDs)';
40
41
42SET default_tablespace = '';
43
44SET default_with_oids = false;
45
46--
47-- Name: organization_unit; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
48--
49
50CREATE TABLE public.organization_unit (
51 id uuid DEFAULT public.uuid_generate_v4() NOT NULL,
52 name text NOT NULL,
53 parent_unit_id uuid
54);
55
56
57ALTER TABLE public.organization_unit OWNER TO postgres;
58
59--
60-- Name: users; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
61--
62
63CREATE TABLE public.users (
64 id uuid DEFAULT public.uuid_generate_v4() NOT NULL,
65 fio text,
66 organization_unit_id uuid NOT NULL
67);
68
69
70ALTER TABLE public.users OWNER TO postgres;
71
72--
73-- Data for Name: organization_unit; Type: TABLE DATA; Schema: public; Owner: postgres
74--
75
76COPY public.organization_unit (id, name, parent_unit_id) FROM stdin;
77cb010346-564c-4726-b462-f17410594b71 zalupa inc \N
78ecfbad21-0f26-4a90-9a28-d273126293f7 v_rot_sral_department cb010346-564c-4726-b462-f17410594b71
79d43e2190-49f5-4d2f-b442-06b18ac18595 v_rot_sral_unit ecfbad21-0f26-4a90-9a28-d273126293f7
801c77f76e-56b8-4c34-a1b1-2a1185fb0223 v_rot_sral_sub_unit d43e2190-49f5-4d2f-b442-06b18ac18595
8166abba4b-bdf9-454a-853e-b5d6d3b339c4 v_rot_bral_department cb010346-564c-4726-b462-f17410594b71
82\.
83
84
85--
86-- Data for Name: users; Type: TABLE DATA; Schema: public; Owner: postgres
87--
88
89COPY public.users (id, fio, organization_unit_id) FROM stdin;
90bb06df35-dd87-441d-b5c3-f90864de30af ganvoed ivan invanuch 66abba4b-bdf9-454a-853e-b5d6d3b339c4
910a4b1dae-9209-4ac8-a617-bfe2de2549cb ganvdav petr petrovich 1c77f76e-56b8-4c34-a1b1-2a1185fb0223
92\.
93
94
95--
96-- Name: organization_unit_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
97--
98
99ALTER TABLE ONLY public.organization_unit
100 ADD CONSTRAINT organization_unit_pkey PRIMARY KEY (id);
101
102
103--
104-- Name: users_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
105--
106
107ALTER TABLE ONLY public.users
108 ADD CONSTRAINT users_pkey PRIMARY KEY (id);
109
110
111--
112-- Name: organization_unit_parent_unit_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
113--
114
115ALTER TABLE ONLY public.organization_unit
116 ADD CONSTRAINT organization_unit_parent_unit_id_fkey FOREIGN KEY (parent_unit_id) REFERENCES public.organization_unit(id);
117
118
119--
120-- Name: users_organization_unit_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
121--
122
123ALTER TABLE ONLY public.users
124 ADD CONSTRAINT users_organization_unit_id_fkey FOREIGN KEY (organization_unit_id) REFERENCES public.organization_unit(id);
125
126
127--
128-- Name: SCHEMA public; Type: ACL; Schema: -; Owner: postgres
129--
130
131REVOKE ALL ON SCHEMA public FROM PUBLIC;
132REVOKE ALL ON SCHEMA public FROM postgres;
133GRANT ALL ON SCHEMA public TO postgres;
134GRANT ALL ON SCHEMA public TO PUBLIC;
135
136
137--
138-- PostgreSQL database dump complete
139--
140
141artem@artem-office:~$ psql -U postgres
142psql (11.2 (Ubuntu 11.2-1.pgdg18.04+1), server 9.4.21)
143Type "help" for help.
144
145postgres=# \c example
146psql (11.2 (Ubuntu 11.2-1.pgdg18.04+1), server 9.4.21)
147You are now connected to database "example" as user "postgres".
148example=# \dt
149 List of relations
150 Schema | Name | Type | Owner
151--------+-------------------+-------+----------
152 public | organization_unit | table | postgres
153 public | users | table | postgres
154(2 rows)
155
156example=# \d organization_unit
157 Table "public.organization_unit"
158 Column | Type | Collation | Nullable | Default
159----------------+------+-----------+----------+--------------------
160 id | uuid | | not null | uuid_generate_v4()
161 name | text | | not null |
162 parent_unit_id | uuid | | |
163Indexes:
164 "organization_unit_pkey" PRIMARY KEY, btree (id)
165Foreign-key constraints:
166 "organization_unit_parent_unit_id_fkey" FOREIGN KEY (parent_unit_id) REFERENCES organization_unit(id)
167Referenced by:
168 TABLE "organization_unit" CONSTRAINT "organization_unit_parent_unit_id_fkey" FOREIGN KEY (parent_unit_id) REFERENCES organization_unit(id)
169 TABLE "users" CONSTRAINT "users_organization_unit_id_fkey" FOREIGN KEY (organization_unit_id) REFERENCES organization_unit(id)
170
171example=# \d users
172 Table "public.users"
173 Column | Type | Collation | Nullable | Default
174----------------------+------+-----------+----------+--------------------
175 id | uuid | | not null | uuid_generate_v4()
176 fio | text | | |
177 organization_unit_id | uuid | | not null |
178Indexes:
179 "users_pkey" PRIMARY KEY, btree (id)
180Foreign-key constraints:
181 "users_organization_unit_id_fkey" FOREIGN KEY (organization_unit_id) REFERENCES organization_unit(id)
182
183example=#
184example=# select * from users;
185 id | fio | organization_unit_id
186--------------------------------------+------------------------+--------------------------------------
187 bb06df35-dd87-441d-b5c3-f90864de30af | ganvoed ivan invanuch | 66abba4b-bdf9-454a-853e-b5d6d3b339c4
188 0a4b1dae-9209-4ac8-a617-bfe2de2549cb | ganvdav petr petrovich | 1c77f76e-56b8-4c34-a1b1-2a1185fb0223
189(2 rows)
190
191example=# WITH RECURSIVE unit_tree AS (
192SELECT id, name, parent_unit_id FROM organization_unit WHERE id = '1c77f76e-56b8-4c34-a1b1-2a1185fb0223'
193UNION ALL
194select u.id, u.name, u.parent_unit_id FROM organization_unit u
195JOIN unit_tree u_tree ON u_tree.parent_unit_id = u.id
196)
197SELECT * FROM unit_tree;
198 id | name | parent_unit_id
199--------------------------------------+-----------------------+--------------------------------------
200 1c77f76e-56b8-4c34-a1b1-2a1185fb0223 | v_rot_sral_sub_unit | d43e2190-49f5-4d2f-b442-06b18ac18595
201 d43e2190-49f5-4d2f-b442-06b18ac18595 | v_rot_sral_unit | ecfbad21-0f26-4a90-9a28-d273126293f7
202 ecfbad21-0f26-4a90-9a28-d273126293f7 | v_rot_sral_department | cb010346-564c-4726-b462-f17410594b71
203 cb010346-564c-4726-b462-f17410594b71 | zalupa inc |
204(4 rows)
205
206example=#