PolarDB-for-PostgreSQL

_int.out
698 строк · 10.4 Кб
Перенос по словам
1
CREATE EXTENSION intarray;
2
-- Check whether any of our opclasses fail amvalidate
3
SELECT amname, opcname
4
FROM pg_opclass opc LEFT JOIN pg_am am ON am.oid = opcmethod
5
WHERE opc.oid >= 16384 AND NOT amvalidate(opc.oid);
6
 amname | opcname 
7
--------+---------
8
(0 rows)
9

10
SELECT intset(1234);
11
 intset 
12
--------
13
 {1234}
14
(1 row)
15

16
SELECT icount('{1234234,234234}');
17
 icount 
18
--------
19
      2
20
(1 row)
21

22
SELECT sort('{1234234,-30,234234}');
23
         sort         
24
----------------------
25
 {-30,234234,1234234}
26
(1 row)
27

28
SELECT sort('{1234234,-30,234234}','asc');
29
         sort         
30
----------------------
31
 {-30,234234,1234234}
32
(1 row)
33

34
SELECT sort('{1234234,-30,234234}','desc');
35
         sort         
36
----------------------
37
 {1234234,234234,-30}
38
(1 row)
39

40
SELECT sort_asc('{1234234,-30,234234}');
41
       sort_asc       
42
----------------------
43
 {-30,234234,1234234}
44
(1 row)
45

46
SELECT sort_desc('{1234234,-30,234234}');
47
      sort_desc       
48
----------------------
49
 {1234234,234234,-30}
50
(1 row)
51

52
SELECT uniq('{1234234,-30,-30,234234,-30}');
53
           uniq           
54
--------------------------
55
 {1234234,-30,234234,-30}
56
(1 row)
57

58
SELECT uniq(sort_asc('{1234234,-30,-30,234234,-30}'));
59
         uniq         
60
----------------------
61
 {-30,234234,1234234}
62
(1 row)
63

64
SELECT idx('{1234234,-30,-30,234234,-30}',-30);
65
 idx 
66
-----
67
   2
68
(1 row)
69

70
SELECT subarray('{1234234,-30,-30,234234,-30}',2,3);
71
     subarray     
72
------------------
73
 {-30,-30,234234}
74
(1 row)
75

76
SELECT subarray('{1234234,-30,-30,234234,-30}',-1,1);
77
 subarray 
78
----------
79
 {-30}
80
(1 row)
81

82
SELECT subarray('{1234234,-30,-30,234234,-30}',0,-1);
83
         subarray         
84
--------------------------
85
 {1234234,-30,-30,234234}
86
(1 row)
87

88
SELECT #'{1234234,234234}'::int[];
89
 ?column? 
90
----------
91
        2
92
(1 row)
93

94
SELECT '{123,623,445}'::int[] + 1245;
95
      ?column?      
96
--------------------
97
 {123,623,445,1245}
98
(1 row)
99

100
SELECT '{123,623,445}'::int[] + 445;
101
     ?column?      
102
-------------------
103
 {123,623,445,445}
104
(1 row)
105

106
SELECT '{123,623,445}'::int[] + '{1245,87,445}';
107
         ?column?          
108
---------------------------
109
 {123,623,445,1245,87,445}
110
(1 row)
111

112
SELECT '{123,623,445}'::int[] - 623;
113
 ?column?  
114
-----------
115
 {123,445}
116
(1 row)
117

118
SELECT '{123,623,445}'::int[] - '{1623,623}';
119
 ?column?  
120
-----------
121
 {123,445}
122
(1 row)
123

124
SELECT '{123,623,445}'::int[] | 623;
125
   ?column?    
126
---------------
127
 {123,445,623}
128
(1 row)
129

130
SELECT '{123,623,445}'::int[] | 1623;
131
      ?column?      
132
--------------------
133
 {123,445,623,1623}
134
(1 row)
135

136
SELECT '{123,623,445}'::int[] | '{1623,623}';
137
      ?column?      
138
--------------------
139
 {123,445,623,1623}
140
(1 row)
141

142
SELECT '{123,623,445}'::int[] & '{1623,623}';
143
 ?column? 
144
----------
145
 {623}
146
(1 row)
147

148
SELECT '{-1,3,1}'::int[] & '{1,2}';
149
 ?column? 
150
----------
151
 {1}
152
(1 row)
153

154
SELECT '{1}'::int[] & '{2}'::int[];
155
 ?column? 
156
----------
157
 {}
158
(1 row)
159

