<!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.8 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/changelog.css" type="text/css" />
<link rel="stylesheet" href="../_static/sphinx_paramlinks.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.8.7',
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.8 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.8.7</span> | Release Date: July 22, 2014
</div>
<h1>SQLAlchemy 0.8 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.8 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-cross-schema-table-introspection">Remote / Cross-Schema Table Introspection</a></li>
<li><a class="reference internal" href="#insert-update-returning">INSERT/UPDATE...RETURNING</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">Unicode</a></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="#id3">Unicode</a></li>
<li><a class="reference internal" href="#interval">Interval</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> dialect also offers the special level <tt class="docutils literal"><span class="pre">AUTOCOMMIT</span></tt>. See
<a class="reference internal" href="#psycopg2-isolation-level"><em>Psycopg2 Transaction Isolation Level</em></a> for details.</p>
</div>
<div class="section" id="remote-cross-schema-table-introspection">
<h2>Remote / Cross-Schema Table Introspection<a class="headerlink" href="#remote-cross-schema-table-introspection" title="Permalink to this headline">¶</a></h2>
<p>Tables can be introspected from any accessible schema, including
inter-schema foreign key relationships. However, care must be taken
when specifying the “schema” argument for a given <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>, when
the given schema is also present in PostgreSQL’s <tt class="docutils literal"><span class="pre">search_path</span></tt> variable
for the current connection.</p>
<p>If a FOREIGN KEY constraint reports that the remote table’s schema is within
the current <tt class="docutils literal"><span class="pre">search_path</span></tt>, the “schema” attribute of the resulting
<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> will be set to <tt class="docutils literal"><span class="pre">None</span></tt>, unless the actual schema of the
remote table matches that of the referencing table, and the “schema” argument
was explicitly stated on the referencing table.</p>
<p>The best practice here is to not use the <tt class="docutils literal"><span class="pre">schema</span></tt> argument
on <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> for any schemas that are present in <tt class="docutils literal"><span class="pre">search_path</span></tt>.
<tt class="docutils literal"><span class="pre">search_path</span></tt> defaults to “public”, but care should be taken
to inspect the actual value using:</p>
<div class="highlight-python"><pre>SHOW search_path;</pre>
</div>
<div class="versionchanged">
<p><span>Changed in version 0.7.3: </span>Prior to this version, cross-schema foreign keys when the schemas
were also in the <tt class="docutils literal"><span class="pre">search_path</span></tt> could make an incorrect assumption
if the schemas were explicitly stated on each <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>.</p>
</div>
<p>Background on PG’s <tt class="docutils literal"><span class="pre">search_path</span></tt> is at:
<a class="reference external" href="http://www.postgresql.org/docs/9.0/static/ddl-schemas.html#DDL-SCHEMAS-PATH">http://www.postgresql.org/docs/9.0/static/ddl-schemas.html#DDL-SCHEMAS-PATH</a></p>
</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="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">MACADDR</span><span class="p">,</span> <span class="n">NUMERIC</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></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><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><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>
</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><big>(</big><em>*args</em>, <em>**kwargs</em><big>)</big><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="method">
<dt id="sqlalchemy.dialects.postgresql.CIDR.__init__">
<tt class="descname">__init__</tt><big>(</big><em>*args</em>, <em>**kwargs</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.CIDR.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Support implementations that were passing arguments</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>**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>**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.**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><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/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><big>(</big><em>*args</em>, <em>**kwargs</em><big>)</big><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="method">
<dt id="sqlalchemy.dialects.postgresql.INET.__init__">
<tt class="descname">__init__</tt><big>(</big><em>*args</em>, <em>**kwargs</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.INET.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Support implementations that were passing arguments</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.MACADDR">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.postgresql.</tt><tt class="descname">MACADDR</tt><big>(</big><em>*args</em>, <em>**kwargs</em><big>)</big><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="method">
<dt id="sqlalchemy.dialects.postgresql.MACADDR.__init__">
<tt class="descname">__init__</tt><big>(</big><em>*args</em>, <em>**kwargs</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.MACADDR.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Support implementations that were passing arguments</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>**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>**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.**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.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 founds 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><big>(</big><em>*args</em>, <em>**kwargs</em><big>)</big><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><big>(</big><em>*args</em>, <em>**kwargs</em><big>)</big><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><big>(</big><em>*args</em>, <em>**kwargs</em><big>)</big><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><big>(</big><em>*args</em>, <em>**kwargs</em><big>)</big><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><big>(</big><em>*args</em>, <em>**kwargs</em><big>)</big><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><big>(</big><em>*args</em>, <em>**kwargs</em><big>)</big><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>
</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">RoomBookings</span><span class="p">(</span><span class="n">Base</span><span class="p">):</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 class="simple">
<li><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.</li>
<li><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.</li>
<li><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. See <a class="reference internal" href="#psycopg2-isolation-level"><em>Psycopg2 Transaction Isolation Level</em></a>.</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">
<h3>Unicode<a class="headerlink" href="#unicode" 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">utf-8</span></tt>, as a more useful default:</p>
<div class="highlight-python"><div class="highlight"><pre><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 <tt class="docutils literal"><span class="pre">set_client_encoding()</span></tt>
method (see: <a class="reference external" href="http://initd.org/psycopg/docs/connection.html#connection.set_client_encoding">http://initd.org/psycopg/docs/connection.html#connection.set_client_encoding</a>)
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="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.</p>
<div class="versionadded">
<p><span>New in version 0.7.3: </span>The psycopg2-specific <tt class="docutils literal"><span class="pre">client_encoding</span></tt> 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>.</p>
</div>
<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 more DBAPIs support unicode fully along with the approach of
Python 3; in modern usage psycopg2 should be relied upon to handle unicode.</p>
</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>
<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="http://pybrary.net/pg8000/">http://pybrary.net/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="id3">
<h3>Unicode<a class="headerlink" href="#id3" title="Permalink to this headline">¶</a></h3>
<p>pg8000 requires that the postgresql client encoding be
configured in the postgresql.conf file in order to use encodings
other than ascii. Set this value to the same value as the
“encoding” parameter on create_engine(), usually “utf-8”.</p>
</div>
<div class="section" id="interval">
<h3>Interval<a class="headerlink" href="#interval" title="Permalink to this headline">¶</a></h3>
<p>Passing data from/to the Interval type is not supported as of
yet.</p>
</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>
