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
1496
1497
1498
1499
1500
1501
1502
1503
1504
1505
1506
1507
1508
1509
1510
1511
1512
1513
1514
1515
1516
1517
1518
1519
1520
1521
1522
1523
1524
1525
1526
1527
1528
1529
1530
1531
1532
1533
1534
1535
1536
1537
1538
1539
1540
1541
1542
1543
1544
1545
1546
1547
1548
1549
1550
1551
1552
1553
1554
1555
1556
1557
1558
1559
1560
1561
1562
1563
1564
1565
1566
1567
1568
1569
1570
1571
1572
1573
1574
1575
1576
1577
1578
1579
1580
1581
1582
1583
1584
1585
1586
1587
1588
1589
1590
1591
1592
1593
1594
1595
1596
1597
1598
1599
1600
1601
1602
1603
1604
1605
1606
1607
1608
1609
1610
1611
1612
1613
1614
1615
1616
1617
1618
1619
1620
1621
1622
1623
1624
1625
1626
1627
1628
1629
1630
1631
1632
1633
1634
1635
1636
1637
1638
1639
1640
1641
1642
1643
1644
1645
1646
1647
1648
1649
1650
1651
1652
1653
1654
1655
1656
1657
1658
1659
1660
1661
1662
1663
1664
1665
1666
1667
1668
1669
1670
1671
1672
1673
1674
1675
1676
1677
1678
1679
1680
1681
1682
1683
1684
1685
1686
1687
1688
1689
1690
1691
1692
1693
1694
1695
1696
1697
1698
1699
1700
1701
1702
1703
1704
1705
1706
1707
1708
1709
1710
1711
1712
1713
1714
1715
1716
1717
1718
1719
1720
1721
1722
1723
1724
1725
1726
1727
1728
1729
1730
1731
1732
1733
1734
1735
1736
1737
1738
1739
1740
1741
1742
1743
1744
1745
1746
1747
1748
1749
1750
1751
1752
1753
1754
1755
1756
1757
1758
1759
1760
1761
1762
1763
1764
1765
1766
1767
1768
1769
1770
1771
1772
1773
1774
1775
1776
1777
1778
1779
1780
1781
1782
1783
1784
1785
1786
1787
1788
1789
1790
1791
1792
1793
1794
1795
1796
1797
1798
1799
1800
1801
1802
1803
1804
1805
1806
1807
1808
1809
1810
1811
1812
1813
1814
1815
1816
1817
1818
1819
1820
1821
1822
1823
1824
1825
1826
1827
1828
1829
1830
1831
1832
1833
1834
1835
1836
1837
1838
1839
1840
1841
1842
1843
1844
1845
1846
1847
1848
1849
1850
1851
1852
1853
1854
1855
1856
1857
1858
1859
1860
1861
1862
1863
1864
1865
1866
1867
1868
1869
1870
1871
1872
1873
1874
1875
1876
1877
1878
1879
1880
1881
1882
1883
1884
1885
1886
1887
1888
1889
1890
1891
1892
1893
1894
1895
1896
1897
1898
1899
1900
1901
1902
1903
1904
1905
1906
1907
1908
1909
1910
1911
1912
1913
1914
1915
1916
1917
1918
1919
1920
1921
1922
1923
1924
1925
1926
1927
1928
1929
1930
1931
1932
1933
1934
1935
1936
1937
1938
1939
1940
1941
1942
1943
1944
1945
1946
1947
1948
1949
1950
1951
1952
1953
1954
1955
1956
1957
1958
1959
1960
1961
1962
1963
1964
1965
1966
1967
1968
1969
1970
1971
1972
1973
1974
1975
1976
1977
1978
1979
1980
1981
1982
1983
1984
1985
1986
1987
1988
1989
1990
1991
1992
1993
1994
1995
1996
1997
1998
1999
2000
2001
2002
2003
2004
2005
2006
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018
2019
2020
2021
2022
2023
2024
2025
2026
2027
2028
2029
2030
2031
2032
2033
2034
2035
2036
2037
2038
2039
2040
2041
2042
2043
2044
2045
2046
2047
2048
2049
2050
2051
2052
2053
2054
2055
2056
2057
2058
2059
2060
2061
2062
2063
2064
2065
2066
2067
2068
2069
2070
2071
2072
2073
2074
2075
2076
2077
2078
2079
2080
2081
2082
2083
2084
2085
2086
2087
2088
2089
2090
2091
2092
2093
2094
2095
2096
2097
2098
2099
2100
2101
2102
2103
2104
2105
2106
2107
2108
2109
2110
2111
2112
2113
2114
2115
2116
2117
2118
2119
2120
2121
2122
2123
2124
2125
2126
2127
2128
2129
2130
2131
2132
2133
2134
2135
2136
2137
2138
2139
2140
2141
2142
2143
2144
2145
2146
2147
2148
2149
2150
2151
2152
2153
2154
2155
2156
2157
2158
2159
2160
2161
2162
2163
2164
2165
2166
2167
2168
2169
2170
2171
2172
2173
2174
2175
2176
2177
2178
2179
2180
2181
2182
2183
2184
2185
2186
2187
|
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>
PostgreSQL
—
SQLAlchemy 0.9 Documentation
</title>
<!-- begin iterate through SQLA + sphinx environment css_files -->
<link rel="stylesheet" href="../_static/pygments.css" type="text/css" />
<link rel="stylesheet" href="../_static/docs.css" type="text/css" />
<link rel="stylesheet" href="../_static/sphinx_paramlinks.css" type="text/css" />
<link rel="stylesheet" href="../_static/changelog.css" type="text/css" />
<!-- end iterate through SQLA + sphinx environment css_files -->
<!-- begin layout.mako headers -->
<script type="text/javascript">
var DOCUMENTATION_OPTIONS = {
URL_ROOT: '../',
VERSION: '0.9.8',
COLLAPSE_MODINDEX: false,
FILE_SUFFIX: '.html'
};
</script>
<!-- begin iterate through sphinx environment script_files -->
<script type="text/javascript" src="../_static/jquery.js"></script>
<script type="text/javascript" src="../_static/underscore.js"></script>
<script type="text/javascript" src="../_static/doctools.js"></script>
<!-- end iterate through sphinx environment script_files -->
<script type="text/javascript" src="../_static/detectmobile.js"></script>
<script type="text/javascript" src="../_static/init.js"></script>
<link rel="index" title="Index" href="../genindex.html" />
<link rel="search" title="Search" href="../search.html" />
<link rel="copyright" title="Copyright" href="../copyright.html" />
<link rel="top" title="SQLAlchemy 0.9 Documentation" href="../index.html" />
<link rel="up" title="Dialects" href="index.html" />
<link rel="next" title="SQLite" href="sqlite.html" />
<link rel="prev" title="Oracle" href="oracle.html" />
<!-- end layout.mako headers -->
</head>
<body>
<div id="docs-container">
<div id="docs-top-navigation-container" class="body-background">
<div id="docs-header">
<div id="docs-version-header">
Release: <span class="version-num">0.9.8</span> | Release Date: October 13, 2014
</div>
<h1>SQLAlchemy 0.9 Documentation</h1>
</div>
</div>
<div id="docs-body-container">
<div id="fixed-sidebar" class="withsidebar">
<div id="docs-sidebar-popout">
<h3><a href="../index.html">SQLAlchemy 0.9 Documentation</a></h3>
<p id="sidebar-paginate">
<a href="index.html" title="Dialects">Up</a> |
<a href="oracle.html" title="Oracle">Prev</a> |
<a href="sqlite.html" title="SQLite">Next</a>
</p>
<p id="sidebar-topnav">
<a href="../index.html">Contents</a> |
<a href="../genindex.html">Index</a>
</p>
<div id="sidebar-search">
<form class="search" action="../search.html" method="get">
<input type="text" name="q" size="12" /> <input type="submit" value="Search" />
<input type="hidden" name="check_keywords" value="yes" />
<input type="hidden" name="area" value="default" />
</form>
</div>
</div>
<div id="docs-sidebar">
<h3><a href="#">
PostgreSQL
</a></h3>
<ul>
<li><a class="reference internal" href="#">PostgreSQL</a><ul>
<li><a class="reference internal" href="#dialect-postgresql">Support for the PostgreSQL database.</a></li>
<li><a class="reference internal" href="#sequences-serial">Sequences/SERIAL</a></li>
<li><a class="reference internal" href="#transaction-isolation-level">Transaction Isolation Level</a></li>
<li><a class="reference internal" href="#remote-schema-table-introspection-and-postgresql-search-path">Remote-Schema Table Introspection and Postgresql search_path</a></li>
<li><a class="reference internal" href="#insert-update-returning">INSERT/UPDATE...RETURNING</a></li>
<li><a class="reference internal" href="#full-text-search">Full Text Search</a></li>
<li><a class="reference internal" href="#from-only">FROM ONLY ...</a></li>
<li><a class="reference internal" href="#postgresql-specific-index-options">Postgresql-Specific Index Options</a><ul>
<li><a class="reference internal" href="#partial-indexes">Partial Indexes</a></li>
<li><a class="reference internal" href="#operator-classes">Operator Classes</a></li>
<li><a class="reference internal" href="#index-types">Index Types</a></li>
</ul>
</li>
<li><a class="reference internal" href="#postgresql-data-types">PostgreSQL Data Types</a><ul>
<li><a class="reference internal" href="#range-types">Range Types</a></li>
</ul>
</li>
<li><a class="reference internal" href="#postgresql-constraint-types">PostgreSQL Constraint Types</a></li>
<li><a class="reference internal" href="#module-sqlalchemy.dialects.postgresql.psycopg2">psycopg2</a><ul>
<li><a class="reference internal" href="#dialect-postgresql-psycopg2-url">DBAPI</a></li>
<li><a class="reference internal" href="#dialect-postgresql-psycopg2-connect">Connecting</a></li>
<li><a class="reference internal" href="#psycopg2-connect-arguments">psycopg2 Connect Arguments</a></li>
<li><a class="reference internal" href="#unix-domain-connections">Unix Domain Connections</a></li>
<li><a class="reference internal" href="#per-statement-connection-execution-options">Per-Statement/Connection Execution Options</a></li>
<li><a class="reference internal" href="#unicode-with-psycopg2">Unicode with Psycopg2</a><ul>
<li><a class="reference internal" href="#disabling-native-unicode">Disabling Native Unicode</a></li>
</ul>
</li>
<li><a class="reference internal" href="#transactions">Transactions</a></li>
<li><a class="reference internal" href="#psycopg2-transaction-isolation-level">Psycopg2 Transaction Isolation Level</a></li>
<li><a class="reference internal" href="#notice-logging">NOTICE logging</a></li>
<li><a class="reference internal" href="#hstore-type">HSTORE type</a></li>
</ul>
</li>
<li><a class="reference internal" href="#module-sqlalchemy.dialects.postgresql.pypostgresql">py-postgresql</a><ul>
<li><a class="reference internal" href="#dialect-postgresql-pypostgresql-url">DBAPI</a></li>
<li><a class="reference internal" href="#dialect-postgresql-pypostgresql-connect">Connecting</a></li>
</ul>
</li>
<li><a class="reference internal" href="#module-sqlalchemy.dialects.postgresql.pg8000">pg8000</a><ul>
<li><a class="reference internal" href="#dialect-postgresql-pg8000-url">DBAPI</a></li>
<li><a class="reference internal" href="#dialect-postgresql-pg8000-connect">Connecting</a></li>
<li><a class="reference internal" href="#unicode">Unicode</a></li>
<li><a class="reference internal" href="#pg8000-transaction-isolation-level">pg8000 Transaction Isolation Level</a></li>
</ul>
</li>
<li><a class="reference internal" href="#module-sqlalchemy.dialects.postgresql.zxjdbc">zxjdbc</a><ul>
<li><a class="reference internal" href="#dialect-postgresql-zxjdbc-url">DBAPI</a></li>
<li><a class="reference internal" href="#dialect-postgresql-zxjdbc-connect">Connecting</a></li>
</ul>
</li>
</ul>
</li>
</ul>
</div>
</div>
<div id="docs-body" class="withsidebar" >
<div class="section" id="module-sqlalchemy.dialects.postgresql.base">
<span id="postgresql"></span><span id="postgresql-toplevel"></span><h1>PostgreSQL<a class="headerlink" href="#module-sqlalchemy.dialects.postgresql.base" title="Permalink to this headline">¶</a></h1>
<div class="section" id="dialect-postgresql">
<p>Support for the PostgreSQL database.</p>
<h2>DBAPI Support<a class="headerlink" href="#dialect-postgresql" title="Permalink to this headline">¶</a></h2>
<p>The following dialect/DBAPI options are available. Please refer to individual DBAPI sections for connect information.<ul class="simple">
<li><a class="reference external" href="#module-sqlalchemy.dialects.postgresql.psycopg2">psycopg2</a></li>
<li><a class="reference external" href="#module-sqlalchemy.dialects.postgresql.pypostgresql">py-postgresql</a></li>
<li><a class="reference external" href="#module-sqlalchemy.dialects.postgresql.pg8000">pg8000</a></li>
<li><a class="reference external" href="#module-sqlalchemy.dialects.postgresql.zxjdbc">zxJDBC for Jython</a></li>
</ul>
</p>
</div>
<div class="section" id="sequences-serial">
<h2>Sequences/SERIAL<a class="headerlink" href="#sequences-serial" title="Permalink to this headline">¶</a></h2>
<p>PostgreSQL supports sequences, and SQLAlchemy uses these as the default means
of creating new primary key values for integer-based primary key columns. When
creating tables, SQLAlchemy will issue the <tt class="docutils literal"><span class="pre">SERIAL</span></tt> datatype for
integer-based primary key columns, which generates a sequence and server side
default corresponding to the column.</p>
<p>To specify a specific named sequence to be used for primary key generation,
use the <a class="reference internal" href="../core/defaults.html#sqlalchemy.schema.Sequence" title="sqlalchemy.schema.Sequence"><tt class="xref py py-func docutils literal"><span class="pre">Sequence()</span></tt></a> construct:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">Table</span><span class="p">(</span><span class="s">'sometable'</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">Sequence</span><span class="p">(</span><span class="s">'some_id_seq'</span><span class="p">),</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
<span class="p">)</span></pre></div>
</div>
<p>When SQLAlchemy issues a single INSERT statement, to fulfill the contract of
having the “last insert identifier” available, a RETURNING clause is added to
the INSERT statement which specifies the primary key columns should be
returned after the statement completes. The RETURNING functionality only takes
place if Postgresql 8.2 or later is in use. As a fallback approach, the
sequence, whether specified explicitly or implicitly via <tt class="docutils literal"><span class="pre">SERIAL</span></tt>, is
executed independently beforehand, the returned value to be used in the
subsequent insert. Note that when an
<a class="reference internal" href="../core/dml.html#sqlalchemy.sql.expression.insert" title="sqlalchemy.sql.expression.insert"><tt class="xref py py-func docutils literal"><span class="pre">insert()</span></tt></a> construct is executed using
“executemany” semantics, the “last inserted identifier” functionality does not
apply; no RETURNING clause is emitted nor is the sequence pre-executed in this
case.</p>
<p>To force the usage of RETURNING by default off, specify the flag
<tt class="docutils literal"><span class="pre">implicit_returning=False</span></tt> to <a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><tt class="xref py py-func docutils literal"><span class="pre">create_engine()</span></tt></a>.</p>
</div>
<div class="section" id="transaction-isolation-level">
<span id="postgresql-isolation-level"></span><h2>Transaction Isolation Level<a class="headerlink" href="#transaction-isolation-level" title="Permalink to this headline">¶</a></h2>
<p>All Postgresql dialects support setting of transaction isolation level
both via a dialect-specific parameter <tt class="docutils literal"><span class="pre">isolation_level</span></tt>
accepted by <a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><tt class="xref py py-func docutils literal"><span class="pre">create_engine()</span></tt></a>,
as well as the <tt class="docutils literal"><span class="pre">isolation_level</span></tt> argument as passed to
<a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Connection.execution_options" title="sqlalchemy.engine.Connection.execution_options"><tt class="xref py py-meth docutils literal"><span class="pre">Connection.execution_options()</span></tt></a>. When using a non-psycopg2 dialect,
this feature works by issuing the command
<tt class="docutils literal"><span class="pre">SET</span> <span class="pre">SESSION</span> <span class="pre">CHARACTERISTICS</span> <span class="pre">AS</span> <span class="pre">TRANSACTION</span> <span class="pre">ISOLATION</span> <span class="pre">LEVEL</span> <span class="pre"><level></span></tt> for
each new connection.</p>
<p>To set isolation level using <a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><tt class="xref py py-func docutils literal"><span class="pre">create_engine()</span></tt></a>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span>
<span class="s">"postgresql+pg8000://scott:tiger@localhost/test"</span><span class="p">,</span>
<span class="n">isolation_level</span><span class="o">=</span><span class="s">"READ UNCOMMITTED"</span>
<span class="p">)</span></pre></div>
</div>
<p>To set using per-connection execution options:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">connection</span> <span class="o">=</span> <span class="n">engine</span><span class="o">.</span><span class="n">connect</span><span class="p">()</span>
<span class="n">connection</span> <span class="o">=</span> <span class="n">connection</span><span class="o">.</span><span class="n">execution_options</span><span class="p">(</span>
<span class="n">isolation_level</span><span class="o">=</span><span class="s">"READ COMMITTED"</span>
<span class="p">)</span></pre></div>
</div>
<p>Valid values for <tt class="docutils literal"><span class="pre">isolation_level</span></tt> include:</p>
<ul class="simple">
<li><tt class="docutils literal"><span class="pre">READ</span> <span class="pre">COMMITTED</span></tt></li>
<li><tt class="docutils literal"><span class="pre">READ</span> <span class="pre">UNCOMMITTED</span></tt></li>
<li><tt class="docutils literal"><span class="pre">REPEATABLE</span> <span class="pre">READ</span></tt></li>
<li><tt class="docutils literal"><span class="pre">SERIALIZABLE</span></tt></li>
</ul>
<p>The <a class="reference internal" href="#module-sqlalchemy.dialects.postgresql.psycopg2" title="sqlalchemy.dialects.postgresql.psycopg2"><tt class="xref py py-mod docutils literal"><span class="pre">psycopg2</span></tt></a> and
<a class="reference internal" href="#module-sqlalchemy.dialects.postgresql.pg8000" title="sqlalchemy.dialects.postgresql.pg8000"><tt class="xref py py-mod docutils literal"><span class="pre">pg8000</span></tt></a> dialects also offer the
special level <tt class="docutils literal"><span class="pre">AUTOCOMMIT</span></tt>.</p>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p><a class="reference internal" href="#psycopg2-isolation-level"><em>Psycopg2 Transaction Isolation Level</em></a></p>
<p class="last"><a class="reference internal" href="#pg8000-isolation-level"><em>pg8000 Transaction Isolation Level</em></a></p>
</div>
</div>
<div class="section" id="remote-schema-table-introspection-and-postgresql-search-path">
<span id="postgresql-schema-reflection"></span><h2>Remote-Schema Table Introspection and Postgresql search_path<a class="headerlink" href="#remote-schema-table-introspection-and-postgresql-search-path" title="Permalink to this headline">¶</a></h2>
<p>The Postgresql dialect can reflect tables from any schema. The
<a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Table.params.schema" title="sqlalchemy.schema.Table"><tt class="xref py py-paramref docutils literal"><span class="pre">Table.schema</span></tt></a> argument, or alternatively the
<a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.MetaData.reflect.params.schema" title="sqlalchemy.schema.MetaData.reflect"><tt class="xref py py-paramref docutils literal"><span class="pre">MetaData.reflect.schema</span></tt></a> argument determines which schema will
be searched for the table or tables. The reflected <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a> objects
will in all cases retain this <tt class="docutils literal"><span class="pre">.schema</span></tt> attribute as was specified.
However, with regards to tables which these <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a> objects refer to
via foreign key constraint, a decision must be made as to how the <tt class="docutils literal"><span class="pre">.schema</span></tt>
is represented in those remote tables, in the case where that remote
schema name is also a member of the current
<a class="reference external" href="http://www.postgresql.org/docs/9.0/static/ddl-schemas.html#DDL-SCHEMAS-PATH">Postgresql search path</a>.</p>
<p>By default, the Postgresql dialect mimics the behavior encouraged by
Postgresql’s own <tt class="docutils literal"><span class="pre">pg_get_constraintdef()</span></tt> builtin procedure. This function
returns a sample definition for a particular foreign key constraint,
omitting the referenced schema name from that definition when the name is
also in the Postgresql schema search path. The interaction below
illustrates this behavior:</p>
<div class="highlight-python"><pre>test=> CREATE TABLE test_schema.referred(id INTEGER PRIMARY KEY);
CREATE TABLE
test=> CREATE TABLE referring(
test(> id INTEGER PRIMARY KEY,
test(> referred_id INTEGER REFERENCES test_schema.referred(id));
CREATE TABLE
test=> SET search_path TO public, test_schema;
test=> SELECT pg_catalog.pg_get_constraintdef(r.oid, true) FROM
test-> pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n
test-> ON n.oid = c.relnamespace
test-> JOIN pg_catalog.pg_constraint r ON c.oid = r.conrelid
test-> WHERE c.relname='referring' AND r.contype = 'f'
test-> ;
pg_get_constraintdef
---------------------------------------------------
FOREIGN KEY (referred_id) REFERENCES referred(id)
(1 row)</pre>
</div>
<p>Above, we created a table <tt class="docutils literal"><span class="pre">referred</span></tt> as a member of the remote schema
<tt class="docutils literal"><span class="pre">test_schema</span></tt>, however when we added <tt class="docutils literal"><span class="pre">test_schema</span></tt> to the
PG <tt class="docutils literal"><span class="pre">search_path</span></tt> and then asked <tt class="docutils literal"><span class="pre">pg_get_constraintdef()</span></tt> for the
<tt class="docutils literal"><span class="pre">FOREIGN</span> <span class="pre">KEY</span></tt> syntax, <tt class="docutils literal"><span class="pre">test_schema</span></tt> was not included in the output of
the function.</p>
<p>On the other hand, if we set the search path back to the typical default
of <tt class="docutils literal"><span class="pre">public</span></tt>:</p>
<div class="highlight-python"><pre>test=> SET search_path TO public;
SET</pre>
</div>
<p>The same query against <tt class="docutils literal"><span class="pre">pg_get_constraintdef()</span></tt> now returns the fully
schema-qualified name for us:</p>
<div class="highlight-python"><pre>test=> SELECT pg_catalog.pg_get_constraintdef(r.oid, true) FROM
test-> pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n
test-> ON n.oid = c.relnamespace
test-> JOIN pg_catalog.pg_constraint r ON c.oid = r.conrelid
test-> WHERE c.relname='referring' AND r.contype = 'f';
pg_get_constraintdef
---------------------------------------------------------------
FOREIGN KEY (referred_id) REFERENCES test_schema.referred(id)
(1 row)</pre>
</div>
<p>SQLAlchemy will by default use the return value of <tt class="docutils literal"><span class="pre">pg_get_constraintdef()</span></tt>
in order to determine the remote schema name. That is, if our <tt class="docutils literal"><span class="pre">search_path</span></tt>
were set to include <tt class="docutils literal"><span class="pre">test_schema</span></tt>, and we invoked a table
reflection process as follows:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">Table</span><span class="p">,</span> <span class="n">MetaData</span><span class="p">,</span> <span class="n">create_engine</span>
<span class="gp">>>> </span><span class="n">engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">"postgresql://scott:tiger@localhost/test"</span><span class="p">)</span>
<span class="gp">>>> </span><span class="k">with</span> <span class="n">engine</span><span class="o">.</span><span class="n">connect</span><span class="p">()</span> <span class="k">as</span> <span class="n">conn</span><span class="p">:</span>
<span class="gp">... </span> <span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s">"SET search_path TO test_schema, public"</span><span class="p">)</span>
<span class="gp">... </span> <span class="n">meta</span> <span class="o">=</span> <span class="n">MetaData</span><span class="p">()</span>
<span class="gp">... </span> <span class="n">referring</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'referring'</span><span class="p">,</span> <span class="n">meta</span><span class="p">,</span>
<span class="gp">... </span> <span class="n">autoload</span><span class="o">=</span><span class="bp">True</span><span class="p">,</span> <span class="n">autoload_with</span><span class="o">=</span><span class="n">conn</span><span class="p">)</span>
<span class="gp">...</span>
<span class="go"><sqlalchemy.engine.result.ResultProxy object at 0x101612ed0></span></pre></div>
</div>
<p>The above process would deliver to the <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.MetaData.tables" title="sqlalchemy.schema.MetaData.tables"><tt class="xref py py-attr docutils literal"><span class="pre">MetaData.tables</span></tt></a> collection
<tt class="docutils literal"><span class="pre">referred</span></tt> table named <strong>without</strong> the schema:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">meta</span><span class="o">.</span><span class="n">tables</span><span class="p">[</span><span class="s">'referred'</span><span class="p">]</span><span class="o">.</span><span class="n">schema</span> <span class="ow">is</span> <span class="bp">None</span>
<span class="go">True</span></pre></div>
</div>
<p>To alter the behavior of reflection such that the referred schema is
maintained regardless of the <tt class="docutils literal"><span class="pre">search_path</span></tt> setting, use the
<tt class="docutils literal"><span class="pre">postgresql_ignore_search_path</span></tt> option, which can be specified as a
dialect-specific argument to both <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a> as well as
<a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.MetaData.reflect" title="sqlalchemy.schema.MetaData.reflect"><tt class="xref py py-meth docutils literal"><span class="pre">MetaData.reflect()</span></tt></a>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="k">with</span> <span class="n">engine</span><span class="o">.</span><span class="n">connect</span><span class="p">()</span> <span class="k">as</span> <span class="n">conn</span><span class="p">:</span>
<span class="gp">... </span> <span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s">"SET search_path TO test_schema, public"</span><span class="p">)</span>
<span class="gp">... </span> <span class="n">meta</span> <span class="o">=</span> <span class="n">MetaData</span><span class="p">()</span>
<span class="gp">... </span> <span class="n">referring</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'referring'</span><span class="p">,</span> <span class="n">meta</span><span class="p">,</span> <span class="n">autoload</span><span class="o">=</span><span class="bp">True</span><span class="p">,</span>
<span class="gp">... </span> <span class="n">autoload_with</span><span class="o">=</span><span class="n">conn</span><span class="p">,</span>
<span class="gp">... </span> <span class="n">postgresql_ignore_search_path</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
<span class="gp">...</span>
<span class="go"><sqlalchemy.engine.result.ResultProxy object at 0x1016126d0></span></pre></div>
</div>
<p>We will now have <tt class="docutils literal"><span class="pre">test_schema.referred</span></tt> stored as schema-qualified:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">meta</span><span class="o">.</span><span class="n">tables</span><span class="p">[</span><span class="s">'test_schema.referred'</span><span class="p">]</span><span class="o">.</span><span class="n">schema</span>
<span class="go">'test_schema'</span></pre></div>
</div>
<div class="sidebar">
<p class="first sidebar-title">Best Practices for Postgresql Schema reflection</p>
<p class="last">The description of Postgresql schema reflection behavior is complex, and
is the product of many years of dealing with widely varied use cases and
user preferences. But in fact, there’s no need to understand any of it if
you just stick to the simplest use pattern: leave the <tt class="docutils literal"><span class="pre">search_path</span></tt> set
to its default of <tt class="docutils literal"><span class="pre">public</span></tt> only, never refer to the name <tt class="docutils literal"><span class="pre">public</span></tt> as
an explicit schema name otherwise, and refer to all other schema names
explicitly when building up a <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a> object. The options
described here are only for those users who can’t, or prefer not to, stay
within these guidelines.</p>
</div>
<p>Note that <strong>in all cases</strong>, the “default” schema is always reflected as
<tt class="docutils literal"><span class="pre">None</span></tt>. The “default” schema on Postgresql is that which is returned by the
Postgresql <tt class="docutils literal"><span class="pre">current_schema()</span></tt> function. On a typical Postgresql
installation, this is the name <tt class="docutils literal"><span class="pre">public</span></tt>. So a table that refers to another
which is in the <tt class="docutils literal"><span class="pre">public</span></tt> (i.e. default) schema will always have the
<tt class="docutils literal"><span class="pre">.schema</span></tt> attribute set to <tt class="docutils literal"><span class="pre">None</span></tt>.</p>
<div class="versionadded">
<p><span>New in version 0.9.2: </span>Added the <tt class="docutils literal"><span class="pre">postgresql_ignore_search_path</span></tt>
dialect-level option accepted by <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a> and
<a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.MetaData.reflect" title="sqlalchemy.schema.MetaData.reflect"><tt class="xref py py-meth docutils literal"><span class="pre">MetaData.reflect()</span></tt></a>.</p>
</div>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p class="last"><a class="reference external" href="http://www.postgresql.org/docs/9.0/static/ddl-schemas.html#DDL-SCHEMAS-PATH">The Schema Search Path</a>
- on the Postgresql website.</p>
</div>
</div>
<div class="section" id="insert-update-returning">
<h2>INSERT/UPDATE...RETURNING<a class="headerlink" href="#insert-update-returning" title="Permalink to this headline">¶</a></h2>
<p>The dialect supports PG 8.2’s <tt class="docutils literal"><span class="pre">INSERT..RETURNING</span></tt>, <tt class="docutils literal"><span class="pre">UPDATE..RETURNING</span></tt> and
<tt class="docutils literal"><span class="pre">DELETE..RETURNING</span></tt> syntaxes. <tt class="docutils literal"><span class="pre">INSERT..RETURNING</span></tt> is used by default
for single-row INSERT statements in order to fetch newly generated
primary key identifiers. To specify an explicit <tt class="docutils literal"><span class="pre">RETURNING</span></tt> clause,
use the <tt class="xref py py-meth docutils literal"><span class="pre">_UpdateBase.returning()</span></tt> method on a per-statement basis:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="c"># INSERT..RETURNING</span>
<span class="n">result</span> <span class="o">=</span> <span class="n">table</span><span class="o">.</span><span class="n">insert</span><span class="p">()</span><span class="o">.</span><span class="n">returning</span><span class="p">(</span><span class="n">table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">col1</span><span class="p">,</span> <span class="n">table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">col2</span><span class="p">)</span><span class="o">.</span>\
<span class="n">values</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">'foo'</span><span class="p">)</span>
<span class="k">print</span> <span class="n">result</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>
<span class="c"># UPDATE..RETURNING</span>
<span class="n">result</span> <span class="o">=</span> <span class="n">table</span><span class="o">.</span><span class="n">update</span><span class="p">()</span><span class="o">.</span><span class="n">returning</span><span class="p">(</span><span class="n">table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">col1</span><span class="p">,</span> <span class="n">table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">col2</span><span class="p">)</span><span class="o">.</span>\
<span class="n">where</span><span class="p">(</span><span class="n">table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span><span class="o">==</span><span class="s">'foo'</span><span class="p">)</span><span class="o">.</span><span class="n">values</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">'bar'</span><span class="p">)</span>
<span class="k">print</span> <span class="n">result</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>
<span class="c"># DELETE..RETURNING</span>
<span class="n">result</span> <span class="o">=</span> <span class="n">table</span><span class="o">.</span><span class="n">delete</span><span class="p">()</span><span class="o">.</span><span class="n">returning</span><span class="p">(</span><span class="n">table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">col1</span><span class="p">,</span> <span class="n">table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">col2</span><span class="p">)</span><span class="o">.</span>\
<span class="n">where</span><span class="p">(</span><span class="n">table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span><span class="o">==</span><span class="s">'foo'</span><span class="p">)</span>
<span class="k">print</span> <span class="n">result</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span></pre></div>
</div>
</div>
<div class="section" id="full-text-search">
<span id="postgresql-match"></span><h2>Full Text Search<a class="headerlink" href="#full-text-search" title="Permalink to this headline">¶</a></h2>
<p>SQLAlchemy makes available the Postgresql <tt class="docutils literal"><span class="pre">@@</span></tt> operator via the
<a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.expression.ColumnElement.match" title="sqlalchemy.sql.expression.ColumnElement.match"><tt class="xref py py-meth docutils literal"><span class="pre">ColumnElement.match()</span></tt></a> method on any textual column expression.
On a Postgresql dialect, an expression like the following:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">select</span><span class="p">([</span><span class="n">sometable</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">text</span><span class="o">.</span><span class="n">match</span><span class="p">(</span><span class="s">"search string"</span><span class="p">)])</span></pre></div>
</div>
<p>will emit to the database:</p>
<div class="highlight-python"><pre>SELECT text @@ to_tsquery('search string') FROM table</pre>
</div>
<p>The Postgresql text search functions such as <tt class="docutils literal"><span class="pre">to_tsquery()</span></tt>
and <tt class="docutils literal"><span class="pre">to_tsvector()</span></tt> are available
explicitly using the standard <tt class="xref py py-attr docutils literal"><span class="pre">func</span></tt> construct. For example:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">select</span><span class="p">([</span>
<span class="n">func</span><span class="o">.</span><span class="n">to_tsvector</span><span class="p">(</span><span class="s">'fat cats ate rats'</span><span class="p">)</span><span class="o">.</span><span class="n">match</span><span class="p">(</span><span class="s">'cat & rat'</span><span class="p">)</span>
<span class="p">])</span></pre></div>
</div>
<p>Emits the equivalent of:</p>
<div class="highlight-python"><pre>SELECT to_tsvector('fat cats ate rats') @@ to_tsquery('cat & rat')</pre>
</div>
<p>The <a class="reference internal" href="#sqlalchemy.dialects.postgresql.TSVECTOR" title="sqlalchemy.dialects.postgresql.TSVECTOR"><tt class="xref py py-class docutils literal"><span class="pre">postgresql.TSVECTOR</span></tt></a> type can provide for explicit CAST:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy.dialects.postgresql</span> <span class="kn">import</span> <span class="n">TSVECTOR</span>
<span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">select</span><span class="p">,</span> <span class="n">cast</span>
<span class="n">select</span><span class="p">([</span><span class="n">cast</span><span class="p">(</span><span class="s">"some text"</span><span class="p">,</span> <span class="n">TSVECTOR</span><span class="p">)])</span></pre></div>
</div>
<p>produces a statement equivalent to:</p>
<div class="highlight-python"><pre>SELECT CAST('some text' AS TSVECTOR) AS anon_1</pre>
</div>
<p>Full Text Searches in Postgresql are influenced by a combination of: the
PostgresSQL setting of <tt class="docutils literal"><span class="pre">default_text_search_config</span></tt>, the <tt class="docutils literal"><span class="pre">regconfig</span></tt> used
to build the GIN/GiST indexes, and the <tt class="docutils literal"><span class="pre">regconfig</span></tt> optionally passed in
during a query.</p>
<p>When performing a Full Text Search against a column that has a GIN or
GiST index that is already pre-computed (which is common on full text
searches) one may need to explicitly pass in a particular PostgresSQL
<tt class="docutils literal"><span class="pre">regconfig</span></tt> value to ensure the query-planner utilizes the index and does
not re-compute the column on demand.</p>
<p>In order to provide for this explicit query planning, or to use different
search strategies, the <tt class="docutils literal"><span class="pre">match</span></tt> method accepts a <tt class="docutils literal"><span class="pre">postgresql_regconfig</span></tt>
keyword argument.</p>
<blockquote>
<div><dl class="docutils">
<dt>select([mytable.c.id]).where(</dt>
<dd>mytable.c.title.match(‘somestring’, postgresql_regconfig=’english’)</dd>
</dl>
<p>)</p>
</div></blockquote>
<p>Emits the equivalent of:</p>
<div class="highlight-python"><pre>SELECT mytable.id FROM mytable
WHERE mytable.title @@ to_tsquery('english', 'somestring')</pre>
</div>
<p>One can also specifically pass in a <cite>‘regconfig’</cite> value to the
<tt class="docutils literal"><span class="pre">to_tsvector()</span></tt> command as the initial argument.</p>
<blockquote>
<div><dl class="docutils">
<dt>select([mytable.c.id]).where(</dt>
<dd><blockquote class="first">
<div>func.to_tsvector(‘english’, mytable.c.title ) .match(‘somestring’, postgresql_regconfig=’english’)</div></blockquote>
<p class="last">)</p>
</dd>
</dl>
</div></blockquote>
<p>produces a statement equivalent to:</p>
<div class="highlight-python"><pre>SELECT mytable.id FROM mytable
WHERE to_tsvector('english', mytable.title) @@
to_tsquery('english', 'somestring')</pre>
</div>
<p>It is recommended that you use the <tt class="docutils literal"><span class="pre">EXPLAIN</span> <span class="pre">ANALYZE...</span></tt> tool from
PostgresSQL to ensure that you are generating queries with SQLAlchemy that
take full advantage of any indexes you may have created for full text search.</p>
</div>
<div class="section" id="from-only">
<h2>FROM ONLY ...<a class="headerlink" href="#from-only" title="Permalink to this headline">¶</a></h2>
<p>The dialect supports PostgreSQL’s ONLY keyword for targeting only a particular
table in an inheritance hierarchy. This can be used to produce the
<tt class="docutils literal"><span class="pre">SELECT</span> <span class="pre">...</span> <span class="pre">FROM</span> <span class="pre">ONLY</span></tt>, <tt class="docutils literal"><span class="pre">UPDATE</span> <span class="pre">ONLY</span> <span class="pre">...</span></tt>, and <tt class="docutils literal"><span class="pre">DELETE</span> <span class="pre">FROM</span> <span class="pre">ONLY</span> <span class="pre">...</span></tt>
syntaxes. It uses SQLAlchemy’s hints mechanism:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="c"># SELECT ... FROM ONLY ...</span>
<span class="n">result</span> <span class="o">=</span> <span class="n">table</span><span class="o">.</span><span class="n">select</span><span class="p">()</span><span class="o">.</span><span class="n">with_hint</span><span class="p">(</span><span class="n">table</span><span class="p">,</span> <span class="s">'ONLY'</span><span class="p">,</span> <span class="s">'postgresql'</span><span class="p">)</span>
<span class="k">print</span> <span class="n">result</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>
<span class="c"># UPDATE ONLY ...</span>
<span class="n">table</span><span class="o">.</span><span class="n">update</span><span class="p">(</span><span class="n">values</span><span class="o">=</span><span class="nb">dict</span><span class="p">(</span><span class="n">foo</span><span class="o">=</span><span class="s">'bar'</span><span class="p">))</span><span class="o">.</span><span class="n">with_hint</span><span class="p">(</span><span class="s">'ONLY'</span><span class="p">,</span>
<span class="n">dialect_name</span><span class="o">=</span><span class="s">'postgresql'</span><span class="p">)</span>
<span class="c"># DELETE FROM ONLY ...</span>
<span class="n">table</span><span class="o">.</span><span class="n">delete</span><span class="p">()</span><span class="o">.</span><span class="n">with_hint</span><span class="p">(</span><span class="s">'ONLY'</span><span class="p">,</span> <span class="n">dialect_name</span><span class="o">=</span><span class="s">'postgresql'</span><span class="p">)</span></pre></div>
</div>
</div>
<div class="section" id="postgresql-specific-index-options">
<span id="postgresql-indexes"></span><h2>Postgresql-Specific Index Options<a class="headerlink" href="#postgresql-specific-index-options" title="Permalink to this headline">¶</a></h2>
<p>Several extensions to the <a class="reference internal" href="../core/constraints.html#sqlalchemy.schema.Index" title="sqlalchemy.schema.Index"><tt class="xref py py-class docutils literal"><span class="pre">Index</span></tt></a> construct are available, specific
to the PostgreSQL dialect.</p>
<div class="section" id="partial-indexes">
<h3>Partial Indexes<a class="headerlink" href="#partial-indexes" title="Permalink to this headline">¶</a></h3>
<p>Partial indexes add criterion to the index definition so that the index is
applied to a subset of rows. These can be specified on <a class="reference internal" href="../core/constraints.html#sqlalchemy.schema.Index" title="sqlalchemy.schema.Index"><tt class="xref py py-class docutils literal"><span class="pre">Index</span></tt></a>
using the <tt class="docutils literal"><span class="pre">postgresql_where</span></tt> keyword argument:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">Index</span><span class="p">(</span><span class="s">'my_index'</span><span class="p">,</span> <span class="n">my_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">,</span> <span class="n">postgresql_where</span><span class="o">=</span><span class="n">tbl</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">value</span> <span class="o">></span> <span class="mi">10</span><span class="p">)</span></pre></div>
</div>
</div>
<div class="section" id="operator-classes">
<h3>Operator Classes<a class="headerlink" href="#operator-classes" title="Permalink to this headline">¶</a></h3>
<p>PostgreSQL allows the specification of an <em>operator class</em> for each column of
an index (see
<a class="reference external" href="http://www.postgresql.org/docs/8.3/interactive/indexes-opclass.html">http://www.postgresql.org/docs/8.3/interactive/indexes-opclass.html</a>).
The <a class="reference internal" href="../core/constraints.html#sqlalchemy.schema.Index" title="sqlalchemy.schema.Index"><tt class="xref py py-class docutils literal"><span class="pre">Index</span></tt></a> construct allows these to be specified via the
<tt class="docutils literal"><span class="pre">postgresql_ops</span></tt> keyword argument:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">Index</span><span class="p">(</span><span class="s">'my_index'</span><span class="p">,</span> <span class="n">my_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">,</span> <span class="n">my_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data</span><span class="p">,</span>
<span class="n">postgresql_ops</span><span class="o">=</span><span class="p">{</span>
<span class="s">'data'</span><span class="p">:</span> <span class="s">'text_pattern_ops'</span><span class="p">,</span>
<span class="s">'id'</span><span class="p">:</span> <span class="s">'int4_ops'</span>
<span class="p">})</span></pre></div>
</div>
<div class="versionadded">
<p><span>New in version 0.7.2: </span><tt class="docutils literal"><span class="pre">postgresql_ops</span></tt> keyword argument to <a class="reference internal" href="../core/constraints.html#sqlalchemy.schema.Index" title="sqlalchemy.schema.Index"><tt class="xref py py-class docutils literal"><span class="pre">Index</span></tt></a> construct.</p>
</div>
<p>Note that the keys in the <tt class="docutils literal"><span class="pre">postgresql_ops</span></tt> dictionary are the “key” name of
the <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal"><span class="pre">Column</span></tt></a>, i.e. the name used to access it from the <tt class="docutils literal"><span class="pre">.c</span></tt>
collection of <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a>, which can be configured to be different than
the actual name of the column as expressed in the database.</p>
</div>
<div class="section" id="index-types">
<h3>Index Types<a class="headerlink" href="#index-types" title="Permalink to this headline">¶</a></h3>
<p>PostgreSQL provides several index types: B-Tree, Hash, GiST, and GIN, as well
as the ability for users to create their own (see
<a class="reference external" href="http://www.postgresql.org/docs/8.3/static/indexes-types.html">http://www.postgresql.org/docs/8.3/static/indexes-types.html</a>). These can be
specified on <a class="reference internal" href="../core/constraints.html#sqlalchemy.schema.Index" title="sqlalchemy.schema.Index"><tt class="xref py py-class docutils literal"><span class="pre">Index</span></tt></a> using the <tt class="docutils literal"><span class="pre">postgresql_using</span></tt> keyword argument:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">Index</span><span class="p">(</span><span class="s">'my_index'</span><span class="p">,</span> <span class="n">my_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data</span><span class="p">,</span> <span class="n">postgresql_using</span><span class="o">=</span><span class="s">'gin'</span><span class="p">)</span></pre></div>
</div>
<p>The value passed to the keyword argument will be simply passed through to the
underlying CREATE INDEX command, so it <em>must</em> be a valid index type for your
version of PostgreSQL.</p>
</div>
</div>
<div class="section" id="postgresql-data-types">
<h2>PostgreSQL Data Types<a class="headerlink" href="#postgresql-data-types" title="Permalink to this headline">¶</a></h2>
<p>As with all SQLAlchemy dialects, all UPPERCASE types that are known to be
valid with Postgresql are importable from the top level dialect, whether
they originate from <a class="reference internal" href="../core/types.html#module-sqlalchemy.types" title="sqlalchemy.types"><tt class="xref py py-mod docutils literal"><span class="pre">sqlalchemy.types</span></tt></a> or from the local dialect:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy.dialects.postgresql</span> <span class="kn">import</span> \
<span class="n">ARRAY</span><span class="p">,</span> <span class="n">BIGINT</span><span class="p">,</span> <span class="n">BIT</span><span class="p">,</span> <span class="n">BOOLEAN</span><span class="p">,</span> <span class="n">BYTEA</span><span class="p">,</span> <span class="n">CHAR</span><span class="p">,</span> <span class="n">CIDR</span><span class="p">,</span> <span class="n">DATE</span><span class="p">,</span> \
<span class="n">DOUBLE_PRECISION</span><span class="p">,</span> <span class="n">ENUM</span><span class="p">,</span> <span class="n">FLOAT</span><span class="p">,</span> <span class="n">HSTORE</span><span class="p">,</span> <span class="n">INET</span><span class="p">,</span> <span class="n">INTEGER</span><span class="p">,</span> \
<span class="n">INTERVAL</span><span class="p">,</span> <span class="n">JSON</span><span class="p">,</span> <span class="n">JSONB</span><span class="p">,</span> <span class="n">MACADDR</span><span class="p">,</span> <span class="n">NUMERIC</span><span class="p">,</span> <span class="n">OID</span><span class="p">,</span> <span class="n">REAL</span><span class="p">,</span> <span class="n">SMALLINT</span><span class="p">,</span> <span class="n">TEXT</span><span class="p">,</span> \
<span class="n">TIME</span><span class="p">,</span> <span class="n">TIMESTAMP</span><span class="p">,</span> <span class="n">UUID</span><span class="p">,</span> <span class="n">VARCHAR</span><span class="p">,</span> <span class="n">INT4RANGE</span><span class="p">,</span> <span class="n">INT8RANGE</span><span class="p">,</span> <span class="n">NUMRANGE</span><span class="p">,</span> \
<span class="n">DATERANGE</span><span class="p">,</span> <span class="n">TSRANGE</span><span class="p">,</span> <span class="n">TSTZRANGE</span><span class="p">,</span> <span class="n">TSVECTOR</span></pre></div>
</div>
<p>Types which are specific to PostgreSQL, or have PostgreSQL-specific
construction arguments, are as follows:</p>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.array">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.postgresql.</tt><tt class="descname">array</tt><big>(</big><em>clauses</em>, <em>**kw</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.array" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.expression.Tuple" title="sqlalchemy.sql.expression.Tuple"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.sql.expression.Tuple</span></tt></a></p>
<p>A Postgresql ARRAY literal.</p>
<p>This is used to produce ARRAY literals in SQL expressions, e.g.:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy.dialects.postgresql</span> <span class="kn">import</span> <span class="n">array</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.dialects</span> <span class="kn">import</span> <span class="n">postgresql</span>
<span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">select</span><span class="p">,</span> <span class="n">func</span>
<span class="n">stmt</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span>
<span class="n">array</span><span class="p">([</span><span class="mi">1</span><span class="p">,</span><span class="mi">2</span><span class="p">])</span> <span class="o">+</span> <span class="n">array</span><span class="p">([</span><span class="mi">3</span><span class="p">,</span><span class="mi">4</span><span class="p">,</span><span class="mi">5</span><span class="p">])</span>
<span class="p">])</span>
<span class="k">print</span> <span class="n">stmt</span><span class="o">.</span><span class="n">compile</span><span class="p">(</span><span class="n">dialect</span><span class="o">=</span><span class="n">postgresql</span><span class="o">.</span><span class="n">dialect</span><span class="p">())</span></pre></div>
</div>
<p>Produces the SQL:</p>
<div class="highlight-python"><pre>SELECT ARRAY[%(param_1)s, %(param_2)s] ||
ARRAY[%(param_3)s, %(param_4)s, %(param_5)s]) AS anon_1</pre>
</div>
<p>An instance of <a class="reference internal" href="#sqlalchemy.dialects.postgresql.array" title="sqlalchemy.dialects.postgresql.array"><tt class="xref py py-class docutils literal"><span class="pre">array</span></tt></a> will always have the datatype
<a class="reference internal" href="#sqlalchemy.dialects.postgresql.ARRAY" title="sqlalchemy.dialects.postgresql.ARRAY"><tt class="xref py py-class docutils literal"><span class="pre">ARRAY</span></tt></a>. The “inner” type of the array is inferred from
the values present, unless the <tt class="docutils literal"><span class="pre">type_</span></tt> keyword argument is passed:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">array</span><span class="p">([</span><span class="s">'foo'</span><span class="p">,</span> <span class="s">'bar'</span><span class="p">],</span> <span class="n">type_</span><span class="o">=</span><span class="n">CHAR</span><span class="p">)</span></pre></div>
</div>
<div class="versionadded">
<p><span>New in version 0.8: </span>Added the <a class="reference internal" href="#sqlalchemy.dialects.postgresql.array" title="sqlalchemy.dialects.postgresql.array"><tt class="xref py py-class docutils literal"><span class="pre">array</span></tt></a> literal type.</p>
</div>
<p>See also:</p>
<p><a class="reference internal" href="#sqlalchemy.dialects.postgresql.ARRAY" title="sqlalchemy.dialects.postgresql.ARRAY"><tt class="xref py py-class docutils literal"><span class="pre">postgresql.ARRAY</span></tt></a></p>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.ARRAY">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.postgresql.</tt><tt class="descname">ARRAY</tt><big>(</big><em>item_type</em>, <em>as_tuple=False</em>, <em>dimensions=None</em>, <em>zero_indexes=False</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.ARRAY" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/types.html#sqlalchemy.types.Concatenable" title="sqlalchemy.types.Concatenable"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.Concatenable</span></tt></a>, <a class="reference internal" href="../core/types.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.TypeEngine</span></tt></a></p>
<p>Postgresql ARRAY type.</p>
<p>Represents values as Python lists.</p>
<p>An <a class="reference internal" href="#sqlalchemy.dialects.postgresql.ARRAY" title="sqlalchemy.dialects.postgresql.ARRAY"><tt class="xref py py-class docutils literal"><span class="pre">ARRAY</span></tt></a> type is constructed given the “type”
of element:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">mytable</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">"mytable"</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s">"data"</span><span class="p">,</span> <span class="n">ARRAY</span><span class="p">(</span><span class="n">Integer</span><span class="p">))</span>
<span class="p">)</span></pre></div>
</div>
<p>The above type represents an N-dimensional array,
meaning Postgresql will interpret values with any number
of dimensions automatically. To produce an INSERT
construct that passes in a 1-dimensional array of integers:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">connection</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span>
<span class="n">mytable</span><span class="o">.</span><span class="n">insert</span><span class="p">(),</span>
<span class="n">data</span><span class="o">=</span><span class="p">[</span><span class="mi">1</span><span class="p">,</span><span class="mi">2</span><span class="p">,</span><span class="mi">3</span><span class="p">]</span>
<span class="p">)</span></pre></div>
</div>
<p>The <a class="reference internal" href="#sqlalchemy.dialects.postgresql.ARRAY" title="sqlalchemy.dialects.postgresql.ARRAY"><tt class="xref py py-class docutils literal"><span class="pre">ARRAY</span></tt></a> type can be constructed given a fixed number
of dimensions:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">mytable</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">"mytable"</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s">"data"</span><span class="p">,</span> <span class="n">ARRAY</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">dimensions</span><span class="o">=</span><span class="mi">2</span><span class="p">))</span>
<span class="p">)</span></pre></div>
</div>
<p>This has the effect of the <a class="reference internal" href="#sqlalchemy.dialects.postgresql.ARRAY" title="sqlalchemy.dialects.postgresql.ARRAY"><tt class="xref py py-class docutils literal"><span class="pre">ARRAY</span></tt></a> type
specifying that number of bracketed blocks when a <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a>
is used in a CREATE TABLE statement, or when the type is used
within a <a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.expression.cast" title="sqlalchemy.sql.expression.cast"><tt class="xref py py-func docutils literal"><span class="pre">expression.cast()</span></tt></a> construct; it also causes
the bind parameter and result set processing of the type
to optimize itself to expect exactly that number of dimensions.
Note that Postgresql itself still allows N dimensions with such a type.</p>
<p>SQL expressions of type <a class="reference internal" href="#sqlalchemy.dialects.postgresql.ARRAY" title="sqlalchemy.dialects.postgresql.ARRAY"><tt class="xref py py-class docutils literal"><span class="pre">ARRAY</span></tt></a> have support for “index” and
“slice” behavior. The Python <tt class="docutils literal"><span class="pre">[]</span></tt> operator works normally here, given
integer indexes or slices. Note that Postgresql arrays default
to 1-based indexing. The operator produces binary expression
constructs which will produce the appropriate SQL, both for
SELECT statements:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">select</span><span class="p">([</span><span class="n">mytable</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data</span><span class="p">[</span><span class="mi">5</span><span class="p">],</span> <span class="n">mytable</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data</span><span class="p">[</span><span class="mi">2</span><span class="p">:</span><span class="mi">7</span><span class="p">]])</span></pre></div>
</div>
<p>as well as UPDATE statements when the <a class="reference internal" href="../core/dml.html#sqlalchemy.sql.expression.Update.values" title="sqlalchemy.sql.expression.Update.values"><tt class="xref py py-meth docutils literal"><span class="pre">Update.values()</span></tt></a> method
is used:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">mytable</span><span class="o">.</span><span class="n">update</span><span class="p">()</span><span class="o">.</span><span class="n">values</span><span class="p">({</span>
<span class="n">mytable</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data</span><span class="p">[</span><span class="mi">5</span><span class="p">]:</span> <span class="mi">7</span><span class="p">,</span>
<span class="n">mytable</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data</span><span class="p">[</span><span class="mi">2</span><span class="p">:</span><span class="mi">7</span><span class="p">]:</span> <span class="p">[</span><span class="mi">1</span><span class="p">,</span> <span class="mi">2</span><span class="p">,</span> <span class="mi">3</span><span class="p">]</span>
<span class="p">})</span></pre></div>
</div>
<p><a class="reference internal" href="#sqlalchemy.dialects.postgresql.ARRAY" title="sqlalchemy.dialects.postgresql.ARRAY"><tt class="xref py py-class docutils literal"><span class="pre">ARRAY</span></tt></a> provides special methods for containment operations,
e.g.:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">mytable</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data</span><span class="o">.</span><span class="n">contains</span><span class="p">([</span><span class="mi">1</span><span class="p">,</span> <span class="mi">2</span><span class="p">])</span></pre></div>
</div>
<p>For a full list of special methods see <a class="reference internal" href="#sqlalchemy.dialects.postgresql.ARRAY.Comparator" title="sqlalchemy.dialects.postgresql.ARRAY.Comparator"><tt class="xref py py-class docutils literal"><span class="pre">ARRAY.Comparator</span></tt></a>.</p>
<div class="versionadded">
<p><span>New in version 0.8: </span>Added support for index and slice operations
to the <a class="reference internal" href="#sqlalchemy.dialects.postgresql.ARRAY" title="sqlalchemy.dialects.postgresql.ARRAY"><tt class="xref py py-class docutils literal"><span class="pre">ARRAY</span></tt></a> type, including support for UPDATE
statements, and special array containment operations.</p>
</div>
<p>The <a class="reference internal" href="#sqlalchemy.dialects.postgresql.ARRAY" title="sqlalchemy.dialects.postgresql.ARRAY"><tt class="xref py py-class docutils literal"><span class="pre">ARRAY</span></tt></a> type may not be supported on all DBAPIs.
It is known to work on psycopg2 and not pg8000.</p>
<p>See also:</p>
<p><a class="reference internal" href="#sqlalchemy.dialects.postgresql.array" title="sqlalchemy.dialects.postgresql.array"><tt class="xref py py-class docutils literal"><span class="pre">postgresql.array</span></tt></a> - produce a literal array value.</p>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.ARRAY.Comparator">
<em class="property">class </em><tt class="descname">Comparator</tt><big>(</big><em>expr</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.ARRAY.Comparator" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.Comparator</span></tt></p>
<p>Define comparison operations for <a class="reference internal" href="#sqlalchemy.dialects.postgresql.ARRAY" title="sqlalchemy.dialects.postgresql.ARRAY"><tt class="xref py py-class docutils literal"><span class="pre">ARRAY</span></tt></a>.</p>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.ARRAY.Comparator.all">
<tt class="descname">all</tt><big>(</big><em>other</em>, <em>operator=<built-in function eq></em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.ARRAY.Comparator.all" title="Permalink to this definition">¶</a></dt>
<dd><p>Return <tt class="docutils literal"><span class="pre">other</span> <span class="pre">operator</span> <span class="pre">ALL</span> <span class="pre">(array)</span></tt> clause.</p>
<p>Argument places are switched, because ALL requires array
expression to be on the right hand-side.</p>
<p>E.g.:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy.sql</span> <span class="kn">import</span> <span class="n">operators</span>
<span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span>
<span class="n">select</span><span class="p">([</span><span class="n">table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data</span><span class="p">])</span><span class="o">.</span><span class="n">where</span><span class="p">(</span>
<span class="n">table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data</span><span class="o">.</span><span class="n">all</span><span class="p">(</span><span class="mi">7</span><span class="p">,</span> <span class="n">operator</span><span class="o">=</span><span class="n">operators</span><span class="o">.</span><span class="n">lt</span><span class="p">)</span>
<span class="p">)</span>
<span class="p">)</span></pre></div>
</div>
<table class="docutils field-list" frame="void" rules="none">
<col class="field-name" />
<col class="field-body" />
<tbody valign="top">
<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body"><ul class="first last simple">
<li><span class="target" id="sqlalchemy.dialects.postgresql.ARRAY.Comparator.all.params.other"></span><strong>other</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.postgresql.ARRAY.Comparator.all.params.other">¶</a> – expression to be compared</li>
<li><span class="target" id="sqlalchemy.dialects.postgresql.ARRAY.Comparator.all.params.operator"></span><strong>operator</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.postgresql.ARRAY.Comparator.all.params.operator">¶</a> – an operator object from the
<tt class="xref py py-mod docutils literal"><span class="pre">sqlalchemy.sql.operators</span></tt>
package, defaults to <tt class="xref py py-func docutils literal"><span class="pre">operators.eq()</span></tt>.</li>
</ul>
</td>
</tr>
</tbody>
</table>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p><a class="reference internal" href="#sqlalchemy.dialects.postgresql.All" title="sqlalchemy.dialects.postgresql.All"><tt class="xref py py-class docutils literal"><span class="pre">postgresql.All</span></tt></a></p>
<p class="last"><a class="reference internal" href="#sqlalchemy.dialects.postgresql.ARRAY.Comparator.any" title="sqlalchemy.dialects.postgresql.ARRAY.Comparator.any"><tt class="xref py py-meth docutils literal"><span class="pre">postgresql.ARRAY.Comparator.any()</span></tt></a></p>
</div>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.ARRAY.Comparator.any">
<tt class="descname">any</tt><big>(</big><em>other</em>, <em>operator=<built-in function eq></em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.ARRAY.Comparator.any" title="Permalink to this definition">¶</a></dt>
<dd><p>Return <tt class="docutils literal"><span class="pre">other</span> <span class="pre">operator</span> <span class="pre">ANY</span> <span class="pre">(array)</span></tt> clause.</p>
<p>Argument places are switched, because ANY requires array
expression to be on the right hand-side.</p>
<p>E.g.:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy.sql</span> <span class="kn">import</span> <span class="n">operators</span>
<span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span>
<span class="n">select</span><span class="p">([</span><span class="n">table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data</span><span class="p">])</span><span class="o">.</span><span class="n">where</span><span class="p">(</span>
<span class="n">table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data</span><span class="o">.</span><span class="n">any</span><span class="p">(</span><span class="mi">7</span><span class="p">,</span> <span class="n">operator</span><span class="o">=</span><span class="n">operators</span><span class="o">.</span><span class="n">lt</span><span class="p">)</span>
<span class="p">)</span>
<span class="p">)</span></pre></div>
</div>
<table class="docutils field-list" frame="void" rules="none">
<col class="field-name" />
<col class="field-body" />
<tbody valign="top">
<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body"><ul class="first last simple">
<li><span class="target" id="sqlalchemy.dialects.postgresql.ARRAY.Comparator.any.params.other"></span><strong>other</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.postgresql.ARRAY.Comparator.any.params.other">¶</a> – expression to be compared</li>
<li><span class="target" id="sqlalchemy.dialects.postgresql.ARRAY.Comparator.any.params.operator"></span><strong>operator</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.postgresql.ARRAY.Comparator.any.params.operator">¶</a> – an operator object from the
<tt class="xref py py-mod docutils literal"><span class="pre">sqlalchemy.sql.operators</span></tt>
package, defaults to <tt class="xref py py-func docutils literal"><span class="pre">operators.eq()</span></tt>.</li>
</ul>
</td>
</tr>
</tbody>
</table>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p><a class="reference internal" href="#sqlalchemy.dialects.postgresql.Any" title="sqlalchemy.dialects.postgresql.Any"><tt class="xref py py-class docutils literal"><span class="pre">postgresql.Any</span></tt></a></p>
<p class="last"><a class="reference internal" href="#sqlalchemy.dialects.postgresql.ARRAY.Comparator.all" title="sqlalchemy.dialects.postgresql.ARRAY.Comparator.all"><tt class="xref py py-meth docutils literal"><span class="pre">postgresql.ARRAY.Comparator.all()</span></tt></a></p>
</div>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.ARRAY.Comparator.contained_by">
<tt class="descname">contained_by</tt><big>(</big><em>other</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.ARRAY.Comparator.contained_by" title="Permalink to this definition">¶</a></dt>
<dd><p>Boolean expression. Test if elements are a proper subset of the
elements of the argument array expression.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.ARRAY.Comparator.contains">
<tt class="descname">contains</tt><big>(</big><em>other</em>, <em>**kwargs</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.ARRAY.Comparator.contains" title="Permalink to this definition">¶</a></dt>
<dd><p>Boolean expression. Test if elements are a superset of the
elements of the argument array expression.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.ARRAY.Comparator.overlap">
<tt class="descname">overlap</tt><big>(</big><em>other</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.ARRAY.Comparator.overlap" title="Permalink to this definition">¶</a></dt>
<dd><p>Boolean expression. Test if array has elements in common with
an argument array expression.</p>
</dd></dl>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.ARRAY.__init__">
<tt class="descclassname">ARRAY.</tt><tt class="descname">__init__</tt><big>(</big><em>item_type</em>, <em>as_tuple=False</em>, <em>dimensions=None</em>, <em>zero_indexes=False</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.ARRAY.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Construct an ARRAY.</p>
<p>E.g.:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">Column</span><span class="p">(</span><span class="s">'myarray'</span><span class="p">,</span> <span class="n">ARRAY</span><span class="p">(</span><span class="n">Integer</span><span class="p">))</span></pre></div>
</div>
<p>Arguments are:</p>
<table class="docutils field-list" frame="void" rules="none">
<col class="field-name" />
<col class="field-body" />
<tbody valign="top">
<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body"><ul class="first last simple">
<li><span class="target" id="sqlalchemy.dialects.postgresql.ARRAY.params.item_type"></span><strong>item_type</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.postgresql.ARRAY.params.item_type">¶</a> – The data type of items of this array. Note that
dimensionality is irrelevant here, so multi-dimensional arrays like
<tt class="docutils literal"><span class="pre">INTEGER[][]</span></tt>, are constructed as <tt class="docutils literal"><span class="pre">ARRAY(Integer)</span></tt>, not as
<tt class="docutils literal"><span class="pre">ARRAY(ARRAY(Integer))</span></tt> or such.</li>
<li><span class="target" id="sqlalchemy.dialects.postgresql.ARRAY.params.as_tuple"></span><strong>as_tuple=False</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.postgresql.ARRAY.params.as_tuple">¶</a> – Specify whether return results
should be converted to tuples from lists. DBAPIs such
as psycopg2 return lists by default. When tuples are
returned, the results are hashable.</li>
<li><span class="target" id="sqlalchemy.dialects.postgresql.ARRAY.params.dimensions"></span><strong>dimensions</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.postgresql.ARRAY.params.dimensions">¶</a> – if non-None, the ARRAY will assume a fixed
number of dimensions. This will cause the DDL emitted for this
ARRAY to include the exact number of bracket clauses <tt class="docutils literal"><span class="pre">[]</span></tt>,
and will also optimize the performance of the type overall.
Note that PG arrays are always implicitly “non-dimensioned”,
meaning they can store any number of dimensions no matter how
they were declared.</li>
<li><span class="target" id="sqlalchemy.dialects.postgresql.ARRAY.params.zero_indexes"></span><strong>zero_indexes=False</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.postgresql.ARRAY.params.zero_indexes">¶</a> – <p>when True, index values will be converted
between Python zero-based and Postgresql one-based indexes, e.g.
a value of one will be added to all index values before passing
to the database.</p>
<div class="versionadded">
<p><span>New in version 0.9.5.</span></p>
</div>
</li>
</ul>
</td>
</tr>
</tbody>
</table>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.Any">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.postgresql.</tt><tt class="descname">Any</tt><big>(</big><em>left</em>, <em>right</em>, <em>operator=<built-in function eq></em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.Any" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.expression.ColumnElement" title="sqlalchemy.sql.expression.ColumnElement"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.sql.expression.ColumnElement</span></tt></a></p>
<p>Represent the clause <tt class="docutils literal"><span class="pre">left</span> <span class="pre">operator</span> <span class="pre">ANY</span> <span class="pre">(right)</span></tt>. <tt class="docutils literal"><span class="pre">right</span></tt> must be
an array expression.</p>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p><a class="reference internal" href="#sqlalchemy.dialects.postgresql.ARRAY" title="sqlalchemy.dialects.postgresql.ARRAY"><tt class="xref py py-class docutils literal"><span class="pre">postgresql.ARRAY</span></tt></a></p>
<p class="last"><a class="reference internal" href="#sqlalchemy.dialects.postgresql.ARRAY.Comparator.any" title="sqlalchemy.dialects.postgresql.ARRAY.Comparator.any"><tt class="xref py py-meth docutils literal"><span class="pre">postgresql.ARRAY.Comparator.any()</span></tt></a> - ARRAY-bound method</p>
</div>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.All">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.postgresql.</tt><tt class="descname">All</tt><big>(</big><em>left</em>, <em>right</em>, <em>operator=<built-in function eq></em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.All" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.expression.ColumnElement" title="sqlalchemy.sql.expression.ColumnElement"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.sql.expression.ColumnElement</span></tt></a></p>
<p>Represent the clause <tt class="docutils literal"><span class="pre">left</span> <span class="pre">operator</span> <span class="pre">ALL</span> <span class="pre">(right)</span></tt>. <tt class="docutils literal"><span class="pre">right</span></tt> must be
an array expression.</p>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p><a class="reference internal" href="#sqlalchemy.dialects.postgresql.ARRAY" title="sqlalchemy.dialects.postgresql.ARRAY"><tt class="xref py py-class docutils literal"><span class="pre">postgresql.ARRAY</span></tt></a></p>
<p class="last"><a class="reference internal" href="#sqlalchemy.dialects.postgresql.ARRAY.Comparator.all" title="sqlalchemy.dialects.postgresql.ARRAY.Comparator.all"><tt class="xref py py-meth docutils literal"><span class="pre">postgresql.ARRAY.Comparator.all()</span></tt></a> - ARRAY-bound method</p>
</div>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.BIT">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.postgresql.</tt><tt class="descname">BIT</tt><big>(</big><em>length=None</em>, <em>varying=False</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.BIT" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/types.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.TypeEngine</span></tt></a></p>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.BYTEA">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.postgresql.</tt><tt class="descname">BYTEA</tt><big>(</big><em>length=None</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.BYTEA" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/types.html#sqlalchemy.types.LargeBinary" title="sqlalchemy.types.LargeBinary"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.LargeBinary</span></tt></a></p>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.BYTEA.__init__">
<tt class="descname">__init__</tt><big>(</big><em>length=None</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.BYTEA.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Construct a LargeBinary type.</p>
<table class="docutils field-list" frame="void" rules="none">
<col class="field-name" />
<col class="field-body" />
<tbody valign="top">
<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body"><span class="target" id="sqlalchemy.dialects.postgresql.BYTEA.params.length"></span><strong>length</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.postgresql.BYTEA.params.length">¶</a> – optional, a length for the column for use in
DDL statements, for those BLOB types that accept a length
(i.e. MySQL). It does <em>not</em> produce a small BINARY/VARBINARY
type - use the BINARY/VARBINARY types specifically for those.
May be safely omitted if no <tt class="docutils literal"><span class="pre">CREATE</span>
<span class="pre">TABLE</span></tt> will be issued. Certain databases may require a
<em>length</em> for use in DDL, and will raise an exception when
the <tt class="docutils literal"><span class="pre">CREATE</span> <span class="pre">TABLE</span></tt> DDL is issued.</td>
</tr>
</tbody>
</table>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.CIDR">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.postgresql.</tt><tt class="descname">CIDR</tt><a class="headerlink" href="#sqlalchemy.dialects.postgresql.CIDR" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/types.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.TypeEngine</span></tt></a></p>
<dl class="attribute">
<dt id="sqlalchemy.dialects.postgresql.CIDR.__init__">
<tt class="descname">__init__</tt><a class="headerlink" href="#sqlalchemy.dialects.postgresql.CIDR.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>x.__init__(...) initializes x; see help(type(x)) for signature</p>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.DOUBLE_PRECISION">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.postgresql.</tt><tt class="descname">DOUBLE_PRECISION</tt><big>(</big><em>precision=None</em>, <em>asdecimal=False</em>, <em>decimal_return_scale=None</em>, <em>**kwargs</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.DOUBLE_PRECISION" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/types.html#sqlalchemy.types.Float" title="sqlalchemy.types.Float"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.Float</span></tt></a></p>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.DOUBLE_PRECISION.__init__">
<tt class="descname">__init__</tt><big>(</big><em>precision=None</em>, <em>asdecimal=False</em>, <em>decimal_return_scale=None</em>, <em>**kwargs</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.DOUBLE_PRECISION.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Construct a Float.</p>
<table class="docutils field-list" frame="void" rules="none">
<col class="field-name" />
<col class="field-body" />
<tbody valign="top">
<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body"><ul class="first last simple">
<li><span class="target" id="sqlalchemy.dialects.postgresql.DOUBLE_PRECISION.params.precision"></span><strong>precision</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.postgresql.DOUBLE_PRECISION.params.precision">¶</a> – the numeric precision for use in DDL <tt class="docutils literal"><span class="pre">CREATE</span>
<span class="pre">TABLE</span></tt>.</li>
<li><span class="target" id="sqlalchemy.dialects.postgresql.DOUBLE_PRECISION.params.asdecimal"></span><strong>asdecimal</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.postgresql.DOUBLE_PRECISION.params.asdecimal">¶</a> – the same flag as that of <a class="reference internal" href="../core/types.html#sqlalchemy.types.Numeric" title="sqlalchemy.types.Numeric"><tt class="xref py py-class docutils literal"><span class="pre">Numeric</span></tt></a>, but
defaults to <tt class="docutils literal"><span class="pre">False</span></tt>. Note that setting this flag to <tt class="docutils literal"><span class="pre">True</span></tt>
results in floating point conversion.</li>
<li><span class="target" id="sqlalchemy.dialects.postgresql.DOUBLE_PRECISION.params.decimal_return_scale"></span><strong>decimal_return_scale</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.postgresql.DOUBLE_PRECISION.params.decimal_return_scale">¶</a> – <p>Default scale to use when converting
from floats to Python decimals. Floating point values will typically
be much longer due to decimal inaccuracy, and most floating point
database types don’t have a notion of “scale”, so by default the
float type looks for the first ten decimal places when converting.
Specfiying this value will override that length. Note that the
MySQL float types, which do include “scale”, will use “scale”
as the default for decimal_return_scale, if not otherwise specified.</p>
<div class="versionadded">
<p><span>New in version 0.9.0.</span></p>
</div>
</li>
<li><span class="target" id="sqlalchemy.dialects.postgresql.DOUBLE_PRECISION.params.**kwargs"></span><strong>**kwargs</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.postgresql.DOUBLE_PRECISION.params.**kwargs">¶</a> – deprecated. Additional arguments here are ignored
by the default <a class="reference internal" href="../core/types.html#sqlalchemy.types.Float" title="sqlalchemy.types.Float"><tt class="xref py py-class docutils literal"><span class="pre">Float</span></tt></a> type. For database specific
floats that support additional arguments, see that dialect’s
documentation for details, such as
<a class="reference internal" href="mysql.html#sqlalchemy.dialects.mysql.FLOAT" title="sqlalchemy.dialects.mysql.FLOAT"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.dialects.mysql.FLOAT</span></tt></a>.</li>
</ul>
</td>
</tr>
</tbody>
</table>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.ENUM">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.postgresql.</tt><tt class="descname">ENUM</tt><big>(</big><em>*enums</em>, <em>**kw</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.ENUM" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/types.html#sqlalchemy.types.Enum" title="sqlalchemy.types.Enum"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.Enum</span></tt></a></p>
<p>Postgresql ENUM type.</p>
<p>This is a subclass of <a class="reference internal" href="../core/types.html#sqlalchemy.types.Enum" title="sqlalchemy.types.Enum"><tt class="xref py py-class docutils literal"><span class="pre">types.Enum</span></tt></a> which includes
support for PG’s <tt class="docutils literal"><span class="pre">CREATE</span> <span class="pre">TYPE</span></tt>.</p>
<p><a class="reference internal" href="#sqlalchemy.dialects.postgresql.ENUM" title="sqlalchemy.dialects.postgresql.ENUM"><tt class="xref py py-class docutils literal"><span class="pre">ENUM</span></tt></a> is used automatically when
using the <a class="reference internal" href="../core/types.html#sqlalchemy.types.Enum" title="sqlalchemy.types.Enum"><tt class="xref py py-class docutils literal"><span class="pre">types.Enum</span></tt></a> type on PG assuming
the <tt class="docutils literal"><span class="pre">native_enum</span></tt> is left as <tt class="docutils literal"><span class="pre">True</span></tt>. However, the
<a class="reference internal" href="#sqlalchemy.dialects.postgresql.ENUM" title="sqlalchemy.dialects.postgresql.ENUM"><tt class="xref py py-class docutils literal"><span class="pre">ENUM</span></tt></a> class can also be instantiated
directly in order to access some additional Postgresql-specific
options, namely finer control over whether or not
<tt class="docutils literal"><span class="pre">CREATE</span> <span class="pre">TYPE</span></tt> should be emitted.</p>
<p>Note that both <a class="reference internal" href="../core/types.html#sqlalchemy.types.Enum" title="sqlalchemy.types.Enum"><tt class="xref py py-class docutils literal"><span class="pre">types.Enum</span></tt></a> as well as
<a class="reference internal" href="#sqlalchemy.dialects.postgresql.ENUM" title="sqlalchemy.dialects.postgresql.ENUM"><tt class="xref py py-class docutils literal"><span class="pre">ENUM</span></tt></a> feature create/drop
methods; the base <a class="reference internal" href="../core/types.html#sqlalchemy.types.Enum" title="sqlalchemy.types.Enum"><tt class="xref py py-class docutils literal"><span class="pre">types.Enum</span></tt></a> type ultimately
delegates to the <a class="reference internal" href="#sqlalchemy.dialects.postgresql.ENUM.create" title="sqlalchemy.dialects.postgresql.ENUM.create"><tt class="xref py py-meth docutils literal"><span class="pre">create()</span></tt></a> and
<a class="reference internal" href="#sqlalchemy.dialects.postgresql.ENUM.drop" title="sqlalchemy.dialects.postgresql.ENUM.drop"><tt class="xref py py-meth docutils literal"><span class="pre">drop()</span></tt></a> methods present here.</p>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.ENUM.__init__">
<tt class="descname">__init__</tt><big>(</big><em>*enums</em>, <em>**kw</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.ENUM.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Construct an <a class="reference internal" href="#sqlalchemy.dialects.postgresql.ENUM" title="sqlalchemy.dialects.postgresql.ENUM"><tt class="xref py py-class docutils literal"><span class="pre">ENUM</span></tt></a>.</p>
<p>Arguments are the same as that of
<a class="reference internal" href="../core/types.html#sqlalchemy.types.Enum" title="sqlalchemy.types.Enum"><tt class="xref py py-class docutils literal"><span class="pre">types.Enum</span></tt></a>, but also including
the following parameters.</p>
<table class="docutils field-list" frame="void" rules="none">
<col class="field-name" />
<col class="field-body" />
<tbody valign="top">
<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body"><span class="target" id="sqlalchemy.dialects.postgresql.ENUM.params.create_type"></span><strong>create_type</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.postgresql.ENUM.params.create_type">¶</a> – <p>Defaults to True.
Indicates that <tt class="docutils literal"><span class="pre">CREATE</span> <span class="pre">TYPE</span></tt> should be
emitted, after optionally checking for the
presence of the type, when the parent
table is being created; and additionally
that <tt class="docutils literal"><span class="pre">DROP</span> <span class="pre">TYPE</span></tt> is called when the table
is dropped. When <tt class="docutils literal"><span class="pre">False</span></tt>, no check
will be performed and no <tt class="docutils literal"><span class="pre">CREATE</span> <span class="pre">TYPE</span></tt>
or <tt class="docutils literal"><span class="pre">DROP</span> <span class="pre">TYPE</span></tt> is emitted, unless
<a class="reference internal" href="#sqlalchemy.dialects.postgresql.ENUM.create" title="sqlalchemy.dialects.postgresql.ENUM.create"><tt class="xref py py-meth docutils literal"><span class="pre">create()</span></tt></a>
or <a class="reference internal" href="#sqlalchemy.dialects.postgresql.ENUM.drop" title="sqlalchemy.dialects.postgresql.ENUM.drop"><tt class="xref py py-meth docutils literal"><span class="pre">drop()</span></tt></a>
are called directly.
Setting to <tt class="docutils literal"><span class="pre">False</span></tt> is helpful
when invoking a creation scheme to a SQL file
without access to the actual database -
the <a class="reference internal" href="#sqlalchemy.dialects.postgresql.ENUM.create" title="sqlalchemy.dialects.postgresql.ENUM.create"><tt class="xref py py-meth docutils literal"><span class="pre">create()</span></tt></a> and
<a class="reference internal" href="#sqlalchemy.dialects.postgresql.ENUM.drop" title="sqlalchemy.dialects.postgresql.ENUM.drop"><tt class="xref py py-meth docutils literal"><span class="pre">drop()</span></tt></a> methods can
be used to emit SQL to a target bind.</p>
<div class="versionadded">
<p><span>New in version 0.7.4.</span></p>
</div>
</td>
</tr>
</tbody>
</table>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.ENUM.create">
<tt class="descname">create</tt><big>(</big><em>bind=None</em>, <em>checkfirst=True</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.ENUM.create" title="Permalink to this definition">¶</a></dt>
<dd><p>Emit <tt class="docutils literal"><span class="pre">CREATE</span> <span class="pre">TYPE</span></tt> for this
<a class="reference internal" href="#sqlalchemy.dialects.postgresql.ENUM" title="sqlalchemy.dialects.postgresql.ENUM"><tt class="xref py py-class docutils literal"><span class="pre">ENUM</span></tt></a>.</p>
<p>If the underlying dialect does not support
Postgresql CREATE TYPE, no action is taken.</p>
<table class="docutils field-list" frame="void" rules="none">
<col class="field-name" />
<col class="field-body" />
<tbody valign="top">
<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body"><ul class="first last simple">
<li><span class="target" id="sqlalchemy.dialects.postgresql.ENUM.create.params.bind"></span><strong>bind</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.postgresql.ENUM.create.params.bind">¶</a> – a connectable <a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><tt class="xref py py-class docutils literal"><span class="pre">Engine</span></tt></a>,
<a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><tt class="xref py py-class docutils literal"><span class="pre">Connection</span></tt></a>, or similar object to emit
SQL.</li>
<li><span class="target" id="sqlalchemy.dialects.postgresql.ENUM.create.params.checkfirst"></span><strong>checkfirst</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.postgresql.ENUM.create.params.checkfirst">¶</a> – if <tt class="docutils literal"><span class="pre">True</span></tt>, a query against
the PG catalog will be first performed to see
if the type does not exist already before
creating.</li>
</ul>
</td>
</tr>
</tbody>
</table>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.ENUM.drop">
<tt class="descname">drop</tt><big>(</big><em>bind=None</em>, <em>checkfirst=True</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.ENUM.drop" title="Permalink to this definition">¶</a></dt>
<dd><p>Emit <tt class="docutils literal"><span class="pre">DROP</span> <span class="pre">TYPE</span></tt> for this
<a class="reference internal" href="#sqlalchemy.dialects.postgresql.ENUM" title="sqlalchemy.dialects.postgresql.ENUM"><tt class="xref py py-class docutils literal"><span class="pre">ENUM</span></tt></a>.</p>
<p>If the underlying dialect does not support
Postgresql DROP TYPE, no action is taken.</p>
<table class="docutils field-list" frame="void" rules="none">
<col class="field-name" />
<col class="field-body" />
<tbody valign="top">
<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body"><ul class="first last simple">
<li><span class="target" id="sqlalchemy.dialects.postgresql.ENUM.drop.params.bind"></span><strong>bind</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.postgresql.ENUM.drop.params.bind">¶</a> – a connectable <a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><tt class="xref py py-class docutils literal"><span class="pre">Engine</span></tt></a>,
<a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><tt class="xref py py-class docutils literal"><span class="pre">Connection</span></tt></a>, or similar object to emit
SQL.</li>
<li><span class="target" id="sqlalchemy.dialects.postgresql.ENUM.drop.params.checkfirst"></span><strong>checkfirst</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.postgresql.ENUM.drop.params.checkfirst">¶</a> – if <tt class="docutils literal"><span class="pre">True</span></tt>, a query against
the PG catalog will be first performed to see
if the type actually exists before dropping.</li>
</ul>
</td>
</tr>
</tbody>
</table>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.HSTORE">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.postgresql.</tt><tt class="descname">HSTORE</tt><a class="headerlink" href="#sqlalchemy.dialects.postgresql.HSTORE" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/types.html#sqlalchemy.types.Concatenable" title="sqlalchemy.types.Concatenable"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.Concatenable</span></tt></a>, <a class="reference internal" href="../core/types.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.TypeEngine</span></tt></a></p>
<p>Represent the Postgresql HSTORE type.</p>
<p>The <a class="reference internal" href="#sqlalchemy.dialects.postgresql.HSTORE" title="sqlalchemy.dialects.postgresql.HSTORE"><tt class="xref py py-class docutils literal"><span class="pre">HSTORE</span></tt></a> type stores dictionaries containing strings, e.g.:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">data_table</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'data_table'</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'data'</span><span class="p">,</span> <span class="n">HSTORE</span><span class="p">)</span>
<span class="p">)</span>
<span class="k">with</span> <span class="n">engine</span><span class="o">.</span><span class="n">connect</span><span class="p">()</span> <span class="k">as</span> <span class="n">conn</span><span class="p">:</span>
<span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span>
<span class="n">data_table</span><span class="o">.</span><span class="n">insert</span><span class="p">(),</span>
<span class="n">data</span> <span class="o">=</span> <span class="p">{</span><span class="s">"key1"</span><span class="p">:</span> <span class="s">"value1"</span><span class="p">,</span> <span class="s">"key2"</span><span class="p">:</span> <span class="s">"value2"</span><span class="p">}</span>
<span class="p">)</span></pre></div>
</div>
<p><a class="reference internal" href="#sqlalchemy.dialects.postgresql.HSTORE" title="sqlalchemy.dialects.postgresql.HSTORE"><tt class="xref py py-class docutils literal"><span class="pre">HSTORE</span></tt></a> provides for a wide range of operations, including:</p>
<ul>
<li><p class="first">Index operations:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">data_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data</span><span class="p">[</span><span class="s">'some key'</span><span class="p">]</span> <span class="o">==</span> <span class="s">'some value'</span></pre></div>
</div>
</li>
<li><p class="first">Containment operations:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">data_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data</span><span class="o">.</span><span class="n">has_key</span><span class="p">(</span><span class="s">'some key'</span><span class="p">)</span>
<span class="n">data_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data</span><span class="o">.</span><span class="n">has_all</span><span class="p">([</span><span class="s">'one'</span><span class="p">,</span> <span class="s">'two'</span><span class="p">,</span> <span class="s">'three'</span><span class="p">])</span></pre></div>
</div>
</li>
<li><p class="first">Concatenation:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">data_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data</span> <span class="o">+</span> <span class="p">{</span><span class="s">"k1"</span><span class="p">:</span> <span class="s">"v1"</span><span class="p">}</span></pre></div>
</div>
</li>
</ul>
<p>For a full list of special methods see
<a class="reference internal" href="#sqlalchemy.dialects.postgresql.HSTORE.comparator_factory" title="sqlalchemy.dialects.postgresql.HSTORE.comparator_factory"><tt class="xref py py-class docutils literal"><span class="pre">HSTORE.comparator_factory</span></tt></a>.</p>
<p>For usage with the SQLAlchemy ORM, it may be desirable to combine
the usage of <a class="reference internal" href="#sqlalchemy.dialects.postgresql.HSTORE" title="sqlalchemy.dialects.postgresql.HSTORE"><tt class="xref py py-class docutils literal"><span class="pre">HSTORE</span></tt></a> with <a class="reference internal" href="../orm/extensions/mutable.html#sqlalchemy.ext.mutable.MutableDict" title="sqlalchemy.ext.mutable.MutableDict"><tt class="xref py py-class docutils literal"><span class="pre">MutableDict</span></tt></a> dictionary
now part of the <a class="reference internal" href="../orm/extensions/mutable.html#module-sqlalchemy.ext.mutable" title="sqlalchemy.ext.mutable"><tt class="xref py py-mod docutils literal"><span class="pre">sqlalchemy.ext.mutable</span></tt></a>
extension. This extension will allow “in-place” changes to the
dictionary, e.g. addition of new keys or replacement/removal of existing
keys to/from the current dictionary, to produce events which will be
detected by the unit of work:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy.ext.mutable</span> <span class="kn">import</span> <span class="n">MutableDict</span>
<span class="k">class</span> <span class="nc">MyClass</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="n">__tablename__</span> <span class="o">=</span> <span class="s">'data_table'</span>
<span class="nb">id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
<span class="n">data</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">MutableDict</span><span class="o">.</span><span class="n">as_mutable</span><span class="p">(</span><span class="n">HSTORE</span><span class="p">))</span>
<span class="n">my_object</span> <span class="o">=</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">MyClass</span><span class="p">)</span><span class="o">.</span><span class="n">one</span><span class="p">()</span>
<span class="c"># in-place mutation, requires Mutable extension</span>
<span class="c"># in order for the ORM to detect</span>
<span class="n">my_object</span><span class="o">.</span><span class="n">data</span><span class="p">[</span><span class="s">'some_key'</span><span class="p">]</span> <span class="o">=</span> <span class="s">'some value'</span>
<span class="n">session</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span></pre></div>
</div>
<p>When the <a class="reference internal" href="../orm/extensions/mutable.html#module-sqlalchemy.ext.mutable" title="sqlalchemy.ext.mutable"><tt class="xref py py-mod docutils literal"><span class="pre">sqlalchemy.ext.mutable</span></tt></a> extension is not used, the ORM
will not be alerted to any changes to the contents of an existing
dictionary, unless that dictionary value is re-assigned to the
HSTORE-attribute itself, thus generating a change event.</p>
<div class="versionadded">
<p><span>New in version 0.8.</span></p>
</div>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p class="last"><a class="reference internal" href="#sqlalchemy.dialects.postgresql.hstore" title="sqlalchemy.dialects.postgresql.hstore"><tt class="xref py py-class docutils literal"><span class="pre">hstore</span></tt></a> - render the Postgresql <tt class="docutils literal"><span class="pre">hstore()</span></tt> function.</p>
</div>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.HSTORE.comparator_factory">
<em class="property">class </em><tt class="descname">comparator_factory</tt><big>(</big><em>expr</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.HSTORE.comparator_factory" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.Comparator</span></tt></p>
<p>Define comparison operations for <a class="reference internal" href="#sqlalchemy.dialects.postgresql.HSTORE" title="sqlalchemy.dialects.postgresql.HSTORE"><tt class="xref py py-class docutils literal"><span class="pre">HSTORE</span></tt></a>.</p>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.HSTORE.comparator_factory.array">
<tt class="descname">array</tt><big>(</big><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.HSTORE.comparator_factory.array" title="Permalink to this definition">¶</a></dt>
<dd><p>Text array expression. Returns array of alternating keys and
values.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.HSTORE.comparator_factory.contained_by">
<tt class="descname">contained_by</tt><big>(</big><em>other</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.HSTORE.comparator_factory.contained_by" title="Permalink to this definition">¶</a></dt>
<dd><p>Boolean expression. Test if keys are a proper subset of the
keys of the argument hstore expression.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.HSTORE.comparator_factory.contains">
<tt class="descname">contains</tt><big>(</big><em>other</em>, <em>**kwargs</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.HSTORE.comparator_factory.contains" title="Permalink to this definition">¶</a></dt>
<dd><p>Boolean expression. Test if keys are a superset of the keys of
the argument hstore expression.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.HSTORE.comparator_factory.defined">
<tt class="descname">defined</tt><big>(</big><em>key</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.HSTORE.comparator_factory.defined" title="Permalink to this definition">¶</a></dt>
<dd><p>Boolean expression. Test for presence of a non-NULL value for
the key. Note that the key may be a SQLA expression.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.HSTORE.comparator_factory.delete">
<tt class="descname">delete</tt><big>(</big><em>key</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.HSTORE.comparator_factory.delete" title="Permalink to this definition">¶</a></dt>
<dd><p>HStore expression. Returns the contents of this hstore with the
given key deleted. Note that the key may be a SQLA expression.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.HSTORE.comparator_factory.has_all">
<tt class="descname">has_all</tt><big>(</big><em>other</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.HSTORE.comparator_factory.has_all" title="Permalink to this definition">¶</a></dt>
<dd><p>Boolean expression. Test for presence of all keys in the PG
array.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.HSTORE.comparator_factory.has_any">
<tt class="descname">has_any</tt><big>(</big><em>other</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.HSTORE.comparator_factory.has_any" title="Permalink to this definition">¶</a></dt>
<dd><p>Boolean expression. Test for presence of any key in the PG
array.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.HSTORE.comparator_factory.has_key">
<tt class="descname">has_key</tt><big>(</big><em>other</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.HSTORE.comparator_factory.has_key" title="Permalink to this definition">¶</a></dt>
<dd><p>Boolean expression. Test for presence of a key. Note that the
key may be a SQLA expression.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.HSTORE.comparator_factory.keys">
<tt class="descname">keys</tt><big>(</big><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.HSTORE.comparator_factory.keys" title="Permalink to this definition">¶</a></dt>
<dd><p>Text array expression. Returns array of keys.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.HSTORE.comparator_factory.matrix">
<tt class="descname">matrix</tt><big>(</big><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.HSTORE.comparator_factory.matrix" title="Permalink to this definition">¶</a></dt>
<dd><p>Text array expression. Returns array of [key, value] pairs.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.HSTORE.comparator_factory.slice">
<tt class="descname">slice</tt><big>(</big><em>array</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.HSTORE.comparator_factory.slice" title="Permalink to this definition">¶</a></dt>
<dd><p>HStore expression. Returns a subset of an hstore defined by
array of keys.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.HSTORE.comparator_factory.vals">
<tt class="descname">vals</tt><big>(</big><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.HSTORE.comparator_factory.vals" title="Permalink to this definition">¶</a></dt>
<dd><p>Text array expression. Returns array of values.</p>
</dd></dl>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.hstore">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.postgresql.</tt><tt class="descname">hstore</tt><big>(</big><em>*args</em>, <em>**kwargs</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.hstore" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/functions.html#sqlalchemy.sql.functions.GenericFunction" title="sqlalchemy.sql.functions.GenericFunction"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.sql.functions.GenericFunction</span></tt></a></p>
<p>Construct an hstore value within a SQL expression using the
Postgresql <tt class="docutils literal"><span class="pre">hstore()</span></tt> function.</p>
<p>The <a class="reference internal" href="#sqlalchemy.dialects.postgresql.hstore" title="sqlalchemy.dialects.postgresql.hstore"><tt class="xref py py-class docutils literal"><span class="pre">hstore</span></tt></a> function accepts one or two arguments as described
in the Postgresql documentation.</p>
<p>E.g.:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy.dialects.postgresql</span> <span class="kn">import</span> <span class="n">array</span><span class="p">,</span> <span class="n">hstore</span>
<span class="n">select</span><span class="p">([</span><span class="n">hstore</span><span class="p">(</span><span class="s">'key1'</span><span class="p">,</span> <span class="s">'value1'</span><span class="p">)])</span>
<span class="n">select</span><span class="p">([</span>
<span class="n">hstore</span><span class="p">(</span>
<span class="n">array</span><span class="p">([</span><span class="s">'key1'</span><span class="p">,</span> <span class="s">'key2'</span><span class="p">,</span> <span class="s">'key3'</span><span class="p">]),</span>
<span class="n">array</span><span class="p">([</span><span class="s">'value1'</span><span class="p">,</span> <span class="s">'value2'</span><span class="p">,</span> <span class="s">'value3'</span><span class="p">])</span>
<span class="p">)</span>
<span class="p">])</span></pre></div>
</div>
<div class="versionadded">
<p><span>New in version 0.8.</span></p>
</div>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p class="last"><a class="reference internal" href="#sqlalchemy.dialects.postgresql.HSTORE" title="sqlalchemy.dialects.postgresql.HSTORE"><tt class="xref py py-class docutils literal"><span class="pre">HSTORE</span></tt></a> - the Postgresql <tt class="docutils literal"><span class="pre">HSTORE</span></tt> datatype.</p>
</div>
<dl class="attribute">
<dt id="sqlalchemy.dialects.postgresql.hstore.type">
<tt class="descname">type</tt><a class="headerlink" href="#sqlalchemy.dialects.postgresql.hstore.type" title="Permalink to this definition">¶</a></dt>
<dd><p>alias of <a class="reference internal" href="#sqlalchemy.dialects.postgresql.HSTORE" title="sqlalchemy.dialects.postgresql.HSTORE"><tt class="xref py py-class docutils literal"><span class="pre">HSTORE</span></tt></a></p>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.INET">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.postgresql.</tt><tt class="descname">INET</tt><a class="headerlink" href="#sqlalchemy.dialects.postgresql.INET" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/types.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.TypeEngine</span></tt></a></p>
<dl class="attribute">
<dt id="sqlalchemy.dialects.postgresql.INET.__init__">
<tt class="descname">__init__</tt><a class="headerlink" href="#sqlalchemy.dialects.postgresql.INET.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>x.__init__(...) initializes x; see help(type(x)) for signature</p>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.INTERVAL">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.postgresql.</tt><tt class="descname">INTERVAL</tt><big>(</big><em>precision=None</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.INTERVAL" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/types.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.TypeEngine</span></tt></a></p>
<p>Postgresql INTERVAL type.</p>
<p>The INTERVAL type may not be supported on all DBAPIs.
It is known to work on psycopg2 and not pg8000 or zxjdbc.</p>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.JSON">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.postgresql.</tt><tt class="descname">JSON</tt><big>(</big><em>none_as_null=False</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.JSON" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/types.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.TypeEngine</span></tt></a></p>
<p>Represent the Postgresql JSON type.</p>
<p>The <a class="reference internal" href="#sqlalchemy.dialects.postgresql.JSON" title="sqlalchemy.dialects.postgresql.JSON"><tt class="xref py py-class docutils literal"><span class="pre">JSON</span></tt></a> type stores arbitrary JSON format data, e.g.:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">data_table</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'data_table'</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'data'</span><span class="p">,</span> <span class="n">JSON</span><span class="p">)</span>
<span class="p">)</span>
<span class="k">with</span> <span class="n">engine</span><span class="o">.</span><span class="n">connect</span><span class="p">()</span> <span class="k">as</span> <span class="n">conn</span><span class="p">:</span>
<span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span>
<span class="n">data_table</span><span class="o">.</span><span class="n">insert</span><span class="p">(),</span>
<span class="n">data</span> <span class="o">=</span> <span class="p">{</span><span class="s">"key1"</span><span class="p">:</span> <span class="s">"value1"</span><span class="p">,</span> <span class="s">"key2"</span><span class="p">:</span> <span class="s">"value2"</span><span class="p">}</span>
<span class="p">)</span></pre></div>
</div>
<p><a class="reference internal" href="#sqlalchemy.dialects.postgresql.JSON" title="sqlalchemy.dialects.postgresql.JSON"><tt class="xref py py-class docutils literal"><span class="pre">JSON</span></tt></a> provides several operations:</p>
<ul>
<li><p class="first">Index operations:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">data_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data</span><span class="p">[</span><span class="s">'some key'</span><span class="p">]</span></pre></div>
</div>
</li>
<li><p class="first">Index operations returning text (required for text comparison):</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">data_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data</span><span class="p">[</span><span class="s">'some key'</span><span class="p">]</span><span class="o">.</span><span class="n">astext</span> <span class="o">==</span> <span class="s">'some value'</span></pre></div>
</div>
</li>
<li><p class="first">Index operations with a built-in CAST call:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">data_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data</span><span class="p">[</span><span class="s">'some key'</span><span class="p">]</span><span class="o">.</span><span class="n">cast</span><span class="p">(</span><span class="n">Integer</span><span class="p">)</span> <span class="o">==</span> <span class="mi">5</span></pre></div>
</div>
</li>
<li><p class="first">Path index operations:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">data_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data</span><span class="p">[(</span><span class="s">'key_1'</span><span class="p">,</span> <span class="s">'key_2'</span><span class="p">,</span> <span class="o">...</span><span class="p">,</span> <span class="s">'key_n'</span><span class="p">)]</span></pre></div>
</div>
</li>
<li><p class="first">Path index operations returning text (required for text comparison):</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">data_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data</span><span class="p">[(</span><span class="s">'key_1'</span><span class="p">,</span> <span class="s">'key_2'</span><span class="p">,</span> <span class="o">...</span><span class="p">,</span> <span class="s">'key_n'</span><span class="p">)]</span><span class="o">.</span><span class="n">astext</span> <span class="o">==</span> \
<span class="s">'some value'</span></pre></div>
</div>
</li>
</ul>
<p>Index operations return an instance of <a class="reference internal" href="#sqlalchemy.dialects.postgresql.JSONElement" title="sqlalchemy.dialects.postgresql.JSONElement"><tt class="xref py py-class docutils literal"><span class="pre">JSONElement</span></tt></a>, which
represents an expression such as <tt class="docutils literal"><span class="pre">column</span> <span class="pre">-></span> <span class="pre">index</span></tt>. This element then
defines methods such as <a class="reference internal" href="#sqlalchemy.dialects.postgresql.JSONElement.astext" title="sqlalchemy.dialects.postgresql.JSONElement.astext"><tt class="xref py py-attr docutils literal"><span class="pre">JSONElement.astext</span></tt></a> and
<a class="reference internal" href="#sqlalchemy.dialects.postgresql.JSONElement.cast" title="sqlalchemy.dialects.postgresql.JSONElement.cast"><tt class="xref py py-meth docutils literal"><span class="pre">JSONElement.cast()</span></tt></a> for setting up type behavior.</p>
<p>The <a class="reference internal" href="#sqlalchemy.dialects.postgresql.JSON" title="sqlalchemy.dialects.postgresql.JSON"><tt class="xref py py-class docutils literal"><span class="pre">JSON</span></tt></a> type, when used with the SQLAlchemy ORM, does not
detect in-place mutations to the structure. In order to detect these, the
<a class="reference internal" href="../orm/extensions/mutable.html#module-sqlalchemy.ext.mutable" title="sqlalchemy.ext.mutable"><tt class="xref py py-mod docutils literal"><span class="pre">sqlalchemy.ext.mutable</span></tt></a> extension must be used. This extension will
allow “in-place” changes to the datastructure to produce events which
will be detected by the unit of work. See the example at <a class="reference internal" href="#sqlalchemy.dialects.postgresql.HSTORE" title="sqlalchemy.dialects.postgresql.HSTORE"><tt class="xref py py-class docutils literal"><span class="pre">HSTORE</span></tt></a>
for a simple example involving a dictionary.</p>
<p>Custom serializers and deserializers are specified at the dialect level,
that is using <a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><tt class="xref py py-func docutils literal"><span class="pre">create_engine()</span></tt></a>. The reason for this is that when
using psycopg2, the DBAPI only allows serializers at the per-cursor
or per-connection level. E.g.:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">"postgresql://scott:tiger@localhost/test"</span><span class="p">,</span>
<span class="n">json_serializer</span><span class="o">=</span><span class="n">my_serialize_fn</span><span class="p">,</span>
<span class="n">json_deserializer</span><span class="o">=</span><span class="n">my_deserialize_fn</span>
<span class="p">)</span></pre></div>
</div>
<p>When using the psycopg2 dialect, the json_deserializer is registered
against the database using <tt class="docutils literal"><span class="pre">psycopg2.extras.register_default_json</span></tt>.</p>
<div class="versionadded">
<p><span>New in version 0.9.</span></p>
</div>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.JSON.__init__">
<tt class="descname">__init__</tt><big>(</big><em>none_as_null=False</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.JSON.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Construct a <a class="reference internal" href="#sqlalchemy.dialects.postgresql.JSON" title="sqlalchemy.dialects.postgresql.JSON"><tt class="xref py py-class docutils literal"><span class="pre">JSON</span></tt></a> type.</p>
<table class="docutils field-list" frame="void" rules="none">
<col class="field-name" />
<col class="field-body" />
<tbody valign="top">
<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body"><span class="target" id="sqlalchemy.dialects.postgresql.JSON.params.none_as_null"></span><strong>none_as_null</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.postgresql.JSON.params.none_as_null">¶</a> – <p>if True, persist the value <tt class="docutils literal"><span class="pre">None</span></tt> as a
SQL NULL value, not the JSON encoding of <tt class="docutils literal"><span class="pre">null</span></tt>. Note that
when this flag is False, the <a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.expression.null" title="sqlalchemy.sql.expression.null"><tt class="xref py py-func docutils literal"><span class="pre">null()</span></tt></a> construct can still
be used to persist a NULL value:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">null</span>
<span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">table</span><span class="o">.</span><span class="n">insert</span><span class="p">(),</span> <span class="n">data</span><span class="o">=</span><span class="n">null</span><span class="p">())</span></pre></div>
</div>
<div class="versionchanged">
<p><span>Changed in version 0.9.8: </span>- Added <tt class="docutils literal"><span class="pre">none_as_null</span></tt>, and <a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.expression.null" title="sqlalchemy.sql.expression.null"><tt class="xref py py-func docutils literal"><span class="pre">null()</span></tt></a>
is now supported in order to persist a NULL value.</p>
</div>
</td>
</tr>
</tbody>
</table>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.JSON.comparator_factory">
<em class="property">class </em><tt class="descname">comparator_factory</tt><big>(</big><em>expr</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.JSON.comparator_factory" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.Comparator</span></tt></p>
<p>Define comparison operations for <a class="reference internal" href="#sqlalchemy.dialects.postgresql.JSON" title="sqlalchemy.dialects.postgresql.JSON"><tt class="xref py py-class docutils literal"><span class="pre">JSON</span></tt></a>.</p>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.JSONB">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.postgresql.</tt><tt class="descname">JSONB</tt><big>(</big><em>none_as_null=False</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.JSONB" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.dialects.postgresql.json.JSON</span></tt></p>
<p>Represent the Postgresql JSONB type.</p>
<p>The <a class="reference internal" href="#sqlalchemy.dialects.postgresql.JSONB" title="sqlalchemy.dialects.postgresql.JSONB"><tt class="xref py py-class docutils literal"><span class="pre">JSONB</span></tt></a> type stores arbitrary JSONB format data, e.g.:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">data_table</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'data_table'</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'data'</span><span class="p">,</span> <span class="n">JSONB</span><span class="p">)</span>
<span class="p">)</span>
<span class="k">with</span> <span class="n">engine</span><span class="o">.</span><span class="n">connect</span><span class="p">()</span> <span class="k">as</span> <span class="n">conn</span><span class="p">:</span>
<span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span>
<span class="n">data_table</span><span class="o">.</span><span class="n">insert</span><span class="p">(),</span>
<span class="n">data</span> <span class="o">=</span> <span class="p">{</span><span class="s">"key1"</span><span class="p">:</span> <span class="s">"value1"</span><span class="p">,</span> <span class="s">"key2"</span><span class="p">:</span> <span class="s">"value2"</span><span class="p">}</span>
<span class="p">)</span></pre></div>
</div>
<p><a class="reference internal" href="#sqlalchemy.dialects.postgresql.JSONB" title="sqlalchemy.dialects.postgresql.JSONB"><tt class="xref py py-class docutils literal"><span class="pre">JSONB</span></tt></a> provides several operations:</p>
<ul>
<li><p class="first">Index operations:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">data_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data</span><span class="p">[</span><span class="s">'some key'</span><span class="p">]</span></pre></div>
</div>
</li>
<li><p class="first">Index operations returning text (required for text comparison):</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">data_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data</span><span class="p">[</span><span class="s">'some key'</span><span class="p">]</span><span class="o">.</span><span class="n">astext</span> <span class="o">==</span> <span class="s">'some value'</span></pre></div>
</div>
</li>
<li><p class="first">Index operations with a built-in CAST call:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">data_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data</span><span class="p">[</span><span class="s">'some key'</span><span class="p">]</span><span class="o">.</span><span class="n">cast</span><span class="p">(</span><span class="n">Integer</span><span class="p">)</span> <span class="o">==</span> <span class="mi">5</span></pre></div>
</div>
</li>
<li><p class="first">Path index operations:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">data_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data</span><span class="p">[(</span><span class="s">'key_1'</span><span class="p">,</span> <span class="s">'key_2'</span><span class="p">,</span> <span class="o">...</span><span class="p">,</span> <span class="s">'key_n'</span><span class="p">)]</span></pre></div>
</div>
</li>
<li><p class="first">Path index operations returning text (required for text comparison):</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">data_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data</span><span class="p">[(</span><span class="s">'key_1'</span><span class="p">,</span> <span class="s">'key_2'</span><span class="p">,</span> <span class="o">...</span><span class="p">,</span> <span class="s">'key_n'</span><span class="p">)]</span><span class="o">.</span><span class="n">astext</span> <span class="o">==</span> \
<span class="s">'some value'</span></pre></div>
</div>
</li>
</ul>
<p>Index operations return an instance of <a class="reference internal" href="#sqlalchemy.dialects.postgresql.JSONElement" title="sqlalchemy.dialects.postgresql.JSONElement"><tt class="xref py py-class docutils literal"><span class="pre">JSONElement</span></tt></a>, which
represents an expression such as <tt class="docutils literal"><span class="pre">column</span> <span class="pre">-></span> <span class="pre">index</span></tt>. This element then
defines methods such as <a class="reference internal" href="#sqlalchemy.dialects.postgresql.JSONElement.astext" title="sqlalchemy.dialects.postgresql.JSONElement.astext"><tt class="xref py py-attr docutils literal"><span class="pre">JSONElement.astext</span></tt></a> and
<a class="reference internal" href="#sqlalchemy.dialects.postgresql.JSONElement.cast" title="sqlalchemy.dialects.postgresql.JSONElement.cast"><tt class="xref py py-meth docutils literal"><span class="pre">JSONElement.cast()</span></tt></a> for setting up type behavior.</p>
<p>The <a class="reference internal" href="#sqlalchemy.dialects.postgresql.JSON" title="sqlalchemy.dialects.postgresql.JSON"><tt class="xref py py-class docutils literal"><span class="pre">JSON</span></tt></a> type, when used with the SQLAlchemy ORM, does not
detect in-place mutations to the structure. In order to detect these, the
<a class="reference internal" href="../orm/extensions/mutable.html#module-sqlalchemy.ext.mutable" title="sqlalchemy.ext.mutable"><tt class="xref py py-mod docutils literal"><span class="pre">sqlalchemy.ext.mutable</span></tt></a> extension must be used. This extension will
allow “in-place” changes to the datastructure to produce events which
will be detected by the unit of work. See the example at <a class="reference internal" href="#sqlalchemy.dialects.postgresql.HSTORE" title="sqlalchemy.dialects.postgresql.HSTORE"><tt class="xref py py-class docutils literal"><span class="pre">HSTORE</span></tt></a>
for a simple example involving a dictionary.</p>
<p>Custom serializers and deserializers are specified at the dialect level,
that is using <a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><tt class="xref py py-func docutils literal"><span class="pre">create_engine()</span></tt></a>. The reason for this is that when
using psycopg2, the DBAPI only allows serializers at the per-cursor
or per-connection level. E.g.:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">"postgresql://scott:tiger@localhost/test"</span><span class="p">,</span>
<span class="n">json_serializer</span><span class="o">=</span><span class="n">my_serialize_fn</span><span class="p">,</span>
<span class="n">json_deserializer</span><span class="o">=</span><span class="n">my_deserialize_fn</span>
<span class="p">)</span></pre></div>
</div>
<p>When using the psycopg2 dialect, the json_deserializer is registered
against the database using <tt class="docutils literal"><span class="pre">psycopg2.extras.register_default_json</span></tt>.</p>
<div class="versionadded">
<p><span>New in version 0.9.7.</span></p>
</div>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.JSONB.comparator_factory">
<em class="property">class </em><tt class="descname">comparator_factory</tt><big>(</big><em>expr</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.JSONB.comparator_factory" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.Comparator</span></tt></p>
<p>Define comparison operations for <a class="reference internal" href="#sqlalchemy.dialects.postgresql.JSON" title="sqlalchemy.dialects.postgresql.JSON"><tt class="xref py py-class docutils literal"><span class="pre">JSON</span></tt></a>.</p>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.JSONB.comparator_factory.contained_by">
<tt class="descname">contained_by</tt><big>(</big><em>other</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.JSONB.comparator_factory.contained_by" title="Permalink to this definition">¶</a></dt>
<dd><p>Boolean expression. Test if keys are a proper subset of the
keys of the argument jsonb expression.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.JSONB.comparator_factory.contains">
<tt class="descname">contains</tt><big>(</big><em>other</em>, <em>**kwargs</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.JSONB.comparator_factory.contains" title="Permalink to this definition">¶</a></dt>
<dd><p>Boolean expression. Test if keys (or array) are a superset of/contained
the keys of the argument jsonb expression.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.JSONB.comparator_factory.has_all">
<tt class="descname">has_all</tt><big>(</big><em>other</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.JSONB.comparator_factory.has_all" title="Permalink to this definition">¶</a></dt>
<dd><p>Boolean expression. Test for presence of all keys in jsonb</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.JSONB.comparator_factory.has_any">
<tt class="descname">has_any</tt><big>(</big><em>other</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.JSONB.comparator_factory.has_any" title="Permalink to this definition">¶</a></dt>
<dd><p>Boolean expression. Test for presence of any key in jsonb</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.JSONB.comparator_factory.has_key">
<tt class="descname">has_key</tt><big>(</big><em>other</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.JSONB.comparator_factory.has_key" title="Permalink to this definition">¶</a></dt>
<dd><p>Boolean expression. Test for presence of a key. Note that the
key may be a SQLA expression.</p>
</dd></dl>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.JSONElement">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.postgresql.</tt><tt class="descname">JSONElement</tt><big>(</big><em>left</em>, <em>right</em>, <em>astext=False</em>, <em>opstring=None</em>, <em>result_type=None</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.JSONElement" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.expression.BinaryExpression" title="sqlalchemy.sql.expression.BinaryExpression"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.sql.expression.BinaryExpression</span></tt></a></p>
<p>Represents accessing an element of a <a class="reference internal" href="#sqlalchemy.dialects.postgresql.JSON" title="sqlalchemy.dialects.postgresql.JSON"><tt class="xref py py-class docutils literal"><span class="pre">JSON</span></tt></a> value.</p>
<p>The <a class="reference internal" href="#sqlalchemy.dialects.postgresql.JSONElement" title="sqlalchemy.dialects.postgresql.JSONElement"><tt class="xref py py-class docutils literal"><span class="pre">JSONElement</span></tt></a> is produced whenever using the Python index
operator on an expression that has the type <a class="reference internal" href="#sqlalchemy.dialects.postgresql.JSON" title="sqlalchemy.dialects.postgresql.JSON"><tt class="xref py py-class docutils literal"><span class="pre">JSON</span></tt></a>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">expr</span> <span class="o">=</span> <span class="n">mytable</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">json_data</span><span class="p">[</span><span class="s">'some_key'</span><span class="p">]</span></pre></div>
</div>
<p>The expression typically compiles to a JSON access such as <tt class="docutils literal"><span class="pre">col</span> <span class="pre">-></span> <span class="pre">key</span></tt>.
Modifiers are then available for typing behavior, including
<a class="reference internal" href="#sqlalchemy.dialects.postgresql.JSONElement.cast" title="sqlalchemy.dialects.postgresql.JSONElement.cast"><tt class="xref py py-meth docutils literal"><span class="pre">JSONElement.cast()</span></tt></a> and <a class="reference internal" href="#sqlalchemy.dialects.postgresql.JSONElement.astext" title="sqlalchemy.dialects.postgresql.JSONElement.astext"><tt class="xref py py-attr docutils literal"><span class="pre">JSONElement.astext</span></tt></a>.</p>
<dl class="attribute">
<dt id="sqlalchemy.dialects.postgresql.JSONElement.astext">
<tt class="descname">astext</tt><a class="headerlink" href="#sqlalchemy.dialects.postgresql.JSONElement.astext" title="Permalink to this definition">¶</a></dt>
<dd><p>Convert this <a class="reference internal" href="#sqlalchemy.dialects.postgresql.JSONElement" title="sqlalchemy.dialects.postgresql.JSONElement"><tt class="xref py py-class docutils literal"><span class="pre">JSONElement</span></tt></a> to use the ‘astext’ operator
when evaluated.</p>
<p>E.g.:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">select</span><span class="p">([</span><span class="n">data_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data</span><span class="p">[</span><span class="s">'some key'</span><span class="p">]</span><span class="o">.</span><span class="n">astext</span><span class="p">])</span></pre></div>
</div>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p class="last"><a class="reference internal" href="#sqlalchemy.dialects.postgresql.JSONElement.cast" title="sqlalchemy.dialects.postgresql.JSONElement.cast"><tt class="xref py py-meth docutils literal"><span class="pre">JSONElement.cast()</span></tt></a></p>
</div>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.JSONElement.cast">
<tt class="descname">cast</tt><big>(</big><em>type_</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.JSONElement.cast" title="Permalink to this definition">¶</a></dt>
<dd><p>Convert this <a class="reference internal" href="#sqlalchemy.dialects.postgresql.JSONElement" title="sqlalchemy.dialects.postgresql.JSONElement"><tt class="xref py py-class docutils literal"><span class="pre">JSONElement</span></tt></a> to apply both the ‘astext’ operator
as well as an explicit type cast when evaulated.</p>
<p>E.g.:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">select</span><span class="p">([</span><span class="n">data_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data</span><span class="p">[</span><span class="s">'some key'</span><span class="p">]</span><span class="o">.</span><span class="n">cast</span><span class="p">(</span><span class="n">Integer</span><span class="p">)])</span></pre></div>
</div>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p class="last"><a class="reference internal" href="#sqlalchemy.dialects.postgresql.JSONElement.astext" title="sqlalchemy.dialects.postgresql.JSONElement.astext"><tt class="xref py py-attr docutils literal"><span class="pre">JSONElement.astext</span></tt></a></p>
</div>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.MACADDR">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.postgresql.</tt><tt class="descname">MACADDR</tt><a class="headerlink" href="#sqlalchemy.dialects.postgresql.MACADDR" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/types.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.TypeEngine</span></tt></a></p>
<dl class="attribute">
<dt id="sqlalchemy.dialects.postgresql.MACADDR.__init__">
<tt class="descname">__init__</tt><a class="headerlink" href="#sqlalchemy.dialects.postgresql.MACADDR.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>x.__init__(...) initializes x; see help(type(x)) for signature</p>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.OID">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.postgresql.</tt><tt class="descname">OID</tt><a class="headerlink" href="#sqlalchemy.dialects.postgresql.OID" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/types.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.TypeEngine</span></tt></a></p>
<p>Provide the Postgresql OID type.</p>
<div class="versionadded">
<p><span>New in version 0.9.5.</span></p>
</div>
<dl class="attribute">
<dt id="sqlalchemy.dialects.postgresql.OID.__init__">
<tt class="descname">__init__</tt><a class="headerlink" href="#sqlalchemy.dialects.postgresql.OID.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>x.__init__(...) initializes x; see help(type(x)) for signature</p>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.REAL">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.postgresql.</tt><tt class="descname">REAL</tt><big>(</big><em>precision=None</em>, <em>asdecimal=False</em>, <em>decimal_return_scale=None</em>, <em>**kwargs</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.REAL" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/types.html#sqlalchemy.types.Float" title="sqlalchemy.types.Float"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.Float</span></tt></a></p>
<p>The SQL REAL type.</p>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.REAL.__init__">
<tt class="descname">__init__</tt><big>(</big><em>precision=None</em>, <em>asdecimal=False</em>, <em>decimal_return_scale=None</em>, <em>**kwargs</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.REAL.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Construct a Float.</p>
<table class="docutils field-list" frame="void" rules="none">
<col class="field-name" />
<col class="field-body" />
<tbody valign="top">
<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body"><ul class="first last simple">
<li><span class="target" id="sqlalchemy.dialects.postgresql.REAL.params.precision"></span><strong>precision</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.postgresql.REAL.params.precision">¶</a> – the numeric precision for use in DDL <tt class="docutils literal"><span class="pre">CREATE</span>
<span class="pre">TABLE</span></tt>.</li>
<li><span class="target" id="sqlalchemy.dialects.postgresql.REAL.params.asdecimal"></span><strong>asdecimal</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.postgresql.REAL.params.asdecimal">¶</a> – the same flag as that of <a class="reference internal" href="../core/types.html#sqlalchemy.types.Numeric" title="sqlalchemy.types.Numeric"><tt class="xref py py-class docutils literal"><span class="pre">Numeric</span></tt></a>, but
defaults to <tt class="docutils literal"><span class="pre">False</span></tt>. Note that setting this flag to <tt class="docutils literal"><span class="pre">True</span></tt>
results in floating point conversion.</li>
<li><span class="target" id="sqlalchemy.dialects.postgresql.REAL.params.decimal_return_scale"></span><strong>decimal_return_scale</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.postgresql.REAL.params.decimal_return_scale">¶</a> – <p>Default scale to use when converting
from floats to Python decimals. Floating point values will typically
be much longer due to decimal inaccuracy, and most floating point
database types don’t have a notion of “scale”, so by default the
float type looks for the first ten decimal places when converting.
Specfiying this value will override that length. Note that the
MySQL float types, which do include “scale”, will use “scale”
as the default for decimal_return_scale, if not otherwise specified.</p>
<div class="versionadded">
<p><span>New in version 0.9.0.</span></p>
</div>
</li>
<li><span class="target" id="sqlalchemy.dialects.postgresql.REAL.params.**kwargs"></span><strong>**kwargs</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.postgresql.REAL.params.**kwargs">¶</a> – deprecated. Additional arguments here are ignored
by the default <a class="reference internal" href="../core/types.html#sqlalchemy.types.Float" title="sqlalchemy.types.Float"><tt class="xref py py-class docutils literal"><span class="pre">Float</span></tt></a> type. For database specific
floats that support additional arguments, see that dialect’s
documentation for details, such as
<a class="reference internal" href="mysql.html#sqlalchemy.dialects.mysql.FLOAT" title="sqlalchemy.dialects.mysql.FLOAT"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.dialects.mysql.FLOAT</span></tt></a>.</li>
</ul>
</td>
</tr>
</tbody>
</table>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.TSVECTOR">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.postgresql.</tt><tt class="descname">TSVECTOR</tt><a class="headerlink" href="#sqlalchemy.dialects.postgresql.TSVECTOR" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/types.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.TypeEngine</span></tt></a></p>
<p>The <a class="reference internal" href="#sqlalchemy.dialects.postgresql.TSVECTOR" title="sqlalchemy.dialects.postgresql.TSVECTOR"><tt class="xref py py-class docutils literal"><span class="pre">postgresql.TSVECTOR</span></tt></a> type implements the Postgresql
text search type TSVECTOR.</p>
<p>It can be used to do full text queries on natural language
documents.</p>
<div class="versionadded">
<p><span>New in version 0.9.0.</span></p>
</div>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p class="last"><a class="reference internal" href="#postgresql-match"><em>Full Text Search</em></a></p>
</div>
<dl class="attribute">
<dt id="sqlalchemy.dialects.postgresql.TSVECTOR.__init__">
<tt class="descname">__init__</tt><a class="headerlink" href="#sqlalchemy.dialects.postgresql.TSVECTOR.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>x.__init__(...) initializes x; see help(type(x)) for signature</p>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.UUID">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.postgresql.</tt><tt class="descname">UUID</tt><big>(</big><em>as_uuid=False</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.UUID" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/types.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.TypeEngine</span></tt></a></p>
<p>Postgresql UUID type.</p>
<p>Represents the UUID column type, interpreting
data either as natively returned by the DBAPI
or as Python uuid objects.</p>
<p>The UUID type may not be supported on all DBAPIs.
It is known to work on psycopg2 and not pg8000.</p>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.UUID.__init__">
<tt class="descname">__init__</tt><big>(</big><em>as_uuid=False</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.UUID.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Construct a UUID type.</p>
<table class="docutils field-list" frame="void" rules="none">
<col class="field-name" />
<col class="field-body" />
<tbody valign="top">
<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body"><span class="target" id="sqlalchemy.dialects.postgresql.UUID.params.as_uuid"></span><strong>as_uuid=False</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.postgresql.UUID.params.as_uuid">¶</a> – if True, values will be interpreted
as Python uuid objects, converting to/from string via the
DBAPI.</td>
</tr>
</tbody>
</table>
</dd></dl>
</dd></dl>
<div class="section" id="range-types">
<h3>Range Types<a class="headerlink" href="#range-types" title="Permalink to this headline">¶</a></h3>
<p>The new range column types found in PostgreSQL 9.2 onwards are
catered for by the following types:</p>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.INT4RANGE">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.postgresql.</tt><tt class="descname">INT4RANGE</tt><a class="headerlink" href="#sqlalchemy.dialects.postgresql.INT4RANGE" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="#sqlalchemy.dialects.postgresql.ranges.RangeOperators" title="sqlalchemy.dialects.postgresql.ranges.RangeOperators"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.dialects.postgresql.ranges.RangeOperators</span></tt></a>, <a class="reference internal" href="../core/types.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.TypeEngine</span></tt></a></p>
<p>Represent the Postgresql INT4RANGE type.</p>
<div class="versionadded">
<p><span>New in version 0.8.2.</span></p>
</div>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.INT8RANGE">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.postgresql.</tt><tt class="descname">INT8RANGE</tt><a class="headerlink" href="#sqlalchemy.dialects.postgresql.INT8RANGE" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="#sqlalchemy.dialects.postgresql.ranges.RangeOperators" title="sqlalchemy.dialects.postgresql.ranges.RangeOperators"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.dialects.postgresql.ranges.RangeOperators</span></tt></a>, <a class="reference internal" href="../core/types.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.TypeEngine</span></tt></a></p>
<p>Represent the Postgresql INT8RANGE type.</p>
<div class="versionadded">
<p><span>New in version 0.8.2.</span></p>
</div>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.NUMRANGE">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.postgresql.</tt><tt class="descname">NUMRANGE</tt><a class="headerlink" href="#sqlalchemy.dialects.postgresql.NUMRANGE" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="#sqlalchemy.dialects.postgresql.ranges.RangeOperators" title="sqlalchemy.dialects.postgresql.ranges.RangeOperators"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.dialects.postgresql.ranges.RangeOperators</span></tt></a>, <a class="reference internal" href="../core/types.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.TypeEngine</span></tt></a></p>
<p>Represent the Postgresql NUMRANGE type.</p>
<div class="versionadded">
<p><span>New in version 0.8.2.</span></p>
</div>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.DATERANGE">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.postgresql.</tt><tt class="descname">DATERANGE</tt><a class="headerlink" href="#sqlalchemy.dialects.postgresql.DATERANGE" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="#sqlalchemy.dialects.postgresql.ranges.RangeOperators" title="sqlalchemy.dialects.postgresql.ranges.RangeOperators"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.dialects.postgresql.ranges.RangeOperators</span></tt></a>, <a class="reference internal" href="../core/types.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.TypeEngine</span></tt></a></p>
<p>Represent the Postgresql DATERANGE type.</p>
<div class="versionadded">
<p><span>New in version 0.8.2.</span></p>
</div>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.TSRANGE">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.postgresql.</tt><tt class="descname">TSRANGE</tt><a class="headerlink" href="#sqlalchemy.dialects.postgresql.TSRANGE" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="#sqlalchemy.dialects.postgresql.ranges.RangeOperators" title="sqlalchemy.dialects.postgresql.ranges.RangeOperators"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.dialects.postgresql.ranges.RangeOperators</span></tt></a>, <a class="reference internal" href="../core/types.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.TypeEngine</span></tt></a></p>
<p>Represent the Postgresql TSRANGE type.</p>
<div class="versionadded">
<p><span>New in version 0.8.2.</span></p>
</div>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.TSTZRANGE">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.postgresql.</tt><tt class="descname">TSTZRANGE</tt><a class="headerlink" href="#sqlalchemy.dialects.postgresql.TSTZRANGE" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="#sqlalchemy.dialects.postgresql.ranges.RangeOperators" title="sqlalchemy.dialects.postgresql.ranges.RangeOperators"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.dialects.postgresql.ranges.RangeOperators</span></tt></a>, <a class="reference internal" href="../core/types.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.TypeEngine</span></tt></a></p>
<p>Represent the Postgresql TSTZRANGE type.</p>
<div class="versionadded">
<p><span>New in version 0.8.2.</span></p>
</div>
</dd></dl>
<p>The types above get most of their functionality from the following
mixin:</p>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.ranges.RangeOperators">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.postgresql.ranges.</tt><tt class="descname">RangeOperators</tt><a class="headerlink" href="#sqlalchemy.dialects.postgresql.ranges.RangeOperators" title="Permalink to this definition">¶</a></dt>
<dd><p>This mixin provides functionality for the Range Operators
listed in Table 9-44 of the <a class="reference external" href="http://www.postgresql.org/docs/devel/static/functions-range.html">postgres documentation</a> for Range
Functions and Operators. It is used by all the range types
provided in the <tt class="docutils literal"><span class="pre">postgres</span></tt> dialect and can likely be used for
any range types you create yourself.</p>
<p>No extra support is provided for the Range Functions listed in
Table 9-45 of the postgres documentation. For these, the normal
<a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.expression.func" title="sqlalchemy.sql.expression.func"><tt class="xref py py-func docutils literal"><span class="pre">func()</span></tt></a> object should be used.</p>
<div class="versionadded">
<p><span>New in version 0.8.2: </span>Support for Postgresql RANGE operations.</p>
</div>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.ranges.RangeOperators.comparator_factory">
<em class="property">class </em><tt class="descname">comparator_factory</tt><big>(</big><em>expr</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.ranges.RangeOperators.comparator_factory" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.Comparator</span></tt></p>
<p>Define comparison operations for range types.</p>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.ranges.RangeOperators.comparator_factory.__ne__">
<tt class="descname">__ne__</tt><big>(</big><em>other</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.ranges.RangeOperators.comparator_factory.__ne__" title="Permalink to this definition">¶</a></dt>
<dd><p>Boolean expression. Returns true if two ranges are not equal</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.ranges.RangeOperators.comparator_factory.adjacent_to">
<tt class="descname">adjacent_to</tt><big>(</big><em>other</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.ranges.RangeOperators.comparator_factory.adjacent_to" title="Permalink to this definition">¶</a></dt>
<dd><p>Boolean expression. Returns true if the range in the column
is adjacent to the range in the operand.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.ranges.RangeOperators.comparator_factory.contained_by">
<tt class="descname">contained_by</tt><big>(</big><em>other</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.ranges.RangeOperators.comparator_factory.contained_by" title="Permalink to this definition">¶</a></dt>
<dd><p>Boolean expression. Returns true if the column is contained
within the right hand operand.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.ranges.RangeOperators.comparator_factory.contains">
<tt class="descname">contains</tt><big>(</big><em>other</em>, <em>**kw</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.ranges.RangeOperators.comparator_factory.contains" title="Permalink to this definition">¶</a></dt>
<dd><p>Boolean expression. Returns true if the right hand operand,
which can be an element or a range, is contained within the
column.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.ranges.RangeOperators.comparator_factory.not_extend_left_of">
<tt class="descname">not_extend_left_of</tt><big>(</big><em>other</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.ranges.RangeOperators.comparator_factory.not_extend_left_of" title="Permalink to this definition">¶</a></dt>
<dd><p>Boolean expression. Returns true if the range in the column
does not extend left of the range in the operand.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.ranges.RangeOperators.comparator_factory.not_extend_right_of">
<tt class="descname">not_extend_right_of</tt><big>(</big><em>other</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.ranges.RangeOperators.comparator_factory.not_extend_right_of" title="Permalink to this definition">¶</a></dt>
<dd><p>Boolean expression. Returns true if the range in the column
does not extend right of the range in the operand.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.ranges.RangeOperators.comparator_factory.overlaps">
<tt class="descname">overlaps</tt><big>(</big><em>other</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.ranges.RangeOperators.comparator_factory.overlaps" title="Permalink to this definition">¶</a></dt>
<dd><p>Boolean expression. Returns true if the column overlaps
(has points in common with) the right hand operand.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.ranges.RangeOperators.comparator_factory.strictly_left_of">
<tt class="descname">strictly_left_of</tt><big>(</big><em>other</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.ranges.RangeOperators.comparator_factory.strictly_left_of" title="Permalink to this definition">¶</a></dt>
<dd><p>Boolean expression. Returns true if the column is strictly
left of the right hand operand.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.ranges.RangeOperators.comparator_factory.strictly_right_of">
<tt class="descname">strictly_right_of</tt><big>(</big><em>other</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.ranges.RangeOperators.comparator_factory.strictly_right_of" title="Permalink to this definition">¶</a></dt>
<dd><p>Boolean expression. Returns true if the column is strictly
right of the right hand operand.</p>
</dd></dl>
</dd></dl>
</dd></dl>
<div class="admonition warning">
<p class="first admonition-title">Warning</p>
<p class="last">The range type DDL support should work with any Postgres DBAPI
driver, however the data types returned may vary. If you are using
<tt class="docutils literal"><span class="pre">psycopg2</span></tt>, it’s recommended to upgrade to version 2.5 or later
before using these column types.</p>
</div>
<p>When instantiating models that use these column types, you should pass
whatever data type is expected by the DBAPI driver you’re using for
the column type. For <a class="reference external" href="http://pythonhosted.org/psycopg2/module.html#module-psycopg2" title="(in Psycopg v2.5)"><tt class="xref py py-mod docutils literal"><span class="pre">psycopg2</span></tt></a> these are
<a class="reference external" href="http://pythonhosted.org/psycopg2/extras.html#psycopg2.extras.NumericRange" title="(in Psycopg v2.5)"><tt class="xref py py-class docutils literal"><span class="pre">NumericRange</span></tt></a>,
<a class="reference external" href="http://pythonhosted.org/psycopg2/extras.html#psycopg2.extras.DateRange" title="(in Psycopg v2.5)"><tt class="xref py py-class docutils literal"><span class="pre">DateRange</span></tt></a>,
<a class="reference external" href="http://pythonhosted.org/psycopg2/extras.html#psycopg2.extras.DateTimeRange" title="(in Psycopg v2.5)"><tt class="xref py py-class docutils literal"><span class="pre">DateTimeRange</span></tt></a> and
<a class="reference external" href="http://pythonhosted.org/psycopg2/extras.html#psycopg2.extras.DateTimeTZRange" title="(in Psycopg v2.5)"><tt class="xref py py-class docutils literal"><span class="pre">DateTimeTZRange</span></tt></a> or the class you’ve
registered with <a class="reference external" href="http://pythonhosted.org/psycopg2/extras.html#psycopg2.extras.register_range" title="(in Psycopg v2.5)"><tt class="xref py py-func docutils literal"><span class="pre">register_range()</span></tt></a>.</p>
<p>For example:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">psycopg2.extras</span> <span class="kn">import</span> <span class="n">DateTimeRange</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.dialects.postgresql</span> <span class="kn">import</span> <span class="n">TSRANGE</span>
<span class="k">class</span> <span class="nc">RoomBooking</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="n">__tablename__</span> <span class="o">=</span> <span class="s">'room_booking'</span>
<span class="n">room</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">(),</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
<span class="n">during</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">TSRANGE</span><span class="p">())</span>
<span class="n">booking</span> <span class="o">=</span> <span class="n">RoomBooking</span><span class="p">(</span>
<span class="n">room</span><span class="o">=</span><span class="mi">101</span><span class="p">,</span>
<span class="n">during</span><span class="o">=</span><span class="n">DateTimeRange</span><span class="p">(</span><span class="n">datetime</span><span class="p">(</span><span class="mi">2013</span><span class="p">,</span> <span class="mi">3</span><span class="p">,</span> <span class="mi">23</span><span class="p">),</span> <span class="bp">None</span><span class="p">)</span>
<span class="p">)</span></pre></div>
</div>
</div>
</div>
<div class="section" id="postgresql-constraint-types">
<h2>PostgreSQL Constraint Types<a class="headerlink" href="#postgresql-constraint-types" title="Permalink to this headline">¶</a></h2>
<p>SQLAlchemy supports Postgresql EXCLUDE constraints via the
<a class="reference internal" href="#sqlalchemy.dialects.postgresql.ExcludeConstraint" title="sqlalchemy.dialects.postgresql.ExcludeConstraint"><tt class="xref py py-class docutils literal"><span class="pre">ExcludeConstraint</span></tt></a> class:</p>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.ExcludeConstraint">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.postgresql.</tt><tt class="descname">ExcludeConstraint</tt><big>(</big><em>*elements</em>, <em>**kw</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.ExcludeConstraint" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/constraints.html#sqlalchemy.schema.ColumnCollectionConstraint" title="sqlalchemy.schema.ColumnCollectionConstraint"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.schema.ColumnCollectionConstraint</span></tt></a></p>
<p>A table-level EXCLUDE constraint.</p>
<p>Defines an EXCLUDE constraint as described in the <a class="reference external" href="http://www.postgresql.org/docs/9.0/static/sql-createtable.html#SQL-CREATETABLE-EXCLUDE">postgres
documentation</a>.</p>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.ExcludeConstraint.__init__">
<tt class="descname">__init__</tt><big>(</big><em>*elements</em>, <em>**kw</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.ExcludeConstraint.__init__" title="Permalink to this definition">¶</a></dt>
<dd><table class="docutils field-list" frame="void" rules="none">
<col class="field-name" />
<col class="field-body" />
<tbody valign="top">
<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body"><ul class="first last simple">
<li><span class="target" id="sqlalchemy.dialects.postgresql.ExcludeConstraint.params.*elements"></span><strong>*elements</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.postgresql.ExcludeConstraint.params.*elements">¶</a> – A sequence of two tuples of the form <tt class="docutils literal"><span class="pre">(column,</span> <span class="pre">operator)</span></tt> where
column must be a column name or Column object and operator must
be a string containing the operator to use.</li>
<li><span class="target" id="sqlalchemy.dialects.postgresql.ExcludeConstraint.params.name"></span><strong>name</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.postgresql.ExcludeConstraint.params.name">¶</a> – Optional, the in-database name of this constraint.</li>
<li><span class="target" id="sqlalchemy.dialects.postgresql.ExcludeConstraint.params.deferrable"></span><strong>deferrable</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.postgresql.ExcludeConstraint.params.deferrable">¶</a> – Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when
issuing DDL for this constraint.</li>
<li><span class="target" id="sqlalchemy.dialects.postgresql.ExcludeConstraint.params.initially"></span><strong>initially</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.postgresql.ExcludeConstraint.params.initially">¶</a> – Optional string. If set, emit INITIALLY <value> when issuing DDL
for this constraint.</li>
<li><span class="target" id="sqlalchemy.dialects.postgresql.ExcludeConstraint.params.using"></span><strong>using</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.postgresql.ExcludeConstraint.params.using">¶</a> – Optional string. If set, emit USING <index_method> when issuing DDL
for this constraint. Defaults to ‘gist’.</li>
<li><span class="target" id="sqlalchemy.dialects.postgresql.ExcludeConstraint.params.where"></span><strong>where</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.postgresql.ExcludeConstraint.params.where">¶</a> – Optional string. If set, emit WHERE <predicate> when issuing DDL
for this constraint.</li>
</ul>
</td>
</tr>
</tbody>
</table>
</dd></dl>
</dd></dl>
<p>For example:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy.dialects.postgresql</span> <span class="kn">import</span> <span class="n">ExcludeConstraint</span><span class="p">,</span> <span class="n">TSRANGE</span>
<span class="k">class</span> <span class="nc">RoomBooking</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="n">__tablename__</span> <span class="o">=</span> <span class="s">'room_booking'</span>
<span class="n">room</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">(),</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
<span class="n">during</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">TSRANGE</span><span class="p">())</span>
<span class="n">__table_args__</span> <span class="o">=</span> <span class="p">(</span>
<span class="n">ExcludeConstraint</span><span class="p">((</span><span class="s">'room'</span><span class="p">,</span> <span class="s">'='</span><span class="p">),</span> <span class="p">(</span><span class="s">'during'</span><span class="p">,</span> <span class="s">'&&'</span><span class="p">)),</span>
<span class="p">)</span></pre></div>
</div>
</div>
<div class="section" id="module-sqlalchemy.dialects.postgresql.psycopg2">
<span id="psycopg2"></span><h2>psycopg2<a class="headerlink" href="#module-sqlalchemy.dialects.postgresql.psycopg2" title="Permalink to this headline">¶</a></h2>
<p>Support for the PostgreSQL database via the psycopg2 driver.</p>
<div class="section" id="dialect-postgresql-psycopg2-url">
<h3>DBAPI<a class="headerlink" href="#dialect-postgresql-psycopg2-url" title="Permalink to this headline">¶</a></h3>
<p>Documentation and download information (if applicable) for psycopg2 is available at:
<a class="reference external" href="http://pypi.python.org/pypi/psycopg2/">http://pypi.python.org/pypi/psycopg2/</a></p>
</div>
<div class="section" id="dialect-postgresql-psycopg2-connect">
<h3>Connecting<a class="headerlink" href="#dialect-postgresql-psycopg2-connect" title="Permalink to this headline">¶</a></h3>
<p>Connect String:<div class="highlight-python"><pre>postgresql+psycopg2://user:password@host:port/dbname[?key=value&key=value...]</pre>
</div>
</p>
</div>
<div class="section" id="psycopg2-connect-arguments">
<h3>psycopg2 Connect Arguments<a class="headerlink" href="#psycopg2-connect-arguments" title="Permalink to this headline">¶</a></h3>
<p>psycopg2-specific keyword arguments which are accepted by
<a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><tt class="xref py py-func docutils literal"><span class="pre">create_engine()</span></tt></a> are:</p>
<ul>
<li><p class="first"><tt class="docutils literal"><span class="pre">server_side_cursors</span></tt>: Enable the usage of “server side cursors” for SQL
statements which support this feature. What this essentially means from a
psycopg2 point of view is that the cursor is created using a name, e.g.
<tt class="docutils literal"><span class="pre">connection.cursor('some</span> <span class="pre">name')</span></tt>, which has the effect that result rows
are not immediately pre-fetched and buffered after statement execution, but
are instead left on the server and only retrieved as needed. SQLAlchemy’s
<a class="reference internal" href="../core/connections.html#sqlalchemy.engine.ResultProxy" title="sqlalchemy.engine.ResultProxy"><tt class="xref py py-class docutils literal"><span class="pre">ResultProxy</span></tt></a> uses special row-buffering
behavior when this feature is enabled, such that groups of 100 rows at a
time are fetched over the wire to reduce conversational overhead.
Note that the <tt class="docutils literal"><span class="pre">stream_results=True</span></tt> execution option is a more targeted
way of enabling this mode on a per-execution basis.</p>
</li>
<li><p class="first"><tt class="docutils literal"><span class="pre">use_native_unicode</span></tt>: Enable the usage of Psycopg2 “native unicode” mode
per connection. True by default.</p>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p class="last"><a class="reference internal" href="#psycopg2-disable-native-unicode"><em>Disabling Native Unicode</em></a></p>
</div>
</li>
<li><p class="first"><tt class="docutils literal"><span class="pre">isolation_level</span></tt>: This option, available for all PostgreSQL dialects,
includes the <tt class="docutils literal"><span class="pre">AUTOCOMMIT</span></tt> isolation level when using the psycopg2
dialect.</p>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p class="last"><a class="reference internal" href="#psycopg2-isolation-level"><em>Psycopg2 Transaction Isolation Level</em></a></p>
</div>
</li>
<li><p class="first"><tt class="docutils literal"><span class="pre">client_encoding</span></tt>: sets the client encoding in a libpq-agnostic way,
using psycopg2’s <tt class="docutils literal"><span class="pre">set_client_encoding()</span></tt> method.</p>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p class="last"><a class="reference internal" href="#psycopg2-unicode"><em>Unicode with Psycopg2</em></a></p>
</div>
</li>
</ul>
</div>
<div class="section" id="unix-domain-connections">
<h3>Unix Domain Connections<a class="headerlink" href="#unix-domain-connections" title="Permalink to this headline">¶</a></h3>
<p>psycopg2 supports connecting via Unix domain connections. When the <tt class="docutils literal"><span class="pre">host</span></tt>
portion of the URL is omitted, SQLAlchemy passes <tt class="docutils literal"><span class="pre">None</span></tt> to psycopg2,
which specifies Unix-domain communication rather than TCP/IP communication:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">create_engine</span><span class="p">(</span><span class="s">"postgresql+psycopg2://user:password@/dbname"</span><span class="p">)</span></pre></div>
</div>
<p>By default, the socket file used is to connect to a Unix-domain socket
in <tt class="docutils literal"><span class="pre">/tmp</span></tt>, or whatever socket directory was specified when PostgreSQL
was built. This value can be overridden by passing a pathname to psycopg2,
using <tt class="docutils literal"><span class="pre">host</span></tt> as an additional keyword argument:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">create_engine</span><span class="p">(</span><span class="s">"postgresql+psycopg2://user:password@/dbname?host=/var/lib/postgresql"</span><span class="p">)</span></pre></div>
</div>
<p>See also:</p>
<p><a class="reference external" href="http://www.postgresql.org/docs/9.1/static/libpq-connect.html#LIBPQ-PQCONNECTDBPARAMS">PQconnectdbParams</a></p>
</div>
<div class="section" id="per-statement-connection-execution-options">
<h3>Per-Statement/Connection Execution Options<a class="headerlink" href="#per-statement-connection-execution-options" title="Permalink to this headline">¶</a></h3>
<p>The following DBAPI-specific options are respected when used with
<a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Connection.execution_options" title="sqlalchemy.engine.Connection.execution_options"><tt class="xref py py-meth docutils literal"><span class="pre">Connection.execution_options()</span></tt></a>, <a class="reference internal" href="../core/selectable.html#sqlalchemy.sql.expression.Executable.execution_options" title="sqlalchemy.sql.expression.Executable.execution_options"><tt class="xref py py-meth docutils literal"><span class="pre">Executable.execution_options()</span></tt></a>,
<a class="reference internal" href="../orm/query.html#sqlalchemy.orm.query.Query.execution_options" title="sqlalchemy.orm.query.Query.execution_options"><tt class="xref py py-meth docutils literal"><span class="pre">Query.execution_options()</span></tt></a>, in addition to those not specific to DBAPIs:</p>
<ul class="simple">
<li>isolation_level - Set the transaction isolation level for the lifespan of a
<a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><tt class="xref py py-class docutils literal"><span class="pre">Connection</span></tt></a> (can only be set on a connection, not a statement
or query). See <a class="reference internal" href="#psycopg2-isolation-level"><em>Psycopg2 Transaction Isolation Level</em></a>.</li>
<li>stream_results - Enable or disable usage of psycopg2 server side cursors -
this feature makes use of “named” cursors in combination with special
result handling methods so that result rows are not fully buffered.
If <tt class="docutils literal"><span class="pre">None</span></tt> or not set, the <tt class="docutils literal"><span class="pre">server_side_cursors</span></tt> option of the
<a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><tt class="xref py py-class docutils literal"><span class="pre">Engine</span></tt></a> is used.</li>
</ul>
</div>
<div class="section" id="unicode-with-psycopg2">
<span id="psycopg2-unicode"></span><h3>Unicode with Psycopg2<a class="headerlink" href="#unicode-with-psycopg2" title="Permalink to this headline">¶</a></h3>
<p>By default, the psycopg2 driver uses the <tt class="docutils literal"><span class="pre">psycopg2.extensions.UNICODE</span></tt>
extension, such that the DBAPI receives and returns all strings as Python
Unicode objects directly - SQLAlchemy passes these values through without
change. Psycopg2 here will encode/decode string values based on the
current “client encoding” setting; by default this is the value in
the <tt class="docutils literal"><span class="pre">postgresql.conf</span></tt> file, which often defaults to <tt class="docutils literal"><span class="pre">SQL_ASCII</span></tt>.
Typically, this can be changed to <tt class="docutils literal"><span class="pre">utf8</span></tt>, as a more useful default:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="c"># postgresql.conf file</span>
<span class="c"># client_encoding = sql_ascii # actually, defaults to database</span>
<span class="c"># encoding</span>
<span class="n">client_encoding</span> <span class="o">=</span> <span class="n">utf8</span></pre></div>
</div>
<p>A second way to affect the client encoding is to set it within Psycopg2
locally. SQLAlchemy will call psycopg2’s
<a class="reference external" href="http://pythonhosted.org/psycopg2/connection.html#connection.set_client_encoding" title="(in Psycopg v2.5)"><tt class="docutils literal"><span class="pre">connection.set_client_encoding()</span></tt></a> method
on all new connections based on the value passed to
<a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><tt class="xref py py-func docutils literal"><span class="pre">create_engine()</span></tt></a> using the <tt class="docutils literal"><span class="pre">client_encoding</span></tt> parameter:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="c"># set_client_encoding() setting;</span>
<span class="c"># works for *all* Postgresql versions</span>
<span class="n">engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">"postgresql://user:pass@host/dbname"</span><span class="p">,</span>
<span class="n">client_encoding</span><span class="o">=</span><span class="s">'utf8'</span><span class="p">)</span></pre></div>
</div>
<p>This overrides the encoding specified in the Postgresql client configuration.
When using the parameter in this way, the psycopg2 driver emits
<tt class="docutils literal"><span class="pre">SET</span> <span class="pre">client_encoding</span> <span class="pre">TO</span> <span class="pre">'utf8'</span></tt> on the connection explicitly, and works
in all Postgresql versions.</p>
<p>Note that the <tt class="docutils literal"><span class="pre">client_encoding</span></tt> setting as passed to <a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><tt class="xref py py-func docutils literal"><span class="pre">create_engine()</span></tt></a>
is <strong>not the same</strong> as the more recently added <tt class="docutils literal"><span class="pre">client_encoding</span></tt> parameter
now supported by libpq directly. This is enabled when <tt class="docutils literal"><span class="pre">client_encoding</span></tt>
is passed directly to <tt class="docutils literal"><span class="pre">psycopg2.connect()</span></tt>, and from SQLAlchemy is passed
using the <a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine.params.connect_args" title="sqlalchemy.create_engine"><tt class="xref py py-paramref docutils literal"><span class="pre">create_engine.connect_args</span></tt></a> parameter:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="c"># libpq direct parameter setting;</span>
<span class="c"># only works for Postgresql **9.1 and above**</span>
<span class="n">engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">"postgresql://user:pass@host/dbname"</span><span class="p">,</span>
<span class="n">connect_args</span><span class="o">=</span><span class="p">{</span><span class="s">'client_encoding'</span><span class="p">:</span> <span class="s">'utf8'</span><span class="p">})</span>
<span class="c"># using the query string is equivalent</span>
<span class="n">engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">"postgresql://user:pass@host/dbname?client_encoding=utf8"</span><span class="p">)</span></pre></div>
</div>
<p>The above parameter was only added to libpq as of version 9.1 of Postgresql,
so using the previous method is better for cross-version support.</p>
<div class="section" id="disabling-native-unicode">
<span id="psycopg2-disable-native-unicode"></span><h4>Disabling Native Unicode<a class="headerlink" href="#disabling-native-unicode" title="Permalink to this headline">¶</a></h4>
<p>SQLAlchemy can also be instructed to skip the usage of the psycopg2
<tt class="docutils literal"><span class="pre">UNICODE</span></tt> extension and to instead utilize its own unicode encode/decode
services, which are normally reserved only for those DBAPIs that don’t
fully support unicode directly. Passing <tt class="docutils literal"><span class="pre">use_native_unicode=False</span></tt> to
<a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><tt class="xref py py-func docutils literal"><span class="pre">create_engine()</span></tt></a> will disable usage of <tt class="docutils literal"><span class="pre">psycopg2.extensions.UNICODE</span></tt>.
SQLAlchemy will instead encode data itself into Python bytestrings on the way
in and coerce from bytes on the way back,
using the value of the <a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><tt class="xref py py-func docutils literal"><span class="pre">create_engine()</span></tt></a> <tt class="docutils literal"><span class="pre">encoding</span></tt> parameter, which
defaults to <tt class="docutils literal"><span class="pre">utf-8</span></tt>.
SQLAlchemy’s own unicode encode/decode functionality is steadily becoming
obsolete as most DBAPIs now support unicode fully.</p>
</div>
</div>
<div class="section" id="transactions">
<h3>Transactions<a class="headerlink" href="#transactions" title="Permalink to this headline">¶</a></h3>
<p>The psycopg2 dialect fully supports SAVEPOINT and two-phase commit operations.</p>
</div>
<div class="section" id="psycopg2-transaction-isolation-level">
<span id="psycopg2-isolation-level"></span><h3>Psycopg2 Transaction Isolation Level<a class="headerlink" href="#psycopg2-transaction-isolation-level" title="Permalink to this headline">¶</a></h3>
<p>As discussed in <a class="reference internal" href="#postgresql-isolation-level"><em>Transaction Isolation Level</em></a>,
all Postgresql dialects support setting of transaction isolation level
both via the <tt class="docutils literal"><span class="pre">isolation_level</span></tt> parameter passed to <a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><tt class="xref py py-func docutils literal"><span class="pre">create_engine()</span></tt></a>,
as well as the <tt class="docutils literal"><span class="pre">isolation_level</span></tt> argument used by
<a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Connection.execution_options" title="sqlalchemy.engine.Connection.execution_options"><tt class="xref py py-meth docutils literal"><span class="pre">Connection.execution_options()</span></tt></a>. When using the psycopg2 dialect, these
options make use of psycopg2’s <tt class="docutils literal"><span class="pre">set_isolation_level()</span></tt> connection method,
rather than emitting a Postgresql directive; this is because psycopg2’s
API-level setting is always emitted at the start of each transaction in any
case.</p>
<p>The psycopg2 dialect supports these constants for isolation level:</p>
<ul class="simple">
<li><tt class="docutils literal"><span class="pre">READ</span> <span class="pre">COMMITTED</span></tt></li>
<li><tt class="docutils literal"><span class="pre">READ</span> <span class="pre">UNCOMMITTED</span></tt></li>
<li><tt class="docutils literal"><span class="pre">REPEATABLE</span> <span class="pre">READ</span></tt></li>
<li><tt class="docutils literal"><span class="pre">SERIALIZABLE</span></tt></li>
<li><tt class="docutils literal"><span class="pre">AUTOCOMMIT</span></tt></li>
</ul>
<div class="versionadded">
<p><span>New in version 0.8.2: </span>support for AUTOCOMMIT isolation level when using
psycopg2.</p>
</div>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p><a class="reference internal" href="#postgresql-isolation-level"><em>Transaction Isolation Level</em></a></p>
<p class="last"><a class="reference internal" href="#pg8000-isolation-level"><em>pg8000 Transaction Isolation Level</em></a></p>
</div>
</div>
<div class="section" id="notice-logging">
<h3>NOTICE logging<a class="headerlink" href="#notice-logging" title="Permalink to this headline">¶</a></h3>
<p>The psycopg2 dialect will log Postgresql NOTICE messages via the
<tt class="docutils literal"><span class="pre">sqlalchemy.dialects.postgresql</span></tt> logger:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">import</span> <span class="nn">logging</span>
<span class="n">logging</span><span class="o">.</span><span class="n">getLogger</span><span class="p">(</span><span class="s">'sqlalchemy.dialects.postgresql'</span><span class="p">)</span><span class="o">.</span><span class="n">setLevel</span><span class="p">(</span><span class="n">logging</span><span class="o">.</span><span class="n">INFO</span><span class="p">)</span></pre></div>
</div>
</div>
<div class="section" id="hstore-type">
<h3>HSTORE type<a class="headerlink" href="#hstore-type" title="Permalink to this headline">¶</a></h3>
<p>The <tt class="docutils literal"><span class="pre">psycopg2</span></tt> DBAPI includes an extension to natively handle marshalling of
the HSTORE type. The SQLAlchemy psycopg2 dialect will enable this extension
by default when it is detected that the target database has the HSTORE
type set up for use. In other words, when the dialect makes the first
connection, a sequence like the following is performed:</p>
<ol class="arabic simple">
<li>Request the available HSTORE oids using
<tt class="docutils literal"><span class="pre">psycopg2.extras.HstoreAdapter.get_oids()</span></tt>.
If this function returns a list of HSTORE identifiers, we then determine
that the <tt class="docutils literal"><span class="pre">HSTORE</span></tt> extension is present.</li>
<li>If the <tt class="docutils literal"><span class="pre">use_native_hstore</span></tt> flag is at its default of <tt class="docutils literal"><span class="pre">True</span></tt>, and
we’ve detected that <tt class="docutils literal"><span class="pre">HSTORE</span></tt> oids are available, the
<tt class="docutils literal"><span class="pre">psycopg2.extensions.register_hstore()</span></tt> extension is invoked for all
connections.</li>
</ol>
<p>The <tt class="docutils literal"><span class="pre">register_hstore()</span></tt> extension has the effect of <strong>all Python
dictionaries being accepted as parameters regardless of the type of target
column in SQL</strong>. The dictionaries are converted by this extension into a
textual HSTORE expression. If this behavior is not desired, disable the
use of the hstore extension by setting <tt class="docutils literal"><span class="pre">use_native_hstore</span></tt> to <tt class="docutils literal"><span class="pre">False</span></tt> as
follows:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">"postgresql+psycopg2://scott:tiger@localhost/test"</span><span class="p">,</span>
<span class="n">use_native_hstore</span><span class="o">=</span><span class="bp">False</span><span class="p">)</span></pre></div>
</div>
<p>The <tt class="docutils literal"><span class="pre">HSTORE</span></tt> type is <strong>still supported</strong> when the
<tt class="docutils literal"><span class="pre">psycopg2.extensions.register_hstore()</span></tt> extension is not used. It merely
means that the coercion between Python dictionaries and the HSTORE
string format, on both the parameter side and the result side, will take
place within SQLAlchemy’s own marshalling logic, and not that of <tt class="docutils literal"><span class="pre">psycopg2</span></tt>
which may be more performant.</p>
</div>
</div>
<div class="section" id="module-sqlalchemy.dialects.postgresql.pypostgresql">
<span id="py-postgresql"></span><h2>py-postgresql<a class="headerlink" href="#module-sqlalchemy.dialects.postgresql.pypostgresql" title="Permalink to this headline">¶</a></h2>
<p>Support for the PostgreSQL database via the py-postgresql driver.</p>
<div class="section" id="dialect-postgresql-pypostgresql-url">
<h3>DBAPI<a class="headerlink" href="#dialect-postgresql-pypostgresql-url" title="Permalink to this headline">¶</a></h3>
<p>Documentation and download information (if applicable) for py-postgresql is available at:
<a class="reference external" href="http://python.projects.pgfoundry.org/">http://python.projects.pgfoundry.org/</a></p>
</div>
<div class="section" id="dialect-postgresql-pypostgresql-connect">
<h3>Connecting<a class="headerlink" href="#dialect-postgresql-pypostgresql-connect" title="Permalink to this headline">¶</a></h3>
<p>Connect String:<div class="highlight-python"><pre>postgresql+pypostgresql://user:password@host:port/dbname[?key=value&key=value...]</pre>
</div>
</p>
</div>
</div>
<div class="section" id="module-sqlalchemy.dialects.postgresql.pg8000">
<span id="pg8000"></span><h2>pg8000<a class="headerlink" href="#module-sqlalchemy.dialects.postgresql.pg8000" title="Permalink to this headline">¶</a></h2>
<p>Support for the PostgreSQL database via the pg8000 driver.</p>
<div class="section" id="dialect-postgresql-pg8000-url">
<h3>DBAPI<a class="headerlink" href="#dialect-postgresql-pg8000-url" title="Permalink to this headline">¶</a></h3>
<p>Documentation and download information (if applicable) for pg8000 is available at:
<a class="reference external" href="https://pythonhosted.org/pg8000/">https://pythonhosted.org/pg8000/</a></p>
</div>
<div class="section" id="dialect-postgresql-pg8000-connect">
<h3>Connecting<a class="headerlink" href="#dialect-postgresql-pg8000-connect" title="Permalink to this headline">¶</a></h3>
<p>Connect String:<div class="highlight-python"><pre>postgresql+pg8000://user:password@host:port/dbname[?key=value&key=value...]</pre>
</div>
</p>
</div>
<div class="section" id="unicode">
<h3>Unicode<a class="headerlink" href="#unicode" title="Permalink to this headline">¶</a></h3>
<p>When communicating with the server, pg8000 <strong>always uses the server-side
character set</strong>. SQLAlchemy has no ability to modify what character set
pg8000 chooses to use, and additionally SQLAlchemy does no unicode conversion
of any kind with the pg8000 backend. The origin of the client encoding setting
is ultimately the CLIENT_ENCODING setting in postgresql.conf.</p>
<p>It is not necessary, though is also harmless, to pass the “encoding” parameter
to <a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><tt class="xref py py-func docutils literal"><span class="pre">create_engine()</span></tt></a> when using pg8000.</p>
</div>
<div class="section" id="pg8000-transaction-isolation-level">
<span id="pg8000-isolation-level"></span><h3>pg8000 Transaction Isolation Level<a class="headerlink" href="#pg8000-transaction-isolation-level" title="Permalink to this headline">¶</a></h3>
<p>The pg8000 dialect offers the same isolation level settings as that
of the <a class="reference internal" href="#psycopg2-isolation-level"><em>psycopg2</em></a> dialect:</p>
<ul class="simple">
<li><tt class="docutils literal"><span class="pre">READ</span> <span class="pre">COMMITTED</span></tt></li>
<li><tt class="docutils literal"><span class="pre">READ</span> <span class="pre">UNCOMMITTED</span></tt></li>
<li><tt class="docutils literal"><span class="pre">REPEATABLE</span> <span class="pre">READ</span></tt></li>
<li><tt class="docutils literal"><span class="pre">SERIALIZABLE</span></tt></li>
<li><tt class="docutils literal"><span class="pre">AUTOCOMMIT</span></tt></li>
</ul>
<div class="versionadded">
<p><span>New in version 0.9.5: </span>support for AUTOCOMMIT isolation level when using
pg8000.</p>
</div>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p><a class="reference internal" href="#postgresql-isolation-level"><em>Transaction Isolation Level</em></a></p>
<p class="last"><a class="reference internal" href="#psycopg2-isolation-level"><em>Psycopg2 Transaction Isolation Level</em></a></p>
</div>
</div>
</div>
<div class="section" id="module-sqlalchemy.dialects.postgresql.zxjdbc">
<span id="zxjdbc"></span><h2>zxjdbc<a class="headerlink" href="#module-sqlalchemy.dialects.postgresql.zxjdbc" title="Permalink to this headline">¶</a></h2>
<p>Support for the PostgreSQL database via the zxJDBC for Jython driver.</p>
<div class="section" id="dialect-postgresql-zxjdbc-url">
<h3>DBAPI<a class="headerlink" href="#dialect-postgresql-zxjdbc-url" title="Permalink to this headline">¶</a></h3>
<p>Drivers for this database are available at:
<a class="reference external" href="http://jdbc.postgresql.org/">http://jdbc.postgresql.org/</a></p>
</div>
<div class="section" id="dialect-postgresql-zxjdbc-connect">
<h3>Connecting<a class="headerlink" href="#dialect-postgresql-zxjdbc-connect" title="Permalink to this headline">¶</a></h3>
<p>Connect String:<div class="highlight-python"><pre>postgresql+zxjdbc://scott:tiger@localhost/db</pre>
</div>
</p>
</div>
</div>
</div>
</div>
</div>
<div id="docs-bottom-navigation" class="docs-navigation-links">
Previous:
<a href="oracle.html" title="previous chapter">Oracle</a>
Next:
<a href="sqlite.html" title="next chapter">SQLite</a>
<div id="docs-copyright">
© <a href="../copyright.html">Copyright</a> 2007-2014, the SQLAlchemy authors and contributors.
Created using <a href="http://sphinx.pocoo.org/">Sphinx</a> 1.2b1.
</div>
</div>
</div>
</body>
</html>
|