160
SELECT array_dims('{1}'::int[] & '{2}'::int[]);
161
 array_dims 
162
------------
163
 
164
(1 row)
165

166
SELECT ('{1}'::int[] & '{2}'::int[]) = '{}'::int[];
167
 ?column? 
168
----------
169
 t
170
(1 row)
171

172
SELECT ('{}'::int[] & '{}'::int[]) = '{}'::int[];
173
 ?column? 
174
----------
175
 t
176
(1 row)
177

178
--test query_int
179
SELECT '1'::query_int;
180
 query_int 
181
-----------
182
 1
183
(1 row)
184

185
SELECT ' 1'::query_int;
186
 query_int 
187
-----------
188
 1
189
(1 row)
190

191
SELECT '1 '::query_int;
192
 query_int 
193
-----------
194
 1
195
(1 row)
196

197
SELECT ' 1 '::query_int;
198
 query_int 
199
-----------
200
 1
201
(1 row)
202

203
SELECT ' ! 1 '::query_int;
204
 query_int 
205
-----------
206
 !1
207
(1 row)
208

209
SELECT '!1'::query_int;
210
 query_int 
211
-----------
212
 !1
213
(1 row)
214

215
SELECT '1|2'::query_int;
216
 query_int 
217
-----------
218
 1 | 2
219
(1 row)
220

221
SELECT '1|!2'::query_int;
222
 query_int 
223
-----------
224
 1 | !2
225
(1 row)
226

227
SELECT '!1|2'::query_int;
228
 query_int 
229
-----------
230
 !1 | 2
231
(1 row)
232

233
SELECT '!1|!2'::query_int;
234
 query_int 
235
-----------
236
 !1 | !2
237
(1 row)
238

239
SELECT '!(!1|!2)'::query_int;
240
  query_int   
241
--------------
242
 !( !1 | !2 )
243
(1 row)
244

245
SELECT '!(!1|2)'::query_int;
246
  query_int  
247
-------------
248
 !( !1 | 2 )
249
(1 row)
250

251
SELECT '!(1|!2)'::query_int;
252
  query_int  
253
-------------
254
 !( 1 | !2 )
255
(1 row)
256

257
SELECT '!(1|2)'::query_int;
258
 query_int  
259
------------
260
 !( 1 | 2 )
261
(1 row)
262

263
SELECT '1&2'::query_int;
264
 query_int 
265
-----------
266
 1 & 2
267
(1 row)
268

269
SELECT '!1&2'::query_int;
270
 query_int 
271
-----------
272
 !1 & 2
273
(1 row)
274

275
SELECT '1&!2'::query_int;
276
 query_int 
277
-----------
278
 1 & !2
279
(1 row)
280

281
SELECT '!1&!2'::query_int;
282
 query_int 
283
-----------
284
 !1 & !2
285
(1 row)
286

287
SELECT '(1&2)'::query_int;
288
 query_int 
289
-----------
290
 1 & 2
291
(1 row)
292

293
SELECT '1&(2)'::query_int;
294
 query_int 
295
-----------
296
 1 & 2
297
(1 row)
298

299
SELECT '!(1)&2'::query_int;
300
 query_int 
301
-----------
302
 !1 & 2
303
(1 row)
304

305
SELECT '!(1&2)'::query_int;
306
 query_int  
307
------------
308
 !( 1 & 2 )
309
(1 row)
310

311
SELECT '1|2&3'::query_int;
312
 query_int 
313
-----------
314
 1 | 2 & 3
315
(1 row)
316

317
SELECT '1|(2&3)'::query_int;
318
 query_int 
319
-----------
320
 1 | 2 & 3
321
(1 row)
322

323
SELECT '(1|2)&3'::query_int;
324
   query_int   
325
---------------
326
 ( 1 | 2 ) & 3
327
(1 row)
328

329
SELECT '1|2&!3'::query_int;
330
 query_int  
331
------------
332
 1 | 2 & !3
333
(1 row)
334

335
SELECT '1|!2&3'::query_int;
336
 query_int  
337
------------
338
 1 | !2 & 3
339
(1 row)
340

341
SELECT '!1|2&3'::query_int;
342
 query_int  
343
------------
344
 !1 | 2 & 3
345
(1 row)
346

347
SELECT '!1|(2&3)'::query_int;
348
 query_int  
349
------------
350
 !1 | 2 & 3
351
(1 row)
352

353
SELECT '!(1|2)&3'::query_int;
354
   query_int    
