-
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
-
1001
-
1002
-
1003
-
1004
-
1005
-
1006
-
1007
-
1008
-
1009
-
1010
-
1011
-
1012
-
1013
-
1014
-
1015
-
1016
-
1017
-
1018
-
1019
-
1020
-
1021
-
1022
-
1023
-
1024
-
1025
-
1026
-
1027
-
1028
-
1029
-
1030
-
1031
-
1032
-
1033
-
1034
-
1035
-
1036
-
1037
-
1038
-
1039
-
1040
-
1041
-
1042
-
1043
-
1044
-
1045
-
1046
-
1047
-
1048
-
1049
-
1050
-
1051
-
1052
-
1053
-
1054
-
1055
-
1056
-
1057
-
1058
-
1059
-
1060
-
1061
-
1062
-
1063
-
1064
-
1065
-
1066
-
1067
-
1068
-
1069
-
1070
-
1071
-
1072
-
1073
-
1074
-
1075
-
1076
-
1077
-
1078
-
1079
-
1080
-
1081
-
1082
-
1083
-
1084
-
1085
-
1086
-
1087
-
1088
-
1089
-
1090
-
1091
-
1092
-
1093
-
1094
-
1095
-
1096
-
1097
-
1098
-
1099
-
1100
-
1101
-
1102
-
1103
-
1104
-
1105
-
1106
-
1107
-
1108
-
1109
-
1110
-
1111
-
1112
-
1113
-
1114
-
1115
-
1116
-
1117
-
1118
-
1119
-
1120
-
1121
-
1122
-
1123
-
1124
-
1125
-
1126
-
1127
-
1128
-
1129
-
1130
-
1131
-
1132
-
1133
-
1134
-
1135
-
1136
-
1137
-
1138
-
1139
-
1140
-
1141
-
1142
-
1143
-
1144
-
1145
-
1146
-
1147
-
1148
-
1149
-
1150
-
1151
-
1152
-
1153
-
1154
-
1155
-
1156
-
1157
-
1158
-
1159
-
1160
-
1161
-
1162
-
1163
-
1164
-
1165
-
1166
-
1167
-
1168
-
1169
-
1170
-
1171
-
1172
-
1173
-
1174
-
1175
-
1176
-
1177
-
1178
-
1179
-
1180
-
1181
-
1182
-
1183
-
1184
-
1185
-
1186
-
1187
-
1188
-
1189
-
1190
-
1191
-
1192
-
1193
-
1194
-
1195
-
1196
-
1197
-
1198
-
1199
-
1200
-
1201
-
1202
-
1203
-
1204
-
1205
-
1206
-
1207
-
1208
-
1209
-
1210
-
1211
-
1212
-
1213
-
1214
-
1215
-
1216
-
1217
-
1218
-
1219
-
1220
-
1221
-
1222
-
1223
-
1224
-
1225
-
1226
-
1227
-
1228
-
1229
-
1230
-
1231
-
1232
-
1233
-
1234
-
1235
-
1236
-
1237
-
1238
-
1239
-
1240
-
1241
-
1242
-
1243
-
1244
-
1245
-
1246
-
1247
-
1248
-
1249
-
1250
-
1251
-
1252
-
1253
-
1254
-
1255
-
1256
-
1257
-
1258
-
1259
-
1260
-
1261
-
1262
-
1263
-
1264
-
1265
-
1266
-
1267
-
1268
-
1269
-
1270
-
1271
-
1272
-
1273
-
1274
-
1275
-
1276
-
1277
-
1278
-
1279
-
1280
-
1281
-
1282
-
1283
-
1284
-
1285
-
1286
-
1287
-
1288
-
1289
-
1290
-
1291
-
1292
-
1293
-
1294
-
1295
-
1296
-
1297
-
1298
-
1299
-
1300
-
1301
-
1302
-
1303
-
1304
-
1305
-
1306
-
1307
-
1308
-
1309
-
1310
-
1311
-
1312
-
1313
-
1314
-
1315
-
1316
-
1317
-
1318
-
1319
-
1320
-
1321
-
1322
-
1323
-
1324
-
1325
-
1326
-
1327
-
1328
-
1329
-
1330
-
1331
-
1332
-
1333
-
1334
-
1335
-
1336
-
1337
-
1338
-
1339
-
1340
-
1341
-
1342
-
1343
-
1344
-
1345
-
1346
-
1347
-
1348
-
1349
-
1350
-
1351
-
1352
-
1353
-
1354
-
1355
-
1356
-
1357
-
1358
-
1359
-
1360
-
1361
-
1362
-
1363
-
1364
-
1365
-
1366
-
1367
-
1368
-
1369
-
1370
-
1371
-
1372
-
1373
-
1374
-
1375
-
1376
-
1377
-
1378
-
1379
-
1380
-
1381
-
1382
-
1383
-
1384
-
1385
-
1386
-
1387
-
1388
-
1389
-
1390
-
1391
-
1392
-
1393
-
1394
-
1395
-
1396
-
1397
-
1398
-
1399
-
1400
-
1401
-
1402
-
1403
-
1404
-
1405
-
1406
-
1407
-
1408
-
1409
-
1410
-
1411
-
1412
-
1413
-
1414
-
1415
-
1416
-
1417
-
1418
-
1419
-
1420
-
1421
-
1422
-
1423
-
1424
-
1425
-
1426
-
1427
-
1428
-
1429
-
1430
-
1431
-
1432
-
1433
-
1434
-
1435
-
1436
-
1437
-
1438
-
1439
-
1440
-
1441
-
1442
-
1443
-
1444
-
1445
-
1446
-
1447
-
1448
-
1449
-
1450
-
1451
-
1452
-
1453
-
1454
-
1455
-
1456
-
1457
-
1458
-
1459
-
1460
-
1461
-
1462
-
1463
-
1464
-
1465
-
1466
-
1467
-
1468
-
1469
-
1470
-
1471
-
1472
-
1473
-
1474
-
1475
-
1476
-
1477
-
1478
-
1479
-
1480
-
1481
-
1482
-
1483
-
1484
-
1485
-
1486
-
1487
-
1488
-
1489
-
1490
-
1491
-
1492
-
1493
-
1494
-
1495
// Copyright 2015 The Xorm Authors. All rights reserved.
// Use of this source code is governed by a BSD-style
// license that can be found in the LICENSE file.
package dialects
import (
"context"
"database/sql"
"errors"
"fmt"
"net/url"
"strconv"
"strings"
"xorm.io/xorm/core"
"xorm.io/xorm/schemas"
)
// from http://www.postgresql.org/docs/current/static/sql-keywords-appendix.html
var (
postgresReservedWords = map[string]bool{
"A": true,
"ABORT": true,
"ABS": true,
"ABSENT": true,
"ABSOLUTE": true,
"ACCESS": true,
"ACCORDING": true,
"ACTION": true,
"ADA": true,
"ADD": true,
"ADMIN": true,
"AFTER": true,
"AGGREGATE": true,
"ALL": true,
"ALLOCATE": true,
"ALSO": true,
"ALTER": true,
"ALWAYS": true,
"ANALYSE": true,
"ANALYZE": true,
"AND": true,
"ANY": true,
"ARE": true,
"ARRAY": true,
"ARRAY_AGG": true,
"ARRAY_MAX_CARDINALITY": true,
"AS": true,
"ASC": true,
"ASENSITIVE": true,
"ASSERTION": true,
"ASSIGNMENT": true,
"ASYMMETRIC": true,
"AT": true,
"ATOMIC": true,
"ATTRIBUTE": true,
"ATTRIBUTES": true,
"AUTHORIZATION": true,
"AVG": true,
"BACKWARD": true,
"BASE64": true,
"BEFORE": true,
"BEGIN": true,
"BEGIN_FRAME": true,
"BEGIN_PARTITION": true,
"BERNOULLI": true,
"BETWEEN": true,
"BIGINT": true,
"BINARY": true,
"BIT": true,
"BIT_LENGTH": true,
"BLOB": true,
"BLOCKED": true,
"BOM": true,
"BOOLEAN": true,
"BOTH": true,
"BREADTH": true,
"BY": true,
"C": true,
"CACHE": true,
"CALL": true,
"CALLED": true,
"CARDINALITY": true,
"CASCADE": true,
"CASCADED": true,
"CASE": true,
"CAST": true,
"CATALOG": true,
"CATALOG_NAME": true,
"CEIL": true,
"CEILING": true,
"CHAIN": true,
"CHAR": true,
"CHARACTER": true,
"CHARACTERISTICS": true,
"CHARACTERS": true,
"CHARACTER_LENGTH": true,
"CHARACTER_SET_CATALOG": true,
"CHARACTER_SET_NAME": true,
"CHARACTER_SET_SCHEMA": true,
"CHAR_LENGTH": true,
"CHECK": true,
"CHECKPOINT": true,
"CLASS": true,
"CLASS_ORIGIN": true,
"CLOB": true,
"CLOSE": true,
"CLUSTER": true,
"COALESCE": true,
"COBOL": true,
"COLLATE": true,
"COLLATION": true,
"COLLATION_CATALOG": true,
"COLLATION_NAME": true,
"COLLATION_SCHEMA": true,
"COLLECT": true,
"COLUMN": true,
"COLUMNS": true,
"COLUMN_NAME": true,
"COMMAND_FUNCTION": true,
"COMMAND_FUNCTION_CODE": true,
"COMMENT": true,
"COMMENTS": true,
"COMMIT": true,
"COMMITTED": true,
"CONCURRENTLY": true,
"CONDITION": true,
"CONDITION_NUMBER": true,
"CONFIGURATION": true,
"CONNECT": true,
"CONNECTION": true,
"CONNECTION_NAME": true,
"CONSTRAINT": true,
"CONSTRAINTS": true,
"CONSTRAINT_CATALOG": true,
"CONSTRAINT_NAME": true,
"CONSTRAINT_SCHEMA": true,
"CONSTRUCTOR": true,
"CONTAINS": true,
"CONTENT": true,
"CONTINUE": true,
"CONTROL": true,
"CONVERSION": true,
"CONVERT": true,
"COPY": true,
"CORR": true,
"CORRESPONDING": true,
"COST": true,
"COUNT": true,
"COVAR_POP": true,
"COVAR_SAMP": true,
"CREATE": true,
"CROSS": true,
"CSV": true,
"CUBE": true,
"CUME_DIST": true,
"CURRENT": true,
"CURRENT_CATALOG": true,
"CURRENT_DATE": true,
"CURRENT_DEFAULT_TRANSFORM_GROUP": true,
"CURRENT_PATH": true,
"CURRENT_ROLE": true,
"CURRENT_ROW": true,
"CURRENT_SCHEMA": true,
"CURRENT_TIME": true,
"CURRENT_TIMESTAMP": true,
"CURRENT_TRANSFORM_GROUP_FOR_TYPE": true,
"CURRENT_USER": true,
"CURSOR": true,
"CURSOR_NAME": true,
"CYCLE": true,
"DATA": true,
"DATABASE": true,
"DATALINK": true,
"DATE": true,
"DATETIME_INTERVAL_CODE": true,
"DATETIME_INTERVAL_PRECISION": true,
"DAY": true,
"DB": true,
"DEALLOCATE": true,
"DEC": true,
"DECIMAL": true,
"DECLARE": true,
"DEFAULT": true,
"DEFAULTS": true,
"DEFERRABLE": true,
"DEFERRED": true,
"DEFINED": true,
"DEFINER": true,
"DEGREE": true,
"DELETE": true,
"DELIMITER": true,
"DELIMITERS": true,
"DENSE_RANK": true,
"DEPTH": true,
"DEREF": true,
"DERIVED": true,
"DESC": true,
"DESCRIBE": true,
"DESCRIPTOR": true,
"DETERMINISTIC": true,
"DIAGNOSTICS": true,
"DICTIONARY": true,
"DISABLE": true,
"DISCARD": true,
"DISCONNECT": true,
"DISPATCH": true,
"DISTINCT": true,
"DLNEWCOPY": true,
"DLPREVIOUSCOPY": true,
"DLURLCOMPLETE": true,
"DLURLCOMPLETEONLY": true,
"DLURLCOMPLETEWRITE": true,
"DLURLPATH": true,
"DLURLPATHONLY": true,
"DLURLPATHWRITE": true,
"DLURLSCHEME": true,
"DLURLSERVER": true,
"DLVALUE": true,
"DO": true,
"DOCUMENT": true,
"DOMAIN": true,
"DOUBLE": true,
"DROP": true,
"DYNAMIC": true,
"DYNAMIC_FUNCTION": true,
"DYNAMIC_FUNCTION_CODE": true,
"EACH": true,
"ELEMENT": true,
"ELSE": true,
"EMPTY": true,
"ENABLE": true,
"ENCODING": true,
"ENCRYPTED": true,
"END": true,
"END-EXEC": true,
"END_FRAME": true,
"END_PARTITION": true,
"ENFORCED": true,
"ENUM": true,
"EQUALS": true,
"ESCAPE": true,
"EVENT": true,
"EVERY": true,
"EXCEPT": true,
"EXCEPTION": true,
"EXCLUDE": true,
"EXCLUDING": true,
"EXCLUSIVE": true,
"EXEC": true,
"EXECUTE": true,
"EXISTS": true,
"EXP": true,
"EXPLAIN": true,
"EXPRESSION": true,
"EXTENSION": true,
"EXTERNAL": true,
"EXTRACT": true,
"FALSE": true,
"FAMILY": true,
"FETCH": true,
"FILE": true,
"FILTER": true,
"FINAL": true,
"FIRST": true,
"FIRST_VALUE": true,
"FLAG": true,
"FLOAT": true,
"FLOOR": true,
"FOLLOWING": true,
"FOR": true,
"FORCE": true,
"FOREIGN": true,
"FORTRAN": true,
"FORWARD": true,
"FOUND": true,
"FRAME_ROW": true,
"FREE": true,
"FREEZE": true,
"FROM": true,
"FS": true,
"FULL": true,
"FUNCTION": true,
"FUNCTIONS": true,
"FUSION": true,
"G": true,
"GENERAL": true,
"GENERATED": true,
"GET": true,
"GLOBAL": true,
"GO": true,
"GOTO": true,
"GRANT": true,
"GRANTED": true,
"GREATEST": true,
"GROUP": true,
"GROUPING": true,
"GROUPS": true,
"HANDLER": true,
"HAVING": true,
"HEADER": true,
"HEX": true,
"HIERARCHY": true,
"HOLD": true,
"HOUR": true,
"ID": true,
"IDENTITY": true,
"IF": true,
"IGNORE": true,
"ILIKE": true,
"IMMEDIATE": true,
"IMMEDIATELY": true,
"IMMUTABLE": true,
"IMPLEMENTATION": true,
"IMPLICIT": true,
"IMPORT": true,
"IN": true,
"INCLUDING": true,
"INCREMENT": true,
"INDENT": true,
"INDEX": true,
"INDEXES": true,
"INDICATOR": true,
"INHERIT": true,
"INHERITS": true,
"INITIALLY": true,
"INLINE": true,
"INNER": true,
"INOUT": true,
"INPUT": true,
"INSENSITIVE": true,
"INSERT": true,
"INSTANCE": true,
"INSTANTIABLE": true,
"INSTEAD": true,
"INT": true,
"INTEGER": true,
"INTEGRITY": true,
"INTERSECT": true,
"INTERSECTION": true,
"INTERVAL": true,
"INTO": true,
"INVOKER": true,
"IS": true,
"ISNULL": true,
"ISOLATION": true,
"JOIN": true,
"K": true,
"KEY": true,
"KEY_MEMBER": true,
"KEY_TYPE": true,
"LABEL": true,
"LAG": true,
"LANGUAGE": true,
"LARGE": true,
"LAST": true,
"LAST_VALUE": true,
"LATERAL": true,
"LC_COLLATE": true,
"LC_CTYPE": true,
"LEAD": true,
"LEADING": true,
"LEAKPROOF": true,
"LEAST": true,
"LEFT": true,
"LENGTH": true,
"LEVEL": true,
"LIBRARY": true,
"LIKE": true,
"LIKE_REGEX": true,
"LIMIT": true,
"LINK": true,
"LISTEN": true,
"LN": true,
"LOAD": true,
"LOCAL": true,
"LOCALTIME": true,
"LOCALTIMESTAMP": true,
"LOCATION": true,
"LOCATOR": true,
"LOCK": true,
"LOWER": true,
"M": true,
"MAP": true,
"MAPPING": true,
"MATCH": true,
"MATCHED": true,
"MATERIALIZED": true,
"MAX": true,
"MAXVALUE": true,
"MAX_CARDINALITY": true,
"MEMBER": true,
"MERGE": true,
"MESSAGE_LENGTH": true,
"MESSAGE_OCTET_LENGTH": true,
"MESSAGE_TEXT": true,
"METHOD": true,
"MIN": true,
"MINUTE": true,
"MINVALUE": true,
"MOD": true,
"MODE": true,
"MODIFIES": true,
"MODULE": true,
"MONTH": true,
"MORE": true,
"MOVE": true,
"MULTISET": true,
"MUMPS": true,
"NAME": true,
"NAMES": true,
"NAMESPACE": true,
"NATIONAL": true,
"NATURAL": true,
"NCHAR": true,
"NCLOB": true,
"NESTING": true,
"NEW": true,
"NEXT": true,
"NFC": true,
"NFD": true,
"NFKC": true,
"NFKD": true,
"NIL": true,
"NO": true,
"NONE": true,
"NORMALIZE": true,
"NORMALIZED": true,
"NOT": true,
"NOTHING": true,
"NOTIFY": true,
"NOTNULL": true,
"NOWAIT": true,
"NTH_VALUE": true,
"NTILE": true,
"NULL": true,
"NULLABLE": true,
"NULLIF": true,
"NULLS": true,
"NUMBER": true,
"NUMERIC": true,
"OBJECT": true,
"OCCURRENCES_REGEX": true,
"OCTETS": true,
"OCTET_LENGTH": true,
"OF": true,
"OFF": true,
"OFFSET": true,
"OIDS": true,
"OLD": true,
"ON": true,
"ONLY": true,
"OPEN": true,
"OPERATOR": true,
"OPTION": true,
"OPTIONS": true,
"OR": true,
"ORDER": true,
"ORDERING": true,
"ORDINALITY": true,
"OTHERS": true,
"OUT": true,
"OUTER": true,
"OUTPUT": true,
"OVER": true,
"OVERLAPS": true,
"OVERLAY": true,
"OVERRIDING": true,
"OWNED": true,
"OWNER": true,
"P": true,
"PAD": true,
"PARAMETER": true,
"PARAMETER_MODE": true,
"PARAMETER_NAME": true,
"PARAMETER_ORDINAL_POSITION": true,
"PARAMETER_SPECIFIC_CATALOG": true,
"PARAMETER_SPECIFIC_NAME": true,
"PARAMETER_SPECIFIC_SCHEMA": true,
"PARSER": true,
"PARTIAL": true,
"PARTITION": true,
"PASCAL": true,
"PASSING": true,
"PASSTHROUGH": true,
"PASSWORD": true,
"PATH": true,
"PERCENT": true,
"PERCENTILE_CONT": true,
"PERCENTILE_DISC": true,
"PERCENT_RANK": true,
"PERIOD": true,
"PERMISSION": true,
"PLACING": true,
"PLANS": true,
"PLI": true,
"PORTION": true,
"POSITION": true,
"POSITION_REGEX": true,
"POWER": true,
"PRECEDES": true,
"PRECEDING": true,
"PRECISION": true,
"PREPARE": true,
"PREPARED": true,
"PRESERVE": true,
"PRIMARY": true,
"PRIOR": true,
"PRIVILEGES": true,
"PROCEDURAL": true,
"PROCEDURE": true,
"PROGRAM": true,
"PUBLIC": true,
"QUOTE": true,
"RANGE": true,
"RANK": true,
"READ": true,
"READS": true,
"REAL": true,
"REASSIGN": true,
"RECHECK": true,
"RECOVERY": true,
"RECURSIVE": true,
"REF": true,
"REFERENCES": true,
"REFERENCING": true,
"REFRESH": true,
"REGR_AVGX": true,
"REGR_AVGY": true,
"REGR_COUNT": true,
"REGR_INTERCEPT": true,
"REGR_R2": true,
"REGR_SLOPE": true,
"REGR_SXX": true,
"REGR_SXY": true,
"REGR_SYY": true,
"REINDEX": true,
"RELATIVE": true,
"RELEASE": true,
"RENAME": true,
"REPEATABLE": true,
"REPLACE": true,
"REPLICA": true,
"REQUIRING": true,
"RESET": true,
"RESPECT": true,
"RESTART": true,
"RESTORE": true,
"RESTRICT": true,
"RESULT": true,
"RETURN": true,
"RETURNED_CARDINALITY": true,
"RETURNED_LENGTH": true,
"RETURNED_OCTET_LENGTH": true,
"RETURNED_SQLSTATE": true,
"RETURNING": true,
"RETURNS": true,
"REVOKE": true,
"RIGHT": true,
"ROLE": true,
"ROLLBACK": true,
"ROLLUP": true,
"ROUTINE": true,
"ROUTINE_CATALOG": true,
"ROUTINE_NAME": true,
"ROUTINE_SCHEMA": true,
"ROW": true,
"ROWS": true,
"ROW_COUNT": true,
"ROW_NUMBER": true,
"RULE": true,
"SAVEPOINT": true,
"SCALE": true,
"SCHEMA": true,
"SCHEMA_NAME": true,
"SCOPE": true,
"SCOPE_CATALOG": true,
"SCOPE_NAME": true,
"SCOPE_SCHEMA": true,
"SCROLL": true,
"SEARCH": true,
"SECOND": true,
"SECTION": true,
"SECURITY": true,
"SELECT": true,
"SELECTIVE": true,
"SELF": true,
"SENSITIVE": true,
"SEQUENCE": true,
"SEQUENCES": true,
"SERIALIZABLE": true,
"SERVER": true,
"SERVER_NAME": true,
"SESSION": true,
"SESSION_USER": true,
"SET": true,
"SETOF": true,
"SETS": true,
"SHARE": true,
"SHOW": true,
"SIMILAR": true,
"SIMPLE": true,
"SIZE": true,
"SMALLINT": true,
"SNAPSHOT": true,
"SOME": true,
"SOURCE": true,
"SPACE": true,
"SPECIFIC": true,
"SPECIFICTYPE": true,
"SPECIFIC_NAME": true,
"SQL": true,
"SQLCODE": true,
"SQLERROR": true,
"SQLEXCEPTION": true,
"SQLSTATE": true,
"SQLWARNING": true,
"SQRT": true,
"STABLE": true,
"STANDALONE": true,
"START": true,
"STATE": true,
"STATEMENT": true,
"STATIC": true,
"STATISTICS": true,
"STDDEV_POP": true,
"STDDEV_SAMP": true,
"STDIN": true,
"STDOUT": true,
"STORAGE": true,
"STRICT": true,
"STRIP": true,
"STRUCTURE": true,
"STYLE": true,
"SUBCLASS_ORIGIN": true,
"SUBMULTISET": true,
"SUBSTRING": true,
"SUBSTRING_REGEX": true,
"SUCCEEDS": true,
"SUM": true,
"SYMMETRIC": true,
"SYSID": true,
"SYSTEM": true,
"SYSTEM_TIME": true,
"SYSTEM_USER": true,
"T": true,
"TABLE": true,
"TABLES": true,
"TABLESAMPLE": true,
"TABLESPACE": true,
"TABLE_NAME": true,
"TEMP": true,
"TEMPLATE": true,
"TEMPORARY": true,
"TEXT": true,
"THEN": true,
"TIES": true,
"TIME": true,
"TIMESTAMP": true,
"TIMEZONE_HOUR": true,
"TIMEZONE_MINUTE": true,
"TO": true,
"TOKEN": true,
"TOP_LEVEL_COUNT": true,
"TRAILING": true,
"TRANSACTION": true,
"TRANSACTIONS_COMMITTED": true,
"TRANSACTIONS_ROLLED_BACK": true,
"TRANSACTION_ACTIVE": true,
"TRANSFORM": true,
"TRANSFORMS": true,
"TRANSLATE": true,
"TRANSLATE_REGEX": true,
"TRANSLATION": true,
"TREAT": true,
"TRIGGER": true,
"TRIGGER_CATALOG": true,
"TRIGGER_NAME": true,
"TRIGGER_SCHEMA": true,
"TRIM": true,
"TRIM_ARRAY": true,
"TRUE": true,
"TRUNCATE": true,
"TRUSTED": true,
"TYPE": true,
"TYPES": true,
"UESCAPE": true,
"UNBOUNDED": true,
"UNCOMMITTED": true,
"UNDER": true,
"UNENCRYPTED": true,
"UNION": true,
"UNIQUE": true,
"UNKNOWN": true,
"UNLINK": true,
"UNLISTEN": true,
"UNLOGGED": true,
"UNNAMED": true,
"UNNEST": true,
"UNTIL": true,
"UNTYPED": true,
"UPDATE": true,
"UPPER": true,
"URI": true,
"USAGE": true,
"USER": true,
"USER_DEFINED_TYPE_CATALOG": true,
"USER_DEFINED_TYPE_CODE": true,
"USER_DEFINED_TYPE_NAME": true,
"USER_DEFINED_TYPE_SCHEMA": true,
"USING": true,
"VACUUM": true,
"VALID": true,
"VALIDATE": true,
"VALIDATOR": true,
"VALUE": true,
"VALUES": true,
"VALUE_OF": true,
"VARBINARY": true,
"VARCHAR": true,
"VARIADIC": true,
"VARYING": true,
"VAR_POP": true,
"VAR_SAMP": true,
"VERBOSE": true,
"VERSION": true,
"VERSIONING": true,
"VIEW": true,
"VOLATILE": true,
"WHEN": true,
"WHENEVER": true,
"WHERE": true,
"WHITESPACE": true,
"WIDTH_BUCKET": true,
"WINDOW": true,
"WITH": true,
"WITHIN": true,
"WITHOUT": true,
"WORK": true,
"WRAPPER": true,
"WRITE": true,
"XML": true,
"XMLAGG": true,
"XMLATTRIBUTES": true,
"XMLBINARY": true,
"XMLCAST": true,
"XMLCOMMENT": true,
"XMLCONCAT": true,
"XMLDECLARATION": true,
"XMLDOCUMENT": true,
"XMLELEMENT": true,
"XMLEXISTS": true,
"XMLFOREST": true,
"XMLITERATE": true,
"XMLNAMESPACES": true,
"XMLPARSE": true,
"XMLPI": true,
"XMLQUERY": true,
"XMLROOT": true,
"XMLSCHEMA": true,
"XMLSERIALIZE": true,
"XMLTABLE": true,
"XMLTEXT": true,
"XMLVALIDATE": true,
"YEAR": true,
"YES": true,
"ZONE": true,
}
postgresQuoter = schemas.Quoter{
Prefix: '"',
Suffix: '"',
IsReserved: schemas.AlwaysReserve,
}
)
var (
// DefaultPostgresSchema default postgres schema
DefaultPostgresSchema = "public"
postgresColAliases = map[string]string{
"numeric": "decimal",
}
)
type postgres struct {
Base
}
// Alias returns a alias of column
func (db *postgres) Alias(col string) string {
v, ok := postgresColAliases[strings.ToLower(col)]
if ok {
return v
}
return col
}
func (db *postgres) Init(uri *URI) error {
db.quoter = postgresQuoter
return db.Base.Init(db, uri)
}
func (db *postgres) Version(ctx context.Context, queryer core.Queryer) (*schemas.Version, error) {
rows, err := queryer.QueryContext(ctx, "SELECT version()")
if err != nil {
return nil, err
}
defer rows.Close()
var version string
if !rows.Next() {
if rows.Err() != nil {
return nil, rows.Err()
}
return nil, errors.New("unknow version")
}
if err := rows.Scan(&version); err != nil {
return nil, err
}
// Postgres: 9.5.22 on x86_64-pc-linux-gnu (Debian 9.5.22-1.pgdg90+1), compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit
// CockroachDB CCL v19.2.4 (x86_64-unknown-linux-gnu, built
if strings.HasPrefix(version, "CockroachDB") {
versions := strings.Split(strings.TrimPrefix(version, "CockroachDB CCL "), " ")
return &schemas.Version{
Number: strings.TrimPrefix(versions[0], "v"),
Edition: "CockroachDB",
}, nil
} else if strings.HasPrefix(version, "PostgreSQL") {
versions := strings.Split(strings.TrimPrefix(version, "PostgreSQL "), " on ")
return &schemas.Version{
Number: versions[0],
Level: versions[1],
Edition: "PostgreSQL",
}, nil
}
return nil, errors.New("unknow database version")
}
func (db *postgres) getSchema() string {
if db.uri.Schema != "" {
return db.uri.Schema
}
return DefaultPostgresSchema
}
func (db *postgres) needQuote(name string) bool {
if db.IsReserved(name) {
return true
}
for _, c := range name {
if c >= 'A' && c <= 'Z' {
return true
}
}
return false
}
func (db *postgres) SetQuotePolicy(quotePolicy QuotePolicy) {
switch quotePolicy {
case QuotePolicyNone:
var q = postgresQuoter
q.IsReserved = schemas.AlwaysNoReserve
db.quoter = q
case QuotePolicyReserved:
var q = postgresQuoter
q.IsReserved = db.needQuote
db.quoter = q
case QuotePolicyAlways:
fallthrough
default:
db.quoter = postgresQuoter
}
}
func (db *postgres) SQLType(c *schemas.Column) string {
var res string
switch t := c.SQLType.Name; t {
case schemas.TinyInt, schemas.UnsignedTinyInt:
res = schemas.SmallInt
return res
case schemas.Bit:
res = schemas.Boolean
return res
case schemas.MediumInt, schemas.Int, schemas.Integer, schemas.UnsignedMediumInt, schemas.UnsignedSmallInt:
if c.IsAutoIncrement {
return schemas.Serial
}
return schemas.Integer
case schemas.BigInt, schemas.UnsignedBigInt, schemas.UnsignedInt:
if c.IsAutoIncrement {
return schemas.BigSerial
}
return schemas.BigInt
case schemas.Serial, schemas.BigSerial:
c.IsAutoIncrement = true
c.Nullable = false
res = t
case schemas.Binary, schemas.VarBinary:
return schemas.Bytea
case schemas.DateTime:
res = schemas.TimeStamp
case schemas.TimeStampz:
return "timestamp with time zone"
case schemas.Float:
res = schemas.Real
case schemas.TinyText, schemas.MediumText, schemas.LongText:
res = schemas.Text
case schemas.NChar:
res = schemas.Char
case schemas.NVarchar:
res = schemas.Varchar
case schemas.Uuid:
return schemas.Uuid
case schemas.Blob, schemas.TinyBlob, schemas.MediumBlob, schemas.LongBlob:
return schemas.Bytea
case schemas.Double:
return "DOUBLE PRECISION"
default:
if c.IsAutoIncrement {
return schemas.Serial
}
res = t
}
if strings.EqualFold(res, "bool") {
// for bool, we don't need length information
return res
}
hasLen1 := (c.Length > 0)
hasLen2 := (c.Length2 > 0)
if hasLen2 {
res += "(" + strconv.Itoa(c.Length) + "," + strconv.Itoa(c.Length2) + ")"
} else if hasLen1 {
res += "(" + strconv.Itoa(c.Length) + ")"
}
return res
}
func (db *postgres) ColumnTypeKind(t string) int {
switch strings.ToUpper(t) {
case "DATETIME", "TIMESTAMP":
return schemas.TIME_TYPE
case "VARCHAR", "TEXT":
return schemas.TEXT_TYPE
case "BIGINT", "BIGSERIAL", "SMALLINT", "INT", "INT8", "INT4", "INTEGER", "SERIAL", "FLOAT", "FLOAT4", "REAL", "DOUBLE PRECISION":
return schemas.NUMERIC_TYPE
case "BOOL":
return schemas.BOOL_TYPE
default:
return schemas.UNKNOW_TYPE
}
}
func (db *postgres) IsReserved(name string) bool {
_, ok := postgresReservedWords[strings.ToUpper(name)]
return ok
}
func (db *postgres) AutoIncrStr() string {
return ""
}
func (db *postgres) IndexCheckSQL(tableName, idxName string) (string, []interface{}) {
if len(db.getSchema()) == 0 {
args := []interface{}{tableName, idxName}
return `SELECT indexname FROM pg_indexes WHERE tablename = ? AND indexname = ?`, args
}
args := []interface{}{db.getSchema(), tableName, idxName}
return `SELECT indexname FROM pg_indexes ` +
`WHERE schemaname = ? AND tablename = ? AND indexname = ?`, args
}
func (db *postgres) IsTableExist(queryer core.Queryer, ctx context.Context, tableName string) (bool, error) {
if len(db.getSchema()) == 0 {
return db.HasRecords(queryer, ctx, `SELECT tablename FROM pg_tables WHERE tablename = $1`, tableName)
}
return db.HasRecords(queryer, ctx, `SELECT tablename FROM pg_tables WHERE schemaname = $1 AND tablename = $2`,
db.getSchema(), tableName)
}
func (db *postgres) ModifyColumnSQL(tableName string, col *schemas.Column) string {
if len(db.getSchema()) == 0 || strings.Contains(tableName, ".") {
return fmt.Sprintf("alter table %s ALTER COLUMN %s TYPE %s",
db.quoter.Quote(tableName), db.quoter.Quote(col.Name), db.SQLType(col))
}
return fmt.Sprintf("alter table %s.%s ALTER COLUMN %s TYPE %s",
db.quoter.Quote(db.getSchema()), db.quoter.Quote(tableName), db.quoter.Quote(col.Name), db.SQLType(col))
}
func (db *postgres) DropIndexSQL(tableName string, index *schemas.Index) string {
idxName := index.Name
tableParts := strings.Split(strings.Replace(tableName, `"`, "", -1), ".")
tableName = tableParts[len(tableParts)-1]
if !strings.HasPrefix(idxName, "UQE_") &&
!strings.HasPrefix(idxName, "IDX_") {
if index.Type == schemas.UniqueType {
idxName = fmt.Sprintf("UQE_%v_%v", tableName, index.Name)
} else {
idxName = fmt.Sprintf("IDX_%v_%v", tableName, index.Name)
}
}
if db.getSchema() != "" {
idxName = db.getSchema() + "." + idxName
}
return fmt.Sprintf("DROP INDEX %v", db.Quoter().Quote(idxName))
}
func (db *postgres) IsColumnExist(queryer core.Queryer, ctx context.Context, tableName, colName string) (bool, error) {
args := []interface{}{db.getSchema(), tableName, colName}
query := "SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = $1 AND table_name = $2" +
" AND column_name = $3"
if len(db.getSchema()) == 0 {
args = []interface{}{tableName, colName}
query = "SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = $1" +
" AND column_name = $2"
}
rows, err := queryer.QueryContext(ctx, query, args...)
if err != nil {
return false, err
}
defer rows.Close()
if rows.Next() {
return true, nil
}
return false, rows.Err()
}
func (db *postgres) GetColumns(queryer core.Queryer, ctx context.Context, tableName string) ([]string, map[string]*schemas.Column, error) {
args := []interface{}{tableName}
s := `SELECT column_name, column_default, is_nullable, data_type, character_maximum_length, description,
CASE WHEN p.contype = 'p' THEN true ELSE false END AS primarykey,
CASE WHEN p.contype = 'u' THEN true ELSE false END AS uniquekey
FROM pg_attribute f
JOIN pg_class c ON c.oid = f.attrelid JOIN pg_type t ON t.oid = f.atttypid
LEFT JOIN pg_attrdef d ON d.adrelid = c.oid AND d.adnum = f.attnum
LEFT JOIN pg_description de ON f.attrelid=de.objoid AND f.attnum=de.objsubid
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_constraint p ON p.conrelid = c.oid AND f.attnum = ANY (p.conkey)
LEFT JOIN pg_class AS g ON p.confrelid = g.oid
LEFT JOIN INFORMATION_SCHEMA.COLUMNS s ON s.column_name=f.attname AND c.relname=s.table_name
WHERE n.nspname= s.table_schema AND c.relkind = 'r'::char AND c.relname = $1%s AND f.attnum > 0 ORDER BY f.attnum;`
schema := db.getSchema()
if schema != "" {
s = fmt.Sprintf(s, " AND s.table_schema = $2")
args = append(args, schema)
} else {
s = fmt.Sprintf(s, "")
}
rows, err := queryer.QueryContext(ctx, s, args...)
if err != nil {
return nil, nil, err
}
defer rows.Close()
cols := make(map[string]*schemas.Column)
colSeq := make([]string, 0)
for rows.Next() {
col := new(schemas.Column)
col.Indexes = make(map[string]int)
var colName, isNullable, dataType string
var maxLenStr, colDefault, description *string
var isPK, isUnique bool
err = rows.Scan(&colName, &colDefault, &isNullable, &dataType, &maxLenStr, &description, &isPK, &isUnique)
if err != nil {
return nil, nil, err
}
var maxLen int
if maxLenStr != nil {
maxLen, err = strconv.Atoi(*maxLenStr)
if err != nil {
return nil, nil, err
}
}
if colDefault != nil && *colDefault == "unique_rowid()" { // ignore the system column added by cockroach
continue
}
col.Name = strings.Trim(colName, `" `)
if colDefault != nil {
var theDefault = *colDefault
// cockroach has type with the default value with :::
// and postgres with ::, we should remove them before store them
idx := strings.Index(theDefault, ":::")
if idx == -1 {
idx = strings.Index(theDefault, "::")
}
if idx > -1 {
theDefault = theDefault[:idx]
}
if strings.HasSuffix(theDefault, "+00:00'") {
theDefault = theDefault[:len(theDefault)-7] + "'"
}
col.Default = theDefault
col.DefaultIsEmpty = false
if strings.HasPrefix(col.Default, "nextval(") {
col.IsAutoIncrement = true
col.Default = ""
col.DefaultIsEmpty = true
}
} else {
col.DefaultIsEmpty = true
}
if description != nil {
col.Comment = *description
}
if isPK {
col.IsPrimaryKey = true
}
col.Nullable = (isNullable == "YES")
switch strings.ToLower(dataType) {
case "character varying", "string":
col.SQLType = schemas.SQLType{Name: schemas.Varchar, DefaultLength: 0, DefaultLength2: 0}
case "character":
col.SQLType = schemas.SQLType{Name: schemas.Char, DefaultLength: 0, DefaultLength2: 0}
case "timestamp without time zone":
col.SQLType = schemas.SQLType{Name: schemas.DateTime, DefaultLength: 0, DefaultLength2: 0}
case "timestamp with time zone":
col.SQLType = schemas.SQLType{Name: schemas.TimeStampz, DefaultLength: 0, DefaultLength2: 0}
case "double precision":
col.SQLType = schemas.SQLType{Name: schemas.Double, DefaultLength: 0, DefaultLength2: 0}
case "boolean":
col.SQLType = schemas.SQLType{Name: schemas.Bool, DefaultLength: 0, DefaultLength2: 0}
case "time without time zone":
col.SQLType = schemas.SQLType{Name: schemas.Time, DefaultLength: 0, DefaultLength2: 0}
case "bytes":
col.SQLType = schemas.SQLType{Name: schemas.Binary, DefaultLength: 0, DefaultLength2: 0}
case "oid":
col.SQLType = schemas.SQLType{Name: schemas.BigInt, DefaultLength: 0, DefaultLength2: 0}
case "array":
col.SQLType = schemas.SQLType{Name: schemas.Array, DefaultLength: 0, DefaultLength2: 0}
default:
startIdx := strings.Index(strings.ToLower(dataType), "string(")
if startIdx != -1 && strings.HasSuffix(dataType, ")") {
length := dataType[startIdx+8 : len(dataType)-1]
l, _ := strconv.Atoi(length)
col.SQLType = schemas.SQLType{Name: "STRING", DefaultLength: l, DefaultLength2: 0}
} else {
col.SQLType = schemas.SQLType{Name: strings.ToUpper(dataType), DefaultLength: 0, DefaultLength2: 0}
}
}
if _, ok := schemas.SqlTypes[col.SQLType.Name]; !ok {
return nil, nil, fmt.Errorf("unknown colType: %s - %s", dataType, col.SQLType.Name)
}
col.Length = maxLen
if !col.DefaultIsEmpty {
if col.SQLType.IsText() {
if strings.HasSuffix(col.Default, "::character varying") {
col.Default = strings.TrimSuffix(col.Default, "::character varying")
} else if !strings.HasPrefix(col.Default, "'") {
col.Default = "'" + col.Default + "'"
}
} else if col.SQLType.IsTime() {
if strings.HasSuffix(col.Default, "::timestamp without time zone") {
col.Default = strings.TrimSuffix(col.Default, "::timestamp without time zone")
}
}
}
cols[col.Name] = col
colSeq = append(colSeq, col.Name)
}
if rows.Err() != nil {
return nil, nil, rows.Err()
}
return colSeq, cols, nil
}
func (db *postgres) GetTables(queryer core.Queryer, ctx context.Context) ([]*schemas.Table, error) {
args := []interface{}{}
s := "SELECT tablename FROM pg_tables"
schema := db.getSchema()
if schema != "" {
args = append(args, schema)
s = s + " WHERE schemaname = $1"
}
rows, err := queryer.QueryContext(ctx, s, args...)
if err != nil {
return nil, err
}
defer rows.Close()
tables := make([]*schemas.Table, 0)
for rows.Next() {
table := schemas.NewEmptyTable()
var name string
err = rows.Scan(&name)
if err != nil {
return nil, err
}
table.Name = name
tables = append(tables, table)
}
if rows.Err() != nil {
return nil, rows.Err()
}
return tables, nil
}
func getIndexColName(indexdef string) []string {
var colNames []string
cs := strings.Split(indexdef, "(")
for _, v := range strings.Split(strings.Split(cs[1], ")")[0], ",") {
colNames = append(colNames, strings.Split(strings.TrimLeft(v, " "), " ")[0])
}
return colNames
}
func (db *postgres) GetIndexes(queryer core.Queryer, ctx context.Context, tableName string) (map[string]*schemas.Index, error) {
args := []interface{}{tableName}
s := "SELECT indexname, indexdef FROM pg_indexes WHERE tablename=$1"
if len(db.getSchema()) != 0 {
args = append(args, db.getSchema())
s = s + " AND schemaname=$2"
}
rows, err := queryer.QueryContext(ctx, s, args...)
if err != nil {
return nil, err
}
defer rows.Close()
indexes := make(map[string]*schemas.Index)
for rows.Next() {
var indexType int
var indexName, indexdef string
var colNames []string
err = rows.Scan(&indexName, &indexdef)
if err != nil {
return nil, err
}
if indexName == "primary" {
continue
}
indexName = strings.Trim(indexName, `" `)
// ignore primary index
if strings.HasSuffix(indexName, "_pkey") || strings.EqualFold(indexName, "primary") {
continue
}
if strings.HasPrefix(indexdef, "CREATE UNIQUE INDEX") {
indexType = schemas.UniqueType
} else {
indexType = schemas.IndexType
}
colNames = getIndexColName(indexdef)
var isRegular bool
if strings.HasPrefix(indexName, "IDX_"+tableName) || strings.HasPrefix(indexName, "UQE_"+tableName) {
newIdxName := indexName[5+len(tableName):]
isRegular = true
if newIdxName != "" {
indexName = newIdxName
}
}
index := &schemas.Index{Name: indexName, Type: indexType, Cols: make([]string, 0)}
for _, colName := range colNames {
col := strings.TrimSpace(strings.Replace(colName, `"`, "", -1))
fields := strings.Split(col, " ")
index.Cols = append(index.Cols, fields[0])
}
index.IsRegular = isRegular
indexes[index.Name] = index
}
if rows.Err() != nil {
return nil, rows.Err()
}
return indexes, nil
}
func (db *postgres) Filters() []Filter {
return []Filter{&SeqFilter{Prefix: "$", Start: 1}}
}
type pqDriver struct {
baseDriver
}
type values map[string]string
func parseURL(connstr string) (string, error) {
u, err := url.Parse(connstr)
if err != nil {
return "", err
}
if u.Scheme != "postgresql" && u.Scheme != "postgres" {
return "", fmt.Errorf("invalid connection protocol: %s", u.Scheme)
}
escaper := strings.NewReplacer(` `, `\ `, `'`, `\'`, `\`, `\\`)
if u.Path != "" {
return escaper.Replace(u.Path[1:]), nil
}
return "", nil
}
func parseOpts(urlStr string, o values) error {
if len(urlStr) == 0 {
return fmt.Errorf("invalid options: %s", urlStr)
}
urlStr = strings.TrimSpace(urlStr)
var (
inQuote bool
state int // 0 key, 1 space, 2 value, 3 equal
start int
key string
)
for i, c := range urlStr {
switch c {
case ' ':
if !inQuote {
if state == 2 {
state = 1
v := urlStr[start:i]
if strings.HasPrefix(v, "'") && strings.HasSuffix(v, "'") {
v = v[1 : len(v)-1]
} else if strings.HasPrefix(v, "'") || strings.HasSuffix(v, "'") {
return fmt.Errorf("wrong single quote in %d of %s", i, urlStr)
}
o[key] = v
} else if state != 1 {
return fmt.Errorf("wrong format: %v", urlStr)
}
}
case '\'':
if state == 3 {
state = 2
start = i
} else if state != 2 {
return fmt.Errorf("wrong format: %v", urlStr)
}
inQuote = !inQuote
case '=':
if !inQuote {
if state != 0 {
return fmt.Errorf("wrong format: %v", urlStr)
}
key = urlStr[start:i]
state = 3
}
default:
if state == 3 {
state = 2
start = i
} else if state == 1 {
state = 0
start = i
}
}
if i == len(urlStr)-1 {
if state != 2 {
return errors.New("no value matched key")
}
v := urlStr[start : i+1]
if strings.HasPrefix(v, "'") && strings.HasSuffix(v, "'") {
v = v[1 : len(v)-1]
} else if strings.HasPrefix(v, "'") || strings.HasSuffix(v, "'") {
return fmt.Errorf("wrong single quote in %d of %s", i, urlStr)
}
o[key] = v
}
}
return nil
}
func (p *pqDriver) Features() *DriverFeatures {
return &DriverFeatures{
SupportReturnInsertedID: false,
}
}
func (p *pqDriver) Parse(driverName, dataSourceName string) (*URI, error) {
db := &URI{DBType: schemas.POSTGRES}
var err error
if strings.Contains(dataSourceName, "://") {
if !strings.HasPrefix(dataSourceName, "postgresql://") && !strings.HasPrefix(dataSourceName, "postgres://") {
return nil, fmt.Errorf("unsupported protocol %v", dataSourceName)
}
db.DBName, err = parseURL(dataSourceName)
if err != nil {
return nil, err
}
} else {
o := make(values)
err = parseOpts(dataSourceName, o)
if err != nil {
return nil, err
}
db.DBName = o["dbname"]
}
if db.DBName == "" {
return nil, errors.New("dbname is empty")
}
return db, nil
}
func (p *pqDriver) GenScanResult(colType string) (interface{}, error) {
switch colType {
case "VARCHAR", "TEXT":
var s sql.NullString
return &s, nil
case "BIGINT", "BIGSERIAL":
var s sql.NullInt64
return &s, nil
case "SMALLINT", "INT", "INT8", "INT4", "INTEGER", "SERIAL":
var s sql.NullInt32
return &s, nil
case "FLOAT", "FLOAT4", "REAL", "DOUBLE PRECISION":
var s sql.NullFloat64
return &s, nil
case "DATETIME", "TIMESTAMP":
var s sql.NullTime
return &s, nil
case "BOOL":
var s sql.NullBool
return &s, nil
default:
var r sql.RawBytes
return &r, nil
}
}
type pqDriverPgx struct {
pqDriver
}
func (pgx *pqDriverPgx) Parse(driverName, dataSourceName string) (*URI, error) {
// Remove the leading characters for driver to work
if len(dataSourceName) >= 9 && dataSourceName[0] == 0 {
dataSourceName = dataSourceName[9:]
}
return pgx.pqDriver.Parse(driverName, dataSourceName)
}
// QueryDefaultPostgresSchema returns the default postgres schema
func QueryDefaultPostgresSchema(ctx context.Context, queryer core.Queryer) (string, error) {
rows, err := queryer.QueryContext(ctx, "SHOW SEARCH_PATH")
if err != nil {
return "", err
}
defer rows.Close()
if rows.Next() {
var defaultSchema string
if err = rows.Scan(&defaultSchema); err != nil {
return "", err
}
parts := strings.Split(defaultSchema, ",")
return strings.TrimSpace(parts[len(parts)-1]), nil
}
if rows.Err() != nil {
return "", rows.Err()
}
return "", errors.New("no default schema")
}