· 4 years ago · Jul 27, 2021, 12:50 PM
1
2CREATE EXTENSION IF NOT EXISTS tds_fdw;
3CREATE SERVER IF NOT EXISTS mssql_altai FOREIGN DATA WRAPPER tds_fdw OPTIONS (servername 'AK-S-ASUTP21.ao.nlmk', port '1433', database 'ALLASUTP', msg_handler 'notice');
4CREATE USER MAPPING IF NOT EXISTS FOR postgres SERVER mssql_altai OPTIONS (username 'allasutp', password '5Xf5CT54mK');
5
6SET TIME ZONE 'Europe/Moscow';
7
8 CREATE FOREIGN TABLE IF NOT EXISTS STORE32_UUP1 (
9 "Silo" FLOAT,
10 "Number_layer" FLOAT,
11 "NumBegShift" FLOAT,
12 "Mine" varchar(40),
13 "Markfull" varchar(30),
14 "Mark" varchar(8),
15 "W" FLOAT,
16 "A" FLOAT,
17 "V" FLOAT,
18 "X" FLOAT,
19 "Y" FLOAT,
20 "P" FLOAT,
21 "S" FLOAT,
22 "Data" TIMESTAMP WITH TIME ZONE,
23 "DATECHANGE" TIMESTAMP WITH TIME ZONE,
24 "LayerOrder" FLOAT,
25 "Code" FLOAT,
26 "NAKLADN" FLOAT,
27 "SULFUR" FLOAT,
28 "SAMPLE" INTEGER,
29 "R_NULL" FLOAT,
30 "VT" FLOAT,
31 "SUMM_OK" FLOAT,
32 "SIGMA_R" FLOAT
33 )
34 SERVER mssql_altai
35 OPTIONS (query 'SELECT * FROM Store32_UUP1;');
36
37 CREATE FOREIGN TABLE IF NOT EXISTS STORE32_UUP2 (
38 "Silo" FLOAT,
39 "Number_layer" FLOAT,
40 "NumBegShift" FLOAT,
41 "Mine" varchar(40),
42 "Markfull" varchar(30),
43 "Mark" varchar(8),
44 "W" FLOAT,
45 "A" FLOAT,
46 "V" FLOAT,
47 "X" FLOAT,
48 "Y" FLOAT,
49 "P" FLOAT,
50 "S" FLOAT,
51 "Data" TIMESTAMP WITH TIME ZONE,
52 "DATECHANGE" TIMESTAMP WITH TIME ZONE,
53 "LayerOrder" FLOAT,
54 "Code" FLOAT,
55 "NAKLADN" FLOAT,
56 "SULFUR" FLOAT,
57 "SAMPLE" INTEGER,
58 "R_NULL" FLOAT,
59 "VT" FLOAT,
60 "SUMM_OK" FLOAT,
61 "SIGMA_R" FLOAT
62 )
63 SERVER mssql_altai
64 OPTIONS (query 'SELECT * FROM Store32_UUP2;');
65
66
67
68 CREATE TABLE IF NOT EXISTS STORE32_UUP (
69 "UUP" INTEGER,
70 "Silo" FLOAT,
71 "Number_layer" FLOAT,
72 "NumBegShift" FLOAT,
73 "Mine" varchar(40),
74 "Markfull" varchar(30),
75 "Mark" varchar(8),
76 "W" FLOAT,
77 "A" FLOAT,
78 "V" FLOAT,
79 "X" FLOAT,
80 "Y" FLOAT,
81 "P" FLOAT,
82 "S" FLOAT,
83 "Data" TIMESTAMP WITH TIME ZONE,
84 "DATECHANGE" TIMESTAMP WITH TIME ZONE,
85 "LayerOrder" FLOAT,
86 "Code" FLOAT,
87 "NAKLADN" FLOAT,
88 "SULFUR" FLOAT,
89 "SAMPLE" INTEGER,
90 "R_NULL" FLOAT,
91 "VT" FLOAT,
92 "SUMM_OK" FLOAT,
93 "SIGMA_R" FLOAT,
94 CONSTRAINT store32_uup_pk PRIMARY KEY ("Silo", "Number_layer", "NumBegShift", "Mine", "Markfull", "Mark", "W","A", "V", "X", "Y", "P", "S", "DATA", "DATACHANGE", "LayerOrder", "Code", "NAKLADN", "SULFUR", "SAMPLE", "R_NULL", "VT", "SUMM_OK", "SIGMA_R")
95 );
96
97 WITH v AS (
98 SELECT
99 '1' as "UUP","Silo","Number_layer","NumBegShift","Mine","Markfull","Mark","W","A","V","X","Y","P","S",
100 "Data","DATECHANGE","LayerOrder","Code","NAKLADN","SULFUR","SAMPLE","R_NULL","VT","SUMM_OK","SIGMA_R"
101 FROM STORE32_UUP1
102 EXCEPT
103 SELECT
104 "UUP","Silo","Number_layer","NumBegShift","Mine","Markfull","Mark","W","A","V","X","Y","P","S",
105 "Data","DATECHANGE","LayerOrder","Code","NAKLADN","SULFUR","SAMPLE","R_NULL","VT","SUMM_OK","SIGMA_R"
106 FROM store32_uup
107 )
108 INSERT INTO store32_uup (
109 "UUP","Silo","Number_layer","NumBegShift","Mine","Markfull","Mark","W","A","V","X","Y","P","S",
110 "Data","DATECHANGE","LayerOrder","Code","NAKLADN","SULFUR","SAMPLE","R_NULL","VT","SUMM_OK","SIGMA_R")
111 SELECT
112 '1' as "UUP","Silo","Number_layer","NumBegShift","Mine","Markfull","Mark","W","A","V","X","Y","P","S",
113 "Data","DATECHANGE","LayerOrder","Code","NAKLADN","SULFUR","SAMPLE","R_NULL","VT","SUMM_OK","SIGMA_R"
114 FROM v ON CONFLICT
115 ("Silo", "Number_layer", "NumBegShift", "Mine", "Markfull", "Mark", "W","A", "V", "X", "Y", "P", "S",
116 "DATA", "DATACHANGE", "LayerOrder", "Code", "NAKLADN", "SULFUR", "SAMPLE", "R_NULL", "VT", "SUMM_OK",
117 "SIGMA_R")
118 DO UPDATE SET
119 "UUP" = EXCLUDED."UUP",
120 "Silo" = EXCLUDED."Silo",
121 "Number_layer" = EXCLUDED."Number_layer",
122 "NumBegShift" = EXCLUDED."NumBegShift",
123 "Mine" = EXCLUDED."Mine",
124 "Markfull" = EXCLUDED."Markfull",
125 "Mark" = EXCLUDED."Mark",
126 "W" = EXCLUDED."W",
127 "A" = EXCLUDED."A",
128 "V" = EXCLUDED."V",
129 "X" = EXCLUDED."X",
130 "Y" = EXCLUDED."Y",
131 "P" = EXCLUDED."P",
132 "S" = EXCLUDED."S",
133 "Data" = EXCLUDED."Data",
134 "DATECHANGE" = EXCLUDED."DATECHANGE",
135 "LayerOrder" = EXCLUDED."LayerOrder",
136 "Code" = EXCLUDED."Code",
137 "NAKLADN" = EXCLUDED."NAKLADN",
138 "SULFUR" = EXCLUDED."SULFUR",
139 "SAMPLE" = EXCLUDED."SAMPLE",
140 "R_NULL" = EXCLUDED."R_NULL",
141 "VT" = EXCLUDED."VT",
142 "SUMM_OK" = EXCLUDED."SUMM_OK",
143 "SIGMA_R" = EXCLUDED."SIGMA_R";
144
145 WITH v AS (
146 SELECT
147 '2' as "UUP","Silo","Number_layer","NumBegShift","Mine","Markfull","Mark","W","A","V","X","Y","P","S",
148 "Data","DATECHANGE","LayerOrder","Code","NAKLADN","SULFUR","SAMPLE","R_NULL","VT","SUMM_OK","SIGMA_R"
149 FROM STORE32_UUP2
150 EXCEPT
151 SELECT
152 "UUP","Silo","Number_layer","NumBegShift","Mine","Markfull","Mark","W","A","V","X","Y","P","S",
153 "Data","DATECHANGE","LayerOrder","Code","NAKLADN","SULFUR","SAMPLE","R_NULL","VT","SUMM_OK","SIGMA_R"
154 FROM store32_uup
155 )
156 INSERT INTO store32_uup (
157 "UUP","Silo","Number_layer","NumBegShift","Mine","Markfull","Mark","W","A","V","X","Y","P","S",
158 "Data","DATECHANGE","LayerOrder","Code","NAKLADN","SULFUR","SAMPLE","R_NULL","VT","SUMM_OK","SIGMA_R")
159 SELECT
160 '2' as "UUP","Silo","Number_layer","NumBegShift","Mine","Markfull","Mark","W","A","V","X","Y","P","S",
161 "Data","DATECHANGE","LayerOrder","Code","NAKLADN","SULFUR","SAMPLE","R_NULL","VT","SUMM_OK","SIGMA_R"
162 FROM v ON CONFLICT
163 ("Silo", "Number_layer", "NumBegShift", "Mine", "Markfull", "Mark", "W","A", "V", "X", "Y", "P", "S",
164 "DATA", "DATACHANGE", "LayerOrder", "Code", "NAKLADN", "SULFUR", "SAMPLE", "R_NULL", "VT", "SUMM_OK",
165 "SIGMA_R")
166 DO UPDATE SET
167 "UUP" = EXCLUDED."UUP",
168 "Silo" = EXCLUDED."Silo",
169 "Number_layer" = EXCLUDED."Number_layer",
170 "NumBegShift" = EXCLUDED."NumBegShift",
171 "Mine" = EXCLUDED."Mine",
172 "Markfull" = EXCLUDED."Markfull",
173 "Mark" = EXCLUDED."Mark",
174 "W" = EXCLUDED."W",
175 "A" = EXCLUDED."A",
176 "V" = EXCLUDED."V",
177 "X" = EXCLUDED."X",
178 "Y" = EXCLUDED."Y",
179 "P" = EXCLUDED."P",
180 "S" = EXCLUDED."S",
181 "Data" = EXCLUDED."Data",
182 "DATECHANGE" = EXCLUDED."DATECHANGE",
183 "LayerOrder" = EXCLUDED."LayerOrder",
184 "Code" = EXCLUDED."Code",
185 "NAKLADN" = EXCLUDED."NAKLADN",
186 "SULFUR" = EXCLUDED."SULFUR",
187 "SAMPLE" = EXCLUDED."SAMPLE",
188 "R_NULL" = EXCLUDED."R_NULL",
189 "VT" = EXCLUDED."VT",
190 "SUMM_OK" = EXCLUDED."SUMM_OK",
191 "SIGMA_R" = EXCLUDED."SIGMA_R";
192
193