355
----------------
356
 !( 1 | 2 ) & 3
357
(1 row)
358

359
SELECT '(!1|2)&3'::query_int;
360
   query_int    
361
----------------
362
 ( !1 | 2 ) & 3
363
(1 row)
364

365
SELECT '1|(2|(4|(5|6)))'::query_int;
366
           query_int           
367
-------------------------------
368
 1 | ( 2 | ( 4 | ( 5 | 6 ) ) )
369
(1 row)
370

371
SELECT '1|2|4|5|6'::query_int;
372
           query_int           
373
-------------------------------
374
 ( ( ( 1 | 2 ) | 4 ) | 5 ) | 6
375
(1 row)
376

377
SELECT '1&(2&(4&(5&6)))'::query_int;
378
     query_int     
379
-------------------
380
 1 & 2 & 4 & 5 & 6
381
(1 row)
382

383
SELECT '1&2&4&5&6'::query_int;
384
     query_int     
385
-------------------
386
 1 & 2 & 4 & 5 & 6
387
(1 row)
388

389
SELECT '1&(2&(4&(5|6)))'::query_int;
390
       query_int       
391
-----------------------
392
 1 & 2 & 4 & ( 5 | 6 )
393
(1 row)
394

395
SELECT '1&(2&(4&(5|!6)))'::query_int;
396
       query_int        
397
------------------------
398
 1 & 2 & 4 & ( 5 | !6 )
399
(1 row)
400

401
CREATE TABLE test__int( a int[] );
402
\copy test__int from 'data/test__int.data'
403
ANALYZE test__int;
404
SELECT count(*) from test__int WHERE a && '{23,50}';
405
 count 
406
-------
407
   403
408
(1 row)
409

410
SELECT count(*) from test__int WHERE a @@ '23|50';
411
 count 
412
-------
413
   403
414
(1 row)
415

416
SELECT count(*) from test__int WHERE a @> '{23,50}';
417
 count 
418
-------
419
    12
420
(1 row)
421

422
SELECT count(*) from test__int WHERE a @@ '23&50';
423
 count 
424
-------
425
    12
426
(1 row)
427

428
SELECT count(*) from test__int WHERE a @> '{20,23}';
429
 count 
430
-------
431
    12
432
(1 row)
433

434
SELECT count(*) from test__int WHERE a <@ '{73,23,20}';
435
 count 
436
-------
437
    10
438
(1 row)
439

440
SELECT count(*) from test__int WHERE a = '{73,23,20}';
441
 count 
442
-------
443
     1
444
(1 row)
445

446
SELECT count(*) from test__int WHERE a @@ '50&68';
447
 count 
448
-------
449
     9
450
(1 row)
451

452
SELECT count(*) from test__int WHERE a @> '{20,23}' or a @> '{50,68}';
453
 count 
454
-------
455
    21
456
(1 row)
457

458
SELECT count(*) from test__int WHERE a @@ '(20&23)|(50&68)';
459
 count 
460
-------
461
    21
462
(1 row)
463

464
SELECT count(*) from test__int WHERE a @@ '20 | !21';
465
 count 
466
-------
467
  6566
468
(1 row)
469

470
SELECT count(*) from test__int WHERE a @@ '!20 & !21';
471
 count 
472
-------
473
  6343
474
(1 row)
475

476
SET enable_seqscan = off;  -- not all of these would use index by default
477
CREATE INDEX text_idx on test__int using gist ( a gist__int_ops );
478
SELECT count(*) from test__int WHERE a && '{23,50}';
479
 count 
480
-------
481
   403
482
(1 row)
483

484
SELECT count(*) from test__int WHERE a @@ '23|50';
485
 count 
486
-------
487
   403
488
(1 row)
489

490
SELECT count(*) from test__int WHERE a @> '{23,50}';
491
 count 
492
-------
493
    12
494
(1 row)
495

496
SELECT count(*) from test__int WHERE a @@ '23&50';
497
 count 
498
-------
499
    12
500
(1 row)
501

502
SELECT count(*) from test__int WHERE a @> '{20,23}';
503
 count 
504
-------
505
    12
506
(1 row)
507

508
SELECT count(*) from test__int WHERE a <@ '{73,23,20}';
509
 count 
510
-------
511
    10
512
(1 row)
513

514
SELECT count(*) from test__int WHERE a = '{73,23,20}';
515
 count 
516
-------
517
     1
518
(1 row)
519

520
SELECT count(*) from test__int WHERE a @@ '50&68';
521
 count 
