This repository has been archived by the owner on Apr 6, 2022. It is now read-only.
-
Notifications
You must be signed in to change notification settings - Fork 0
/
auto
399 lines (399 loc) · 27.9 KB
/
auto
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
create extension postgis;
CREATE EXTENSION postgis_topology;
CREATE EXTENSION dblink;
\q
\d
\q
alter user 'biodiv' with password 'biodiv';
alter user biodiv with password 'biodiv';
\q
SELECT
pg_terminate_backend(pid)
FROM
pg_stat_activity
WHERE
-- don't kill my own connection!
pid <> pg_backend_pid()
-- don't kill the connections to other databases
AND datname ='biodiv';
\q
\d
\q
select * from species_group;
\q
select * from species_group;
\q
\q
\d
\d userGroup
\d usergroup
\d user_group
\d user_group
select webaddress from user_group
\d user_group
select webaddress from user_group
;
select webaddress from user_group;
\d taxonomy_definition
select id from taxonomy_definition where rank=9;
select id from taxonomy_definition where rank=9 and status=accepted;
select id from taxonomy_definition where rank=9 and status='ACCEPTED';
select id from taxonomy_definition where rank=8 and status='ACCEPTED';
\d
select id from taxonomy_definition;
select * from taxonomy_definition where id=872;
!
select * from taxonomy_definition where id='872';
!
select * from taxonomy_definition where id=872;
!
select * from taxonomy_definition where id=872;
select * from taxonomy_definition where id=872;
select * from taxonomy_definition where id='872';
\d taxonomy_registry
select * from taxonomy_registry where id=874
biodiv=# select * from taxonomy_registry where id=874
select * from taxonomy_definition where id='872';
select * from taxonomy_registry where id='872';
select * from taxonomy_registry;
select * from taxonomy_registry where id=387427;
select * from taxonomy_registry where id=874;
select * from taxonomy_definition where id=874;
select * from taxonomy_definition where id=874;
select * from taxonomy_definition where id=874;
select default_hierarchy from taxonomy_definition where id=874;
select path from taxonomy_definition
select path fromtaxony_definition;
select path from taxonomy_definition;
select path from taxonomy_registry
select path from taxonomy_registry;
select path from taxonomy_registry;
select path from taxonomy_registry where id=874;
select path from taxonomy_registry where taxoid=874;
select path from taxonomy_registry where id=874;
select path from taxonomy_registry where id=872;
\d taxonomy_registry
select path from taxonomy_registry where id=872;
select path from taxonomy_registry where id='872';
select path from taxonomy_registry where id='2998';
select path from taxonomy_registry where parent_taxon_id='2998';
select id from taxonomy_registry where id='872';
select * from taxonomy_registry where id='872';
select * from taxonomy_registry;
\d taxonomy_registry
select id from taxonomy_registry
select id from taxonomy_registry;
select id from taxonomy_registry;
select id from taxonomy_registry where id=874;
select id from taxonomy_registry where id='874';
select id from taxonomy_registry where id='874';
select * from taxonomy_registry where id='874';
select id from taxonomy_registry where classification_id='27798;
select id from taxonomy_registry where classification_id ='2997'
\d taxonomy_definition
\d taxonomy_definition'
cd
\d taxonomy_definition;
\d taxonomy_registry
select path from taxonomy_registry where id='874';
select path from taxonomy_registry where id='874';
select path from taxonomy_registry where taxon_definition_id='874';
select path from taxonomy_registry where taxon_definition_id='872';
select path from taxonomy_registry where taxon_definition_id='447252';
select path from taxonomy_registry where taxon_definition_id='298';
select path from taxonomy_registry where taxon_definition_id='2998';
select path from taxonomy_registry where parent_taxon_id='2998';
select path from taxonomy_registry where parent_taxon_id='872';
select path from taxonomy_registry where parent_taxon_definition_id='872';
select path from taxonomy_registry where parent_taxon_definition_id='874';
select path from taxonomy_registry where taxon_definition_id='';
select path from taxonomy_registry where taxon_definition_id='872';
select path from taxonomy_registry where taxon_definition_id='874';
EXPLAIN ANALYSE select * from taxonomy_definition;
EXPLAIN ANALYSE select * from classification ;
EXPLAIN ANALYSE select * from classification ;
EXPLAIN ANALYSE select * from classification;
EXPLAIN ANALYSE select * from classification;
EXPLAIN ANALYSE select * from classification;
EXPLAIN ANALYSE select * from taxonomy_registry ad tr, clasification as c where tr.classification_id=c.id;
EXPLAIN ANALYSE select * from taxonomy_registry as tr, clasification as c where tr.classification_id=c.id;
EXPLAIN ANALYSE select * from taxonomy_registry as tr, classification as c where tr.classification_id=c.id;
select * from taxonomy_registry as tr, classification as c where tr.classification_id=c.id;
select tr.id,tr.classification_id,c.id from taxonomy_registry as tr, classification as c where tr.classification_id=c.id;
select tr.path,tr.classification_id,c.id from taxonomy_registry as tr, classification as c where tr.classification_id=c.id;
select tr.id, tr.path,tr.classification_id,c.id from taxonomy_registry as tr, classification as c where tr.classification_id=c.id;
select tr.id, tr.path,tr.classification_id,c.id from taxonomy_registry as tr, classification as c where tr.classification_id=c.id where tr.id='874';
select tr.id, tr.path,tr.classification_id,c.id from taxonomy_registry as tr, classification as c where tr.classification_id=c.id where tr.id='874';
select tr.id, tr.path,tr.classification_id,c.id from taxonomy_registry as tr, classification as c where tr.classification_id=c.id and tr.id='874';
select tr.id, tr.path,tr.classification_id,c.id from taxonomy_registry as tr, classification as c where tr.classification_id=c.id and tr.taxon_concept_id='874';
select tr.id, tr.path,tr.classification_id,c.id from taxonomy_registry as tr, classification as c where tr.classification_id=c.id and tr.taxon_definition_id='874';
select tr.taxon_definition_id, tr.path,tr.classification_id,c.id from taxonomy_registry as tr, classification as c where tr.classification_id=c.id and tr.taxon_definition_id='874';
select tr.taxon_definition_id, tr.path,tr.classification_id,c.id from taxonomy_registry as tr, classification as c where tr.classification_id=c.id and tr.taxon_definition_id='872';
select tr.taxon_definition_id, tr.path,tr.classification_id,c.id from taxonomy_registry as tr, classification as c where tr.classification_id=c.id and tr.taxon_definition_id='874';
select t.path from taxonomy_registry as t where t.taxon_definitionid=874
;
select t.path from taxonomy_registry as t where t.taxon_definition_id=874
;
select t.path from taxonomy_registry as t where t.taxon_definition_id=874 and tR.classification_id=265799;
select t.name,t.id from taxonomy_definition as t taxonomy_registry as tR where tR.taxon_definition_id=t.id and tR.classification_id=265799 and tR.path like '872_874';
select t.name,t.id from taxonomy_definition as t ,taxonomy_registry as tR where tR.taxon_definition_id=t.id and tR.classification_id=265799 and tR.path like '872_874';
select t.name,t.id from taxonomy_definition as t ,taxonomy_registry as tR where tR.taxon_definition_id=t.id and tR.classification_id=265799 and tR.path like '%872_874%';
select t.id,t.name,tR.path from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.path ~ ^874_[0-9];
select t.id,t.name,tR.path from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.path ~ [^874]_[0-9];
select t.id,t.name,tR.path from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.path ~ ^874_[0-9];
select t.id,t.name,tR.path from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.path ~ '^874_[0-9]';
select t.id,t.name,tR.path from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.path ~ '^[874]_[0-9]';
select t.id,t.name,tR.path from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.path ~ '[874]_[0-9]';
select t.id,t.name,tR.path from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.path ~ '[874]_[0-9]' LIMIT 2;
select t.id,t.name,tR.path from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.path ~ '[874]_[0-9]' LIMIT 2;
select t.id,t.name,tR.path from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.path ~ '[874]_[0-9]' LIMIT 2;
select t.id,t.name,tR.path from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.path ~ '[874]_[0-9]' LIMIT 2;
select t.id,t.name,tR.path from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.path ~ '[874]_[0-9]' LIMIT 2;
\s
select t.id,t.name,tR.path from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.path ~ '[874]_[0-9]' LIMIT 2;
select t.id,t.name,tR.path from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.path ~ '[874]_[0-9]' LIMIT 2;
\du
\list
\l
\dt
\dt taxonomy_definition
\dt taxonomy_definition;
\d taxonomy_definition;
\d+ taxonomy_definition;
\d taxonomy_definition;
\d+ taxonomy_definition;
\d+
\d+ taxonomy_definition;
\d+ taxonomy_definition;
\d+
\s
select t.id,t.name,tR.path from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.path ~ '[874]_[0-9]' LIMIT 2;
select t.id,t.name,tR.path from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.path ~ '^874_[0-9]';
select t.id,t.name,tR.path from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.path ~ '^874_[0-9]$';
select t.id,t.name,tR.path from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.path ~ '^[874]_[0-9]$';
select t.id,t.name,tR.path from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.path ~ '^[874]_[0-9]';
select t.id,t.name,tR.path from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.path ~ '^[874]_[0-9]';
select t.id,t.name,tR.path from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.path ~ '^[874]_[0-9]';
select t.id,t.name,tR.path from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.path ~ '^872_[0-9]';
/s
\s
select t.id,t.name,tR.path from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.path ~ '[874]_[0-9]' LIMIT 2;
select t.id,t.name,tR.path from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.path ~ '[874]_[0-9]' LIMIT 2;
select t.id,t.name,tR.path from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.path ~ '^872_[0-9]%';
select t.id,t.name,tR.path from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.path ~ '^872_[0-9]';
select count(t.id),t.name,tR.path from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.path ~ '^872_[0-9]';
select count(t.id) from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.path ~ '^872_[0-9]';
select count(t.id) from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.path ~ '^874_[0-9]';
select count(t.id) from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.path ~ '^872_874_[0-9]';
select count(t.id) from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.classification_id=265799 tR.path ~ '^872_874_[0-9]';
select count(t.id) from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.classification_id=265799 and tR.path ~ '^872_874_[0-9]';
select count(t.id) from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.classification_id=265799 and tR.path ~ '^872_874_[0-9]';
select count(t.id) from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.classification_id=265799 and tR.path ~ '^872_874_[0-9]_[0-9]';
select count(t.id) from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.classification_id=265799 and tR.path ~ '^872_874';
select count(t.id) from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.classification_id=265799 and tR.path ~ '^872_874_[0-9]' ;
select count(t.id) from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.classification_id=265799 and tR.path ~ '^872_874_[0-9]' group by s.path ;
select count(t.id) from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.classification_id=265799 and tR.path ~ '^872_874_[0-9]' group by tR.path ;
select count(tR.path) from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.classification_id=265799 and tR.path ~ '^872_874_[0-9]' group by tR.path ;
select count(tR.path) as c from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.classification_id=265799 and tR.path ~ '^872_874_[0-9]' group by tR.path where c>10 ;
select count(tR.path) as c from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.classification_id=265799 and tR.path ~ '^872_874_[0-9]' group by tR.path where c > 10 ;
select count(tR.path) as c from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.classification_id=265799 and tR.path ~ '^872_874_[0-9]' group by tR.path and c > 10 ;
select count(tR.path) as c from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.classification_id=265799 and tR.path ~ '^872_874_[0-9]' group by tR.path having c > 10 ;
select count(tR.path) as c from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.classification_id=265799 and tR.path ~ '^872_874_[0-9]' group by tR.path having c > 10 ;
select count(tR.path) as C from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.classification_id=265799 and tR.path ~ '^872_874_[0-9]' group by tR.path having C > 10 ;
select count(tR.path) from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.classification_id=265799 and tR.path ~ '^872_874_[0-9]' group by tR.path as C having C > 10 ;
select count(tR.path) from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.classification_id=265799 and tR.path ~ '^872_874_[0-9]' group by tR.path ;
select count(tR.path) from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.classification_id=265799 and tR.path ~ '^872_874_[0-9]';
select count(tR.path) from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.classification_id=265799 and tR.path ~ '^872';
select tR.path from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.classification_id=265799 and tR.path ~ '^872_874_[0-9]';
select tR.path from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.classification_id=265799 and tR.path ~ '^872_874___';
select tR.path from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.classification_id=265799 and tR.path ~ '^872_874';
select count(tR.path) from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.classification_id=265799 and tR.path ~ '^872_874';
select count(tR.path) from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.classification_id=265799 and tR.path ~ '^872_874_';
select count(tR.path) from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.classification_id=265799 and tR.path ~ '^872_874_8';
select count(tR.path) from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.classification_id=265799 and tR.path ~ '^872_874_...';
select tR.path from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.classification_id=265799 and tR.path ~ '^872_874_...';
select tR.path from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.classification_id=265799 and tR.path ~ '^872_874';
select tR.path from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.classification_id=265799 and tR.path ~ '^872_874' group by tR.path;
select count(tR.path) from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.classification_id=265799 and tR.path ~ '^872_874' group by tR.path;
select count(tR.path) as A from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.classification_id=265799 and tR.path ~ '^872_874' group by tR.path hav;
select count(tR.path) from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.classification_id=265799 and tR.path ~ '^872_874' group by tR.path;
select tR.path from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.classification_id=265799 and tR.path ~ '^872_874' group by tR.path;
select tR.path from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.classification_id=265799 and tR.path ~ '^872_874';
select tR.path from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.classification_id=265799 and tR.path ~ '^872_874_[0-9]';
select tR.path from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.classification_id=265799 and tR.path ~ '^872_874_[0-9]$';
select tR.path from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.classification_id=265799 and tR.path ~ '^872_874_[0-9]+$';
select count(tR.path) from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.classification_id=265799 and tR.path ~ '^872_874_[0-9]+$';
select count(tR.path) from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.classification_id=265799 and tR.path ~ '^872_[0-9]+$';
select count(tR.path) from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.classification_id=265799 and tR.path ~ '^872_[0-9]+$';
select count(tR.path) from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.classification_id=265799 and tR.path ~ '^872_[0-9]+$' or tR.path ~'872_874_[0-9]+$';
\timing
select count(tR.path) from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.classification_id=265799 and tR.path ~ '^872_[0-9]+ ''872_874_[0-9]+$';
select count(tR.path) from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.classification_id=265799 and tR.path ~ '^872_[0-9]+$' or tR.path ~'872_874_[0-9]+$';
select count(tR.path) from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.classification_id=265799 and tR.path ~ '^872_[0-9]+$' or tR.path ~ '872_874_[0-9]+$';
select count(tR.path) from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.classification_id=265799 and tR.path ~ '872_874_[0-9]+$';
select count(tR.path) from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.classification_id=265799 and (tR.path ~ '^872_[0-9]+$' or tR.path ~ '872_874_[0-9]+$');
select tR.path from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.classification_id=265799 and (tR.path ~ '^872_[0-9]+$' or tR.path ~ '872_874_[0-9]+$');
select tR.path from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.classification_id=265799 and (tR.path ~ '^872_[0-9]+$' or tR.path ~ '872_874_[0-9]+$') group by tR.path;
select tR.path from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.classification_id=265799 and (tR.path ~ '^872_[0-9]+$' or tR.path ~ '872_874_[0-9]+$' or tR.path is NULL) group by tR.path;
select count(tR.path) from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.classification_id=265799 and (tR.path ~ '^872_[0-9]+$' or tR.path ~ '872_874_[0-9]+$' or tR.path is NULL) group by tR.path;
select count(tR.path) from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.classification_id=265799 and (tR.path ~ '^872_[0-9]+$' or tR.path ~ '872_874_[0-9]+$' or tR.path is NULL);
select count(tR.path) from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.classification_id=265799 and (tR.path ~ '^872_[0-9]+$' or tR.path ~ '872_874_[0-9]+$' or tR.path is NULL);
select count(tR.path) from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.classification_id=265799 and (tR.path ~ '^872_[0-9]+$' or tR.path ~ '872_874_[0-9]+$');
select count(tR.path) from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.classification_id=265799 and (tR.path ~ '^872_[0-9]+$' or tR.path ~ '872_874_[0-9]+$' or t.rank=0);
select count(tR.path) from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.classification_id=265799 and (tR.path ~ '^872_[0-9]+$' or tR.path ~ '872_874_[0-9]+$' or t.rank=0);
select tR.path from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.classification_id=265799 and (tR.path ~ '^872_[0-9]+$' or tR.path ~ '872_874_[0-9]+$' or t.rank=0);
select t.id from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.classification_id=265799 and (tR.path ~ '^872_[0-9]+$' or tR.path ~ '872_874_[0-9]+$' or t.rank=0);
\d+ taxonomy_definition
\d+ taxonomy_registry
create index on_path on table taxonomy_registry (path);
create index on_path on taxonomy_registry (path);
select t.id from taxonomy_registry as tR,taxonomy_definition as t where t.id=tR.taxon_definition_id and tR.classification_id=265799 and (tR.path ~ '^872_[0-9]+$' or tR.path ~ '872_874_[0-9]+$' or t.rank=0);
\d+ taxonomy_registry
\d+ taxonomy_registry
\d+ taxonomy_definition
\d+ observation
\q
\d+ accepted_synonym
\d+ taxonomy_definition;
\d+ taxonomy_registry;
\d+ species_group
select * from species_group;
\d+ user_group_member_role
\d+ user_group_member_role
\d+ user_group_member_role
select id from taxonomy_definition where name=Gymnopleurus;
select id from taxonomy_definition where name='Gymnopleurus';
pg_dump -t 'taxonomy_definition' --schema-only biodiv
;
COPY taxonomy_definition TO '/home/sunil/taxonomy_definition.csv' DELIMITER ',' CSV HEADER;
\d+ observation;
\d+ taxonomy_definition;
select default_hierarchy from taxonomy_definition LIMIT 1;
select default_hierarchy from taxonomy_definition LIMIT 10;
select default_hierarchy from taxonomy_definition where default_hierarchy IS NOT NULL;
select default_hierarchy from taxonomy_definition where default_hierarchy IS NOT NULL LIMIT 1;
\d+ traits
\d+ trait
select * from trait LIMIT 1;
\x auto
select * from trait LIMIT 1;
\d+ traits
\d+ traits
\d+ trait
\d+ taxonomy_definition;
\d+ trait
\d+ trait_taxonomy_definition ;
\d+ trait_taxonomy_definition
select * from trait_taxonomy_definition LIMIT 2;
select * from trait_taxonomy_definition where trait_taxon_id=840;
select count(*) from trait_taxonomy_definition;
\d+ trait
\d+ trait
\d+ trait_value
select * from trait_value LIMIT 1;
\d+ trait
select * from fact LIMIT 1;
select * from trait_value LIMIT 1;
select * from trait LIMIT 1;
\d+ trait
\d+ trait_taxonomy_definition
\d+ trait_taxonomy_definition
\d+ species_group_mapping
\d+ species_group_mapping
select * from species_group_mapping;
\d+ trait
\d+ trait_taxonomy_definition
\d+ trait_taxonomy_definition
select trait_taxon_id from trait_taxonomy_definition where taxonomy_definition_id=874;
select trait_taxon_id from trait_taxonomy_definition where taxonomy_definition_id='874';
\d+ trait_taxonomy_definition
select trait_taxon_id from trait_taxonomy_definition where taxonomy_definition_id='872';
select trait_taxon_id from trait_taxonomy_definition where taxonomy_definition_id='2998';
\d+ trait_value;
select * from trait_value where trait_id ='11';
\d+ trait
\d+ fact
selct object_id from fact where object_id IS NOT NULL LIMIT 1;
select object_id from fact where object_id IS NOT NULL LIMIT 1;
select object_id from fact where object_id IS NOT NULL LIMIT 5;
\d+ trait_value
\d+ fact
\d+ trait_value
\x auto
select * from fact LIMIT 2;
select * from fact LIMIT 8;
select * from fact where value is not null LIMIT 8 ;
\d+ fact;
select * from fact LIMIT 1;
select * from fact where object_id=373359;
\d+ fact;
select page_taxon_id from fact LIMIT 2;
select page_taxon_id from fact LIMIT 10;
select page_taxon_id from fact where page_taxon_id IS NOT NULL;
select count(page_taxon_id) from fact where page_taxon_id IS NOT NULL;
select count(page_taxon_id) from fact where page_taxon_id IS NULL;
select count(*) from fact where page_taxon_id IS NULL;
select count(*) from fact where page_taxon_id IS NOT NULL;
\d+ trait;
\d+ fact
\d+ trait;
select distinct trait_type from trait;
select distinct trait_types from trait;
fact
;
\d+ fact
\d+ trait
\d+ trait_value
\x auto
select * from trait_value where trait_id=8;
select * from fact where count(objectId)>2;
select * from fact where count(fact.objectId)>2;
select * from fact where count(fact.objectId)>2;
select * from fact where count(object_id)>2;
\d+ fact;
SELECT SalesID, COUNT(*)
FROM AXDelNotesNoTracking
GROUP BY SalesID
;
select object_id, count(*) from fact group by object_id having count(*)>2 LIMIT 1;
select *, count(*) from fact group by object_id having count(*)>2 LIMIT 1;
select * from fact group by object_id having count(*)>2 LIMIT 1;
select *, count(*) from fact group by object_id having count(*)>2 LIMIT 1;
select object_id, count(*) from fact group by object_id having count(*)>2 LIMIT 1;
select object_id, count(*) from fact group by object_id having count(*)>2 LIMIT 2;
\d+ fact
select object_id,page_taxon_id, count(*) from fact group by object_id having count(*)>2 LIMIT 2;
select object_id, count(*) from fact group by object_id having count(*)>2 LIMIT 2;
select object_id, count(*) from fact group by object_id having count(*)>2 LIMIT 10;
select object_id, count(*) from fact group by object_id having count(*)>2 LIMIT 100;
select object_id, count(*) from fact group by object_id having count(*)=1 LIMIT 10;
select * from fact where object_id=7336;
select * from fact where object_id=175348;
select object_id, count(*) from fact group by object_id having count(*)>2 LIMIT 100;
select * from fact where object_id=7455;
select * from fact where object_id=7455;
select object_id, count(*) from fact group by object_id having count(*)>2 LIMIT 100;
\d+ fact;
select object_id, count(*) from fact group by object_id having count(*)>2 LIMIT 100;
select * from fact where object_type=;
select object_id, count(*) from fact group by object_id having count(*)>2 LIMIT 100;
select * from fact where object_id=7455;
select * from fact where species_type=species.participation.Observation;
select * from fact where object_type=species.participation.Observation;
select * from fact where objectType=species.participation.Observation;
\d+ fact
select * from fact where object_type='species.participation.Observation';
\d+ fact
select * from fact ;
select * from fact where object_id='373359';
select * from fact where object_id='373359';
users
\d+ users
\d+ suser
select * from user LIMIT 2;
select * from suser LIMIT 2;
select * from suser LIMIT 2;
select id from observation LIMIT 1;
select id from suser where name='admin';
select * from suser where name='admin';
\s auto