forked from vmware-archive/docs-hd-staging
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathSQLCommandReference.html
1927 lines (1846 loc) · 413 KB
/
SQLCommandReference.html
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
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
<!doctype html>
<html>
<head>
<meta charset="utf-8">
<!-- Always force latest IE rendering engine or request Chrome Frame -->
<meta content="IE=edge,chrome=1" http-equiv="X-UA-Compatible">
<!-- REPLACE X WITH PRODUCT NAME -->
<title>SQL Command Reference | Pivotal Docs</title>
<!-- Local CSS stylesheets -->
<link href="/stylesheets/master.css" media="screen,print" rel="stylesheet" type="text/css" />
<link href="/stylesheets/breadcrumbs.css" media="screen,print" rel="stylesheet" type="text/css" />
<link href="/stylesheets/search.css" media="screen,print" rel="stylesheet" type="text/css" />
<link href="/stylesheets/portal-style.css" media="screen,print" rel="stylesheet" type="text/css" />
<link href="/stylesheets/printable.css" media="print" rel="stylesheet" type="text/css" />
<!-- Confluence HTML stylesheet -->
<link href="/stylesheets/site-conf.css" media="screen,print" rel="stylesheet" type="text/css" />
<!-- Left-navigation code -->
<!-- http://www.designchemical.com/lab/jquery-vertical-accordion-menu-plugin/examples/# -->
<link href="/stylesheets/dcaccordion.css" rel="stylesheet" type="text/css" />
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js" type="text/javascript"></script>
<script src="/javascripts/jquery.cookie.js" type="text/javascript"></script>
<script src="/javascripts/jquery.hoverIntent.minified.js" type="text/javascript"></script>
<script src="/javascripts/jquery.dcjqaccordion.2.7.min.js" type="text/javascript"></script>
<script type="text/javascript">
$(document).ready(function($){
$('#accordion-1').dcAccordion({
eventType: 'click',
autoClose: true,
saveState: true,
disableLink: false,
speed: 'fast',
classActive: 'test',
showCount: false
});
});
</script>
<link href="/stylesheets/grey.css" rel="stylesheet" type="text/css" />
<!-- End left-navigation code -->
<script src="/javascripts/all.js" type="text/javascript"></script>
<link href='http://www.gopivotal.com/misc/favicon.ico' rel='shortcut icon'>
<script type="text/javascript">
if (window.location.host === 'docs.gopivotal.com') {
var _gaq = _gaq || [];
_gaq.push(['_setAccount', 'UA-39702075-1']);
_gaq.push(['_setDomainName', 'gopivotal.com']);
_gaq.push(['_trackPageview']);
(function() {
var ga = document.createElement('script'); ga.type = 'text/javascript'; ga.async = true;
ga.src = ('https:' == document.location.protocol ? 'https://ssl' : 'http://www') + '.google-analytics.com/ga.js';
var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(ga, s);
})();
}
</script>
</head>
<body class="pivotalcf pivotalcf_getstarted pivotalcf_getstarted_index">
<div class="viewport">
<div class="mobile-navigation--wrapper mobile-only">
<div class="navigation-drawer--container">
<div class="navigation-item-list">
<div class="navbar-link active">
<a href="http://gopivotal.com">
Home
<i class="icon-chevron-right pull-right"></i>
</a>
</div>
<div class="navbar-link">
<a href="http://gopivotal.com/paas">
PaaS
<i class="icon-chevron-right pull-right"></i>
</a>
</div>
<div class="navbar-link">
<a href="http://gopivotal.com/big-data">
Big Data
<i class="icon-chevron-right pull-right"></i>
</a>
</div>
<div class="navbar-link">
<a href="http://gopivotal.com/agile">
Agile
<i class="icon-chevron-right pull-right"></i>
</a>
</div>
<div class="navbar-link">
<a href="http://gopivotal.com/support">
Help & Support
<i class="icon-chevron-right pull-right"></i>
</a>
</div>
<div class="navbar-link">
<a href="http://gopivotal.com/products">
Products
<i class="icon-chevron-right pull-right"></i>
</a>
</div>
<div class="navbar-link">
<a href="http://gopivotal.com/solutions">
Solutions
<i class="icon-chevron-right pull-right"></i>
</a>
</div>
<div class="navbar-link">
<a href="http://gopivotal.com/partners">
Partners
<i class="icon-chevron-right pull-right"></i>
</a>
</div>
</div>
</div>
<div class="mobile-nav">
<div class="nav-icon js-open-nav-drawer">
<i class="icon-reorder"></i>
</div>
<div class="header-center-icon">
<a href="http://gopivotal.com">
<div class="icon icon-pivotal-logo-mobile"></div>
</a>
</div>
</div>
</div>
<div class='wrap'>
<script src="//use.typekit.net/clb0qji.js" type="text/javascript"></script>
<script type="text/javascript">
try {
Typekit.load();
} catch (e) {
}
</script>
<script type="text/javascript">
document.domain = "gopivotal.com";
</script>
<script type="text/javascript">
WebFontConfig = {
google: { families: [ 'Source+Sans+Pro:300italic,400italic,600italic,300,400,600:latin' ] }
};
(function() {
var wf = document.createElement('script');
wf.src = ('https:' == document.location.protocol ? 'https' : 'http') +
'://ajax.googleapis.com/ajax/libs/webfont/1/webfont.js';
wf.type = 'text/javascript';
wf.async = 'true';
var s = document.getElementsByTagName('script')[0];
s.parentNode.insertBefore(wf, s);
})(); </script>
<div id="search-dropdown-box">
<div class="search-dropdown--container js-search-dropdown">
<div class="container-fluid">
<div class="close-menu-large"><img src="http://www.gopivotal.com/sites/all/themes/gopo13/images/icon-close.png" /></div>
<div class="search-form--container">
<div class="form-search">
<div class='gcse-search'></div>
<script src="http://www.google.com/jsapi" type="text/javascript"></script>
<script src="/javascripts/cse.js" type="text/javascript"></script>
</div>
</div>
</div>
</div>
</div>
<header class="navbar desktop-only" id="nav">
<div class="navbar-inner">
<div class="container-fluid">
<div class="pivotal-logo--container">
<a class="pivotal-logo" href="http://gopivotal.com"><span></span></a>
</div>
<ul class="nav pull-right">
<li class="navbar-link">
<a href="http://www.gopivotal.com/paas" id="paas-nav-link">PaaS</a>
</li>
<li class="navbar-link">
<a href="http://www.gopivotal.com/big-data" id="big-data-nav-link">BIG DATA</a>
</li>
<li class="navbar-link">
<a href="http://www.gopivotal.com/agile" id="agile-nav-link">AGILE</a>
</li>
<li class="navbar-link">
<a href="http://www.gopivotal.com/oss" id="oss-nav-link">OSS</a>
</li>
<li class="nav-search">
<a class="js-search-input-open" id="click-to-search"><span></span></a>
</li>
</ul>
</div>
<a href="http://www.gopivotal.com/contact">
<img id="get-started" src="http://www.gopivotal.com/sites/all/themes/gopo13/images/get-started.png">
</a>
</div>
</header>
<div class="main-wrap">
<div class="container-fluid">
<!-- Google CSE Search Box -->
<div id='docs-search'>
<gcse:search></gcse:search>
</div>
<div id='all-docs-link'>
<a href="http://docs.gopivotal.com/">All Documentation</a>
</div>
<div class="container">
<div id="sub-nav" class="nav-container">
<!-- Collapsible left-navigation-->
<ul class="accordion" id="accordion-1">
<!-- REPLACE <li/> NODES-->
<li>
<a href="index.html">Home</a></br>
<li>
<a href="PivotalHD.html">Pivotal HD 2.0.1</a>
<ul>
<li>
<a href="PHDEnterprise2.0.1ReleaseNotes.html">PHD Enterprise 2.0.1 Release Notes</a>
</li>
</ul>
<ul>
<li>
<a href="PHDInstallationandAdministration.html">PHD Installation and Administration</a>
<ul>
<li>
<a href="OverviewofPHD.html">Overview of PHD</a>
</li>
</ul>
<ul>
<li>
<a href="InstallationOverview.html">Installation Overview</a>
</li>
</ul>
<ul>
<li>
<a href="PHDInstallationChecklist.html">PHD Installation Checklist</a>
</li>
</ul>
<ul>
<li>
<a href="InstallingPHDUsingtheCLI.html">Installing PHD Using the CLI</a>
</li>
</ul>
<ul>
<li>
<a href="UpgradeChecklist.html">Upgrade Checklist</a>
</li>
</ul>
<ul>
<li>
<a href="UpgradingPHDUsingtheCLI.html">Upgrading PHD Using the CLI</a>
</li>
</ul>
<ul>
<li>
<a href="AdministeringPHDUsingtheCLI.html">Administering PHD Using the CLI</a>
</li>
</ul>
<ul>
<li>
<a href="PHDFAQFrequentlyAskedQuestions.html">PHD FAQ (Frequently Asked Questions)</a>
</li>
</ul>
<ul>
<li>
<a href="PHDTroubleshooting.html">PHD Troubleshooting</a>
</li>
</ul>
</li>
</ul>
<ul>
<li>
<a href="StackandToolsReference.html">Stack and Tools Reference</a>
<ul>
<li>
<a href="OverviewofApacheStackandPivotalComponents.html">Overview of Apache Stack and Pivotal Components</a>
</li>
</ul>
<ul>
<li>
<a href="ManuallyInstallingPivotalHD2.0Stack.html">Manually Installing Pivotal HD 2.0 Stack</a>
</li>
</ul>
<ul>
<li>
<a href="ManuallyUpgradingPivotalHDStackfrom1.1.1to2.0.html">Manually Upgrading Pivotal HD Stack from 1.1.1 to 2.0</a>
</li>
</ul>
<ul>
<li>
<a href="PivotalHadoopEnhancements.html">Pivotal Hadoop Enhancements</a>
</li>
</ul>
<ul>
<li>
<a href="Security.html">Security</a>
</li>
</ul>
</li>
</ul>
</li>
<li>
<a href="PivotalCommandCenter.html">Pivotal Command Center 2.2.1</a>
<ul>
<li>
<a href="PCC2.2.1ReleaseNotes.html">PCC 2.2.1 Release Notes</a>
</li>
</ul>
<ul>
<li>
<a href="PCCUserGuide.html">PCC User Guide</a>
<ul>
<li>
<a href="PCCOverview.html">PCC Overview</a>
</li>
</ul>
<ul>
<li>
<a href="PCCInstallationChecklist.html">PCC Installation Checklist</a>
</li>
</ul>
<ul>
<li>
<a href="InstallingPCC.html">Installing PCC</a>
</li>
</ul>
<ul>
<li>
<a href="UsingPCC.html">Using PCC</a>
</li>
</ul>
<ul>
<li>
<a href="CreatingaYUMEPELRepository.html">Creating a YUM EPEL Repository</a>
</li>
</ul>
<ul>
<li>
<a href="CommandLineReference.html">Command Line Reference</a>
</li>
</ul>
</li>
</ul>
</li>
<li>
<a href="PivotalHAWQ.html">Pivotal HAWQ 1.2.0</a>
<ul>
<li>
<a href="HAWQ1.2.0.1ReleaseNotes.html">HAWQ 1.2.0.1 Release Notes</a>
</li>
</ul>
<ul>
<li>
<a href="HAWQInstallationandUpgrade.html">HAWQ Installation and Upgrade</a>
<ul>
<li>
<a href="PreparingtoInstallHAWQ.html">Preparing to Install HAWQ</a>
</li>
</ul>
<ul>
<li>
<a href="InstallingHAWQ.html">Installing HAWQ</a>
</li>
</ul>
<ul>
<li>
<a href="InstallingtheHAWQComponents.html">Installing the HAWQ Components</a>
</li>
</ul>
<ul>
<li>
<a href="UpgradingHAWQandComponents.html">Upgrading HAWQ and Components</a>
</li>
</ul>
<ul>
<li>
<a href="HAWQConfigurationParameterReference.html">HAWQ Configuration Parameter Reference</a>
</li>
</ul>
</li>
</ul>
<ul>
<li>
<a href="HAWQAdministration.html">HAWQ Administration</a>
<ul>
<li>
<a href="HAWQOverview.html">HAWQ Overview</a>
</li>
</ul>
<ul>
<li>
<a href="HAWQQueryProcessing.html">HAWQ Query Processing</a>
</li>
</ul>
<ul>
<li>
<a href="UsingHAWQtoQueryData.html">Using HAWQ to Query Data</a>
</li>
</ul>
<ul>
<li>
<a href="ConfiguringClientAuthentication.html">Configuring Client Authentication</a>
</li>
</ul>
<ul>
<li>
<a href="KerberosAuthentication.html">Kerberos Authentication</a>
</li>
</ul>
<ul>
<li>
<a href="ExpandingtheHAWQSystem.html">Expanding the HAWQ System</a>
</li>
</ul>
<ul>
<li>
<a href="HAWQInputFormatforMapReduce.html">HAWQ InputFormat for MapReduce</a>
</li>
</ul>
<ul>
<li>
<a href="HAWQFilespacesandHighAvailabilityEnabledHDFS.html">HAWQ Filespaces and High Availability Enabled HDFS</a>
</li>
</ul>
<ul>
<li>
<a href="SQLCommandReference.html">SQL Command Reference</a>
</li>
</ul>
<ul>
<li>
<a href="ManagementUtilityReference.html">Management Utility Reference</a>
</li>
</ul>
<ul>
<li>
<a href="ClientUtilityReference.html">Client Utility Reference</a>
</li>
</ul>
<ul>
<li>
<a href="HAWQServerConfigurationParameters.html">HAWQ Server Configuration Parameters</a>
</li>
</ul>
<ul>
<li>
<a href="HAWQEnvironmentVariables.html">HAWQ Environment Variables</a>
</li>
</ul>
<ul>
<li>
<a href="HAWQDataTypes.html">HAWQ Data Types</a>
</li>
</ul>
<ul>
<li>
<a href="SystemCatalogReference.html">System Catalog Reference</a>
</li>
</ul>
<ul>
<li>
<a href="hawq_toolkitReference.html">hawq_toolkit Reference</a>
</li>
</ul>
</li>
</ul>
<ul>
<li>
<a href="PivotalExtensionFrameworkPXF.html">Pivotal Extension Framework (PXF)</a>
<ul>
<li>
<a href="PXFInstallationandAdministration.html">PXF Installation and Administration</a>
</li>
</ul>
<ul>
<li>
<a href="PXFExternalTableandAPIReference.html">PXF External Table and API Reference</a>
</li>
</ul>
</div><!--end of sub-nav-->
<h3 class="title-container">SQL Command Reference</h3>
<div class="content">
<!-- Python script replaces main content -->
<div id ="main"><div style="visibility:hidden; height:2px;">Pivotal Product Documentation : SQL Command Reference</div><div class="wiki-content group" id="main-content">
<p>This topic contains a description and the syntax of the following SQL commands supported by HAWQ.</p><p><style type="text/css">/*<![CDATA[*/
div.rbtoc1400035794313 {padding: 0px;}
div.rbtoc1400035794313 ul {list-style: disc;margin-left: 0px;}
div.rbtoc1400035794313 li {margin-left: 0px;padding-left: 0px;}
/*]]>*/</style><div class="toc-macro rbtoc1400035794313">
<ul class="toc-indentation">
<li><a href="#SQLCommandReference-ABORT">ABORT</a></li>
<li><a href="#SQLCommandReference-ALTERROLE">ALTER ROLE</a></li>
<li><a href="#SQLCommandReference-ALTERTABLE">ALTER TABLE</a></li>
<li><a href="#SQLCommandReference-ALTERUSER">ALTER USER</a></li>
<li><a href="#SQLCommandReference-ANALYZE">ANALYZE </a></li>
<li><a href="#SQLCommandReference-BEGIN">BEGIN</a></li>
<li><a href="#SQLCommandReference-CHECKPOINT">CHECKPOINT</a></li>
<li><a href="#SQLCommandReference-CLOSE">CLOSE</a></li>
<li><a href="#SQLCommandReference-COMMIT">COMMIT</a></li>
<li><a href="#SQLCommandReference-COPY">COPY</a></li>
<li><a href="#SQLCommandReference-CREATEAGGREGATE">CREATE AGGREGATE</a></li>
<li><a href="#SQLCommandReference-CREATEDATABASE">CREATE DATABASE</a></li>
<li><a href="#SQLCommandReference-CREATEEXTERNALTABLE">CREATE EXTERNAL TABLE</a></li>
<li><a href="#SQLCommandReference-CREATEFUNCTION">CREATE FUNCTION</a></li>
<li><a href="#SQLCommandReference-CREATEGROUP">CREATE GROUP</a></li>
<li><a href="#SQLCommandReference-CREATELANGUAGE">CREATE LANGUAGE</a></li>
<li><a href="#SQLCommandReference-CREATERESOURCEQUEUE">CREATE RESOURCE QUEUE</a></li>
<li><a href="#SQLCommandReference-CREATEROLE">CREATE ROLE</a></li>
<li><a href="#SQLCommandReference-CREATESCHEMA">CREATE SCHEMA</a></li>
<li><a href="#SQLCommandReference-CREATESEQUENCE">CREATE SEQUENCE</a></li>
<li><a href="#SQLCommandReference-CREATETABLE">CREATE TABLE</a></li>
<li><a href="#SQLCommandReference-CREATETABLEAS">CREATE TABLE AS</a></li>
<li><a href="#SQLCommandReference-CREATETYPE">CREATE TYPE </a></li>
<li><a href="#SQLCommandReference-CREATEUSER">CREATE USER</a></li>
<li><a href="#SQLCommandReference-CREATEVIEW">CREATE VIEW</a></li>
<li><a href="#SQLCommandReference-DEALLOCATE">DEALLOCATE</a></li>
<li><a href="#SQLCommandReference-DECLARE">DECLARE</a></li>
<li><a href="#SQLCommandReference-DROPDATABASE">DROP DATABASE</a></li>
<li><a href="#SQLCommandReference-DROPEXTERNALTABLE">DROP EXTERNAL TABLE</a></li>
<li><a href="#SQLCommandReference-DROPFILESPACE">DROP FILESPACE</a></li>
<li><a href="#SQLCommandReference-DROPGROUP">DROP GROUP</a></li>
<li><a href="#SQLCommandReference-DROPOWNED">DROP OWNED</a></li>
<li><a href="#SQLCommandReference-DROPRESOURCEQUEUE">DROP RESOURCE QUEUE</a></li>
<li><a href="#SQLCommandReference-DROPROLE">DROP ROLE</a></li>
<li><a href="#SQLCommandReference-DROPSCHEMA">DROP SCHEMA</a></li>
<li><a href="#SQLCommandReference-DROPSEQUENCE">DROP SEQUENCE</a></li>
<li><a href="#SQLCommandReference-DROPTABLE">DROP TABLE</a></li>
<li><a href="#SQLCommandReference-DROPTABLESPACE">DROP TABLESPACE</a></li>
<li><a href="#SQLCommandReference-DROPUSER">DROP USER</a></li>
<li><a href="#SQLCommandReference-DROPVIEW">DROP VIEW</a></li>
<li><a href="#SQLCommandReference-END">END</a></li>
<li><a href="#SQLCommandReference-EXECUTE">EXECUTE</a></li>
<li><a href="#SQLCommandReference-EXPLAIN">EXPLAIN</a></li>
<li><a href="#SQLCommandReference-FETCH">FETCH</a></li>
<li><a href="#SQLCommandReference-GRANT">GRANT</a></li>
<li><a href="#SQLCommandReference-INSERT">INSERT</a></li>
<li><a href="#SQLCommandReference-PREPARE">PREPARE</a></li>
<li><a href="#SQLCommandReference-REASSIGNOWNED">REASSIGN OWNED</a></li>
<li><a href="#SQLCommandReference-RELEASESAVEPOINT">RELEASE SAVEPOINT</a></li>
<li><a href="#SQLCommandReference-RESET">RESET</a></li>
<li><a href="#SQLCommandReference-REVOKE">REVOKE</a></li>
<li><a href="#SQLCommandReference-ROLLBACK">ROLLBACK</a></li>
<li><a href="#SQLCommandReference-ROLLBACKTOSAVEPOINT">ROLLBACK TO SAVEPOINT</a></li>
<li><a href="#SQLCommandReference-SAVEPOINT">SAVEPOINT</a></li>
<li><a href="#SQLCommandReference-SELECT">SELECT</a></li>
<li><a href="#SQLCommandReference-SELECTINTO">SELECT INTO</a></li>
<li><a href="#SQLCommandReference-SET">SET</a></li>
<li><a href="#SQLCommandReference-SETROLE">SET ROLE</a></li>
<li><a href="#SQLCommandReference-SETSESSIONAUTHORIZATION">SET SESSION AUTHORIZATION</a></li>
<li><a href="#SQLCommandReference-SHOW">SHOW</a></li>
<li><a href="#SQLCommandReference-TRUNCATE">TRUNCATE</a></li>
<li><a href="#SQLCommandReference-VACUUM">VACUUM</a></li>
</ul>
</div></p><h2 id="SQLCommandReference-ABORT">ABORT</h2><p align="LEFT">Aborts the current transaction.</p><h3 id="SQLCommandReference-Synopsis">Synopsis</h3><div class="code panel pdl" style="border-width: 1px;"><div class="codeContent panelContent pdl">
<pre class="theme: Confluence; brush: sql; gutter: false" style="font-size:12px;">ABORT [WORK | TRANSACTION]</pre>
</div></div><h3 id="SQLCommandReference-Description">Description</h3><p>ABORT rolls back the current transaction and causes all the updates made by the transaction to be discarded. This command is identical in behavior to the standard SQL command ROLLBACK, and is present only for historical reasons.</p><h3 id="SQLCommandReference-Parameters">Parameters</h3><pre>WORK<br/>TRANSACTION</pre><p>Optional key words that have no effect.</p><h3 id="SQLCommandReference-Notes">Notes</h3><p>Use COMMIT to successfully terminate a transaction.</p><p align="LEFT">Issuing ABORT when not inside a transaction does no harm, but provokes a warning message.</p><h3 id="SQLCommandReference-Compatibility">Compatibility</h3><p>This command is a HAWQ extension present for historical reasons. ROLLBACK is the equivalent standard SQL command.</p><h3 id="SQLCommandReference-SeeAlso">See Also</h3><p align="LEFT">BEGIN, COMMIT, ROLLBACK<span style="font-size: medium;"> </span></p><h2 id="SQLCommandReference-ALTERROLE">ALTER ROLE</h2><p>Changes a database role (user or group).</p><h3 id="SQLCommandReference-Synopsis.1">Synopsis</h3><div class="code panel pdl" style="border-width: 1px;"><div class="codeContent panelContent pdl">
<pre class="theme: Confluence; brush: sql; gutter: false" style="font-size:12px;">ALTER ROLE name RENAME TO newname
ALTER ROLE name SET config_parameter {TO | =} {value | DEFAULT}
ALTER ROLE name RESET config_parameter
ALTER ROLE name RESOURCE QUEUE {queue_name | NONE}
ALTER ROLE name [ [WITH] option [ ... ] ]
where option can be:
SUPERUSER | NOSUPERUSER
| CREATEDB | NOCREATEDB
| CREATEROLE | NOCREATEROLE
| CREATEEXTTABLE | NOCREATEEXTTABLE
[ ( attribute='value'[, ...] ) ]
where attributes and values are:
type='readable'|'writable'
protocol='gpfdist'|'http'
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
| CONNECTION LIMIT connlimit
| [ENCRYPTED | UNENCRYPTED] PASSWORD 'password'
| VALID UNTIL 'timestamp'
| [ DENY deny_point ]
| [ DENY BETWEEN deny_point AND deny_point]
| [ DROP DENY FOR deny_point ]</pre>
</div></div><h3 id="SQLCommandReference-Description.1">Description</h3><p align="LEFT">ALTER ROLE changes the attributes of a HAWQ role. There are several variants of this command:</p><ul><li>RENAME — Changes the name of the role. Database superusers can rename any role. Roles having CREATEROLE privilege can rename non-superuser roles. The current session user cannot be renamed (connect as a different user to rename a role). Because MD5-encrypted passwords use the role name as cryptographic salt, renaming a role clears its password if the password is MD5-encrypted.</li><li>SET | RESET — changes a role’s session default for a specified configuration parameter. Whenever the role subsequently starts a new session, the specified value becomes the session default, overriding whatever setting is present in server configuration file (postgresql.conf). For a role without LOGIN privilege, session defaults have no effect. Ordinary roles can change their own session defaults. Superusers can change anyone’s session defaults. Roles having CREATEROLE privilege can change defaults for non-superuser roles. See "Server Configuration Parameters" for more information on all user-settable configuration parameters.</li><li>RESOURCE QUEUE — Assigns the role to a workload management resource queue. The role would then be subject to the limits assigned to the resource queue when issuing queries. Specify NONE to assign the role to the default resource queue. A role can only belong to one resource queue. For a role without LOGIN privilege, resource queues have no effect. See CREATE RESOURCE QUEUE for more information.</li><li>WITH option — Changes many of the role attributes that can be specified in CREATE ROLE. Attributes not mentioned in the command retain their previous settings. Database superusers can change any of these settings for any role. Roles having CREATEROLE privilege can change any of these settings, but only for non-superuser roles. Ordinary roles can only change their own password.</li></ul><h3 id="SQLCommandReference-Parameters.1">Parameters</h3><pre>name</pre><p align="LEFT" style="margin-left: 30.0px;">The name of the role whose attributes are to be altered.</p><pre>newname</pre><p align="LEFT" style="margin-left: 30.0px;">The new name of the role.</p><pre>config_parameter=value</pre><p align="LEFT" style="margin-left: 30.0px;">Set this role’s session default for the specified configuration parameter to the given value. If value is DEFAULT or if RESET is used, the role-specific variable setting is removed, so the role will inherit the system-wide default setting in new sessions. Use RESET ALL to clear all role-specific settings. See SET and "Server Configuration Parameters" for more information about configuration parameters.</p><pre>queue_name</pre><p align="LEFT" style="margin-left: 30.0px;">The name of the resource queue to which the user-level role is to be assigned. Only roles with LOGIN privilege can be assigned to a resource queue. To unassign a role from a resource queue and put it in the default resource queue, specify NONE. A role can only belong to one resource queue.</p><pre>SUPERUSER | NOSUPERUSER<br/>CREATEDB | NOCREATEDB<br/>CREATEROLE | NOCREATEROLE<br/>CREATEEXTTABLE | NOCREATEEXTTABLE [(attribute='value')]</pre><p align="LEFT" style="margin-left: 30.0px;">If CREATEEXTTABLE is specified, the role being defined is allowed to create external tables. The default type is readable and the default protocol is gpfdist if not specified. NOCREATEEXTTABLE (the default) denies the role the ability to create external tables. Note that external tables that use the file or execute protocols can only be created by superusers.</p><pre>INHERIT | NOINHERIT<br/>LOGIN | NOLOGIN<br/>CONNECTION LIMIT connlimit<br/>PASSWORD password<br/>ENCRYPTED | UNENCRYPTED<br/>VALID UNTIL 'timestamp'</pre><p align="LEFT" style="margin-left: 30.0px;">These clauses alter role attributes originally set by CREATE ROLE.</p><pre>DENY deny_point<br/>DENY BETWEEN deny_point AND deny_point</pre><p align="LEFT" style="margin-left: 30.0px;">The DENY and DENY BETWEEN keywords set time-based constraints that are enforced at login. DENY sets a day or a day and time to deny access. DENY BETWEEN sets an interval during which access is denied. Both use the parameter deny_point that has following format:</p><pre>DAY day [ TIME 'time' ]</pre><p align="LEFT" style="margin-left: 30.0px;">The two parts of the <em>deny_point</em> parameter use the following formats:</p><p align="LEFT" style="margin-left: 30.0px;">For day:</p><pre> {'Sunday'| 'Monday' | 'Tuesday' |'Wednesday' | 'Thursday' | 'Friday' | 'Saturday' | 0-6 }</pre><p align="LEFT" style="margin-left: 30.0px;">For time:</p><pre> { 00-23 : 00-59 | 01-12 : 00-59 { AM | PM }}</pre><p align="LEFT" style="margin-left: 30.0px;">The<em> DENY BETWEEN clause uses two deny_point parameters</em>.</p><pre> DENY BETWEEN deny_point AND deny_point</pre><pre>DROP DENY FOR deny_point</pre><p align="LEFT" style="margin-left: 30.0px;">The DROP DENY FOR clause removes a time-based constraint from the role. It uses the deny_point parameter described above.</p><h3 id="SQLCommandReference-Notes.1">Notes</h3><p align="LEFT">Use GRANT and REVOKE for adding and removing role memberships.</p><p align="LEFT">Caution must be exercised when specifying an unencrypted password with this command. The password will be transmitted to the server in clear text, and it might also be logged in the client’s command history or the server log. The psql command-line client contains a meta-command \password that can be used to safely change a role’s password.</p><p align="LEFT">It is also possible to tie a session default to a specific database rather than to a role. Role-specific settings override database-specific ones if there is a conflict.</p><h3 id="SQLCommandReference-Examples">Examples</h3><p align="LEFT">Change the password for a role:</p><pre> ALTER ROLE daria WITH PASSWORD 'passwd123';</pre><p align="LEFT">Change a password expiration date:</p><pre> ALTER ROLE scott VALID UNTIL 'May 4 12:00:00 2015 +1';</pre><p align="LEFT">Make a password valid forever:</p><pre> ALTER ROLE luke VALID UNTIL 'infinity';</pre><p align="LEFT">Give a role the ability to create other roles and new databases:</p><pre> ALTER ROLE joelle CREATEROLE CREATEDB;</pre><p align="LEFT">Give a role a non-default setting of the maintenance_work_mem parameter:</p><pre> ALTER ROLE admin SET maintenance_work_mem = 100000;</pre><p align="LEFT">Assign a role to a resource queue:</p><pre> ALTER ROLE sammy RESOURCE QUEUE poweruser;</pre><p align="LEFT">Give a role permission to create writable external tables:</p><pre> ALTER ROLE load CREATEEXTTABLE (type='writable');</pre><p align="LEFT">Alter a role so it does not allow login access on Sundays:</p><pre> ALTER ROLE user3 DENY DAY 'Sunday';</pre><p align="LEFT">Alter a role to remove the constraint that does not allow login access on Sundays:</p><pre> ALTER ROLE user3 DROP DENY FOR DAY 'Sunday';</pre><h3 id="SQLCommandReference-Compatibility.1">Compatibility</h3><p>The ALTER ROLE statement is a HAWQ extension.</p><h3 id="SQLCommandReference-SeeAlso.1">See Also</h3><p>CREATE ROLE, DROP ROLE, SET, CREATE RESOURCE QUEUE, GRANT, REVOKE </p><h2 id="SQLCommandReference-ALTERTABLE">ALTER TABLE</h2><p>Changes the definition of a table.</p><h3 id="SQLCommandReference-Synopsis.2">Synopsis</h3><div class="code panel pdl" style="border-width: 1px;"><div class="codeContent panelContent pdl">
<pre class="theme: Confluence; brush: sql; gutter: false" style="font-size:12px;">ALTER TABLE [ONLY] name RENAME [COLUMN] column TO new_column
ALTER TABLE name RENAME TO new_name
ALTER TABLE name SET SCHEMA new_schema
ALTER TABLE [ONLY] name SET
DISTRIBUTED BY (column, [ ... ] )
| DISTRIBUTED RANDOMLY
| WITH (REORGANIZE=true|false)
ALTER TABLE [ONLY] name action [, ... ]
ALTER TABLE name
[ ALTER PARTITION {partition_name | FOR (RANK(number))
| FOR (value) } partition_action [...] ]
partition_action
where action is one of:
ADD [COLUMN] column_name type
[ ENCODING ( storage_directive [,…] ) ]
[column_constraint [ ... ]]
DROP [COLUMN] column [RESTRICT | CASCADE]
ALTER [COLUMN] column TYPE type [USING expression]
ALTER [COLUMN] column SET DEFAULT expression
ALTER [COLUMN] column DROP DEFAULT
ALTER [COLUMN] column {SET | DROP } NOT NULL
ALTER [COLUMN] column SET STATISTICS integer
ADD table_constraint
DROP CONSTRAINT constraint_name [RESTRICT | CASCADE]
SET WITHOUT OIDS
INHERIT parent_table
NO INHERIT parent_table
OWNER TO new_owner
ALTER DEFAULT PARTITION
DROP DEFAULT PARTITION [IF EXISTS]
DROP PARTITION [IF EXISTS] { partition_name |
FOR (RANK(number)) | FOR (value) } [CASCADE]
TRUNCATE DEFAULT PARTITION
TRUNCATE PARTITION {partition_name | FOR (RANK(number)) |
FOR (value) }
RENAME DEFAULT PARTITION TO new_partition_name
RENAME PARTITION {partition_name | FOR (RANK(number)) |
FOR (value) } TO new_partition_name
ADD DEFAULT PARTITION name [ ( subpartition_spec ) ]
ADD PARTITION [name] partition_element
[ ( subpartition_spec ) ]
EXCHANGE PARTITION {partition_name | FOR (RANK(number)) |
FOR (value) } WITH TABLE table_name
[ WITH | WITHOUT VALIDATION ]
EXCHANGE DEFAULT PARTITION WITH TABLE table_name
[ WITH | WITHOUT VALIDATION ]
SET SUBPARTITION TEMPLATE (subpartition_spec)
SPLIT DEFAULT PARTITION
{ AT (list_value)
| START([datatype] range_value) [INCLUSIVE | EXCLUSIVE]
END([datatype] range_value) [INCLUSIVE | EXCLUSIVE] }
[ INTO ( PARTITION new_partition_name,
PARTITION default_partition_name ) ]
SPLIT PARTITION {partition_name | FOR (RANK(number)) |
FOR (value) } AT (value)
[ INTO (PARTITION partition_name, PARTITION partition_name)]
where partition_element is:
VALUES (list_value [,...] )
| START ([datatype] 'start_value') [INCLUSIVE | EXCLUSIVE]
[ END ([datatype] 'end_value') [INCLUSIVE | EXCLUSIVE] ]
| END ([datatype] 'end_value') [INCLUSIVE | EXCLUSIVE]
[ WITH ( partition_storage_parameter=value [, ... ] ) ]
[ TABLESPACE tablespace ]
where subpartition_spec is:
subpartition_element [, ...]
and subpartition_element is:
DEFAULT SUBPARTITION subpartition_name
| [SUBPARTITION subpartition_name] VALUES (list_value [,...] )
| [SUBPARTITION subpartition_name]
START ([datatype] 'start_value') [INCLUSIVE | EXCLUSIVE]
[ END ([datatype] 'end_value') [INCLUSIVE | EXCLUSIVE] ]
[ EVERY ( [number | datatype] 'interval_value') ]
| [SUBPARTITION subpartition_name]
END ([datatype] 'end_value') [INCLUSIVE | EXCLUSIVE]
[ EVERY ( [number | datatype] 'interval_value') ]
[ WITH ( partition_storage_parameter=value [, ... ] ) ]
[ TABLESPACE tablespace ]
where storage_parameter is:
APPENDONLY={TRUE}
BLOCKSIZE={8192-2097152}
ORIENTATION={COLUMN|ROW}
COMPRESSTYPE={ZLIB|QUICKLZ|RLE_TYPE|NONE}
COMPRESSLEVEL={0-9}
FILLFACTOR={10-100}
OIDS[=TRUE|FALSE]
where storage_directive is:
COMPRESSTYPE={ZLIB | QUICKLZ | RLE_TYPE | NONE}
| COMPRESSLEVEL={0-9}
| BLOCKSIZE={8192-2097152}
Where column_reference_storage_directive is:
COLUMN column_name ENCODING ( storage_directive [, … ] ), … |
DEFAULT COLUMN ENCODING ( storage_directive [, … ] )</pre>
</div></div><h3 id="SQLCommandReference-Description.2">Description</h3><p align="LEFT">ALTER TABLE changes the definition of an existing table. There are several subforms:</p><ul><li>ADD COLUMN — Adds a new column to the table, using the same syntax as CREATE TABLE.</li><li>DROP COLUMN — Drops a column from a table. Note that if you drop table columns that are being used as the HAWQ distribution key, the distribution policy for the table will be changed to DISTRIBUTED RANDOMLY. Table constraints involving the column will be automatically dropped as well. You will need to say CASCADE if anything outside the table depends on the column (such as views).</li><li>ALTER COLUMN TYPE — Changes the data type of a column of a table. Note that you cannot alter column data types that are being used as the HAWQ distribution key. Simple table constraints involving the column will be automatically converted to use the new column type by reparsing the originally supplied expression. The optional USING clause specifies how to compute the new column value from the old. If omitted, the default conversion is the same as an assignment cast from old data type to new. A USING clause must be provided if there is no implicit or assignment cast from old to new type.</li><li>SET/DROP DEFAULT — Sets or removes the default value for a column. The default values only apply to subsequent INSERT commands. They do not cause rows already in the table to change. Defaults may also be created for views, in which case they are inserted into statements on the view before the view’s ON INSERT rule is applied.</li><li>SET/DROP NOT NULL — Changes whether a column is marked to allow null values or to reject null values. You can only use SET NOT NULL when the column contains no null values.</li><li>SET STATISTICS — Sets the per-column statistics-gathering target for subsequent ANALYZE operations. The target can be set in the range 0 to 1000, or set to -1 to revert to using the system default statistics target (default_statistics_target).</li><li>ADD table_constraint — Adds a new constraint to a table (not just a partition) using the same syntax as CREATE TABLE.</li><li>DROP CONSTRAINT — Drops the specified constraint on a table.</li><li>SET WITHOUT OIDS — Removes the OID system column from the table. Note that there is no variant of ALTER TABLE that allows OIDs to be restored to a table once they have been removed.</li><li>SET DISTRIBUTED —<strong> </strong><span style="font-size: medium;"> </span>Changes the distribution policy of a table. Changes to a hash distribution policy will cause the table data to be physically redistributed on disk, which can be resource intensive.</li><li>INHERIT parent_table / NO INHERIT parent_table — Adds or removes the target table as a child of the specified parent table. Queries against the parent will include records of its child table. To be added as a child, the target table must already contain all the same columns as the parent (it could have additional columns, too). The columns must have matching data types, and if they have NOT NULL constraints in the parent then they must also have NOT NULL constraints in the child. There must also be matching child-table constraints for all CHECK constraints of the parent.</li><li>OWNER — Changes the owner of the table, sequence, or view to the specified user.</li><li>RENAME — Changes the name of a table (sequence, or view) or the name of an individual column in a table. There is no effect on the stored data. Note that HAWQ distribution key columns cannot be renamed.</li><li>SET SCHEMA — Moves the table into another schema. Associated constraints and sequences owned by table columns are moved as well.</li><li>ALTER PARTITION | DROP PARTITION | RENAME PARTITION | TRUNCATE PARTITION | ADD PARTITION | SPLIT PARTITION | EXCHANGE PARTITION | SET SUBPARTITION TEMPLATE — Changes the structure of a partitioned table. In most cases, you must go through the parent table to alter one of its child table partitions. </li></ul><p align="LEFT">You must own the table to use ALTER TABLE. To change the schema of a table, you must also have CREATE privilege on the new schema. To add the table as a new child of a parent table, you must own the parent table as well. To alter the owner, you must also be a direct or indirect member of the new owning role, and that role must have CREATE privilege on the table’s schema. A superuser has these privileges automatically.</p><p align="LEFT"><strong>Note:</strong> Memory usage increases significantly when a table has many partitions, if a table has compression, or if the blocksize for a table is large. If the number of relations associated with the table is large, this condition can force an operation on the table to use more memory. For example, if the table is a CO table and has a large number of columns, each column is a relation. An operation like ALTER TABLE ALTER COLUMN opens all the columns in the table allocates associated buffers. If a CO table has 40 columns and 100 partitions, and the columns are compressed and the blocksize is 2 MB (with a system factor of 3), the system attempts to allocate 24 GB, that is <br/>(40 ×100) × (2 ×3) MB or 24 GB.</p><h3 id="SQLCommandReference-Parameters.2">Parameters</h3><pre>ONLY</pre><p align="LEFT" style="margin-left: 30.0px;">Only perform the operation on the table name specified. If the ONLY keyword is not used, the operation will be performed on the named table and any child table partitions associated with that table.</p><pre>name</pre><p align="LEFT" style="margin-left: 30.0px;">The name (possibly schema-qualified) of an existing table to alter. If ONLY is specified, only that table is altered. If ONLY is not specified, the table and all its descendant tables (if any) are updated. Constraints can only be added to an entire table, not to a partition. Because of that restriction, the <em>name</em> parameter can only contain a table name, not a partition name.</p><pre>column</pre><p align="LEFT" style="margin-left: 30.0px;">Name of a new or existing column. Note that ?AWQ distribution key columns must be treated with special care. Altering or dropping these columns can change the distribution policy for the table.</p><pre>new_column</pre><p align="LEFT" style="margin-left: 30.0px;">New name for an existing column.</p><pre>new_name</pre><p align="LEFT" style="margin-left: 30.0px;">New name for the table.</p><pre>type</pre><p align="LEFT" style="margin-left: 30.0px;">Data type of the new column, or new data type for an existing column. If changing the data type of a ?AWQ distribution key column, you are only allowed to change it to a compatible type (for example,text to varchar is OK, but text to int is not).</p><pre>table_constraint</pre><p align="LEFT" style="margin-left: 30.0px;">New table constraint for the table. Note that foreign key constraints are currently not supported in ?AWQ. Also a table is only allowed one unique constraint and the uniqueness must be within the ?AWQ distribution key.</p><pre>constraint_name</pre><p align="LEFT" style="margin-left: 30.0px;">Name of an existing constraint to drop.</p><pre>CASCADE</pre><p align="LEFT" style="margin-left: 30.0px;">Automatically drop objects that depend on the dropped column or constraint (for example, views referencing the column).</p><pre>RESTRICT</pre><p align="LEFT" style="margin-left: 30.0px;">Refuse to drop the column or constraint if there are any dependent objects. This is the default behavior.</p><pre>ALL</pre><p align="LEFT" style="margin-left: 30.0px;">Disable or enable all triggers belonging to the table including constraint related triggers. This requires superuser privilege.</p><pre>USER</pre><p align="LEFT" style="margin-left: 30.0px;">Disable or enable all user-created triggers belonging to the table.</p><pre>DISTRIBUTED BY (column) | DISTRIBUTED RANDOMLY</pre><p align="LEFT" style="margin-left: 30.0px;">Specifies the distribution policy for a table. Changing a hash distribution policy will cause the table data to be physically redistributed on disk, which can be resource intensive. If you declare the same hash distribution policy or change from hash to random distribution, data will not be redistributed unless you declare SET WITH (REORGANIZE=true).</p><pre>REORGANIZE=true|false</pre><p align="LEFT" style="margin-left: 30.0px;">Use REORGANIZE=true when the hash distribution policy has not changed or when you have changed from a hash to a random distribution, and you want to redistribute the data anyways.</p><pre>parent_table</pre><p align="LEFT" style="margin-left: 30.0px;">A parent table to associate or de-associate with this table.</p><pre>new_owner</pre><p align="LEFT" style="margin-left: 30.0px;">The role name of the new owner of the table.</p><pre>new_schema</pre><p align="LEFT" style="margin-left: 30.0px;">The name of the schema to which the table will be moved.</p><pre>parent_table_name</pre><p align="LEFT" style="margin-left: 30.0px;">When altering a partitioned table, the name of the top-level parent table.</p><pre>ALTER [DEFAULT] PARTITION</pre><p align="LEFT" style="margin-left: 30.0px;">If altering a partition deeper than the first level of partitions, the ALTER PARTITION clause is used to specify which subpartition in the hierarchy you want to alter.</p><pre>DROP [DEFAULT] PARTITION</pre><p align="LEFT" style="margin-left: 30.0px;">Drops the specified partition. If the partition has subpartitions, the subpartitions are automatically dropped as well.</p><pre>TRUNCATE [DEFAULT] PARTITION</pre><p align="LEFT" style="margin-left: 30.0px;">Truncates the specified partition. If the partition has subpartitions, the subpartitions are automatically truncated as well.</p><pre>RENAME [DEFAULT] PARTITION</pre><p align="LEFT" style="margin-left: 30.0px;">Changes the partition name of a partition (not the relation name). Partitioned tables are created using the naming convention: <<em>parentname</em>>_<<em>level</em>>_prt_<<em>partition_name</em>>.</p><pre>ADD DEFAULT PARTITION</pre><p align="LEFT" style="margin-left: 30.0px;">Adds a default partition to an existing partition design. When data does not match to an existing partition, it is inserted into the default partition. Partition designs that do not have a default partition will reject incoming rows that do not match to an existing partition. Default partitions must be given a name.</p><pre>ADD PARTITION</pre><p align="LEFT" style="margin-left: 30.0px;">partition_element - Using the existing partition type of the table (range or list), defines the boundaries of new partition you are adding.</p><p align="LEFT" style="margin-left: 30.0px;">name - A name for this new partition.</p><p align="LEFT" style="margin-left: 30.0px;">VALUES - For list partitions, defines the value(s) that the partition will contain.</p><p align="LEFT" style="margin-left: 30.0px;">START - For range partitions, defines the starting range value for the partition. By default, start values are INCLUSIVE. For example, if you declared a start date of ‘2008-01-01’, then the partition would contain all dates greater than or equal to ‘2008-01-01’. Typically the data type of the START expression is the same type as the partition key column. If that is not the case, then you must explicitly cast to the intended data type.</p><p align="LEFT" style="margin-left: 30.0px;">END - For range partitions, defines the ending range value for the partition. By default, end values are EXCLUSIVE. For example, if you declared an end date of ‘2008-02-01’, then the partition would contain all dates less than but not equal to ‘2008-02-01’. Typically the data type of the END expression is the same type as the partition key column. If that is not the case, then you must explicitly cast to the intended data type.</p><p align="LEFT" style="margin-left: 30.0px;">WITH - Sets the table storage options for a partition. For example, you may want older partitions to be append-only tables and newer partitions to be regular heap tables. See "CREATE TABLE" for a description of the storage options.</p><p align="LEFT" style="margin-left: 30.0px;">TABLESPACE - The name of the tablespace in which the partition is to be created.</p><p align="LEFT" style="margin-left: 30.0px;">subpartition_spec - Only allowed on partition designs that were created without a subpartition template. Declares a subpartition specification for the new partition you are adding. If the partitioned table was originally defined using a subpartition template, then the template will be used to generate the subpartitions automatically.</p><pre>EXCHANGE [DEFAULT] PARTITION</pre><p align="LEFT">Exchanges another table into the partition hierarchy into the place of an existing partition. In a multi-level partition design, you can only exchange the lowest level partitions (those that contain data).</p><p align="LEFT" style="margin-left: 30.0px;">WITH TABLE <strong><em>table_name</em></strong><em><span style="font-size: medium;"> </span></em><span style="font-size: medium;"> </span> - The name of the table you are swapping in to the partition design.</p><p align="LEFT" style="margin-left: 30.0px;">WITH | WITHOUT VALIDATION - Validates that the data in the table matches the CHECK constraint of the partition you are exchanging. The default is to validate the data against the CHECK constraint.</p><pre>SET SUBPARTITION TEMPLATE</pre><p align="LEFT" style="margin-left: 30.0px;">Modifies the subpartition template for an existing partition. After a new subpartition template is set, all new partitions added will have the new subpartition design (existing partitions are not modified).</p><pre>SPLIT DEFAULT PARTITION</pre><p align="LEFT" style="margin-left: 30.0px;">Splits a default partition. In a multi-level partition design, you can only split the lowest level default partitions (those that contain data). Splitting a default partition creates a new partition containing the values specified and leaves the default partition containing any values that do not match to an existing partition.</p><p align="LEFT" style="margin-left: 30.0px;">AT - For list partitioned tables, specifies a single list value that should be used as the criteria for the split.</p><p align="LEFT" style="margin-left: 30.0px;">START - For range partitioned tables, specifies a starting value for the new partition.</p><p align="LEFT" style="margin-left: 30.0px;">END - For range partitioned tables, specifies an ending value for the new partition.</p><p align="LEFT" style="margin-left: 30.0px;">INTO - Allows you to specify a name for the new partition. When using the INTO clause to split a default partition, the second partition name specified should always be that of the existing default partition. If you do not know the name of the default partition, you can look it up using the <em>pg_partitions</em> view.</p><pre>SPLIT PARTITION</pre><p align="LEFT" style="margin-left: 30.0px;">Splits an existing partition into two partitions. In a multi-level partition design, you can only split the lowest level partitions (those that contain data).</p><p align="LEFT" style="margin-left: 30.0px;">AT - Specifies a single value that should be used as the criteria for the split. The partition will be divided into two new partitions with the split value specified being the starting range for the <em>latter</em> partition.</p><p align="LEFT" style="margin-left: 30.0px;">INTO - Allows you to specify names for the two new partitions created by the split.</p><pre>partition_name</pre><p align="LEFT" style="margin-left: 30.0px;">The given name of a partition.</p><pre>FOR (RANK(number))</pre><p align="LEFT" style="margin-left: 30.0px;">For range partitions, the rank of the partition in the range.</p><pre>FOR ('value')</pre><p align="LEFT" style="margin-left: 30.0px;">Specifies a partition by declaring a value that falls within the partition boundary specification. If the value declared with FOR matches to both a partition and one of its subpartitions (for example, if the value is a date and the table is partitioned by month and then by day), then FOR will operate on the first level where a match is found (for example, the monthly partition). If your intent is to operate on a subpartition, you must declare so as follows:<br/>ALTER TABLE <em>name</em> ALTER PARTITION FOR ('2008-10-01') DROP PARTITION FOR ('2008-10-01');</p><h3 id="SQLCommandReference-Notes.2">Notes</h3><p align="LEFT">Take special care when altering or dropping columns that are part of the ?AWQ distribution key as this can change the distribution policy for the table. HAWQ does not currently support foreign key constraints. For a unique constraint to be enforced in HAWQ, the table must be hash-distributed (not DISTRIBUTED RANDOMLY), and all of the distribution key columns must be the same as the initial columns of the unique constraint columns.</p><p align="LEFT">Note: The table name specified in the ALTER TABLE command cannot be the name of a partition within a table.</p><p align="LEFT">Adding a CHECK or NOT NULL constraint requires scanning the table to verify that existing rows meet the constraint.</p><p align="LEFT">When a column is added with ADD COLUMN, all existing rows in the table are initialized with the column’s default value (NULL if no DEFAULT clause is specified). Adding a column with a non-null default or changing the type of an existing column will require the entire table to be rewritten. This may take a significant amount of time for a large table; and it will temporarily require double the disk space.</p><p align="LEFT">You can specify multiple changes in a single ALTER TABLE command, which will be done in a single pass over the table.</p><p align="LEFT">The DROP COLUMN form does not physically remove the column, but simply makes it invisible to SQL operations. Subsequent insert and update operations in the table will store a null value for the column. Thus, dropping a column is quick but it will not immediately reduce the on-disk size of your table, as the space occupied by the dropped column is not reclaimed. The space will be reclaimed over time as existing rows are updated.</p><p align="LEFT">The fact that ALTER TYPE requires rewriting the whole table is sometimes an advantage, because the rewriting process eliminates any dead space in the table. For example, to reclaim the space occupied by a dropped column immediately, the fastest way is: ALTER TABLE table ALTER COLUMN anycol TYPE sametype; Where <em>anycol</em> is any remaining table column and <em>sametype</em> is the same type that column already has. This results in no semantically-visible change in the table, but the command forces rewriting, which gets rid of no-longer-useful data.</p><p align="LEFT">If a table is partitioned or has any descendant tables, it is not permitted to add, rename, or change the type of a column in the parent table without doing the same to the descendants. This ensures that the descendants always have columns matching the parent.</p><p align="LEFT">A recursive DROP COLUMN operation will remove a descendant table’s column only if the descendant does not inherit that column from any other parents and never had an independent definition of the column. A nonrecursive DROP COLUMN (ALTER TABLE ONLY ... DROP COLUMN) never removes any descendant columns, but instead marks them as independently defined rather than inherited.</p><p align="LEFT">The OWNER action never recurse to descendant tables; that is, they always act as though ONLY were specified. Adding a constraint can recurse only for CHECK constraints.</p><p align="LEFT">Changing any part of a system catalog table is not permitted.</p><h3 id="SQLCommandReference-Examples.1">Examples</h3><p align="LEFT">Add a column to a table:</p><pre> ALTER TABLE distributors ADD COLUMN address varchar(30);</pre><p align="LEFT">Rename an existing column:</p><pre> ALTER TABLE distributors RENAME COLUMN address TO city;</pre><p align="LEFT">Rename an existing table:</p><pre> ALTER TABLE distributors RENAME TO suppliers;</pre><p align="LEFT">Add a not-null constraint to a column:</p><pre> ALTER TABLE distributors ALTER COLUMN street SET NOT NULL;</pre><p align="LEFT">Add a check constraint to a table:</p><pre> ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5);</pre><p align="LEFT">Move a table to a different schema:</p><pre> ALTER TABLE myschema.distributors SET SCHEMA yourschema;</pre><p align="LEFT">Add a new partition to a partitioned table:</p><pre> ALTER TABLE sales ADD PARTITION<br/> START (date '2009-02-01') INCLUSIVE <br/> END (date '2009-03-01') EXCLUSIVE; </pre><p> Add a default partition to an existing partition design:</p><pre> ALTER TABLE sales ADD DEFAULT PARTITION other;</pre><p align="LEFT">Rename a partition:</p><pre> ALTER TABLE sales RENAME PARTITION FOR ('2008-01-01') TO jan08;</pre><p align="LEFT">Drop the first (oldest) partition in a range sequence:</p><pre> ALTER TABLE sales DROP PARTITION FOR (RANK(1));</pre><p align="LEFT">Exchange a table into your partition design:</p><pre> ALTER TABLE sales EXCHANGE PARTITION FOR ('2008-01-01') WITH TABLE jan08;</pre><p align="LEFT">Split the default partition (where the existing default partition’s name is ‘<em>other</em>’) to add a new monthly partition for January 2009:</p><pre> ALTER TABLE sales SPLIT DEFAULT PARTITION<br/> START ('2009-01-01') INCLUSIVE<br/> END ('2009-02-01') EXCLUSIVE<br/> INTO (PARTITION jan09, PARTITION other);</pre><p align="LEFT">Split a monthly partition into two with the first partition containing dates January 1-15 and the second partition containing dates January 16-31:</p><pre> ALTER TABLE sales SPLIT PARTITION FOR ('2008-01-01')<br/> AT ('2008-01-16')<br/> INTO (PARTITION jan081to15, PARTITION jan0816to31);</pre><h3 id="SQLCommandReference-Compatibility.2">Compatibility</h3><p align="LEFT">The ADD, DROP, and SET DEFAULT forms conform with the SQL standard. The other forms are ?AWQ extensions of the SQL standard. Also, the ability to specify more than one manipulation in a single ALTER TABLE command is an extension. ALTER TABLE DROP COLUMN can be used to drop the only column of a table, leaving a zero-column table. This is an extension of SQL, which disallows zero-column tables.</p><p align="LEFT">See Also</p><p align="LEFT">CREATE TABLE, DROP TABLE</p><h2 id="SQLCommandReference-ALTERUSER">ALTER USER</h2><p align="LEFT">Changes the definition of a database role (user).</p><h3 id="SQLCommandReference-Synopsis.3">Synopsis</h3><div class="code panel pdl" style="border-width: 1px;"><div class="codeContent panelContent pdl">
<pre class="theme: Confluence; brush: sql; gutter: false" style="font-size:12px;">ALTER USER name RENAME TO newname
ALTER USER name SET config_parameter {TO | =} {value | DEFAULT}
ALTER USER name RESET config_parameter
ALTER USER name [ [WITH] option [ ... ] ]
where option can be:
SUPERUSER | NOSUPERUSER
| CREATEDB | NOCREATEDB
| CREATEROLE | NOCREATEROLE
| CREATEUSER | NOCREATEUSER
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
| [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
| VALID UNTIL 'timestamp'</pre>
</div></div><h3 id="SQLCommandReference-Description.3">Description</h3><p align="LEFT">ALTER USER is a deprecated command but is still accepted for historical reasons. It is an alias for ALTER ROLE. See ALTER ROLE for more information.</p><h3 id="SQLCommandReference-Compatibility.3">Compatibility</h3><p align="LEFT">The ALTER USER statement is a HAWQ extension. The SQL standard leaves the definition of users to the implementation.</p><h3 id="SQLCommandReference-SeeAlso.2">See Also</h3><p align="LEFT">ALTER ROLE</p><h2 id="SQLCommandReference-ANALYZE">ANALYZE </h2><p align="LEFT">Collects statistics about a database.</p><h3 id="SQLCommandReference-Synopsis.4">Synopsis</h3><div class="code panel pdl" style="border-width: 1px;"><div class="codeContent panelContent pdl">
<pre class="theme: Confluence; brush: sql; gutter: false" style="font-size:12px;">ANALYZE [VERBOSE] [table [ (column [, ...] ) ]]</pre>
</div></div><h3 id="SQLCommandReference-Description.4">Description</h3><p align="LEFT">ANALYZE collects statistics about the contents of tables in the database, and stores the results in the system table <em>pg_statistic</em>. Subsequently, the query planner uses these statistics to help determine the most efficient execution plans for queries.</p><p align="LEFT">With no parameter, ANALYZE examines every table in the current database. With a parameter, ANALYZE examines only that table. It is further possible to give a list of column names, in which case only the statistics for those columns are collected.</p><h3 id="SQLCommandReference-Parameters.3">Parameters</h3><pre>VERBOSE</pre><p align="LEFT" style="margin-left: 30.0px;">Enables display of progress messages. When specified, ANALYZE emits progress messages to indicate which table is currently being processed. Various statistics about the tables are printed as well.</p><pre>table</pre><p align="LEFT" style="margin-left: 30.0px;">The name (possibly schema-qualified) of a specific table to analyze. Defaults to all tables in the current database.</p><pre>column</pre><p align="LEFT" style="margin-left: 30.0px;">The name of a specific column to analyze. Defaults to all columns.</p><h3 id="SQLCommandReference-Notes.3">Notes</h3><p align="LEFT">It is a good idea to run ANALYZE periodically, or just after making major changes in the contents of a table. Accurate statistics will help the query planner to choose the most appropriate query plan, and thereby improve the speed of query processing. A common strategy is to run VACUUM and ANALYZE once a day during a low-usage time of day.</p><p align="LEFT">ANALYZE requires only a read lock on the target table, so it can run in parallel with other activity on the table.</p><p align="LEFT">The statistics collected by ANALYZE usually include a list of some of the most common values in each column and a histogram showing the approximate data distribution in each column. One or both of these may be omitted if ANALYZE deems them uninteresting (for example, in a unique-key column, there are no common values) or if the column data type does not support the appropriate operators.</p><p align="LEFT">For large tables, ANALYZE takes a random sample of the table contents, rather than examining every row. This allows even very large tables to be analyzed in a small amount of time. Note, however, that the statistics are only approximate, and will change slightly each time ANALYZE is run, even if the actual table contents did not change. This may result in small changes in the planner’s estimated costs shown by EXPLAIN. In rare situations, this non-determinism will cause the query optimizer to choose a different query plan between runs of ANALYZE. To avoid this, raise the amount of statistics collected by ANALYZE by adjusting the <em>default_statistics_target</em> configuration parameter, or on a column-by-column basis by setting the per-column statistics target with ALTER TABLE ... ALTER COLUMN ... SET STATISTICS (see ALTER TABLE). The target value sets the maximum number of entries in the most-common-value list and the maximum number of bins in the histogram. The default target value is 10, but this can be adjusted up or down to trade off accuracy of planner estimates against the time taken for ANALYZE and the amount of space occupied in <em>pg_statistic</em>. In particular, setting the statistics target to zero disables collection of statistics for that column. It may be useful to do that for columns that are never used as part of the WHERE, GROUP BY, or ORDER BY clauses of queries, since the planner will have no use for statistics on such columns.</p><p align="LEFT">The largest statistics target among the columns being analyzed determines the number of table rows sampled to prepare the statistics. Increasing the target causes a proportional increase in the time and space needed to do ANALYZE.</p><p align="LEFT">There may be situations where the remote Analyzer may not be able to perform a task on a PXF table. For example, if a PXF Java component is down, the remote analyzer may not perform the task, so that the database transaction can succeed. In these cases the statistics remain with the default external table values.</p><h3 id="SQLCommandReference-Examples.2">Examples</h3><p align="LEFT">Collect statistics for the table <em>mytable</em>:</p><pre> ANALYZE mytable;</pre><h3 id="SQLCommandReference-Compatibility.4">Compatibility</h3><p align="LEFT">There is no ANALYZE statement in the SQL standard.</p><h3 id="SQLCommandReference-SeeAlso.3">See Also</h3><pre>ALTER TABLE , EXPLAIN, VACUUM</pre><h2 id="SQLCommandReference-BEGIN">BEGIN</h2><p>Starts a transaction block.</p><div class="code panel pdl" style="border-width: 1px;"><div class="codeContent panelContent pdl">
<pre class="theme: Confluence; brush: sql; gutter: false" style="font-size:12px;">BEGIN [WORK | TRANSACTION] [SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED] [READ WRITE | READ ONLY]</pre>
</div></div><h3 id="SQLCommandReference-Description.5">Description</h3><p align="LEFT">BEGIN initiates a transaction block, that is, all statements after a BEGIN command will be executed in a single transaction until an explicit COMMIT or ROLLBACK is given. By default (without BEGIN), ?AWQ executes transactions in autocommit mode, that is, each statement is executed in its own transaction and a commit is implicitly performed at the end of the statement (if execution was successful, otherwise a rollback is done).</p><p align="LEFT">Statements are executed more quickly in a transaction block, because transaction start/commit requires significant CPU and disk activity. Execution of multiple statements inside a transaction is also useful to ensure consistency when making several related changes: other sessions will be unable to see the intermediate states wherein not all the related updates have been done.</p><h3 id="SQLCommandReference-Parameters.4">Parameters</h3><pre>WORK<br/>TRANSACTION</pre><p align="LEFT" style="margin-left: 30.0px;">Optional key words. They have no effect.</p><pre>SERIALIZABLE<br/>REPEATABLE READ<br/>READ COMMITTED<br/>READ UNCOMMITTED</pre><p align="LEFT" style="margin-left: 30.0px;">The SQL standard defines four transaction isolation levels: READ COMMITTED, READ UNCOMMITTED, SERIALIZABLE, and REPEATABLE READ. The default behavior is that a statement can only see rows committed before it began (READ COMMITTED). In ?AWQ READ UNCOMMITTED is treated the same as READ COMMITTED. SERIALIZABLE is supported the same as REPEATABLE READ wherein all statements of the current transaction can only see rows committed before the first statement was executed in the transaction. SERIALIZABLE is the strictest transaction isolation. This level emulates serial transaction execution, as if transactions had been executed one after another, serially, rather than concurrently. Applications using this level must be prepared to retry transactions due to serialization failures.</p><pre>READ WRITE<br/>READ ONLY</pre><p align="LEFT" style="margin-left: 30.0px;">Determines whether the transaction is read/write or read-only. Read/write is the default. When a transaction is read-only, the following SQL commands are disallowed: INSERT, UPDATE, DELETE, and COPY FROM if the table they would write to is not a temporary table; all CREATE, ALTER, and DROP commands; GRANT, REVOKE, TRUNCATE; and EXPLAIN ANALYZE and EXECUTE if the command they would execute is among those listed.</p><h3 id="SQLCommandReference-Notes.4">Notes</h3><p align="LEFT">Use COMMIT or ROLLBACK to terminate a transaction block.</p><p align="LEFT">Issuing BEGIN when already inside a transaction block will provoke a warning message. The state of the transaction is not affected. To nest transactions within a transaction block, use savepoints (see SAVEPOINT).</p><h3 id="SQLCommandReference-Examples.3">Examples</h3><p align="LEFT">To begin a transaction block:</p><pre> BEGIN;</pre><h3 id="SQLCommandReference-Compatibility.5">Compatibility</h3><p align="LEFT">BEGIN is a HAWQ language extension. It is equivalent to the SQL-standard command <span lang="ZH">START TRANSACTION</span><span lang="EN">.</span></p><p align="LEFT">Incidentally, the BEGIN key word is used for a different purpose in embedded SQL. You are advised to be careful about the transaction semantics when porting database applications.</p><h3 id="SQLCommandReference-SeeAlso.4">See Also</h3><pre>COMMIT, ROLLBACK, SAVEPOINT</pre><h2 id="SQLCommandReference-CHECKPOINT">CHECKPOINT</h2><p>Forces a transaction log checkpoint.</p><h3 id="SQLCommandReference-Synopsis.5">Synopsis</h3><div class="code panel pdl" style="border-width: 1px;"><div class="codeContent panelContent pdl">
<pre class="theme: Confluence; brush: sql; gutter: false" style="font-size:12px;">CHECKPOINT</pre>
</div></div><h3 id="SQLCommandReference-Description.6">Description</h3><p align="LEFT">Write-Ahead Logging (WAL) puts a checkpoint in the transaction log every so often. The automatic checkpoint interval is set per HAWQ segment instance by the server configuration parameters <em>checkpoint_segments</em> and <em>checkpoint_timeout</em>. The CHECKPOINT command forces an immediate checkpoint when the command is issued, without waiting for a scheduled checkpoint.</p><p align="LEFT">A checkpoint is a point in the transaction log sequence at which all data files have been updated to reflect the information in the log. All data files will be flushed to disk.</p><p align="LEFT">Only superusers may call CHECKPOINT. The command is not intended for use during normal operation.</p><h3 id="SQLCommandReference-Compatibility.6">Compatibility</h3><p align="LEFT">The CHECKPOINT command is a HAWQ language extension.</p><h2 id="SQLCommandReference-CLOSE">CLOSE</h2><p>Closes a cursor.</p><h3 id="SQLCommandReference-Synopsis.6">Synopsis</h3><div class="code panel pdl" style="border-width: 1px;"><div class="codeContent panelContent pdl">
<pre class="theme: Confluence; brush: sql; gutter: false" style="font-size:12px;">CLOSE cursor_name</pre>
</div></div><h3 id="SQLCommandReference-Description.7">Description</h3><p align="LEFT">CLOSE frees the resources associated with an open cursor. After the cursor is closed, no subsequent operations are allowed on it. A cursor should be closed when it is no longer needed.</p><p align="LEFT">Every non-holdable open cursor is implicitly closed when a transaction is terminated by COMMIT or ROLLBACK. A holdable cursor is implicitly closed if the transaction that created it aborts via ROLLBACK. If the creating transaction successfully commits, the holdable cursor remains open until an explicit CLOSE is executed, or the client disconnects.</p><h3 id="SQLCommandReference-Parameters.5">Parameters</h3><pre>cursor_name</pre><p align="LEFT" style="margin-left: 30.0px;">The name of an open cursor to close.</p><h3 id="SQLCommandReference-Notes.5">Notes</h3><p align="LEFT">HAWQ does not have an explicit OPEN cursor statement. A cursor is considered open when it is declared. Use the DECLARE statement to declare (and open) a cursor.</p><p align="LEFT">You can see all available cursors by querying the <em>pg_cursors</em> system view.</p><h3 id="SQLCommandReference-Examples.4">Examples</h3><p align="LEFT">Close the cursor <em>portala</em>:</p><pre> CLOSE portala;</pre><h3 id="SQLCommandReference-Compatibility.7">Compatibility</h3><p align="LEFT">CLOSE is fully conforming with the SQL standard.</p><h3 id="SQLCommandReference-SeeAlso.5">See Also</h3><pre>DECLARE, FETCH</pre><h2 id="SQLCommandReference-COMMIT">COMMIT</h2><p>Commits the current transaction.</p><h3 id="SQLCommandReference-Synopsis.7">Synopsis</h3><div class="code panel pdl" style="border-width: 1px;"><div class="codeContent panelContent pdl">
<pre class="theme: Confluence; brush: sql; gutter: false" style="font-size:12px;">COMMIT [WORK | TRANSACTION]</pre>
</div></div><h3 id="SQLCommandReference-Description.8">Description</h3><p align="LEFT">COMMIT commits the current transaction. All changes made by the transaction become visible to others and are guaranteed to be durable if a crash occurs.</p><h3 id="SQLCommandReference-Parameters.6">Parameters</h3><pre>WORK<br/>TRANSACTION</pre><p align="LEFT">Optional key words. They have no effect.</p><h3 id="SQLCommandReference-Notes.6">Notes</h3><p align="LEFT">Use ROLLBACK to abort a transaction.</p><p align="LEFT">Issuing COMMIT when not inside a transaction does no harm, but it will provoke a warning message.</p><h3 id="SQLCommandReference-Examples.5">Examples</h3><p align="LEFT">To commit the current transaction and make all changes permanent:</p><pre> COMMIT;</pre><h3 id="SQLCommandReference-Compatibility.8">Compatibility</h3><p align="LEFT">The SQL standard only specifies the two forms COMMIT and COMMIT WORK. Otherwise, this command is fully conforming.</p><h3 id="SQLCommandReference-SeeAlso.6">See Also</h3><pre>BEGIN, END, ROLLBACK</pre><h2 id="SQLCommandReference-COPY">COPY</h2><p>Copies data between a file and a table.</p><h3 id="SQLCommandReference-Synopsis.8">Synopsis</h3><div class="code panel pdl" style="border-width: 1px;"><div class="codeContent panelContent pdl">
<pre class="theme: Confluence; brush: sql; gutter: false" style="font-size:12px;">COPY table [(column [, ...])] FROM {'file' | STDIN}
[ [WITH]
[OIDS]
[HEADER]
[DELIMITER [ AS ] 'delimiter']
[NULL [ AS ] 'null string']
[ESCAPE [ AS ] 'escape' | 'OFF']
[NEWLINE [ AS ] 'LF' | 'CR' | 'CRLF']
[CSV [QUOTE [ AS ] 'quote']
[FORCE NOT NULL column [, ...]]
[FILL MISSING FIELDS]
[ [LOG ERRORS INTO error_table] [KEEP]
SEGMENT REJECT LIMIT count [ROWS | PERCENT] ]
COPY {table [(column [, ...])] | (query)} TO {'file' | STDOUT}
[ [WITH]
[OIDS]
[HEADER]
[DELIMITER [ AS ] 'delimiter']
[NULL [ AS ] 'null string']
[ESCAPE [ AS ] 'escape' | 'OFF']
[CSV [QUOTE [ AS ] 'quote']
[FORCE QUOTE column [, ...]] ]</pre>
</div></div><h3 id="SQLCommandReference-Description.9">Description</h3><p align="LEFT">COPY moves data between HAWQ tables and standard file-system files. COPY TO copies the contents of a table to a file, while COPY FROM copies data from a file to a table (appending the data to whatever is in the table already). COPY TO can also copy the results of a SELECT query.</p><p align="LEFT">If a list of columns is specified, COPY will only copy the data in the specified columns to or from the file. If there are any columns in the table that are not in the column list, COPY FROM will insert the default values for those columns.</p><p align="LEFT">COPY with a file name instructs the HAWQ master host to directly read from or write to a file. The file must be accessible to the master host and the name must be specified from the viewpoint of the master host. When STDIN or STDOUT is specified, data is transmitted via the connection between the client and the master.</p><p align="LEFT">If SEGMENT REJECT LIMIT is used, then a COPY FROM operation will operate in single row error isolation mode. In this release, single row error isolation mode only applies to rows in the input file with format errors — for example, extra or missing attributes, attributes of a wrong data type, or invalid client encoding sequences. Constraint errors such as violation of a NOT NULL, CHECK, or UNIQUE constraint will still be handled in ‘all-or-nothing’ input mode. The user can specify the number of error rows acceptable (on a per-segment basis), after which the entire COPY FROM operation will be aborted and no rows will be loaded. Note that the count of error rows is per-segment, not per entire load operation. If the per-segment reject limit is not reached, then all rows not containing an error will be loaded. If the limit is not reached, all good rows will be loaded and any error rows discarded. If you would like to keep error rows for further examination, you can optionally declare an error table using the LOG ERRORS INTO clause. Any rows containing a format error would then be logged to the specified error table.</p><h3 id="SQLCommandReference-Outputs">Outputs</h3><p align="LEFT">On successful completion, a COPY command returns a command tag of the form, where <em>count</em><span style="font-size: small;"> </span> is the number of rows copied:</p><pre> COPY count</pre><p align="LEFT">If running a COPY FROM command in single row error isolation mode, the following notice message will be returned if any rows were not loaded due to format errors, where <em>count</em><span style="font-size: small;"> </span> is the number of rows rejected:</p><pre> NOTICE: Rejected count badly formatted rows.</pre><h3 id="SQLCommandReference-Parameters.7">Parameters</h3><pre>table</pre><p align="LEFT" style="margin-left: 30.0px;">The name (optionally schema-qualified) of an existing table.</p><pre>column</pre><p align="LEFT" style="margin-left: 30.0px;">An optional list of columns to be copied. If no column list is specified, all columns of the table will be copied.</p><pre>query</pre><p align="LEFT" style="margin-left: 30.0px;">A SELECT or VALUES command whose results are to be copied. Note that parentheses are required around the query.</p><pre>file</pre><p align="LEFT" style="margin-left: 30.0px;">The absolute path name of the input or output file.</p><pre>STDIN</pre><p align="LEFT" style="margin-left: 30.0px;">Specifies that input comes from the client application.</p><pre>STDOUT</pre><p align="LEFT" style="margin-left: 30.0px;">Specifies that output goes to the client application.</p><pre>OIDS</pre><p align="LEFT" style="margin-left: 30.0px;">Specifies copying the OID for each row. (An error is raised if OIDS is specified for a table that does not have OIDs, or in the case of copying a query.)</p><pre>delimiter</pre><p style="margin-left: 30.0px;">The single ASCII character that separates columns within each row (line) of the file. The default is a tab character in text mode, a comma in CSV mode.</p><pre>null string</pre><p style="margin-left: 30.0px;">The string that represents a null value. The default is \N (backslash-N) in text mode, and a empty value with no quotes in CSV mode. You might prefer an empty string even in text mode for cases where you don’t want to distinguish nulls from empty strings. When using COPY FROM, any data item that matches this string will be stored as a null value, so you should make sure that you use the same string as you used with COPY TO.</p><pre>escape<span> </span><span> </span></pre><p align="LEFT" style="margin-left: 30.0px;">Specifies the single character that is used for C escape sequences (such as \n,\t,\100, and so on) and for quoting data characters that might otherwise be taken as row or column delimiters. Make sure to choose an escape character that is not used anywhere in your actual column data. The default escape character is \ (backslash) for text files or " (double quote) for CSV files, however it is possible to specify any other character to represent an escape. It is also possible to disable escaping on text-formatted files by specifying the value 'OFF' as the escape value. This is very useful for data such as web log data that has many embedded backslashes that are not intended to be escapes.</p><pre>NEWLINE</pre><p align="LEFT" style="margin-left: 30.0px;">Specifies the newline used in your data files — LF (Line feed, 0x0A), CR (Carriage return, 0x0D), or CRLF (Carriage return plus line feed, 0x0D 0x0A). If not specified, a ?AWQ segment will detect the newline type by looking at the first row of data it receives and using the first newline type encountered.</p><pre>CSV</pre><p align="LEFT" style="margin-left: 30.0px;">Selects Comma Separated Value (CSV) mode.</p><pre>HEADER</pre><p align="LEFT" style="margin-left: 30.0px;">Specifies that a file contains a header line with the names of each column in the file. On output, the first line contains the column names from the table, and on input, the first line is ignored.</p><pre>quote</pre><p align="LEFT" style="margin-left: 30.0px;">Specifies the quotation character in CSV mode. The default is double-quote.</p><pre>FORCE QUOTE</pre><p align="LEFT" style="margin-left: 30.0px;">In CSV COPY TO mode, forces quoting to be used for all non-NULL values in each specified column. NULL output is never quoted.</p><pre>FORCE NOT NULL</pre><p align="LEFT" style="margin-left: 30.0px;">In CSV COPY FROM mode, process each specified column as though it were quoted and hence not a NULL value. For the default null string in CSV mode (nothing between two delimiters), this causes missing values to be evaluated as zero-length strings.</p><pre>FILL MISSING FIELDS<span><span> </span></span></pre><p align="LEFT" style="margin-left: 30.0px;">In COPY FROM more for both TEXT and CSV, specifying FILL MISSING FIELDS will set missing trailing field values to NULL (instead of reporting an error) when a row of data has missing data fields at the end of a line or row. Blank rows, fields with a NOT NULL constraint, and trailing delimiters on a line will still report an error.<span style="font-size: medium;"> </span></p><pre>LOG ERRORS INTO <em>error_table </em><span style="font-size: medium;"> </span>[KEEP]</pre><p align="LEFT" style="margin-left: 30.0px;">This is an optional clause that may precede a SEGMENT REJECT LIMIT clause. It specifies an error table where rows with formatting errors will be logged when running in single row error isolation mode. You can then examine this error table to see error rows that were not loaded (if any). If the <em>error_table </em><span style="font-size: small;"> </span>specified already exists, it will be used. If it does not exist, it will be automatically generated. If the command auto-generates the error table and no errors are produced, the default is to drop the error table after the operation completes unless KEEP is specified. If the table is auto-generated and the error limit is exceeded, the entire transaction is rolled back and no error data is saved. If you want the error table to persist in this case, create the error table prior to running the COPY. An error table is defined as follows:</p><div class="code panel pdl" style="border-width: 1px;"><div class="codeContent panelContent pdl">
<pre class="theme: Confluence; brush: sql; gutter: false" style="font-size:12px;">CREATE TABLE error_table_name ( cmdtime timestamptz,
relname text, filename text, linenum int, bytenum int,
errmsg text, rawdata text, rawbytes bytea )
DISTRIBUTED RANDOMLY;</pre>
</div></div><pre>SEGMENT REJECT LIMIT count [ROWS | PERCENT]</pre><p style="margin-left: 30.0px;">Runs a COPY FROM operation in single row error isolation mode. If the input rows have format errors they will be discarded provided that the reject limit count is not reached on any HAWQ segment instance during the load operation. The reject limit count can be specified as number of rows (the default) or percentage of total rows (1-100). If PERCENT is used, each segment starts calculating the bad row percentage only after the number of rows specified by the parameter gp_reject_percent_threshold has been processed. The default for gp_reject_percent_threshold is 300 rows. Constraint errors such as violation of a NOT NULL, CHECK, or UNIQUE constraint will still be handled in ‘all-or-nothing’ input mode. If the limit is not reached, all good rows will be loaded and any error rows discarded. </p><h3 id="SQLCommandReference-Notes.7">Notes</h3><p align="LEFT">COPY can only be used with tables, not with views. However, you can write COPY (SELECT * FROM viewname) TO ....</p><p align="LEFT">The BINARY key word causes all data to be stored/read as binary format rather than as text. It is somewhat faster than the normal text mode, but a binary-format file is less portable across machine architectures and HAWQ versions. Also, you cannot run COPY FROM in single row error isolation mode if the data is in binary format.</p><p align="LEFT">You must have SELECT privilege on the table whose values are read by COPY TO, and insert privilege on the table into which values are inserted by COPY FROM.</p><p align="LEFT">Files named in a COPY command are read or written directly by the database server, not by the client application. Therefore, they must reside on or be accessible to the HAWQ master host machine, not the client. They must be accessible to and readable or writable by the HAWQ system user (the user ID the server runs as), not the client. COPY naming a file is only allowed to database superusers, since it allows reading or writing any file that the server has privileges to access.</p><p align="LEFT">COPY FROM will invoke any triggers and check constraints on the destination table. However, it will not invoke rewrite rules. Note that in this release, violations of constraints are not evaluated for single row error isolation mode.</p><p align="LEFT">COPY input and output is affected by DateStyle. To ensure portability to other HAWQ installations that might use non-default DateStyle settings, DateStyle should be set to ISO before using COPY TO.</p><p align="LEFT">By default, COPY stops operation at the first error. This should not lead to problems in the event of a COPY TO, but the target table will already have received earlier rows in a COPY FROM. These rows will not be visible or accessible, but they still occupy disk space. This may amount to a considerable amount of wasted disk space if the failure happened well into a large COPY FROM operation. You may wish to invoke VACUUM to recover the wasted space. Another option would be to use single row error isolation mode to filter out error rows while still loading good rows.</p><p align="LEFT">COPY supports creating readable foreign tables with error tables. The default for concurrently inserting into the error table is 127. You can use error tables with foreign tables under the following circumstances:</p><p align="LEFT">Multiple foreign tables can use different error tables</p><p align="LEFT">Multiple foreign tables cannot use the same error table</p><h3 id="SQLCommandReference-FileFormats">File Formats</h3><h4 id="SQLCommandReference-TextFormat">Text Format</h4><p align="LEFT">When COPY is used without the BINARY or CSV options, the data read or written is a text file with one line per table row. Columns in a row are separated by the <em>delimiter</em><span style="font-size: small;"> </span> character (tab by default). The column values themselves are strings generated by the output function, or acceptable to the input function, of each attribute’s data type. The specified null string is used in place of columns that are null. COPY FROM will raise an error if any line of the input file contains more or fewer columns than are expected. If OIDS is specified, the OID is read or written as the first column, preceding the user data columns.</p><p align="LEFT">The data file has two reserved characters that have special meaning to COPY:</p><ul><li>The designated delimiter character (tab by default), which is used to separate fields in the data file.</li><li>A UNIX-style line feed ( \n or 0x0a), which is used to designate a new row in the data file. It is strongly recommended that applications generating COPY data convert data line feeds to UNIX-style line feeds rather than Microsoft Windows style carriage return line feeds (\r\n or 0x0a 0x0d).</li></ul><p align="LEFT">If your data contains either of these characters, you must escape the character so COPY treats it as data and not as a field separator or new row.</p><p align="LEFT">By default, the escape character is a \ (backslash) for text-formatted files and a " (double quote) for csv-formatted files. If you want to use a different escape character, you can do so using the ESCAPE AS clause. Make sure to choose an escape character that is not used anywhere in your data file as an actual data value. You can also disable escaping in text-formatted files by using ESCAPE 'OFF'.</p><p align="LEFT">For example, suppose you have a table with three columns and you want to load the following three fields using COPY.</p><ul><li>percentage sign = %</li><li>vertical bar = |</li><li>backslash = \</li></ul><p align="LEFT">Your designated DELIMITER character is | (pipe character), and your designated ESCAPE character is * (asterisk). The formatted row in your data file would look like this:</p><pre>percentage sign = % | vertical bar = *| | backslash = \</pre><p align="LEFT">Notice how the pipe character that is part of the data has been escaped using the asterisk character (*). Also notice that we do not need to escape the backslash since we are using an alternative escape character.</p><p align="LEFT">The following characters must be preceded by the escape character if they appear as part of a column value: the escape character itself, newline, carriage return, and the current delimiter character. You can specify a different escape character using the ESCAPE AS clause.</p><h4 id="SQLCommandReference-CSVFormat">CSV Format</h4><p align="LEFT"><span style="font-size: medium;"><span style="font-size: small;"> </span></span></p><p align="LEFT">This format is used for importing and exporting the Comma Separated Value (CSV) file format used by many other programs, such as spreadsheets. Instead of the escaping used by HAWQ standard text mode, it produces and recognizes the common CSV escaping mechanism.</p><p align="LEFT">The values in each record are separated by the DELIMITER character. If the value contains the delimiter character, the QUOTE character, the ESCAPE character (which is double quote by default), the NULL string, a carriage return, or line feed character, then the whole value is prefixed and suffixed by the QUOTE character. You can also use FORCE QUOTE to force quotes when outputting non-NULL values in specific columns.</p><p align="LEFT">The CSV format has no standard way to distinguish a NULL value from an empty string. HAWQ COPY handles this by quoting. A NULL is output as the NULL string and is not quoted, while a data value matching the NULL string is quoted. Therefore, using the default settings, a NULL is written as an unquoted empty string, while an empty string is written with double quotes (""). Reading values follows similar rules. You can use FORCE NOT NULL to prevent NULL input comparisons for specific columns.</p><p align="LEFT">Because backslash is not a special character in the CSV format, \., the end-of-data marker, could also appear as a data value. To avoid any misinterpretation, a \. data value appearing as a lone entry on a line is automatically quoted on output, and on input, if quoted, is not interpreted as the end-of-data marker. If you are loading a file created by another application that has a single unquoted column and might have a value of \., you might need to quote that value in the input file.</p><p align="LEFT">Note: In CSV mode, all characters are significant. A quoted value surrounded by white space, or any characters other than DELIMITER, will include those characters. This can cause errors if you import data from a system that pads CSV lines with white space out to some fixed width. If such a situation arises you might need to preprocess the CSV file to remove the trailing white space, before importing the data into ?AWQ.</p><p align="LEFT">Note: CSV mode will both recognize and produce CSV files with quoted values containing embedded carriage returns and line feeds. Thus the files are not strictly one line per table row like text-mode files.</p><p align="LEFT">Note: Many programs produce strange and occasionally perverse CSV files, so the file format is more a convention than a standard. Thus you might encounter some files that cannot be imported using this mechanism, and COPY might produce files that other programs cannot process.</p><h4 id="SQLCommandReference-BinaryFormat">Binary Format</h4><p align="LEFT">The BINARY format consists of a file header, zero or more tuples containing the row data, and a file trailer. Headers and data are in network byte order.</p><ul><li>File Header — The file header consists of 15 bytes of fixed fields, followed by a variable-length header extension area. The fixed fields are:</li><ul><li>Signature — 11-byte sequence PGCOPY\n\377\r\n\0 — note that the zero byte is a required part of the signature. (The signature is designed to allow easy identification of files that have been munged by a non-8-bit-clean transfer. This signature will be changed by end-of-line-translation filters, dropped zero bytes, dropped high bits, or parity changes.)</li><li>Flags field — 32-bit integer bit mask to denote important aspects of the file format. Bits are numbered from 0 (LSB) to 31 (MSB). Note that this field is stored in network byte order (most significant byte first), as are all the integer fields used in the file format. Bits 16-31 are reserved to denote critical file format issues; a reader should abort if it finds an unexpected bit set in this range. Bits 0-15 are reserved to signal backwards-compatible format issues; a reader should simply ignore any unexpected bits set in this range. Currently only one flag is defined, and the rest must be zero (Bit 16: 1 if data has OIDs, 0 if not).</li><li>Header extension area length — 32-bit integer, length in bytes of remainder of header, not including self. Currently, this is zero, and the first tuple follows immediately. Future changes to the format might allow additional data to be present in the header. A reader should silently skip over any header extension data it does not know what to do with. The header extension area is envisioned to contain a sequence of self-identifying chunks. The flags field is not intended to tell readers what is in the extension area. Specific design of header extension contents is left for a later release.</li></ul><li>Tuples — Each tuple begins with a 16-bit integer count of the number of fields in the tuple. (Presently, all tuples in a table will have the same count, but that might not always be true.) Then, repeated for each field in the tuple, there is a 32-bit length word followed by that many bytes of field data. (The length word does not include itself, and can be zero.) As a special case, -1 indicates a NULL field value. No value bytes follow in the NULL case.</li></ul><p align="LEFT" style="margin-left: 30.0px;"> There is no alignment padding or any other extra data between fields.</p><p align="LEFT" style="margin-left: 30.0px;"> Presently, all data values in a COPY BINARY file are assumed to be in binary format (format code one). It is anticipated that a future extension may add a header field <br/> that allows per-column format codes to be specified.</p><p align="LEFT" style="margin-left: 30.0px;"> If OIDs are included in the file, the OID field immediately follows the field-count word. It is a normal field except that it's not included in the field-count. In particular it has<br/> a length word — this will allow handling of 4-byte vs. 8-byte OIDs without too much pain, and will allow OIDs to be shown as null if that ever proves desirable.</p><ul><li>File Trailer — The file trailer consists of a 16-bit integer word containing -1. This is easily distinguished from a tuple’s field-count word. A reader should report an error if a field-count word is neither -1 nor the expected number of columns. This provides an extra check against somehow getting out of sync with the data.</li></ul><h3 id="SQLCommandReference-Examples.6">Examples</h3><p align="LEFT">Copy a table to the client using the vertical bar (|) as the field delimiter:</p><pre> COPY country TO STDOUT WITH DELIMITER '|';</pre><p align="LEFT">Copy data from a file into the <em>country</em> table:</p><pre> COPY country FROM '/home/usr1/sql/country_data';</pre><p align="LEFT">Copy into a file just the countries whose names start with 'A':</p><pre> COPY (SELECT * FROM country WHERE country_name LIKE 'A%') TO '/home/usr1/sql/a_list_countries.copy';</pre><p>Create an error table called err_sales to use with single row error isolation mode:</p><div class="code panel pdl" style="border-width: 1px;"><div class="codeContent panelContent pdl">
<pre class="theme: Confluence; brush: sql; gutter: false" style="font-size:12px;">CREATE TABLE err_sales (
cmdtime timestamptz, relname text, filename text, linenum int, bytenum int, errmsg text, rawdata text, rawbytes bytea )
DISTRIBUTED RANDOMLY;</pre>
</div></div><p>Copy data from a file into the <em>sales </em><span style="font-size: medium;"> </span>table using single row error isolation mode:</p><div class="code panel pdl" style="border-width: 1px;"><div class="codeContent panelContent pdl">
<pre class="theme: Confluence; brush: sql; gutter: false" style="font-size:12px;">COPY sales FROM '/home/usr1/sql/sales_data' LOG ERRORS INTO err_sales SEGMENT REJECT LIMIT 10 ROWS;</pre>
</div></div><h3 id="SQLCommandReference-Compatibility.9">Compatibility</h3><p align="LEFT">There is no COPY statement in the SQL standard.</p><h3 id="SQLCommandReference-SeeAlso.7">See Also</h3><pre>CREATE EXTERNAL TABLE</pre><h2 id="SQLCommandReference-CREATEAGGREGATE">CREATE AGGREGATE</h2><p>Defines a new aggregate function.</p><h3 id="SQLCommandReference-Synopsis.9">Synopsis</h3><div class="code panel pdl" style="border-width: 1px;"><div class="codeContent panelContent pdl">
<pre class="theme: Confluence; brush: sql; gutter: false" style="font-size:12px;">CREATE [ORDERED] AGGREGATE name (input_data_type [ , ... ])
( SFUNC = sfunc,
STYPE = state_data_type
[, PREFUNC = prefunc]
[, FINALFUNC = ffunc]
[, INITCOND = initial_condition]
[, SORTOP = sort_operator] )</pre>
</div></div><p><span style="font-size: medium;"> </span></p><h3 id="SQLCommandReference-Description.10">Description</h3><p>CREATE AGGREGATE defines a new aggregate function. Some basic and commonly-used aggregate functions such as count, min, max, sum, avg and so on are already provided in HAWQ. If one defines new types or needs an aggregate function not already provided, then CREATE AGGREGATE can be used to provide the desired features.</p><p>An aggregate function is identified by its name and input data type(s). Two aggregates in the same schema can have the same name if they operate on different input types. The name and input data type(s) of an aggregate must also be distinct from the name and input data type(s) of every ordinary function in the same schema.</p><p>An aggregate function is made from one, two or three ordinary functions (all of which must be IMMUTABLE functions):</p><ul><li>a state transition function <em>sfunc</em><span style="font-size: small;"> </span></li><li><span style="font-size: small;"> </span>an optional preliminary segment-level calculation function <em>prefunc</em><span style="font-size: small;"> </span></li><li><span style="font-size: small;"> </span>an optional final calculation function <em>ffunc</em><span style="font-size: small;"> </span></li></ul><p>These are used as follows:</p><div class="code panel pdl" style="border-width: 1px;"><div class="codeContent panelContent pdl">
<pre class="theme: Confluence; brush: sql; gutter: false" style="font-size:12px;">sfunc( internal-state, next-data-values ) ---> next-internal-state
prefunc( internal-state, internal-state ) ---> next-internal-state
ffunc( internal-state ) ---> aggregate-value</pre>
</div></div><p align="LEFT">You can specify PREFUNC as method for optimizing aggregate execution. By specifying PREFUNC, the aggregate can be executed in parallel on segments first and then on the master. When a two-level execution is performed, SFUNC is executed on the segments to generate partial aggregate results, and PREFUNC is executed on the master to aggregate the partial results from segments. If single-level aggregation is performed, all the rows are sent to the master and sfunc is applied to the rows.</p><p>Single-level aggregation and two-level aggregation are equivalent execution strategies. Either type of aggregation can be implemented in a query plan. When you implement the functions prefunc and sfunc, you must ensure that the invocation of sfunc on the segment instances followed by prefunc on the master produce the same result as single-level aggregation that sends all the rows to the master and then applies only the sfunc to the rows.</p><p>HAWQ creates a temporary variable of data type <em>stype </em><span style="font-size: small;"> </span>to hold the current internal state of the aggregate function. At each input row, the aggregate argument values are calculated and the state transition function is invoked with the current state value and the new argument values to calculate a new internal state value. After all the rows have been processed, the final function is invoked once to calculate the aggregate return value. If there is no final function then the ending state value is returned as-is.</p><p>An aggregate function can provide an optional initial condition, an initial value for the internal state value. This is specified and stored in the database as a value of type text, but it must be a valid external representation of a constant of the state value data type. If it is not supplied then the state value starts out NULL.</p><p>If the state transition function is declared STRICT, then it cannot be called with NULL inputs. With such a transition function, aggregate execution behaves as follows. Rows with any null input values are ignored (the function is not called and the previous state value is retained). If the initial state value is NULL, then at the first row with all non-null input values, the first argument value replaces the state value, and the transition function is invoked at subsequent rows with all non-null input values. This is useful for implementing aggregates like max. Note that this behavior is only available when <em>state_data_type </em><span style="font-size: small;"> </span>is the same as the first <em>input_data_type</em><span style="font-size: small;"> </span>. When these types are different, you must supply a non-null initial condition or use a nonstrict transition function.</p><p>If the state transition function is not declared STRICT, then it will be called unconditionally at each input row, and must deal with NULL inputs and NULL transition values for itself. This allows the aggregate author to have full control over the aggregate handling of NULL values.</p><p>If the final function is declared STRICT, then it will not be called when the ending state value is NULL; instead a NULL result will be returned automatically. (This is the normal behavior of STRICT functions.) In any case the final function has the option of returning a NULL value. For example, the final function for avg returns NULL when it sees there were zero input rows.</p><p>Single argument aggregate functions, such as min or max, can sometimes be optimized by looking into an index instead of scanning every input row. If this aggregate can be so optimized, indicate it by specifying a sort operator. The basic requirement is that the aggregate must yield the first element in the sort ordering induced by the operator; in other words</p><p><span style="font-size: medium;"><span style="font-size: medium;"> </span></span></p><div class="code panel pdl" style="border-width: 1px;"><div class="codeContent panelContent pdl">
<pre class="theme: Confluence; brush: scala; gutter: false" style="font-size:12px;">SELECT agg(col) FROM tab; </pre>
</div></div><p>must be equivalent to:</p><div class="code panel pdl" style="border-width: 1px;"><div class="codeContent panelContent pdl">
<pre class="theme: Confluence; brush: sql; gutter: false" style="font-size:12px;">SELECT col FROM tab ORDER BY col USING sortop LIMIT 1;</pre>
</div></div><p>Further assumptions are that the aggregate ignores null inputs, and that it delivers a null result if and only if there were no non-null inputs. Ordinarily, a data type’s < operator is the proper sort operator for MIN, and > is the proper sort operator for MAX. Note that the optimization will never actually take effect unless the specified operator is the "less than" or "greater than" strategy member of a B-tree index operator class.</p><h4 id="SQLCommandReference-OrderedAggregates">Ordered Aggregates</h4><p>If the optional qualification ORDERED appears, the created aggregate function is an <em>ordered aggregate</em><span style="font-size: medium;"> </span>. In this case, the preliminary aggregation function, prefunc cannot be specified.</p><p>An ordered aggregate is called with the following syntax.</p><div class="code panel pdl" style="border-width: 1px;"><div class="codeContent panelContent pdl">
<pre class="theme: Confluence; brush: sql; gutter: false" style="font-size:12px;">name ( arg [ , ... ] [ORDER BY sortspec [ , ...]] )</pre>
</div></div><p>If the optional ORDER BY is omitted, a system-defined ordering is used. The transition function of an ordered aggregate sfunc is called on its input arguments in the specified order and on a single segment. There is a new column aggordered in the pg_aggregate table to indicate the aggregate function is defined as an ordered aggregate.<span style="font-size: medium;"> </span></p><h3 id="SQLCommandReference-Parameters.8">Parameters</h3><pre>name</pre><p style="margin-left: 30.0px;">The name (optionally schema-qualified) of the aggregate function to create.</p><pre>input_data_type</pre><p style="margin-left: 30.0px;">An input data type on which this aggregate function operates. To create a zero-argument aggregate function, write * in place of the list of input data types. An example of such an aggregate is count(*).</p><pre>sfunc</pre><p style="margin-left: 30.0px;">The name of the state transition function to be called for each input row. For an N-argument aggregate function, the <em>sfunc </em><span style="font-size: small;"> </span>must take N+1 arguments, the first being of type <em>state_data_type </em><span style="font-size: small;"> </span>and the rest matching the declared input data type(s) of the aggregate. The function must return a value of type <em>state_data_type</em><span style="font-size: small;"> </span>. This function takes the current state value and the current input data value(s), and returns the next state value.</p><pre>state_data_type</pre><p style="margin-left: 30.0px;">The data type for the aggregate’s state value.</p><pre>prefunc</pre><p style="margin-left: 30.0px;">The name of a preliminary aggregation function. This is a function of two arguments, both of type <em>state_data_type</em><span style="font-size: small;"> </span>. It must return a value of <em>state_data_type</em><span style="font-size: small;"> </span>. A preliminary function takes two transition state values and returns a new transition state value representing the combined aggregation. In HAWQ, if the result of the aggregate function is computed in a segmented fashion, the preliminary aggregation function is invoked on the individual internal states in order to combine them into an ending internal state.</p><p style="margin-left: 30.0px;">Note that this function is also called in hash aggregate mode within a segment. Therefore if you call this aggregate function without a preliminary function, hash aggregate is never chosen. Since hash aggregate is efficient, consider defining preliminary function whenever possible.</p><p style="margin-left: 30.0px;">PREFUNC is optional. If defined, it is executed on master. Input to PREFUNC is partial results from segments, and not the tuples. If PREFUNC is not defined, the aggregate cannot be executed in parallel. PREFUNC and gp_enable_multiphase_agg are used as follows:</p><ul><li style="margin-left: 30.0px;">gp_enable_multiphase_agg = off: SFUNC is executed sequentially on master. PREFUNC, even if defined, is unused.</li><li style="margin-left: 30.0px;"><p>gp_enable_multiphase_agg = on and PREFUNC is defined: SFUNC is executed in parallel, on segments. PREFUNC is invoked on master to aggregate partial results from segments. </p><div class="code panel pdl" style="border-width: 1px;"><div class="codeContent panelContent pdl">
<pre class="theme: Confluence; brush: sql; gutter: false" style="font-size:12px;">CREATE OR REPLACE FUNCTION my_avg_accum(bytea,bigint) returns bytea as 'int8_avg_accum' language internal strict immutable;
CREATE OR REPLACE FUNCTION my_avg_merge(bytea,bytea) returns bytea as 'int8_avg_amalg' language internal strict immutable;
CREATE OR REPLACE FUNCTION my_avg_final(bytea) returns numeric as 'int8_avg' language internal strict immutable;
CREATE AGGREGATE my_avg(bigint) ( stype = bytea,sfunc = my_avg_accum,prefunc = my_avg_merge,finalfunc = my_avg_final,initcond = '' );</pre>
</div></div></li></ul><pre>ffunc</pre><p style="margin-left: 30.0px;">The name of the final function called to compute the aggregate’s result after all input rows have been traversed. The function must take a single argument of type state_data_type. The return data type of the aggregate is defined as the return type of this function. If ffunc is not specified, then the ending state value is used as the aggregate's result, and the return type is state_data_type.</p><pre>initial_condition</pre><p style="margin-left: 30.0px;">The initial setting for the state value. This must be a string constant in the form accepted for the data type <em>state_data_type</em><span style="font-size: medium;"> </span>. If not specified, the state value starts out null.</p><pre>sort_operator</pre><p style="margin-left: 30.0px;">The associated sort operator for a MIN- or MAX-like aggregate. This is just an operator name (possibly schema-qualified). The operator is assumed to have the same input data types as the aggregate (which must be a single-argument aggregate).</p><h3 id="SQLCommandReference-Notes.8">Notes</h3><p>The ordinary functions used to define a new aggregate function must be defined first. Note that in this release of HAWQ, it is required that the <em>sfunc</em><span style="font-size: small;"> </span>, <em>ffunc</em><span style="font-size: small;"> </span>, and <em>prefunc </em><span style="font-size: small;"> </span>functions used to create the aggregate are defined as IMMUTABLE.</p><p>Any compiled code (shared library files) for custom functions must be placed in the same location on every host in your HAWQ array (master and all segments). This location must also be in the LD_LIBRARY_PATH so that the server can locate the files.</p><h3 id="SQLCommandReference-Examples.7">Examples</h3><p>Create a sum of cubes aggregate:</p><div class="code panel pdl" style="border-width: 1px;"><div class="codeContent panelContent pdl">
<pre class="theme: Confluence; brush: sql; gutter: false" style="font-size:12px;">CREATE FUNCTION scube_accum(numeric, numeric) RETURNS numeric
AS 'select $1 + $2 * $2 * $2'
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;
CREATE AGGREGATE scube(numeric) (
SFUNC = scube_accum,
STYPE = numeric,
INITCOND = 0 );</pre>
</div></div><p>To test this aggregate:</p><div class="code panel pdl" style="border-width: 1px;"><div class="codeContent panelContent pdl">
<pre class="theme: Confluence; brush: actionscript3; gutter: false" style="font-size:12px;">CREATE TABLE x(a INT);
INSERT INTO x VALUES (1),(2),(3);
SELECT scube(a) FROM x;</pre>
</div></div><p>Correct answer for reference:</p><div class="code panel pdl" style="border-width: 1px;"><div class="codeContent panelContent pdl">
<pre class="theme: Confluence; brush: sql; gutter: false" style="font-size:12px;">SELECT sum(a*a*a) FROM x;</pre>
</div></div><h3 id="SQLCommandReference-Compatibility.10">Compatibility</h3><p>CREATE AGGREGATE is a HAWQ language extension. The SQL standard does not provide for user-defined aggregate functions.</p><h3 id="SQLCommandReference-SeeAlso.8">See Also</h3><p>ALTER AGGREGATE, DROP AGGREGATE, CREATE FUNCTION<span style="font-size: medium;"><span style="font-size: medium;"><span style="font-size: medium;"> </span></span></span></p><h2 id="SQLCommandReference-CREATEDATABASE">CREATE DATABASE</h2><p><span style="color: rgb(112,255,0);"> </span>Creates a new database.</p><div class="code panel pdl" style="border-width: 1px;"><div class="codeContent panelContent pdl">
<pre class="theme: Confluence; brush: sql; gutter: false" style="font-size:12px;">CREATE DATABASE name [ [WITH] [OWNER [=] dbowner]
[TEMPLATE [=] template]
[ENCODING [=] encoding]
[TABLESPACE [=] tablespace]
[CONNECTION LIMIT [=] connlimit ] ]</pre>
</div></div><h3 id="SQLCommandReference-Description.11">Description</h3><p align="LEFT">CREATE DATABASE creates a new database. To create a database, you must be a superuser or have the special CREATEDB privilege.</p><p align="LEFT">The creator becomes the owner of the new database by default. Superusers can create databases owned by other users by using the OWNER clause. They can even create databases owned by users with no special privileges. Non-superusers with CREATEDB privilege can only create databases owned by themselves.</p><p align="LEFT">By default, the new database will be created by cloning the standard system database template1. A different template can be specified by writing TEMPLATE <em>name</em>. In particular, by writing TEMPLATE template0, you can create a clean database containing only the standard objects predefined by HAWQ. This is useful if you wish to avoid copying any installation-local objects that may have been added to <em>template1</em>.</p><h3 id="SQLCommandReference-Parameters.9">Parameters</h3><pre>name</pre><p align="LEFT" style="margin-left: 30.0px;">The name of a database to create.</p><pre>dbowner</pre><p align="LEFT" style="margin-left: 30.0px;">The name of the database user who will own the new database, or DEFAULT to use the default owner (the user executing the command).</p><pre>template</pre><p align="LEFT" style="margin-left: 30.0px;">The name of the template from which to create the new database, or DEFAULT to use the default template (<em>template1</em><span> </span>).</p><pre>encoding</pre><p align="LEFT" style="margin-left: 30.0px;">Character set encoding to use in the new database. Specify a string constant (such as 'SQL_ASCII'), an integer encoding number, or DEFAULT to use the default encoding.</p><pre>tablespace</pre><p align="LEFT" style="margin-left: 30.0px;">The name of the tablespace that will be associated with the new database, or DEFAULT to use the template database’s tablespace. This tablespace will be the default tablespace used for objects created in this database.</p><pre>connlimit</pre><p style="margin-left: 30.0px;">The maximum number of concurrent connections posible. The default of -1 means there is no limitation.</p><h3 id="SQLCommandReference-Notes.9">Notes</h3><p>CREATE DATABASE cannot be executed inside a transaction block.</p><p align="LEFT">When you copy a database by specifying its name as the template, no other sessions can be connected to the template database while it is being copied. New connections to the template database are locked out until CREATE DATABASE completes.</p><p>The CONNECTION LIMIT is not enforced against superusers.</p><h3 id="SQLCommandReference-Examples.8">Examples</h3><p>To create a new database:</p><div class="code panel pdl" style="border-width: 1px;"><div class="codeContent panelContent pdl">
<pre class="theme: Confluence; brush: java; gutter: false" style="font-size:12px;">CREATE DATABASE gpdb;</pre>
</div></div><p align="LEFT">To create a database <em>sales </em><span> </span>owned by user <em>salesapp </em><span> </span>with a default tablespace of salesspace:</p><div class="code panel pdl" style="border-width: 1px;"><div class="codeContent panelContent pdl">
<pre class="theme: Confluence; brush: java; gutter: false" style="font-size:12px;">CREATE DATABASE sales OWNER salesapp TABLESPACE salesspace;</pre>
</div></div><p align="LEFT">To create a database <em>music </em><span> </span>which supports the ISO-8859-1 character set:</p><div class="code panel pdl" style="border-width: 1px;"><div class="codeContent panelContent pdl">
<pre class="theme: Confluence; brush: java; gutter: false" style="font-size:12px;">CREATE DATABASE music ENCODING 'LATIN1';</pre>
</div></div><h3 id="SQLCommandReference-Compatibility.11">Compatibility</h3><p>There is no CREATE DATABASE statement in the SQL standard. Databases are equivalent to catalogs, whose creation is implementation-defined.</p><h3 id="SQLCommandReference-SeeAlso.9">See Also</h3><pre>DROP DATABASE</pre><h2 id="SQLCommandReference-CREATEEXTERNALTABLE">CREATE EXTERNAL TABLE</h2><p>Defines a new external table.</p><h3 id="SQLCommandReference-Synopsis.10">Synopsis</h3><div class="code panel pdl" style="border-width: 1px;"><div class="codeContent panelContent pdl">
<pre class="theme: Confluence; brush: sql; gutter: false" style="font-size:12px;">CREATE [READABLE] EXTERNAL TABLE table_name
( column_name data_type [, ...] | LIKE other_table )
LOCATION ('file://seghost[:port]/path/file' [, ...])
| ('gpfdist://filehost[:port]/file_pattern[#transform]'
| ('gpfdists://filehost[:port]/file_pattern[#transform]'
[, ...])
| ('gphdfs://hdfs_host[:port]/path/file')
FORMAT 'TEXT'
[( [HEADER]
[DELIMITER [AS] 'delimiter' | 'OFF']
[NULL [AS] 'null string']
[ESCAPE [AS] 'escape' | 'OFF']
[NEWLINE [ AS ] 'LF' | 'CR' | 'CRLF']
[FILL MISSING FIELDS] )]
| 'CSV'
[( [HEADER]
[QUOTE [AS] 'quote']
[DELIMITER [AS] 'delimiter']
[NULL [AS] 'null string']
[FORCE NOT NULL column [, ...]]
[ESCAPE [AS] 'escape']
[NEWLINE [ AS ] 'LF' | 'CR' | 'CRLF']
[FILL MISSING FIELDS] )]
| 'CUSTOM' (Formatter=<formatter specifications>) [ ENCODING 'encoding' ]
[ [LOG ERRORS INTO error_table] SEGMENT REJECT LIMIT count
[ROWS | PERCENT] ]
CREATE [READABLE] EXTERNAL WEB TABLE table_name
( column_name data_type [, ...] | LIKE other_table )
LOCATION ('http://webhost[:port]/path/file' [, ...])
| EXECUTE 'command' [ON ALL
| MASTER
| number_of_segments
| HOST ['segment_hostname']
| SEGMENT segment_id ]
FORMAT 'TEXT'
[( [HEADER]
[DELIMITER [AS] 'delimiter' | 'OFF']
[NULL [AS] 'null string']
[ESCAPE [AS] 'escape' | 'OFF']
[NEWLINE [ AS ] 'LF' | 'CR' | 'CRLF']
[FILL MISSING FIELDS] )]
| 'CSV'
[( [HEADER]
[QUOTE [AS] 'quote']
[DELIMITER [AS] 'delimiter']
[NULL [AS] 'null string']
[FORCE NOT NULL column [, ...]]
[ESCAPE [AS] 'escape']
[NEWLINE [ AS ] 'LF' | 'CR' | 'CRLF']
[FILL MISSING FIELDS] )]
| 'CUSTOM' (Formatter=<formatter specifications>) [ ENCODING 'encoding' ]
[ [LOG ERRORS INTO error_table] SEGMENT REJECT LIMIT count
[ROWS | PERCENT] ]
CREATE WRITABLE EXTERNAL TABLE table_name
( column_name data_type [, ...] | LIKE other_table )
LOCATION('gpfdist://outputhost[:port]/filename[#transform]'
| ('gpfdists://outputhost[:port]/file_pattern[#transform]' [, ...])
| ('gphdfs://hdfs_host[:port]/path') FORMAT 'TEXT'
[( [DELIMITER [AS] 'delimiter']
[NULL [AS] 'null string']
[ESCAPE [AS] 'escape' | 'OFF'] )]
| 'CSV'
[([QUOTE [AS] 'quote']
[DELIMITER [AS] 'delimiter']
[NULL [AS] 'null string']
[FORCE QUOTE column [, ...]] ]
[ESCAPE [AS] 'escape'] )]
| 'CUSTOM' (Formatter=<formatter specifications>) [ ENCODING 'write_encoding' ]
[ DISTRIBUTED BY (column, [ ... ] ) | DISTRIBUTED RANDOMLY ]
CREATE WRITABLE EXTERNAL WEB TABLE table_name
( column_name data_type [, ...] | LIKE other_table )
EXECUTE 'command' [ON ALL]
FORMAT 'TEXT'
[( [DELIMITER [AS] 'delimiter']
[NULL [AS] 'null string']
[ESCAPE [AS] 'escape' | 'OFF'] )]
| 'CSV'
[([QUOTE [AS] 'quote']
[DELIMITER [AS] 'delimiter']
[NULL [AS] 'null string']
[FORCE QUOTE column [, ...]] ]
[ESCAPE [AS] 'escape'] )]
| 'CUSTOM' (Formatter=<formatter specifications>) [ ENCODING 'write_encoding' ]
[ DISTRIBUTED BY (column, [ ... ] ) | DISTRIBUTED RANDOMLY ]</pre>
</div></div><h3 id="SQLCommandReference-Description.12">Description</h3><p align="LEFT">CREATE EXTERNAL TABLE or CREATE EXTERNAL WEB TABLE creates a new readable external table definition in HAWQ. Readable external tables are typically used for fast, parallel data loading. Once an external table is defined, you can query its data directly (and in parallel) using SQL commands. For example, you can select, join, or sort external table data. You can also create views for external tables. DML operations (UPDATE, INSERT, DELETE, or TRUNCATE) are not allowed on readable external tables.</p><p align="LEFT">CREATE WRITABLE EXTERNAL TABLE or CREATE WRITABLE EXTERNAL WEB TABLE creates a new writable external table definition in HAWQ. Writable external tables are typically used for unloading data from the database into a set of files or named pipes.</p><p align="LEFT">Writable external web tables can also be used to output data to an executable program. Once a writable external table is defined, data can be selected from database tables and inserted into the writable external table. Writable external tables only allow INSERT operations – SELECT, UPDATE, DELETE or TRUNCATE are not allowed.</p><p align="LEFT">The main difference between regular external tables and web external tables is their data sources. Regular readable external tables access static flat files, whereas web external tables access dynamic data sources – either on a web server or by executing OS commands or scripts.</p><p align="LEFT">The FORMAT clause is used to describe how the external table files are formatted. Valid file formats are delimited text (TEXT) for all protocols and comma separated values (CSV) format for gpfdist and file protocols, similar to the formatting options available with the PostgreSQL COPY command. If the data in the file does not use the default column delimiter, escape character, null string and so on, you must specify the additional formatting options so that the data in the external file is read correctly by HAWQ.</p><h3 id="SQLCommandReference-Parameters.10">Parameters</h3><pre>READABLE | WRITABLE</pre><p align="LEFT" style="margin-left: 30.0px;">Specifiies the type of external table, readable being the default. Readable external tables are used for loading data into HAWQ. Writable external tables are used for unloading data.</p><pre>WEB</pre><p align="LEFT" style="margin-left: 30.0px;">Creates a readable or wrtiable web external table definition in HAWQ. There are two forms of readable web external tables – those that access files via the http:// protocol or those that access data by executing OS commands. Writable web external tables output data to an executable program that can accept an input stream of data. Web external tables are not rescannable during query execution.</p><pre>table_name</pre><p align="LEFT" style="margin-left: 30.0px;">The name of the new external table.</p><pre>column_name</pre><p align="LEFT" style="margin-left: 30.0px;">The name of a column to create in the external table definition. Unlike regular tables, external tables do not have column constraints or default values, so do not specify those.</p><pre>LIKE <em>other_table</em></pre><p align="LEFT" style="margin-left: 30.0px;">The LIKE clause specifies a table from which the new external table automatically copies all column names, data types and HAWQ distribution policy. If the original table specifies any column constraints or default column values, those will not be copied over to the new external table definition.</p><pre>data_type</pre><p align="LEFT" style="margin-left: 30.0px;">The data type of the column.</p><pre>LOCATION ('<em>protocol</em><span style="font-size: medium;"> </span>://<em>host</em><span style="font-size: medium;"> </span>[:<em>port</em><span style="font-size: medium;"> </span>]/<em>path</em><span style="font-size: medium;"> </span>/<em>file</em><span style="font-size: medium;"> </span>' [, ...])</pre><p align="LEFT" style="margin-left: 30.0px;">For readable external tables, specifies the URI of the external data source(s) to be used to populate the external table or web table. Regular readable external tables allow the gpfdist or file protocols. Web external tables allow the http protocol. If port is omitted, port 8080 is assumed for http and gpfdist protocols. If using the gpfdist protocol, the path is relative to the directory from which gpfdist is serving files (the directory specified when you started the gpfdist program). Also, gpfdist can use wildcards (or other C-style pattern matching) to denote multiple files in a directory. For example:</p><div class="code panel pdl" style="border-width: 1px;"><div class="codeContent panelContent pdl">
<pre class="theme: Confluence; brush: java; gutter: false" style="font-size:12px;">'gpfdist://filehost:8081/*''gpfdist://masterhost/my_load_file''file://seghost1/dbfast1/external/myfile.txt''http://intranet.mycompany.com/finance/expenses.csv'</pre>
</div></div><p> </p><p align="LEFT" style="margin-left: 30.0px;">For writable external tables, specifies the URI location of the gpfdist process that will collect data output from the HAWQ segments and write it to the named file. The path is relative to the directory from which gpfdist is serving files (the directory specified when you started the gpfdist program). If multiple gpfdist locations are listed, the segments sending data will be evenly divided across the available output locations. For example:</p><div class="code panel pdl" style="border-width: 1px;"><div class="codeContent panelContent pdl">
<pre class="theme: Confluence; brush: java; gutter: false" style="font-size:12px;">'gpfdist://outputhost:8081/data1.out','gpfdist://outputhost:8081/data2.out'</pre>
</div></div><p align="LEFT" style="margin-left: 30.0px;"> </p><p align="LEFT" style="margin-left: 30.0px;">With two gpfdist locations listed as in the above example, half of the segments would send their output data to the data1.out file and the other half to the data2.out file.</p><pre>EXECUTE 'command<span> </span>' [ON ...]</pre><p align="LEFT" style="margin-left: 30.0px;">Allowed for readable web external tables or writable external tables only. For readable web external tables, specifies the OS command to be executed by the segment instances. The <em>command </em><span style="font-size: medium;"> </span>can be a single OS command or a script. The ON clause is used to specify which segment instances will execute the given command.</p><ul><ul><li><strong>ON ALL</strong> is the default. The command will be executed by every active (primary) segment instance on all segment hosts in the HAWQ system. If the command executes a script, that script must reside in the same location on all of the segment hosts and be executable by the HAWQ superuser (gpadmin).</li><li><strong>ON MASTER</strong> runs the command on the master host only.</li><li><strong>ON</strong> <em>number </em>means the command will be executed by the specified number of segments. The particular segments are chosen randomly at runtime by the HAWQ system. If the command executes a script, that script must reside in the same location on all of the segment hosts and be executable by the HAWQ superuser (gpadmin).</li><li><strong>HOST</strong> means the command will be executed by one segment on each segment host (once per segment host), regardless of the number of active segment instances per host.</li><li><strong>HOST</strong> <em>segment_hostname </em>means the command will be executed by all active (primary) segment instances on the specified segment host.</li><li><strong>SEGMENT</strong> <em>segment_id </em>means the command will be executed only once by the specified segment. The <em>content </em><span style="font-size: medium;"> </span>ID of the HAWQ master is always -1.</li></ul></ul><p align="LEFT" style="margin-left: 30.0px;">For writable external tables, the <em>command </em><span style="font-size: small;"> </span>specified in the EXECUTE clause must be prepared to have data piped into it. Since all segments that have data to send will write their output to the specified command or program, the only available option for the ON clause is ON ALL.</p><pre>FORMAT 'TEXT | CSV' (<em>options</em><span style="font-size: medium;"> </span>)</pre><p align="LEFT" style="margin-left: 30.0px;">Specifies the format of the external or web table data - either plain text (TEXT) or comma separated values (CSV) format.</p><pre>DELIMITER</pre><p align="LEFT" style="margin-left: 30.0px;">Specifies a single ASCII character that separates columns within each row (line) of data. The default is a tab character in TEXT mode, a comma in CSV mode. In TEXT mode for readable external tables, the delimiter can be set to OFF for special use cases in which unstructured data is loaded into a single-column table.</p><pre>NULL</pre><p align="LEFT" style="margin-left: 30.0px;">Specifies the string that represents a null value. The default is \N (backslash-N) in TEXT mode, and an empty value with no quotations in CSV mode. You might prefer an empty string even in TEXT mode for cases where you do not want to distinguish nulls from empty strings. When using external and web tables, any data item that matches this string will be considered a null value.</p><pre>ESCAPE</pre><p align="LEFT" style="margin-left: 30.0px;">Specifies the single character that is used for C escape sequences (such as \n,\t,\100, and so on) and for escaping data characters that might otherwise be taken as row or column delimiters. Make sure to choose an escape character that is not used anywhere in your actual column data. The default escape character is a \(backslash) for text-formatted files and a " (double quote) for csv-formatted files, however it is possible to specify another character to represent an escape. It is also possible to disable escaping in text-formatted files by specifying the value 'OFF' as the escape value. This is very useful for data such as text-formatted web log data that has many embedded backslashes that are not intended to be escapes.</p><pre>NEWLINE</pre><p align="LEFT" style="margin-left: 30.0px;">Specifies the newline used in your data files – LF (Line feed, 0x0A), CR (Carriage return, 0x0D), or CRLF (Carriage return plus line feed, 0x0D 0x0A). If not specified, a HAWQ segment will detect the newline type by looking at the first row of data it receives and using the first newline type encountered.</p><pre>HEADER</pre><p align="LEFT" style="margin-left: 30.0px;">For readable external tables, specifies that the first line in the data file(s) is a header row (contains the names of the table columns) and should not be included as data for the table. If using multiple data source files, all files must have a header row.</p><pre>QUOTE</pre><p align="LEFT" style="margin-left: 30.0px;">Specifies the quotation character for CSV mode. The default is double-quote (").</p><pre>FORCE NOT NULL</pre><p align="LEFT" style="margin-left: 30.0px;">In CSV mode, processes each specified column as though it were quoted and hence not a NULL value. For the default null string in CSV mode (nothing between two delimiters), this causes missing values to be evaluated as zero-length strings.</p><pre>FORCE QUOTE</pre><p align="LEFT" style="margin-left: 30.0px;">In CSV mode for writable external tables, forces quoting to be used for all non-NULL values in each specified column. NULL output is never quoted.</p><pre>FILL MISSING FIELDS</pre><p align="LEFT" style="margin-left: 30.0px;">In both TEXT and CSV mode for readable external tables, specifying FILL MISSING FIELDS will set missing trailing field values to NULL (instead of reporting an error) when a row of data has missing data fields at the end of a line or row. Blank rows, fields with a NOT NULL constraint, and trailing delimiters on a line will still report an error.</p><pre>ENCODING 'encoding<span> </span>'</pre><p align="LEFT" style="margin-left: 30.0px;">Character set encoding to use for the external table. Specify a string constant (such as'SQL_ASCII'), an integer encoding number, or DEFAULT to use the default client encoding.</p><pre>LOG ERRORS INTO error_table</pre><p align="LEFT" style="margin-left: 30.0px;">This is an optional clause that may precede a SEGMENT REJECT LIMIT clause. It specifies an error table where rows with formatting errors will be logged when running in single row error isolation mode. You can then examine this error table to see error rows that were not loaded (if any). If the error_table <span> </span>specified already exists, it will be used. If it does not exist, it will be automatically generated.</p><pre>SEGMENT REJECT LIMIT count <span> </span>[ROWS | PERCENT]</pre><p align="LEFT" style="margin-left: 30.0px;">Runs a COPY FROM operation in single row error isolation mode. If the input rows have format errors they will be discarded provided that the reject limit count is not reached on any HAWQ segment instance during the load operation. The reject limit count can be specified as number of rows (the default) or percentage of total rows (1-100). If PERCENT is used, each segment starts calculating the bad row percentage only after the number of rows specified by the parameter gp_reject_percent_threshold has been processed. The default for gp_reject_percent_threshold is 300 rows. Constraint errors such as violation of a NOT NULL, CHECK, or UNIQUE constraint will still be handled in "all-or-nothing" input mode. If the limit is not reached, all good rows will be loaded and any error rows discarded</p><p align="LEFT" style="margin-left: 30.0px;"><span style="font-size: medium;"><span style="font-size: medium;"> </span> </span></p><pre>DISTRIBUTED BY (<em>column</em><span style="font-size: medium;"> </span>, [ ... ] )<br/>DISTRIBUTED RANDOMLY</pre><p align="LEFT" style="margin-left: 30.0px;">Used to declare the HAWQ distribution policy for a writable external table. By default, writable external tables are distributed randomly. If the source table you are exporting data from has a hash distribution policy, defining the same distribution key column(s) for the writable external table will improve unload performance by eliminating the need to move rows over the interconnect. When you issue an unload command such as INSERT INTO <em>wex_table </em><span style="font-size: small;"> </span>SELECT * FROM <em>source_table</em><span style="font-size: small;"> </span>, the rows that are unloaded can be sent directly from the segments to the output location if the two tables have the same hash distribution policy.</p><h3 id="SQLCommandReference-Examples.9">Examples</h3><p align="LEFT">Start the gpfdist file server program in the background on port <em>8081 </em><span style="font-size: medium;"> </span>serving files from directory <em>/var/data/staging</em><span style="font-size: medium;"> </span>:</p><div class="code panel pdl" style="border-width: 1px;"><div class="codeContent panelContent pdl">
<pre class="theme: Confluence; brush: java; gutter: false" style="font-size:12px;">gpfdist -p 8081 -d /var/data/staging -l /home/gpadmin/log &</pre>
</div></div><p> </p><p align="LEFT">Create a readable external table named <em>ext_customer </em><span style="font-size: medium;"> </span>using the gpfdist protocol and any text formatted files (*.txt) found in the gpfdist directory. The files are formatted with a pipe (|) as the column delimiter and an empty space as null. Also access the external table in single row error isolation mode:</p><div class="code panel pdl" style="border-width: 1px;"><div class="codeContent panelContent pdl">
<pre class="theme: Confluence; brush: java; gutter: false" style="font-size:12px;">CREATE EXTERNAL TABLE ext_customer
id int, name text, sponsor text)
LOCATION ( 'gpfdist://filehost:8081/*.txt' )
FORMAT 'TEXT' ( DELIMITER '|' NULL ' ')</pre>
</div></div><p> </p><p align="LEFT">Create the same readable external table definition as above, but with CSV formatted files:</p><div class="code panel pdl" style="border-width: 1px;"><div class="codeContent panelContent pdl">
<pre class="theme: Confluence; brush: java; gutter: false" style="font-size:12px;">CREATE EXTERNAL TABLE ext_customer(
id int, name text, sponsor text)
LOCATION ( 'gpfdist://filehost:8081/*.csv' )
FORMAT 'CSV' ( DELIMITER ',' );</pre>
</div></div><p align="LEFT">Create a readable external table named <em>ext_expenses </em><span style="font-size: medium;"> </span>using the file protocol and several CSV formatted files that have a header row:</p><div class="code panel pdl" style="border-width: 1px;"><div class="codeContent panelContent pdl">
<pre class="theme: Confluence; brush: java; gutter: false" style="font-size:12px;">CREATE EXTERNAL TABLE ext_expenses (name text, date date, amount float4, category text, description text)
LOCATION (
'file://seghost1/dbfast/external/expenses1.csv',
'file://seghost1/dbfast/external/expenses2.csv',
'file://seghost2/dbfast/external/expenses3.csv',
'file://seghost2/dbfast/external/expenses4.csv',
'file://seghost3/dbfast/external/expenses5.csv',
'file://seghost3/dbfast/external/expenses6.csv'
)
FORMAT 'CSV' ( HEADER );</pre>
</div></div><p> Create a readable web external table that executes a script once per segment host:</p><div class="code panel pdl" style="border-width: 1px;"><div class="codeContent panelContent pdl">
<pre class="theme: Confluence; brush: java; gutter: false" style="font-size:12px;">CREATE EXTERNAL WEB TABLE log_output (linenum int, message
text) EXECUTE '/var/load_scripts/get_log_data.sh' ON HOST
FORMAT 'TEXT' (DELIMITER '|');</pre>
</div></div><p align="LEFT">Create a writable external table named <em>sales_out </em><span style="font-size: medium;"> </span>that uses gpfdist to write output data to a file named <em>sales.out</em><span style="font-size: medium;"> </span>. The files are formatted with a pipe (|) as the column delimiter and an empty space as null.</p><div class="code panel pdl" style="border-width: 1px;"><div class="codeContent panelContent pdl">
<pre class="theme: Confluence; brush: java; gutter: false" style="font-size:12px;">CREATE WRITABLE EXTERNAL TABLE sales_out (LIKE sales)
LOCATION ('gpfdist://etl1:8081/sales.out')
FORMAT 'TEXT' ( DELIMITER '|' NULL ' ')
DISTRIBUTED BY (txn_id);</pre>
</div></div><p> Create a writable external web table that pipes output data received by the segments to an executable script named <em>to_adreport_etl.sh</em><span style="font-size: medium;"> </span>:</p><div class="code panel pdl" style="border-width: 1px;"><div class="codeContent panelContent pdl">
<pre class="theme: Confluence; brush: java; gutter: false" style="font-size:12px;">CREATE WRITABLE EXTERNAL WEB TABLE campaign_out (LIKE campaign)
EXECUTE '/var/unload_scripts/to_adreport_etl.sh'
FORMAT 'TEXT' (DELIMITER '|');</pre>
</div></div><p align="LEFT">Use the writable external table defined above to unload selected data:</p><div class="code panel pdl" style="border-width: 1px;"><div class="codeContent panelContent pdl">
<pre class="theme: Confluence; brush: java; gutter: false" style="font-size:12px;">INSERT INTO campaign_out SELECT * FROM campaign WHERE
customer_id=123;</pre>
</div></div><h3 id="SQLCommandReference-Compatibility.12">Compatibility</h3><p align="LEFT">CREATE EXTERNAL TABLE is a HAWQ extension. The SQL standard makes no provisions for external tables.</p><h3 id="SQLCommandReference-SeeAlso.10">See Also</h3><p align="LEFT">CREATE TABLE AS, CREATE TABLE, COPY, SELECT INTO, INSERT</p><h2 id="SQLCommandReference-CREATEFUNCTION">CREATE FUNCTION</h2><p>Defines a new function.</p><h3 id="SQLCommandReference-Synopsis.11">Synopsis</h3><div class="code panel pdl" style="border-width: 1px;"><div class="codeContent panelContent pdl">
<pre class="theme: Confluence; brush: java; gutter: false" style="font-size:12px;">CREATE [OR REPLACE] FUNCTION name
( [ [argmode] [argname] argtype [, ...] ] )
[ RETURNS { [ SETOF ] rettype
| TABLE ([{ argname argtype | LIKE other table }
[, ...]])
} ]
{ LANGUAGE langname
| IMMUTABLE | STABLE | VOLATILE
| CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
| [EXTERNAL] SECURITY INVOKER | [EXTERNAL] SECURITY DEFINER
| AS 'definition'
| AS 'obj_file', 'link_symbol' } ...
[ WITH ({ DESCRIBE = describe_function
} [, ...] ) ]</pre>
</div></div><h3 id="SQLCommandReference-Description.13">Description</h3><p>CREATE FUNCTION defines a new function. CREATE OR REPLACE FUNCTION will either create a new function, or replace an existing definition.</p><p>The name of the new function must not match any existing function with the same argument types in the same schema. However, functions of different argument types may share a name (overloading).</p><p>To update the definition of an existing function, use CREATE OR REPLACE FUNCTION. It is not possible to change the name or argument types of a function this way (this would actually create a new, distinct function). Also, CREATE OR REPLACE FUNCTION will not let you change the return type of an existing function. To do that, you must drop and recreate the function. If you drop and then recreate a function, you will have to drop existing objects (rules, views, triggers, and so on) that refer to the old function. Use CREATE OR REPLACE FUNCTION to change a function definition without breaking objects that refer to the function.</p><p>For more information about creating functions, see the User Defined Functions section of the PostgreSQL documentation.</p><h4 id="SQLCommandReference-LimitedUseofVOLATILEandSTABLEFunctions">Limited Use of VOLATILE and STABLE Functions</h4><p>To prevent data from becoming out-of-sync across the segments in HAWQ, any function classified as STABLE or VOLATILE cannot be executed at the segment level if it contains SQL or modifies the database in any way. For example, functions such as random() or timeofday() are not allowed to execute on distributed data in HAWQ because they could potentially cause inconsistent data between the segment instances.</p><p>To ensure data consistency, VOLATILE and STABLE functions can safely be used in statements that are evaluated on and execute from the master. For example, the following statements are always executed on the master (statements without a FROM clause):</p><div class="code panel pdl" style="border-width: 1px;"><div class="codeContent panelContent pdl">
<pre class="theme: Confluence; brush: java; gutter: false" style="font-size:12px;">SELECT setval('myseq', 201);
SELECT foo();</pre>
</div></div><p>In cases where a statement has a FROM clause containing a distributed table <em>and </em><span style="font-size: medium;"> </span>the function used in the FROM clause simply returns a set of rows, execution may be allowed on the segments:</p><div class="code panel pdl" style="border-width: 1px;"><div class="codeContent panelContent pdl">
<pre class="theme: Confluence; brush: java; gutter: false" style="font-size:12px;">SELECT * FROM foo();</pre>
</div></div><p>One exception to this rule are functions that return a table reference (angeFuncs) or functions that use the refCursor data type. These types of functions cannot be used at all in HAWQ. However, they are not very commonly used anyways.</p><h3 id="SQLCommandReference-Parameters.11">Parameters</h3><pre>name</pre><p style="margin-left: 30.0px;">The name (optionally schema-qualified) of the function to create.</p><pre>argmode</pre><p style="margin-left: 30.0px;">The mode of an argument: either IN, OUT, or INOUT. If omitted, the default is IN.</p><pre>argname</pre><p style="margin-left: 30.0px;"><span style="font-size: medium;"><span style="font-size: medium;"><span style="font-size: medium;"><span style="font-size: medium;"><span style="font-size: medium;"><em><span style="font-size: medium;"> </span></em></span></span></span></span></span>The name of an argument. Some languages (currently only PL/pgSQL) let you use the name in the function body. For other languages the name of an input argument is just extra documentation. But the name of an output argument is significant, since it defines the column name in the result row type. (If you omit the name for an output argument, the system will choose a default column name.)</p><pre>argtype</pre><p style="margin-left: 30.0px;">The data type(s) of the function's arguments (optionally schema-qualified), if any. The argument types may be base, composite, or domain types, or may reference the type of a table column.</p><p style="margin-left: 30.0px;">Depending on the implementation language it may also be allowed to specify pseudotypes such as cstring. Pseudotypes indicate that the actual argument type is either incompletely specified, or outside the set of ordinary SQL data types.</p><p style="margin-left: 30.0px;">The type of a column is referenced by writing <em>tablename</em><span style="font-size: small;"> </span>.<em>columnname</em><span style="font-size: small;"> </span>%TYPE. Using this feature can sometimes help make a function independent of changes to the definition of a table.</p><pre>rettype</pre><p style="margin-left: 30.0px;">The return data type (optionally schema-qualified). The return type can be a base, composite, or domain type, or may reference the type of a table column. Depending on the implementation language it may also be allowed to specify pseudotypes such as cstring. If the function is not supposed to return a value, specify void as the return type.</p><p align="LEFT" style="margin-left: 30.0px;">When there are OUT or INOUT parameters, the RETURNS clause may be omitted. If present, it must agree with the result type implied by the output parameters: RECORD if there are multiple output parameters, or the same type as the single output parameter.</p><p align="LEFT" style="margin-left: 30.0px;">The SETOF modifier indicates that the function will return a set of items, rather than a single item.</p><p style="margin-left: 30.0px;">The type of a column is referenced by writing <em>tablename</em><span style="font-size: small;"> </span>.<em>columnname</em><span style="font-size: small;"> </span>%TYPE.</p><pre>langname</pre><p align="LEFT" style="margin-left: 30.0px;">The name of the language that the function is implemented in. May be SQL, C, internal, or the name of a user-defined procedural language. See CREATE LANGUAGE for the procedural languages supported in HAWQ. For backward compatibility, the name may be enclosed by single quotes.</p><pre>IMMUTABLE<br/>STABLE<br/>VOLATILE</pre><p style="margin-left: 30.0px;">These attributes inform the query optimizer about the behavior of the function. At most one choice may be specified. If none of these appear, VOLATILE is the default assumption. Since HAWQ currently has limited use of VOLATILE functions, if a function is truly IMMUTABLE, you must declare it as so to be able to use it without restrictions.</p><p align="LEFT" style="margin-left: 30.0px;">IMMUTABLE indicates that the function cannot modify the database and always returns the same result when given the same argument values. It does not do database lookups or otherwise use information not directly present in its argument list. If this option is given, any call of the function with all-constant arguments can be immediately replaced with the function value.</p><p align="LEFT" style="margin-left: 30.0px;">STABLE indicates that the function cannot modify the database, and that within a single table scan it will consistently return the same result for the same argument values, but that its result could change across SQL statements. This is the appropriate selection for functions whose results depend on database lookups, parameter values (such as the current time zone), and so on. Also note that the <em>current_timestamp </em><span style="font-size: medium;"> </span>family of functions qualify as stable, since their values do not change within a transaction.</p><p style="margin-left: 30.0px;">VOLATILE indicates that the function value can change even within a single table scan, so no optimizations can be made. Relatively few database functions are volatile in this sense; some examples are random(), currval(), timeofday(). But note that any function that has side-effects must be classified volatile, even if its result is quite predictable, to prevent calls from being optimized away; an example is setval().</p><pre>CALLED ON NULL INPUTRETURNS NULL ON NULL INPUTSTRICT</pre><p align="LEFT" style="margin-left: 30.0px;">CALLED ON NULL INPUT (the default) indicates that the function will be called normally when some of its arguments are null. It is then the function author’s responsibility to check for null values if necessary and respond appropriately. RETURNS NULL ON NULL INPUT or STRICT indicates that the function always returns null whenever any of its arguments are null. If this parameter is specified, the function is not executed when there are null arguments; instead a null result is assumed automatically.</p><pre>[EXTERNAL] SECURITY INVOKER[EXTERNAL] SECURITY DEFINER</pre><p align="LEFT" style="margin-left: 30.0px;">SECURITY INVOKER (the default) indicates that the function is to be executed with the privileges of the user that calls it. SECURITY DEFINER specifies that the function is to be executed with the privileges of the user that created it. The key word EXTERNAL is allowed for SQL conformance, but it is optional since, unlike in SQL, this feature applies to all functions not just external ones.</p><pre>definition</pre><p align="LEFT" style="margin-left: 30.0px;">A string constant defining the function; the meaning depends on the language. It may be an internal function name, the path to an object file, an SQL command, or text in a procedural language.</p><pre>obj_file, link_symbol</pre><p align="LEFT" style="margin-left: 30.0px;">This form of the AS clause is used for dynamically loadable C language functions when the function name in the C language source code is not the same as the name of the SQL function. The string <em>obj_file </em><span style="font-size: small;"> </span>is the name of the file containing the dynamically loadable object, and <em>link_symbol </em><span style="font-size: small;"> </span>is the name of the function in the C language source code. If the link symbol is omitted, it is assumed to be the same as the name of the SQL function being defined. It is recommended to locate shared libraries either relative to $libdir (which is located at $GPHOME/lib) or through the dynamic library path (set by the dynamic_library_path server configuration parameter). This simplifies version upgrades if the new installation is at a different location.</p><pre>describe_function</pre><p align="LEFT" style="margin-left: 30.0px;">The name of a callback function to execute when a query that calls this function is parsed. The callback function returns a tuple descriptor that indicates the result type.</p><h3 id="SQLCommandReference-Notes.10">Notes</h3><p align="LEFT">Any compiled code (shared library files) for custom functions must be placed in the same location on every host in your HAWQ array (master and all segments). This location must also be in the LD_LIBRARY_PATH so that the server can locate the files. It is recommended to locate shared libraries either relative to $libdir (which is located at $GPHOME/lib) or through the dynamic library path (set by the dynamic_library_path server configuration parameter) on all master segment instances in the HAWQ array.</p><p align="LEFT">The full SQL type syntax is allowed for input arguments and return value. However, some details of the type specification (such as the precision field for type <em>numeric</em><span style="font-size: medium;"> </span>) are the responsibility of the underlying function implementation and are not recognized or enforced by the CREATE FUNCTION command.HAWQ allows function overloading. The same name can be used for several different functions so long as they have distinct argument types. However, the C names of all functions must be different, so you must give overloaded C functions different C names (for example, use the argument types as part of the C names).</p><p>Two functions are considered the same if they have the same names and input argument types, ignoring any OUT parameters. Thus for example these declarations conflict:</p><div class="code panel pdl" style="border-width: 1px;"><div class="codeContent panelContent pdl">
<pre class="theme: Confluence; brush: java; gutter: false" style="font-size:12px;">CREATE FUNCTION foo(int) ...
CREATE FUNCTION foo(int, out text) ...</pre>
</div></div><p align="LEFT">When repeated CREATE FUNCTION calls refer to the same object file, the file is only loaded once. To unload and reload the file, use the LOAD command.</p><p align="LEFT">To be able to define a function, the user must have the USAGE privilege on the language.</p><p align="LEFT">It is often helpful to use dollar quoting to write the function definition string, rather than the normal single quote syntax. Without dollar quoting, any single quotes or backslashes in the function definition must be escaped by doubling them. A dollar-quoted string constant consists of a dollar sign ($), an optional tag of zero or more characters, another dollar sign, an arbitrary sequence of characters that makes up the string content, a dollar sign, the same tag that began this dollar quote, and a dollar sign. Inside the dollar-quoted string, single quotes, backslashes, or any character can be used without escaping. The string content is always written literally. For example, here are two different ways to specify the string "Dianne’s horse" using dollar quoting:</p><div class="code panel pdl" style="border-width: 1px;"><div class="codeContent panelContent pdl">
<pre class="theme: Confluence; brush: java; gutter: false" style="font-size:12px;">$$Dianne's horse$$
$SomeTag$Dianne's horse$SomeTag$</pre>