522
-------
523
     9
524
(1 row)
525

526
SELECT count(*) from test__int WHERE a @> '{20,23}' or a @> '{50,68}';
527
 count 
528
-------
529
    21
530
(1 row)
531

532
SELECT count(*) from test__int WHERE a @@ '(20&23)|(50&68)';
533
 count 
534
-------
535
    21
536
(1 row)
537

538
SELECT count(*) from test__int WHERE a @@ '20 | !21';
539
 count 
540
-------
541
  6566
542
(1 row)
543

544
SELECT count(*) from test__int WHERE a @@ '!20 & !21';
545
 count 
546
-------
547
  6343
548
(1 row)
549

550
DROP INDEX text_idx;
551
CREATE INDEX text_idx on test__int using gist ( a gist__intbig_ops );
552
SELECT count(*) from test__int WHERE a && '{23,50}';
553
 count 
554
-------
555
   403
556
(1 row)
557

558
SELECT count(*) from test__int WHERE a @@ '23|50';
559
 count 
560
-------
561
   403
562
(1 row)
563

564
SELECT count(*) from test__int WHERE a @> '{23,50}';
565
 count 
566
-------
567
    12
568
(1 row)
569

570
SELECT count(*) from test__int WHERE a @@ '23&50';
571
 count 
572
-------
573
    12
574
(1 row)
575

576
SELECT count(*) from test__int WHERE a @> '{20,23}';
577
 count 
578
-------
579
    12
580
(1 row)
581

582
SELECT count(*) from test__int WHERE a <@ '{73,23,20}';
583
 count 
584
-------
585
    10
586
(1 row)
587

588
SELECT count(*) from test__int WHERE a = '{73,23,20}';
589
 count 
590
-------
591
     1
592
(1 row)
593

594
SELECT count(*) from test__int WHERE a @@ '50&68';
595
 count 
596
-------
597
     9
598
(1 row)
599

600
SELECT count(*) from test__int WHERE a @> '{20,23}' or a @> '{50,68}';
601
 count 
602
-------
603
    21
604
(1 row)
605

606
SELECT count(*) from test__int WHERE a @@ '(20&23)|(50&68)';
607
 count 
608
-------
609
    21
610
(1 row)
611

612
SELECT count(*) from test__int WHERE a @@ '20 | !21';
613
 count 
614
-------
615
  6566
616
(1 row)
617

618
SELECT count(*) from test__int WHERE a @@ '!20 & !21';
619
 count 
620
-------
621
  6343
622
(1 row)
623

624
DROP INDEX text_idx;
625
CREATE INDEX text_idx on test__int using gin ( a gin__int_ops );
626
SELECT count(*) from test__int WHERE a && '{23,50}';
627
 count 
628
-------
629
   403
630
(1 row)
631

632
SELECT count(*) from test__int WHERE a @@ '23|50';
633
 count 
634
-------
635
   403
636
(1 row)
637

638
SELECT count(*) from test__int WHERE a @> '{23,50}';
639
 count 
640
-------
641
    12
642
(1 row)
643

644
SELECT count(*) from test__int WHERE a @@ '23&50';
645
 count 
646
-------
647
    12
648
(1 row)
649

650
SELECT count(*) from test__int WHERE a @> '{20,23}';
651
 count 
652
-------
653
    12
654
(1 row)
655

656
SELECT count(*) from test__int WHERE a <@ '{73,23,20}';
657
 count 
658
-------
659
    10
660
(1 row)
661

662
SELECT count(*) from test__int WHERE a = '{73,23,20}';
663
 count 
664
-------
665
     1
666
(1 row)
667

668
SELECT count(*) from test__int WHERE a @@ '50&68';
669
 count 
670
-------
671
     9
672
(1 row)
673

674
SELECT count(*) from test__int WHERE a @> '{20,23}' or a @> '{50,68}';
675
 count 
676
-------
677
    21
678
(1 row)
679

680
SELECT count(*) from test__int WHERE a @@ '(20&23)|(50&68)';
681
 count 
682
-------
683
    21
684
(1 row)
685

686
SELECT count(*) from test__int WHERE a @@ '20 | !21';
687
 count 
688
-------
689
  6566
690
(1 row)
691

692
SELECT count(*) from test__int WHERE a @@ '!20 & !21';
693
 count 
694
-------
695
  6343
696
(1 row)
697

698
RESET enable_seqscan;
699
PolarDB-for-PostgreSQL

Использование cookies
