File: //usr/share/doc/python-sqlalchemy-0.9.8/doc/dialects/postgresql.html
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>
PostgreSQL
—
SQLAlchemy 0.9 Documentation
</title>
<!-- begin iterate through SQLA + sphinx environment css_files -->
<link rel="stylesheet" href="../_static/pygments.css" type="text/css" />
<link rel="stylesheet" href="../_static/docs.css" type="text/css" />
<link rel="stylesheet" href="../_static/sphinx_paramlinks.css" type="text/css" />
<link rel="stylesheet" href="../_static/changelog.css" type="text/css" />
<!-- end iterate through SQLA + sphinx environment css_files -->
<!-- begin layout.mako headers -->
<script type="text/javascript">
var DOCUMENTATION_OPTIONS = {
URL_ROOT: '../',
VERSION: '0.9.8',
COLLAPSE_MODINDEX: false,
FILE_SUFFIX: '.html'
};
</script>
<!-- begin iterate through sphinx environment script_files -->
<script type="text/javascript" src="../_static/jquery.js"></script>
<script type="text/javascript" src="../_static/underscore.js"></script>
<script type="text/javascript" src="../_static/doctools.js"></script>
<!-- end iterate through sphinx environment script_files -->
<script type="text/javascript" src="../_static/detectmobile.js"></script>
<script type="text/javascript" src="../_static/init.js"></script>
<link rel="index" title="Index" href="../genindex.html" />
<link rel="search" title="Search" href="../search.html" />
<link rel="copyright" title="Copyright" href="../copyright.html" />
<link rel="top" title="SQLAlchemy 0.9 Documentation" href="../index.html" />
<link rel="up" title="Dialects" href="index.html" />
<link rel="next" title="SQLite" href="sqlite.html" />
<link rel="prev" title="Oracle" href="oracle.html" />
<!-- end layout.mako headers -->
</head>
<body>
<div id="docs-container">
<div id="docs-top-navigation-container" class="body-background">
<div id="docs-header">
<div id="docs-version-header">
Release: <span class="version-num">0.9.8</span> | Release Date: October 13, 2014
</div>
<h1>SQLAlchemy 0.9 Documentation</h1>
</div>
</div>
<div id="docs-body-container">
<div id="fixed-sidebar" class="withsidebar">
<div id="docs-sidebar-popout">
<h3><a href="../index.html">SQLAlchemy 0.9 Documentation</a></h3>
<p id="sidebar-paginate">
<a href="index.html" title="Dialects">Up</a> |
<a href="oracle.html" title="Oracle">Prev</a> |
<a href="sqlite.html" title="SQLite">Next</a>
</p>
<p id="sidebar-topnav">
<a href="../index.html">Contents</a> |
<a href="../genindex.html">Index</a>
</p>
<div id="sidebar-search">
<form class="search" action="../search.html" method="get">
<input type="text" name="q" size="12" /> <input type="submit" value="Search" />
<input type="hidden" name="check_keywords" value="yes" />
<input type="hidden" name="area" value="default" />
</form>
</div>
</div>
<div id="docs-sidebar">
<h3><a href="#">
PostgreSQL
</a></h3>
<ul>
<li><a class="reference internal" href="#">PostgreSQL</a><ul>
<li><a class="reference internal" href="#dialect-postgresql">Support for the PostgreSQL database.</a></li>
<li><a class="reference internal" href="#sequences-serial">Sequences/SERIAL</a></li>
<li><a class="reference internal" href="#transaction-isolation-level">Transaction Isolation Level</a></li>
<li><a class="reference internal" href="#remote-schema-table-introspection-and-postgresql-search-path">Remote-Schema Table Introspection and Postgresql search_path</a></li>
<li><a class="reference internal" href="#insert-update-returning">INSERT/UPDATE...RETURNING</a></li>
<li><a class="reference internal" href="#full-text-search">Full Text Search</a></li>
<li><a class="reference internal" href="#from-only">FROM ONLY ...</a></li>
<li><a class="reference internal" href="#postgresql-specific-index-options">Postgresql-Specific Index Options</a><ul>
<li><a class="reference internal" href="#partial-indexes">Partial Indexes</a></li>
<li><a class="reference internal" href="#operator-classes">Operator Classes</a></li>
<li><a class="reference internal" href="#index-types">Index Types</a></li>
</ul>
</li>
<li><a class="reference internal" href="#postgresql-data-types">PostgreSQL Data Types</a><ul>
<li><a class="reference internal" href="#range-types">Range Types</a></li>
</ul>
</li>
<li><a class="reference internal" href="#postgresql-constraint-types">PostgreSQL Constraint Types</a></li>
<li><a class="reference internal" href="#module-sqlalchemy.dialects.postgresql.psycopg2">psycopg2</a><ul>
<li><a class="reference internal" href="#dialect-postgresql-psycopg2-url">DBAPI</a></li>
<li><a class="reference internal" href="#dialect-postgresql-psycopg2-connect">Connecting</a></li>
<li><a class="reference internal" href="#psycopg2-connect-arguments">psycopg2 Connect Arguments</a></li>
<li><a class="reference internal" href="#unix-domain-connections">Unix Domain Connections</a></li>
<li><a class="reference internal" href="#per-statement-connection-execution-options">Per-Statement/Connection Execution Options</a></li>
<li><a class="reference internal" href="#unicode-with-psycopg2">Unicode with Psycopg2</a><ul>
<li><a class="reference internal" href="#disabling-native-unicode">Disabling Native Unicode</a></li>
</ul>
</li>
<li><a class="reference internal" href="#transactions">Transactions</a></li>
<li><a class="reference internal" href="#psycopg2-transaction-isolation-level">Psycopg2 Transaction Isolation Level</a></li>
<li><a class="reference internal" href="#notice-logging">NOTICE logging</a></li>
<li><a class="reference internal" href="#hstore-type">HSTORE type</a></li>
</ul>
</li>
<li><a class="reference internal" href="#module-sqlalchemy.dialects.postgresql.pypostgresql">py-postgresql</a><ul>
<li><a class="reference internal" href="#dialect-postgresql-pypostgresql-url">DBAPI</a></li>
<li><a class="reference internal" href="#dialect-postgresql-pypostgresql-connect">Connecting</a></li>
</ul>
</li>
<li><a class="reference internal" href="#module-sqlalchemy.dialects.postgresql.pg8000">pg8000</a><ul>
<li><a class="reference internal" href="#dialect-postgresql-pg8000-url">DBAPI</a></li>
<li><a class="reference internal" href="#dialect-postgresql-pg8000-connect">Connecting</a></li>
<li><a class="reference internal" href="#unicode">Unicode</a></li>
<li><a class="reference internal" href="#pg8000-transaction-isolation-level">pg8000 Transaction Isolation Level</a></li>
</ul>
</li>
<li><a class="reference internal" href="#module-sqlalchemy.dialects.postgresql.zxjdbc">zxjdbc</a><ul>
<li><a class="reference internal" href="#dialect-postgresql-zxjdbc-url">DBAPI</a></li>
<li><a class="reference internal" href="#dialect-postgresql-zxjdbc-connect">Connecting</a></li>
</ul>
</li>
</ul>
</li>
</ul>
</div>
</div>
<div id="docs-body" class="withsidebar" >
<div class="section" id="module-sqlalchemy.dialects.postgresql.base">
<span id="postgresql"></span><span id="postgresql-toplevel"></span><h1>PostgreSQL<a class="headerlink" href="#module-sqlalchemy.dialects.postgresql.base" title="Permalink to this headline">¶</a></h1>
<div class="section" id="dialect-postgresql">
<p>Support for the PostgreSQL database.</p>
<h2>DBAPI Support<a class="headerlink" href="#dialect-postgresql" title="Permalink to this headline">¶</a></h2>
<p>The following dialect/DBAPI options are available. Please refer to individual DBAPI sections for connect information.<ul class="simple">
<li><a class="reference external" href="#module-sqlalchemy.dialects.postgresql.psycopg2">psycopg2</a></li>
<li><a class="reference external" href="#module-sqlalchemy.dialects.postgresql.pypostgresql">py-postgresql</a></li>
<li><a class="reference external" href="#module-sqlalchemy.dialects.postgresql.pg8000">pg8000</a></li>
<li><a class="reference external" href="#module-sqlalchemy.dialects.postgresql.zxjdbc">zxJDBC for Jython</a></li>
</ul>
</p>
</div>
<div class="section" id="sequences-serial">
<h2>Sequences/SERIAL<a class="headerlink" href="#sequences-serial" title="Permalink to this headline">¶</a></h2>
<p>PostgreSQL supports sequences, and SQLAlchemy uses these as the default means
of creating new primary key values for integer-based primary key columns. When
creating tables, SQLAlchemy will issue the <tt class="docutils literal"><span class="pre">SERIAL</span></tt> datatype for
integer-based primary key columns, which generates a sequence and server side
default corresponding to the column.</p>
<p>To specify a specific named sequence to be used for primary key generation,
use the <a class="reference internal" href="../core/defaults.html#sqlalchemy.schema.Sequence" title="sqlalchemy.schema.Sequence"><tt class="xref py py-func docutils literal"><span class="pre">Sequence()</span></tt></a> construct:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">Table</span><span class="p">(</span><span class="s">'sometable'</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">Sequence</span><span class="p">(</span><span class="s">'some_id_seq'</span><span class="p">),</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
<span class="p">)</span></pre></div>
</div>
<p>When SQLAlchemy issues a single INSERT statement, to fulfill the contract of
having the “last insert identifier” available, a RETURNING clause is added to
the INSERT statement which specifies the primary key columns should be
returned after the statement completes. The RETURNING functionality only takes
place if Postgresql 8.2 or later is in use. As a fallback approach, the
sequence, whether specified explicitly or implicitly via <tt class="docutils literal"><span class="pre">SERIAL</span></tt>, is
executed independently beforehand, the returned value to be used in the
subsequent insert. Note that when an
<a class="reference internal" href="../core/dml.html#sqlalchemy.sql.expression.insert" title="sqlalchemy.sql.expression.insert"><tt class="xref py py-func docutils literal"><span class="pre">insert()</span></tt></a> construct is executed using
“executemany” semantics, the “last inserted identifier” functionality does not
apply; no RETURNING clause is emitted nor is the sequence pre-executed in this
case.</p>
<p>To force the usage of RETURNING by default off, specify the flag
<tt class="docutils literal"><span class="pre">implicit_returning=False</span></tt> to <a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><tt class="xref py py-func docutils literal"><span class="pre">create_engine()</span></tt></a>.</p>
</div>
<div class="section" id="transaction-isolation-level">
<span id="postgresql-isolation-level"></span><h2>Transaction Isolation Level<a class="headerlink" href="#transaction-isolation-level" title="Permalink to this headline">¶</a></h2>
<p>All Postgresql dialects support setting of transaction isolation level
both via a dialect-specific parameter <tt class="docutils literal"><span class="pre">isolation_level</span></tt>
accepted by <a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><tt class="xref py py-func docutils literal"><span class="pre">create_engine()</span></tt></a>,
as well as the <tt class="docutils literal"><span class="pre">isolation_level</span></tt> argument as passed to
<a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Connection.execution_options" title="sqlalchemy.engine.Connection.execution_options"><tt class="xref py py-meth docutils literal"><span class="pre">Connection.execution_options()</span></tt></a>. When using a non-psycopg2 dialect,
this feature works by issuing the command
<tt class="docutils literal"><span class="pre">SET</span> <span class="pre">SESSION</span> <span class="pre">CHARACTERISTICS</span> <span class="pre">AS</span> <span class="pre">TRANSACTION</span> <span class="pre">ISOLATION</span> <span class="pre">LEVEL</span> <span class="pre"><level></span></tt> for
each new connection.</p>
<p>To set isolation level using <a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><tt class="xref py py-func docutils literal"><span class="pre">create_engine()</span></tt></a>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span>
<span class="s">"postgresql+pg8000://scott:tiger@localhost/test"</span><span class="p">,</span>
<span class="n">isolation_level</span><span class="o">=</span><span class="s">"READ UNCOMMITTED"</span>
<span class="p">)</span></pre></div>
</div>
<p>To set using per-connection execution options:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">connection</span> <span class="o">=</span> <span class="n">engine</span><span class="o">.</span><span class="n">connect</span><span class="p">()</span>
<span class="n">connection</span> <span class="o">=</span> <span class="n">connection</span><span class="o">.</span><span class="n">execution_options</span><span class="p">(</span>
<span class="n">isolation_level</span><span class="o">=</span><span class="s">"READ COMMITTED"</span>
<span class="p">)</span></pre></div>
</div>
<p>Valid values for <tt class="docutils literal"><span class="pre">isolation_level</span></tt> include:</p>
<ul class="simple">
<li><tt class="docutils literal"><span class="pre">READ</span> <span class="pre">COMMITTED</span></tt></li>
<li><tt class="docutils literal"><span class="pre">READ</span> <span class="pre">UNCOMMITTED</span></tt></li>
<li><tt class="docutils literal"><span class="pre">REPEATABLE</span> <span class="pre">READ</span></tt></li>
<li><tt class="docutils literal"><span class="pre">SERIALIZABLE</span></tt></li>
</ul>
<p>The <a class="reference internal" href="#module-sqlalchemy.dialects.postgresql.psycopg2" title="sqlalchemy.dialects.postgresql.psycopg2"><tt class="xref py py-mod docutils literal"><span class="pre">psycopg2</span></tt></a> and
<a class="reference internal" href="#module-sqlalchemy.dialects.postgresql.pg8000" title="sqlalchemy.dialects.postgresql.pg8000"><tt class="xref py py-mod docutils literal"><span class="pre">pg8000</span></tt></a> dialects also offer the
special level <tt class="docutils literal"><span class="pre">AUTOCOMMIT</span></tt>.</p>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p><a class="reference internal" href="#psycopg2-isolation-level"><em>Psycopg2 Transaction Isolation Level</em></a></p>
<p class="last"><a class="reference internal" href="#pg8000-isolation-level"><em>pg8000 Transaction Isolation Level</em></a></p>
</div>
</div>
<div class="section" id="remote-schema-table-introspection-and-postgresql-search-path">
<span id="postgresql-schema-reflection"></span><h2>Remote-Schema Table Introspection and Postgresql search_path<a class="headerlink" href="#remote-schema-table-introspection-and-postgresql-search-path" title="Permalink to this headline">¶</a></h2>
<p>The Postgresql dialect can reflect tables from any schema. The
<a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Table.params.schema" title="sqlalchemy.schema.Table"><tt class="xref py py-paramref docutils literal"><span class="pre">Table.schema</span></tt></a> argument, or alternatively the
<a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.MetaData.reflect.params.schema" title="sqlalchemy.schema.MetaData.reflect"><tt class="xref py py-paramref docutils literal"><span class="pre">MetaData.reflect.schema</span></tt></a> argument determines which schema will
be searched for the table or tables. The reflected <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a> objects
will in all cases retain this <tt class="docutils literal"><span class="pre">.schema</span></tt> attribute as was specified.
However, with regards to tables which these <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a> objects refer to
via foreign key constraint, a decision must be made as to how the <tt class="docutils literal"><span class="pre">.schema</span></tt>
is represented in those remote tables, in the case where that remote
schema name is also a member of the current
<a class="reference external" href="http://www.postgresql.org/docs/9.0/static/ddl-schemas.html#DDL-SCHEMAS-PATH">Postgresql search path</a>.</p>
<p>By default, the Postgresql dialect mimics the behavior encouraged by
Postgresql’s own <tt class="docutils literal"><span class="pre">pg_get_constraintdef()</span></tt> builtin procedure. This function
returns a sample definition for a particular foreign key constraint,
omitting the referenced schema name from that definition when the name is
also in the Postgresql schema search path. The interaction below
illustrates this behavior:</p>
<div class="highlight-python"><pre>test=> CREATE TABLE test_schema.referred(id INTEGER PRIMARY KEY);
CREATE TABLE
test=> CREATE TABLE referring(
test(> id INTEGER PRIMARY KEY,
test(> referred_id INTEGER REFERENCES test_schema.referred(id));
CREATE TABLE
test=> SET search_path TO public, test_schema;
test=> SELECT pg_catalog.pg_get_constraintdef(r.oid, true) FROM
test-> pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n
test-> ON n.oid = c.relnamespace
test-> JOIN pg_catalog.pg_constraint r ON c.oid = r.conrelid
test-> WHERE c.relname='referring' AND r.contype = 'f'
test-> ;
pg_get_constraintdef
---------------------------------------------------
FOREIGN KEY (referred_id) REFERENCES referred(id)
(1 row)</pre>
</div>
<p>Above, we created a table <tt class="docutils literal"><span class="pre">referred</span></tt> as a member of the remote schema
<tt class="docutils literal"><span class="pre">test_schema</span></tt>, however when we added <tt class="docutils literal"><span class="pre">test_schema</span></tt> to the
PG <tt class="docutils literal"><span class="pre">search_path</span></tt> and then asked <tt class="docutils literal"><span class="pre">pg_get_constraintdef()</span></tt> for the
<tt class="docutils literal"><span class="pre">FOREIGN</span> <span class="pre">KEY</span></tt> syntax, <tt class="docutils literal"><span class="pre">test_schema</span></tt> was not included in the output of
the function.</p>
<p>On the other hand, if we set the search path back to the typical default
of <tt class="docutils literal"><span class="pre">public</span></tt>:</p>
<div class="highlight-python"><pre>test=> SET search_path TO public;
SET</pre>
</div>
<p>The same query against <tt class="docutils literal"><span class="pre">pg_get_constraintdef()</span></tt> now returns the fully
schema-qualified name for us:</p>
<div class="highlight-python"><pre>test=> SELECT pg_catalog.pg_get_constraintdef(r.oid, true) FROM
test-> pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n
test-> ON n.oid = c.relnamespace
test-> JOIN pg_catalog.pg_constraint r ON c.oid = r.conrelid
test-> WHERE c.relname='referring' AND r.contype = 'f';
pg_get_constraintdef
---------------------------------------------------------------
FOREIGN KEY (referred_id) REFERENCES test_schema.referred(id)
(1 row)</pre>
</div>
<p>SQLAlchemy will by default use the return value of <tt class="docutils literal"><span class="pre">pg_get_constraintdef()</span></tt>
in order to determine the remote schema name. That is, if our <tt class="docutils literal"><span class="pre">search_path</span></tt>
were set to include <tt class="docutils literal"><span class="pre">test_schema</span></tt>, and we invoked a table
reflection process as follows:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">Table</span><span class="p">,</span> <span class="n">MetaData</span><span class="p">,</span> <span class="n">create_engine</span>
<span class="gp">>>> </span><span class="n">engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">"postgresql://scott:tiger@localhost/test"</span><span class="p">)</span>
<span class="gp">>>> </span><span class="k">with</span> <span class="n">engine</span><span class="o">.</span><span class="n">connect</span><span class="p">()</span> <span class="k">as</span> <span class="n">conn</span><span class="p">:</span>
<span class="gp">... </span> <span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s">"SET search_path TO test_schema, public"</span><span class="p">)</span>
<span class="gp">... </span> <span class="n">meta</span> <span class="o">=</span> <span class="n">MetaData</span><span class="p">()</span>
<span class="gp">... </span> <span class="n">referring</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'referring'</span><span class="p">,</span> <span class="n">meta</span><span class="p">,</span>
<span class="gp">... </span> <span class="n">autoload</span><span class="o">=</span><span class="bp">True</span><span class="p">,</span> <span class="n">autoload_with</span><span class="o">=</span><span class="n">conn</span><span class="p">)</span>
<span class="gp">...</span>
<span class="go"><sqlalchemy.engine.result.ResultProxy object at 0x101612ed0></span></pre></div>
</div>
<p>The above process would deliver to the <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.MetaData.tables" title="sqlalchemy.schema.MetaData.tables"><tt class="xref py py-attr docutils literal"><span class="pre">MetaData.tables</span></tt></a> collection
<tt class="docutils literal"><span class="pre">referred</span></tt> table named <strong>without</strong> the schema:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">meta</span><span class="o">.</span><span class="n">tables</span><span class="p">[</span><span class="s">'referred'</span><span class="p">]</span><span class="o">.</span><span class="n">schema</span> <span class="ow">is</span> <span class="bp">None</span>
<span class="go">True</span></pre></div>
</div>
<p>To alter the behavior of reflection such that the referred schema is
maintained regardless of the <tt class="docutils literal"><span class="pre">search_path</span></tt> setting, use the
<tt class="docutils literal"><span class="pre">postgresql_ignore_search_path</span></tt> option, which can be specified as a
dialect-specific argument to both <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a> as well as
<a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.MetaData.reflect" title="sqlalchemy.schema.MetaData.reflect"><tt class="xref py py-meth docutils literal"><span class="pre">MetaData.reflect()</span></tt></a>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="k">with</span> <span class="n">engine</span><span class="o">.</span><span class="n">connect</span><span class="p">()</span> <span class="k">as</span> <span class="n">conn</span><span class="p">:</span>
<span class="gp">... </span> <span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s">"SET search_path TO test_schema, public"</span><span class="p">)</span>
<span class="gp">... </span> <span class="n">meta</span> <span class="o">=</span> <span class="n">MetaData</span><span class="p">()</span>
<span class="gp">... </span> <span class="n">referring</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'referring'</span><span class="p">,</span> <span class="n">meta</span><span class="p">,</span> <span class="n">autoload</span><span class="o">=</span><span class="bp">True</span><span class="p">,</span>
<span class="gp">... </span> <span class="n">autoload_with</span><span class="o">=</span><span class="n">conn</span><span class="p">,</span>
<span class="gp">... </span> <span class="n">postgresql_ignore_search_path</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
<span class="gp">...</span>
<span class="go"><sqlalchemy.engine.result.ResultProxy object at 0x1016126d0></span></pre></div>
</div>
<p>We will now have <tt class="docutils literal"><span class="pre">test_schema.referred</span></tt> stored as schema-qualified:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">meta</span><span class="o">.</span><span class="n">tables</span><span class="p">[</span><span class="s">'test_schema.referred'</span><span class="p">]</span><span class="o">.</span><span class="n">schema</span>
<span class="go">'test_schema'</span></pre></div>
</div>
<div class="sidebar">
<p class="first sidebar-title">Best Practices for Postgresql Schema reflection</p>
<p class="last">The description of Postgresql schema reflection behavior is complex, and
is the product of many years of dealing with widely varied use cases and
user preferences. But in fact, there’s no need to understand any of it if
you just stick to the simplest use pattern: leave the <tt class="docutils literal"><span class="pre">search_path</span></tt> set
to its default of <tt class="docutils literal"><span class="pre">public</span></tt> only, never refer to the name <tt class="docutils literal"><span class="pre">public</span></tt> as
an explicit schema name otherwise, and refer to all other schema names
explicitly when building up a <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a> object. The options
described here are only for those users who can’t, or prefer not to, stay
within these guidelines.</p>
</div>
<p>Note that <strong>in all cases</strong>, the “default” schema is always reflected as
<tt class="docutils literal"><span class="pre">None</span></tt>. The “default” schema on Postgresql is that which is returned by the
Postgresql <tt class="docutils literal"><span class="pre">current_schema()</span></tt> function. On a typical Postgresql
installation, this is the name <tt class="docutils literal"><span class="pre">public</span></tt>. So a table that refers to another
which is in the <tt class="docutils literal"><span class="pre">public</span></tt> (i.e. default) schema will always have the
<tt class="docutils literal"><span class="pre">.schema</span></tt> attribute set to <tt class="docutils literal"><span class="pre">None</span></tt>.</p>
<div class="versionadded">
<p><span>New in version 0.9.2: </span>Added the <tt class="docutils literal"><span class="pre">postgresql_ignore_search_path</span></tt>
dialect-level option accepted by <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a> and
<a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.MetaData.reflect" title="sqlalchemy.schema.MetaData.reflect"><tt class="xref py py-meth docutils literal"><span class="pre">MetaData.reflect()</span></tt></a>.</p>
</div>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p class="last"><a class="reference external" href="http://www.postgresql.org/docs/9.0/static/ddl-schemas.html#DDL-SCHEMAS-PATH">The Schema Search Path</a>
- on the Postgresql website.</p>
</div>
</div>
<div class="section" id="insert-update-returning">
<h2>INSERT/UPDATE...RETURNING<a class="headerlink" href="#insert-update-returning" title="Permalink to this headline">¶</a></h2>
<p>The dialect supports PG 8.2’s <tt class="docutils literal"><span class="pre">INSERT..RETURNING</span></tt>, <tt class="docutils literal"><span class="pre">UPDATE..RETURNING</span></tt> and
<tt class="docutils literal"><span class="pre">DELETE..RETURNING</span></tt> syntaxes. <tt class="docutils literal"><span class="pre">INSERT..RETURNING</span></tt> is used by default
for single-row INSERT statements in order to fetch newly generated
primary key identifiers. To specify an explicit <tt class="docutils literal"><span class="pre">RETURNING</span></tt> clause,
use the <tt class="xref py py-meth docutils literal"><span class="pre">_UpdateBase.returning()</span></tt> method on a per-statement basis:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="c"># INSERT..RETURNING</span>
<span class="n">result</span> <span class="o">=</span> <span class="n">table</span><span class="o">.</span><span class="n">insert</span><span class="p">()</span><span class="o">.</span><span class="n">returning</span><span class="p">(</span><span class="n">table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">col1</span><span class="p">,</span> <span class="n">table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">col2</span><span class="p">)</span><span class="o">.</span>\
<span class="n">values</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">'foo'</span><span class="p">)</span>
<span class="k">print</span> <span class="n">result</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>
<span class="c"># UPDATE..RETURNING</span>
<span class="n">result</span> <span class="o">=</span> <span class="n">table</span><span class="o">.</span><span class="n">update</span><span class="p">()</span><span class="o">.</span><span class="n">returning</span><span class="p">(</span><span class="n">table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">col1</span><span class="p">,</span> <span class="n">table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">col2</span><span class="p">)</span><span class="o">.</span>\
<span class="n">where</span><span class="p">(</span><span class="n">table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span><span class="o">==</span><span class="s">'foo'</span><span class="p">)</span><span class="o">.</span><span class="n">values</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">'bar'</span><span class="p">)</span>
<span class="k">print</span> <span class="n">result</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>
<span class="c"># DELETE..RETURNING</span>
<span class="n">result</span> <span class="o">=</span> <span class="n">table</span><span class="o">.</span><span class="n">delete</span><span class="p">()</span><span class="o">.</span><span class="n">returning</span><span class="p">(</span><span class="n">table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">col1</span><span class="p">,</span> <span class="n">table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">col2</span><span class="p">)</span><span class="o">.</span>\
<span class="n">where</span><span class="p">(</span><span class="n">table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span><span class="o">==</span><span class="s">'foo'</span><span class="p">)</span>
<span class="k">print</span> <span class="n">result</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span></pre></div>
</div>
</div>
<div class="section" id="full-text-search">
<span id="postgresql-match"></span><h2>Full Text Search<a class="headerlink" href="#full-text-search" title="Permalink to this headline">¶</a></h2>
<p>SQLAlchemy makes available the Postgresql <tt class="docutils literal"><span class="pre">@@</span></tt> operator via the
<a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.expression.ColumnElement.match" title="sqlalchemy.sql.expression.ColumnElement.match"><tt class="xref py py-meth docutils literal"><span class="pre">ColumnElement.match()</span></tt></a> method on any textual column expression.
On a Postgresql dialect, an expression like the following:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">select</span><span class="p">([</span><span class="n">sometable</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">text</span><span class="o">.</span><span class="n">match</span><span class="p">(</span><span class="s">"search string"</span><span class="p">)])</span></pre></div>
</div>
<p>will emit to the database:</p>
<div class="highlight-python"><pre>SELECT text @@ to_tsquery('search string') FROM table</pre>
</div>
<p>The Postgresql text search functions such as <tt class="docutils literal"><span class="pre">to_tsquery()</span></tt>
and <tt class="docutils literal"><span class="pre">to_tsvector()</span></tt> are available
explicitly using the standard <tt class="xref py py-attr docutils literal"><span class="pre">func</span></tt> construct. For example:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">select</span><span class="p">([</span>
<span class="n">func</span><span class="o">.</span><span class="n">to_tsvector</span><span class="p">(</span><span class="s">'fat cats ate rats'</span><span class="p">)</span><span class="o">.</span><span class="n">match</span><span class="p">(</span><span class="s">'cat & rat'</span><span class="p">)</span>
<span class="p">])</span></pre></div>
</div>
<p>Emits the equivalent of:</p>
<div class="highlight-python"><pre>SELECT to_tsvector('fat cats ate rats') @@ to_tsquery('cat & rat')</pre>
</div>
<p>The <a class="reference internal" href="#sqlalchemy.dialects.postgresql.TSVECTOR" title="sqlalchemy.dialects.postgresql.TSVECTOR"><tt class="xref py py-class docutils literal"><span class="pre">postgresql.TSVECTOR</span></tt></a> type can provide for explicit CAST:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy.dialects.postgresql</span> <span class="kn">import</span> <span class="n">TSVECTOR</span>
<span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">select</span><span class="p">,</span> <span class="n">cast</span>
<span class="n">select</span><span class="p">([</span><span class="n">cast</span><span class="p">(</span><span class="s">"some text"</span><span class="p">,</span> <span class="n">TSVECTOR</span><span class="p">)])</span></pre></div>
</div>
<p>produces a statement equivalent to:</p>
<div class="highlight-python"><pre>SELECT CAST('some text' AS TSVECTOR) AS anon_1</pre>
</div>
<p>Full Text Searches in Postgresql are influenced by a combination of: the
PostgresSQL setting of <tt class="docutils literal"><span class="pre">default_text_search_config</span></tt>, the <tt class="docutils literal"><span class="pre">regconfig</span></tt> used
to build the GIN/GiST indexes, and the <tt class="docutils literal"><span class="pre">regconfig</span></tt> optionally passed in
during a query.</p>
<p>When performing a Full Text Search against a column that has a GIN or
GiST index that is already pre-computed (which is common on full text
searches) one may need to explicitly pass in a particular PostgresSQL
<tt class="docutils literal"><span class="pre">regconfig</span></tt> value to ensure the query-planner utilizes the index and does
not re-compute the column on demand.</p>
<p>In order to provide for this explicit query planning, or to use different
search strategies, the <tt class="docutils literal"><span class="pre">match</span></tt> method accepts a <tt class="docutils literal"><span class="pre">postgresql_regconfig</span></tt>
keyword argument.</p>
<blockquote>
<div><dl class="docutils">
<dt>select([mytable.c.id]).where(</dt>
<dd>mytable.c.title.match(‘somestring’, postgresql_regconfig=’english’)</dd>
</dl>
<p>)</p>
</div></blockquote>
<p>Emits the equivalent of:</p>
<div class="highlight-python"><pre>SELECT mytable.id FROM mytable
WHERE mytable.title @@ to_tsquery('english', 'somestring')</pre>
</div>
<p>One can also specifically pass in a <cite>‘regconfig’</cite> value to the
<tt class="docutils literal"><span class="pre">to_tsvector()</span></tt> command as the initial argument.</p>
<blockquote>
<div><dl class="docutils">
<dt>select([mytable.c.id]).where(</dt>
<dd><blockquote class="first">
<div>func.to_tsvector(‘english’, mytable.c.title ) .match(‘somestring’, postgresql_regconfig=’english’)</div></blockquote>
<p class="last">)</p>
</dd>
</dl>
</div></blockquote>
<p>produces a statement equivalent to:</p>
<div class="highlight-python"><pre>SELECT mytable.id FROM mytable
WHERE to_tsvector('english', mytable.title) @@
to_tsquery('english', 'somestring')</pre>
</div>
<p>It is recommended that you use the <tt class="docutils literal"><span class="pre">EXPLAIN</span> <span class="pre">ANALYZE...</span></tt> tool from
PostgresSQL to ensure that you are generating queries with SQLAlchemy that
take full advantage of any indexes you may have created for full text search.</p>
</div>
<div class="section" id="from-only">
<h2>FROM ONLY ...<a class="headerlink" href="#from-only" title="Permalink to this headline">¶</a></h2>
<p>The dialect supports PostgreSQL’s ONLY keyword for targeting only a particular
table in an inheritance hierarchy. This can be used to produce the
<tt class="docutils literal"><span class="pre">SELECT</span> <span class="pre">...</span> <span class="pre">FROM</span> <span class="pre">ONLY</span></tt>, <tt class="docutils literal"><span class="pre">UPDATE</span> <span class="pre">ONLY</span> <span class="pre">...</span></tt>, and <tt class="docutils literal"><span class="pre">DELETE</span> <span class="pre">FROM</span> <span class="pre">ONLY</span> <span class="pre">...</span></tt>
syntaxes. It uses SQLAlchemy’s hints mechanism:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="c"># SELECT ... FROM ONLY ...</span>
<span class="n">result</span> <span class="o">=</span> <span class="n">table</span><span class="o">.</span><span class="n">select</span><span class="p">()</span><span class="o">.</span><span class="n">with_hint</span><span class="p">(</span><span class="n">table</span><span class="p">,</span> <span class="s">'ONLY'</span><span class="p">,</span> <span class="s">'postgresql'</span><span class="p">)</span>
<span class="k">print</span> <span class="n">result</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>
<span class="c"># UPDATE ONLY ...</span>
<span class="n">table</span><span class="o">.</span><span class="n">update</span><span class="p">(</span><span class="n">values</span><span class="o">=</span><span class="nb">dict</span><span class="p">(</span><span class="n">foo</span><span class="o">=</span><span class="s">'bar'</span><span class="p">))</span><span class="o">.</span><span class="n">with_hint</span><span class="p">(</span><span class="s">'ONLY'</span><span class="p">,</span>
<span class="n">dialect_name</span><span class="o">=</span><span class="s">'postgresql'</span><span class="p">)</span>
<span class="c"># DELETE FROM ONLY ...</span>
<span class="n">table</span><span class="o">.</span><span class="n">delete</span><span class="p">()</span><span class="o">.</span><span class="n">with_hint</span><span class="p">(</span><span class="s">'ONLY'</span><span class="p">,</span> <span class="n">dialect_name</span><span class="o">=</span><span class="s">'postgresql'</span><span class="p">)</span></pre></div>
</div>
</div>
<div class="section" id="postgresql-specific-index-options">
<span id="postgresql-indexes"></span><h2>Postgresql-Specific Index Options<a class="headerlink" href="#postgresql-specific-index-options" title="Permalink to this headline">¶</a></h2>
<p>Several extensions to the <a class="reference internal" href="../core/constraints.html#sqlalchemy.schema.Index" title="sqlalchemy.schema.Index"><tt class="xref py py-class docutils literal"><span class="pre">Index</span></tt></a> construct are available, specific
to the PostgreSQL dialect.</p>
<div class="section" id="partial-indexes">
<h3>Partial Indexes<a class="headerlink" href="#partial-indexes" title="Permalink to this headline">¶</a></h3>
<p>Partial indexes add criterion to the index definition so that the index is
applied to a subset of rows. These can be specified on <a class="reference internal" href="../core/constraints.html#sqlalchemy.schema.Index" title="sqlalchemy.schema.Index"><tt class="xref py py-class docutils literal"><span class="pre">Index</span></tt></a>
using the <tt class="docutils literal"><span class="pre">postgresql_where</span></tt> keyword argument:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">Index</span><span class="p">(</span><span class="s">'my_index'</span><span class="p">,</span> <span class="n">my_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">,</span> <span class="n">postgresql_where</span><span class="o">=</span><span class="n">tbl</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">value</span> <span class="o">></span> <span class="mi">10</span><span class="p">)</span></pre></div>
</div>
</div>
<div class="section" id="operator-classes">
<h3>Operator Classes<a class="headerlink" href="#operator-classes" title="Permalink to this headline">¶</a></h3>
<p>PostgreSQL allows the specification of an <em>operator class</em> for each column of
an index (see
<a class="reference external" href="http://www.postgresql.org/docs/8.3/interactive/indexes-opclass.html">http://www.postgresql.org/docs/8.3/interactive/indexes-opclass.html</a>).
The <a class="reference internal" href="../core/constraints.html#sqlalchemy.schema.Index" title="sqlalchemy.schema.Index"><tt class="xref py py-class docutils literal"><span class="pre">Index</span></tt></a> construct allows these to be specified via the
<tt class="docutils literal"><span class="pre">postgresql_ops</span></tt> keyword argument:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">Index</span><span class="p">(</span><span class="s">'my_index'</span><span class="p">,</span> <span class="n">my_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">,</span> <span class="n">my_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data</span><span class="p">,</span>
<span class="n">postgresql_ops</span><span class="o">=</span><span class="p">{</span>
<span class="s">'data'</span><span class="p">:</span> <span class="s">'text_pattern_ops'</span><span class="p">,</span>
<span class="s">'id'</span><span class="p">:</span> <span class="s">'int4_ops'</span>
<span class="p">})</span></pre></div>
</div>
<div class="versionadded">
<p><span>New in version 0.7.2: </span><tt class="docutils literal"><span class="pre">postgresql_ops</span></tt> keyword argument to <a class="reference internal" href="../core/constraints.html#sqlalchemy.schema.Index" title="sqlalchemy.schema.Index"><tt class="xref py py-class docutils literal"><span class="pre">Index</span></tt></a> construct.</p>
</div>
<p>Note that the keys in the <tt class="docutils literal"><span class="pre">postgresql_ops</span></tt> dictionary are the “key” name of
the <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal"><span class="pre">Column</span></tt></a>, i.e. the name used to access it from the <tt class="docutils literal"><span class="pre">.c</span></tt>
collection of <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a>, which can be configured to be different than
the actual name of the column as expressed in the database.</p>
</div>
<div class="section" id="index-types">
<h3>Index Types<a class="headerlink" href="#index-types" title="Permalink to this headline">¶</a></h3>
<p>PostgreSQL provides several index types: B-Tree, Hash, GiST, and GIN, as well
as the ability for users to create their own (see
<a class="reference external" href="http://www.postgresql.org/docs/8.3/static/indexes-types.html">http://www.postgresql.org/docs/8.3/static/indexes-types.html</a>). These can be
specified on <a class="reference internal" href="../core/constraints.html#sqlalchemy.schema.Index" title="sqlalchemy.schema.Index"><tt class="xref py py-class docutils literal"><span class="pre">Index</span></tt></a> using the <tt class="docutils literal"><span class="pre">postgresql_using</span></tt> keyword argument:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">Index</span><span class="p">(</span><span class="s">'my_index'</span><span class="p">,</span> <span class="n">my_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data</span><span class="p">,</span> <span class="n">postgresql_using</span><span class="o">=</span><span class="s">'gin'</span><span class="p">)</span></pre></div>
</div>
<p>The value passed to the keyword argument will be simply passed through to the
underlying CREATE INDEX command, so it <em>must</em> be a valid index type for your
version of PostgreSQL.</p>
</div>
</div>
<div class="section" id="postgresql-data-types">
<h2>PostgreSQL Data Types<a class="headerlink" href="#postgresql-data-types" title="Permalink to this headline">¶</a></h2>
<p>As with all SQLAlchemy dialects, all UPPERCASE types that are known to be
valid with Postgresql are importable from the top level dialect, whether
they originate from <a class="reference internal" href="../core/types.html#module-sqlalchemy.types" title="sqlalchemy.types"><tt class="xref py py-mod docutils literal"><span class="pre">sqlalchemy.types</span></tt></a> or from the local dialect:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy.dialects.postgresql</span> <span class="kn">import</span> \
<span class="n">ARRAY</span><span class="p">,</span> <span class="n">BIGINT</span><span class="p">,</span> <span class="n">BIT</span><span class="p">,</span> <span class="n">BOOLEAN</span><span class="p">,</span> <span class="n">BYTEA</span><span class="p">,</span> <span class="n">CHAR</span><span class="p">,</span> <span class="n">CIDR</span><span class="p">,</span> <span class="n">DATE</span><span class="p">,</span> \
<span class="n">DOUBLE_PRECISION</span><span class="p">,</span> <span class="n">ENUM</span><span class="p">,</span> <span class="n">FLOAT</span><span class="p">,</span> <span class="n">HSTORE</span><span class="p">,</span> <span class="n">INET</span><span class="p">,</span> <span class="n">INTEGER</span><span class="p">,</span> \
<span class="n">INTERVAL</span><span class="p">,</span> <span class="n">JSON</span><span class="p">,</span> <span class="n">JSONB</span><span class="p">,</span> <span class="n">MACADDR</span><span class="p">,</span> <span class="n">NUMERIC</span><span class="p">,</span> <span class="n">OID</span><span class="p">,</span> <span class="n">REAL</span><span class="p">,</span> <span class="n">SMALLINT</span><span class="p">,</span> <span class="n">TEXT</span><span class="p">,</span> \
<span class="n">TIME</span><span class="p">,</span> <span class="n">TIMESTAMP</span><span class="p">,</span> <span class="n">UUID</span><span class="p">,</span> <span class="n">VARCHAR</span><span class="p">,</span> <span class="n">INT4RANGE</span><span class="p">,</span> <span class="n">INT8RANGE</span><span class="p">,</span> <span class="n">NUMRANGE</span><span class="p">,</span> \
<span class="n">DATERANGE</span><span class="p">,</span> <span class="n">TSRANGE</span><span class="p">,</span> <span class="n">TSTZRANGE</span><span class="p">,</span> <span class="n">TSVECTOR</span></pre></div>
</div>
<p>Types which are specific to PostgreSQL, or have PostgreSQL-specific
construction arguments, are as follows:</p>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.array">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.postgresql.</tt><tt class="descname">array</tt><big>(</big><em>clauses</em>, <em>**kw</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.array" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.expression.Tuple" title="sqlalchemy.sql.expression.Tuple"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.sql.expression.Tuple</span></tt></a></p>
<p>A Postgresql ARRAY literal.</p>
<p>This is used to produce ARRAY literals in SQL expressions, e.g.:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy.dialects.postgresql</span> <span class="kn">import</span> <span class="n">array</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.dialects</span> <span class="kn">import</span> <span class="n">postgresql</span>
<span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">select</span><span class="p">,</span> <span class="n">func</span>
<span class="n">stmt</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span>
<span class="n">array</span><span class="p">([</span><span class="mi">1</span><span class="p">,</span><span class="mi">2</span><span class="p">])</span> <span class="o">+</span> <span class="n">array</span><span class="p">([</span><span class="mi">3</span><span class="p">,</span><span class="mi">4</span><span class="p">,</span><span class="mi">5</span><span class="p">])</span>
<span class="p">])</span>
<span class="k">print</span> <span class="n">stmt</span><span class="o">.</span><span class="n">compile</span><span class="p">(</span><span class="n">dialect</span><span class="o">=</span><span class="n">postgresql</span><span class="o">.</span><span class="n">dialect</span><span class="p">())</span></pre></div>
</div>
<p>Produces the SQL:</p>
<div class="highlight-python"><pre>SELECT ARRAY[%(param_1)s, %(param_2)s] ||
ARRAY[%(param_3)s, %(param_4)s, %(param_5)s]) AS anon_1</pre>
</div>
<p>An instance of <a class="reference internal" href="#sqlalchemy.dialects.postgresql.array" title="sqlalchemy.dialects.postgresql.array"><tt class="xref py py-class docutils literal"><span class="pre">array</span></tt></a> will always have the datatype
<a class="reference internal" href="#sqlalchemy.dialects.postgresql.ARRAY" title="sqlalchemy.dialects.postgresql.ARRAY"><tt class="xref py py-class docutils literal"><span class="pre">ARRAY</span></tt></a>. The “inner” type of the array is inferred from
the values present, unless the <tt class="docutils literal"><span class="pre">type_</span></tt> keyword argument is passed:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">array</span><span class="p">([</span><span class="s">'foo'</span><span class="p">,</span> <span class="s">'bar'</span><span class="p">],</span> <span class="n">type_</span><span class="o">=</span><span class="n">CHAR</span><span class="p">)</span></pre></div>
</div>
<div class="versionadded">
<p><span>New in version 0.8: </span>Added the <a class="reference internal" href="#sqlalchemy.dialects.postgresql.array" title="sqlalchemy.dialects.postgresql.array"><tt class="xref py py-class docutils literal"><span class="pre">array</span></tt></a> literal type.</p>
</div>
<p>See also:</p>
<p><a class="reference internal" href="#sqlalchemy.dialects.postgresql.ARRAY" title="sqlalchemy.dialects.postgresql.ARRAY"><tt class="xref py py-class docutils literal"><span class="pre">postgresql.ARRAY</span></tt></a></p>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.ARRAY">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.postgresql.</tt><tt class="descname">ARRAY</tt><big>(</big><em>item_type</em>, <em>as_tuple=False</em>, <em>dimensions=None</em>, <em>zero_indexes=False</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.ARRAY" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/types.html#sqlalchemy.types.Concatenable" title="sqlalchemy.types.Concatenable"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.Concatenable</span></tt></a>, <a class="reference internal" href="../core/types.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.TypeEngine</span></tt></a></p>
<p>Postgresql ARRAY type.</p>
<p>Represents values as Python lists.</p>
<p>An <a class="reference internal" href="#sqlalchemy.dialects.postgresql.ARRAY" title="sqlalchemy.dialects.postgresql.ARRAY"><tt class="xref py py-class docutils literal"><span class="pre">ARRAY</span></tt></a> type is constructed given the “type”
of element:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">mytable</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">"mytable"</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s">"data"</span><span class="p">,</span> <span class="n">ARRAY</span><span class="p">(</span><span class="n">Integer</span><span class="p">))</span>
<span class="p">)</span></pre></div>
</div>
<p>The above type represents an N-dimensional array,
meaning Postgresql will interpret values with any number
of dimensions automatically. To produce an INSERT
construct that passes in a 1-dimensional array of integers:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">connection</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span>
<span class="n">mytable</span><span class="o">.</span><span class="n">insert</span><span class="p">(),</span>
<span class="n">data</span><span class="o">=</span><span class="p">[</span><span class="mi">1</span><span class="p">,</span><span class="mi">2</span><span class="p">,</span><span class="mi">3</span><span class="p">]</span>
<span class="p">)</span></pre></div>
</div>
<p>The <a class="reference internal" href="#sqlalchemy.dialects.postgresql.ARRAY" title="sqlalchemy.dialects.postgresql.ARRAY"><tt class="xref py py-class docutils literal"><span class="pre">ARRAY</span></tt></a> type can be constructed given a fixed number
of dimensions:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">mytable</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">"mytable"</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s">"data"</span><span class="p">,</span> <span class="n">ARRAY</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">dimensions</span><span class="o">=</span><span class="mi">2</span><span class="p">))</span>
<span class="p">)</span></pre></div>
</div>
<p>This has the effect of the <a class="reference internal" href="#sqlalchemy.dialects.postgresql.ARRAY" title="sqlalchemy.dialects.postgresql.ARRAY"><tt class="xref py py-class docutils literal"><span class="pre">ARRAY</span></tt></a> type
specifying that number of bracketed blocks when a <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a>
is used in a CREATE TABLE statement, or when the type is used
within a <a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.expression.cast" title="sqlalchemy.sql.expression.cast"><tt class="xref py py-func docutils literal"><span class="pre">expression.cast()</span></tt></a> construct; it also causes
the bind parameter and result set processing of the type
to optimize itself to expect exactly that number of dimensions.
Note that Postgresql itself still allows N dimensions with such a type.</p>
<p>SQL expressions of type <a class="reference internal" href="#sqlalchemy.dialects.postgresql.ARRAY" title="sqlalchemy.dialects.postgresql.ARRAY"><tt class="xref py py-class docutils literal"><span class="pre">ARRAY</span></tt></a> have support for “index” and
“slice” behavior. The Python <tt class="docutils literal"><span class="pre">[]</span></tt> operator works normally here, given
integer indexes or slices. Note that Postgresql arrays default
to 1-based indexing. The operator produces binary expression
constructs which will produce the appropriate SQL, both for
SELECT statements:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">select</span><span class="p">([</span><span class="n">mytable</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data</span><span class="p">[</span><span class="mi">5</span><span class="p">],</span> <span class="n">mytable</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data</span><span class="p">[</span><span class="mi">2</span><span class="p">:</span><span class="mi">7</span><span class="p">]])</span></pre></div>
</div>
<p>as well as UPDATE statements when the <a class="reference internal" href="../core/dml.html#sqlalchemy.sql.expression.Update.values" title="sqlalchemy.sql.expression.Update.values"><tt class="xref py py-meth docutils literal"><span class="pre">Update.values()</span></tt></a> method
is used:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">mytable</span><span class="o">.</span><span class="n">update</span><span class="p">()</span><span class="o">.</span><span class="n">values</span><span class="p">({</span>
<span class="n">mytable</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data</span><span class="p">[</span><span class="mi">5</span><span class="p">]:</span> <span class="mi">7</span><span class="p">,</span>
<span class="n">mytable</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data</span><span class="p">[</span><span class="mi">2</span><span class="p">:</span><span class="mi">7</span><span class="p">]:</span> <span class="p">[</span><span class="mi">1</span><span class="p">,</span> <span class="mi">2</span><span class="p">,</span> <span class="mi">3</span><span class="p">]</span>
<span class="p">})</span></pre></div>
</div>
<p><a class="reference internal" href="#sqlalchemy.dialects.postgresql.ARRAY" title="sqlalchemy.dialects.postgresql.ARRAY"><tt class="xref py py-class docutils literal"><span class="pre">ARRAY</span></tt></a> provides special methods for containment operations,
e.g.:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">mytable</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data</span><span class="o">.</span><span class="n">contains</span><span class="p">([</span><span class="mi">1</span><span class="p">,</span> <span class="mi">2</span><span class="p">])</span></pre></div>
</div>
<p>For a full list of special methods see <a class="reference internal" href="#sqlalchemy.dialects.postgresql.ARRAY.Comparator" title="sqlalchemy.dialects.postgresql.ARRAY.Comparator"><tt class="xref py py-class docutils literal"><span class="pre">ARRAY.Comparator</span></tt></a>.</p>
<div class="versionadded">
<p><span>New in version 0.8: </span>Added support for index and slice operations
to the <a class="reference internal" href="#sqlalchemy.dialects.postgresql.ARRAY" title="sqlalchemy.dialects.postgresql.ARRAY"><tt class="xref py py-class docutils literal"><span class="pre">ARRAY</span></tt></a> type, including support for UPDATE
statements, and special array containment operations.</p>
</div>
<p>The <a class="reference internal" href="#sqlalchemy.dialects.postgresql.ARRAY" title="sqlalchemy.dialects.postgresql.ARRAY"><tt class="xref py py-class docutils literal"><span class="pre">ARRAY</span></tt></a> type may not be supported on all DBAPIs.
It is known to work on psycopg2 and not pg8000.</p>
<p>See also:</p>
<p><a class="reference internal" href="#sqlalchemy.dialects.postgresql.array" title="sqlalchemy.dialects.postgresql.array"><tt class="xref py py-class docutils literal"><span class="pre">postgresql.array</span></tt></a> - produce a literal array value.</p>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.ARRAY.Comparator">
<em class="property">class </em><tt class="descname">Comparator</tt><big>(</big><em>expr</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.ARRAY.Comparator" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.Comparator</span></tt></p>
<p>Define comparison operations for <a class="reference internal" href="#sqlalchemy.dialects.postgresql.ARRAY" title="sqlalchemy.dialects.postgresql.ARRAY"><tt class="xref py py-class docutils literal"><span class="pre">ARRAY</span></tt></a>.</p>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.ARRAY.Comparator.all">
<tt class="descname">all</tt><big>(</big><em>other</em>, <em>operator=<built-in function eq></em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.ARRAY.Comparator.all" title="Permalink to this definition">¶</a></dt>
<dd><p>Return <tt class="docutils literal"><span class="pre">other</span> <span class="pre">operator</span> <span class="pre">ALL</span> <span class="pre">(array)</span></tt> clause.</p>
<p>Argument places are switched, because ALL requires array
expression to be on the right hand-side.</p>
<p>E.g.:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy.sql</span> <span class="kn">import</span> <span class="n">operators</span>
<span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span>
<span class="n">select</span><span class="p">([</span><span class="n">table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data</span><span class="p">])</span><span class="o">.</span><span class="n">where</span><span class="p">(</span>
<span class="n">table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data</span><span class="o">.</span><span class="n">all</span><span class="p">(</span><span class="mi">7</span><span class="p">,</span> <span class="n">operator</span><span class="o">=</span><span class="n">operators</span><span class="o">.</span><span class="n">lt</span><span class="p">)</span>
<span class="p">)</span>
<span class="p">)</span></pre></div>
</div>
<table class="docutils field-list" frame="void" rules="none">
<col class="field-name" />
<col class="field-body" />
<tbody valign="top">
<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body"><ul class="first last simple">
<li><span class="target" id="sqlalchemy.dialects.postgresql.ARRAY.Comparator.all.params.other"></span><strong>other</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.postgresql.ARRAY.Comparator.all.params.other">¶</a> – expression to be compared</li>
<li><span class="target" id="sqlalchemy.dialects.postgresql.ARRAY.Comparator.all.params.operator"></span><strong>operator</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.postgresql.ARRAY.Comparator.all.params.operator">¶</a> – an operator object from the
<tt class="xref py py-mod docutils literal"><span class="pre">sqlalchemy.sql.operators</span></tt>
package, defaults to <tt class="xref py py-func docutils literal"><span class="pre">operators.eq()</span></tt>.</li>
</ul>
</td>
</tr>
</tbody>
</table>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p><a class="reference internal" href="#sqlalchemy.dialects.postgresql.All" title="sqlalchemy.dialects.postgresql.All"><tt class="xref py py-class docutils literal"><span class="pre">postgresql.All</span></tt></a></p>
<p class="last"><a class="reference internal" href="#sqlalchemy.dialects.postgresql.ARRAY.Comparator.any" title="sqlalchemy.dialects.postgresql.ARRAY.Comparator.any"><tt class="xref py py-meth docutils literal"><span class="pre">postgresql.ARRAY.Comparator.any()</span></tt></a></p>
</div>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.ARRAY.Comparator.any">
<tt class="descname">any</tt><big>(</big><em>other</em>, <em>operator=<built-in function eq></em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.ARRAY.Comparator.any" title="Permalink to this definition">¶</a></dt>
<dd><p>Return <tt class="docutils literal"><span class="pre">other</span> <span class="pre">operator</span> <span class="pre">ANY</span> <span class="pre">(array)</span></tt> clause.</p>
<p>Argument places are switched, because ANY requires array
expression to be on the right hand-side.</p>
<p>E.g.:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy.sql</span> <span class="kn">import</span> <span class="n">operators</span>
<span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span>
<span class="n">select</span><span class="p">([</span><span class="n">table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data</span><span class="p">])</span><span class="o">.</span><span class="n">where</span><span class="p">(</span>
<span class="n">table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data</span><span class="o">.</span><span class="n">any</span><span class="p">(</span><span class="mi">7</span><span class="p">,</span> <span class="n">operator</span><span class="o">=</span><span class="n">operators</span><span class="o">.</span><span class="n">lt</span><span class="p">)</span>
<span class="p">)</span>
<span class="p">)</span></pre></div>
</div>
<table class="docutils field-list" frame="void" rules="none">
<col class="field-name" />
<col class="field-body" />
<tbody valign="top">
<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body"><ul class="first last simple">
<li><span class="target" id="sqlalchemy.dialects.postgresql.ARRAY.Comparator.any.params.other"></span><strong>other</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.postgresql.ARRAY.Comparator.any.params.other">¶</a> – expression to be compared</li>
<li><span class="target" id="sqlalchemy.dialects.postgresql.ARRAY.Comparator.any.params.operator"></span><strong>operator</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.postgresql.ARRAY.Comparator.any.params.operator">¶</a> – an operator object from the
<tt class="xref py py-mod docutils literal"><span class="pre">sqlalchemy.sql.operators</span></tt>
package, defaults to <tt class="xref py py-func docutils literal"><span class="pre">operators.eq()</span></tt>.</li>
</ul>
</td>
</tr>
</tbody>
</table>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p><a class="reference internal" href="#sqlalchemy.dialects.postgresql.Any" title="sqlalchemy.dialects.postgresql.Any"><tt class="xref py py-class docutils literal"><span class="pre">postgresql.Any</span></tt></a></p>
<p class="last"><a class="reference internal" href="#sqlalchemy.dialects.postgresql.ARRAY.Comparator.all" title="sqlalchemy.dialects.postgresql.ARRAY.Comparator.all"><tt class="xref py py-meth docutils literal"><span class="pre">postgresql.ARRAY.Comparator.all()</span></tt></a></p>
</div>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.ARRAY.Comparator.contained_by">
<tt class="descname">contained_by</tt><big>(</big><em>other</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.ARRAY.Comparator.contained_by" title="Permalink to this definition">¶</a></dt>
<dd><p>Boolean expression. Test if elements are a proper subset of the
elements of the argument array expression.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.ARRAY.Comparator.contains">
<tt class="descname">contains</tt><big>(</big><em>other</em>, <em>**kwargs</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.ARRAY.Comparator.contains" title="Permalink to this definition">¶</a></dt>
<dd><p>Boolean expression. Test if elements are a superset of the
elements of the argument array expression.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.ARRAY.Comparator.overlap">
<tt class="descname">overlap</tt><big>(</big><em>other</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.ARRAY.Comparator.overlap" title="Permalink to this definition">¶</a></dt>
<dd><p>Boolean expression. Test if array has elements in common with
an argument array expression.</p>
</dd></dl>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.ARRAY.__init__">
<tt class="descclassname">ARRAY.</tt><tt class="descname">__init__</tt><big>(</big><em>item_type</em>, <em>as_tuple=False</em>, <em>dimensions=None</em>, <em>zero_indexes=False</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.ARRAY.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Construct an ARRAY.</p>
<p>E.g.:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">Column</span><span class="p">(</span><span class="s">'myarray'</span><span class="p">,</span> <span class="n">ARRAY</span><span class="p">(</span><span class="n">Integer</span><span class="p">))</span></pre></div>
</div>
<p>Arguments are:</p>
<table class="docutils field-list" frame="void" rules="none">
<col class="field-name" />
<col class="field-body" />
<tbody valign="top">
<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body"><ul class="first last simple">
<li><span class="target" id="sqlalchemy.dialects.postgresql.ARRAY.params.item_type"></span><strong>item_type</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.postgresql.ARRAY.params.item_type">¶</a> – The data type of items of this array. Note that
dimensionality is irrelevant here, so multi-dimensional arrays like
<tt class="docutils literal"><span class="pre">INTEGER[][]</span></tt>, are constructed as <tt class="docutils literal"><span class="pre">ARRAY(Integer)</span></tt>, not as
<tt class="docutils literal"><span class="pre">ARRAY(ARRAY(Integer))</span></tt> or such.</li>
<li><span class="target" id="sqlalchemy.dialects.postgresql.ARRAY.params.as_tuple"></span><strong>as_tuple=False</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.postgresql.ARRAY.params.as_tuple">¶</a> – Specify whether return results
should be converted to tuples from lists. DBAPIs such
as psycopg2 return lists by default. When tuples are
returned, the results are hashable.</li>
<li><span class="target" id="sqlalchemy.dialects.postgresql.ARRAY.params.dimensions"></span><strong>dimensions</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.postgresql.ARRAY.params.dimensions">¶</a> – if non-None, the ARRAY will assume a fixed
number of dimensions. This will cause the DDL emitted for this
ARRAY to include the exact number of bracket clauses <tt class="docutils literal"><span class="pre">[]</span></tt>,
and will also optimize the performance of the type overall.
Note that PG arrays are always implicitly “non-dimensioned”,
meaning they can store any number of dimensions no matter how
they were declared.</li>
<li><span class="target" id="sqlalchemy.dialects.postgresql.ARRAY.params.zero_indexes"></span><strong>zero_indexes=False</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.postgresql.ARRAY.params.zero_indexes">¶</a> – <p>when True, index values will be converted
between Python zero-based and Postgresql one-based indexes, e.g.
a value of one will be added to all index values before passing
to the database.</p>
<div class="versionadded">
<p><span>New in version 0.9.5.</span></p>
</div>
</li>
</ul>
</td>
</tr>
</tbody>
</table>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.Any">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.postgresql.</tt><tt class="descname">Any</tt><big>(</big><em>left</em>, <em>right</em>, <em>operator=<built-in function eq></em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.Any" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.expression.ColumnElement" title="sqlalchemy.sql.expression.ColumnElement"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.sql.expression.ColumnElement</span></tt></a></p>
<p>Represent the clause <tt class="docutils literal"><span class="pre">left</span> <span class="pre">operator</span> <span class="pre">ANY</span> <span class="pre">(right)</span></tt>. <tt class="docutils literal"><span class="pre">right</span></tt> must be
an array expression.</p>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p><a class="reference internal" href="#sqlalchemy.dialects.postgresql.ARRAY" title="sqlalchemy.dialects.postgresql.ARRAY"><tt class="xref py py-class docutils literal"><span class="pre">postgresql.ARRAY</span></tt></a></p>
<p class="last"><a class="reference internal" href="#sqlalchemy.dialects.postgresql.ARRAY.Comparator.any" title="sqlalchemy.dialects.postgresql.ARRAY.Comparator.any"><tt class="xref py py-meth docutils literal"><span class="pre">postgresql.ARRAY.Comparator.any()</span></tt></a> - ARRAY-bound method</p>
</div>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.All">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.postgresql.</tt><tt class="descname">All</tt><big>(</big><em>left</em>, <em>right</em>, <em>operator=<built-in function eq></em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.All" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.expression.ColumnElement" title="sqlalchemy.sql.expression.ColumnElement"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.sql.expression.ColumnElement</span></tt></a></p>
<p>Represent the clause <tt class="docutils literal"><span class="pre">left</span> <span class="pre">operator</span> <span class="pre">ALL</span> <span class="pre">(right)</span></tt>. <tt class="docutils literal"><span class="pre">right</span></tt> must be
an array expression.</p>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p><a class="reference internal" href="#sqlalchemy.dialects.postgresql.ARRAY" title="sqlalchemy.dialects.postgresql.ARRAY"><tt class="xref py py-class docutils literal"><span class="pre">postgresql.ARRAY</span></tt></a></p>
<p class="last"><a class="reference internal" href="#sqlalchemy.dialects.postgresql.ARRAY.Comparator.all" title="sqlalchemy.dialects.postgresql.ARRAY.Comparator.all"><tt class="xref py py-meth docutils literal"><span class="pre">postgresql.ARRAY.Comparator.all()</span></tt></a> - ARRAY-bound method</p>
</div>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.BIT">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.postgresql.</tt><tt class="descname">BIT</tt><big>(</big><em>length=None</em>, <em>varying=False</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.BIT" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/types.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.TypeEngine</span></tt></a></p>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.BYTEA">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.postgresql.</tt><tt class="descname">BYTEA</tt><big>(</big><em>length=None</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.BYTEA" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/types.html#sqlalchemy.types.LargeBinary" title="sqlalchemy.types.LargeBinary"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.LargeBinary</span></tt></a></p>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.BYTEA.__init__">
<tt class="descname">__init__</tt><big>(</big><em>length=None</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.BYTEA.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Construct a LargeBinary type.</p>
<table class="docutils field-list" frame="void" rules="none">
<col class="field-name" />
<col class="field-body" />
<tbody valign="top">
<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body"><span class="target" id="sqlalchemy.dialects.postgresql.BYTEA.params.length"></span><strong>length</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.postgresql.BYTEA.params.length">¶</a> – optional, a length for the column for use in
DDL statements, for those BLOB types that accept a length
(i.e. MySQL). It does <em>not</em> produce a small BINARY/VARBINARY
type - use the BINARY/VARBINARY types specifically for those.
May be safely omitted if no <tt class="docutils literal"><span class="pre">CREATE</span>
<span class="pre">TABLE</span></tt> will be issued. Certain databases may require a
<em>length</em> for use in DDL, and will raise an exception when
the <tt class="docutils literal"><span class="pre">CREATE</span> <span class="pre">TABLE</span></tt> DDL is issued.</td>
</tr>
</tbody>
</table>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.CIDR">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.postgresql.</tt><tt class="descname">CIDR</tt><a class="headerlink" href="#sqlalchemy.dialects.postgresql.CIDR" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/types.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.TypeEngine</span></tt></a></p>
<dl class="attribute">
<dt id="sqlalchemy.dialects.postgresql.CIDR.__init__">
<tt class="descname">__init__</tt><a class="headerlink" href="#sqlalchemy.dialects.postgresql.CIDR.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>x.__init__(...) initializes x; see help(type(x)) for signature</p>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.DOUBLE_PRECISION">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.postgresql.</tt><tt class="descname">DOUBLE_PRECISION</tt><big>(</big><em>precision=None</em>, <em>asdecimal=False</em>, <em>decimal_return_scale=None</em>, <em>**kwargs</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.DOUBLE_PRECISION" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/types.html#sqlalchemy.types.Float" title="sqlalchemy.types.Float"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.Float</span></tt></a></p>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.DOUBLE_PRECISION.__init__">
<tt class="descname">__init__</tt><big>(</big><em>precision=None</em>, <em>asdecimal=False</em>, <em>decimal_return_scale=None</em>, <em>**kwargs</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.DOUBLE_PRECISION.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Construct a Float.</p>
<table class="docutils field-list" frame="void" rules="none">
<col class="field-name" />
<col class="field-body" />
<tbody valign="top">
<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body"><ul class="first last simple">
<li><span class="target" id="sqlalchemy.dialects.postgresql.DOUBLE_PRECISION.params.precision"></span><strong>precision</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.postgresql.DOUBLE_PRECISION.params.precision">¶</a> – the numeric precision for use in DDL <tt class="docutils literal"><span class="pre">CREATE</span>
<span class="pre">TABLE</span></tt>.</li>
<li><span class="target" id="sqlalchemy.dialects.postgresql.DOUBLE_PRECISION.params.asdecimal"></span><strong>asdecimal</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.postgresql.DOUBLE_PRECISION.params.asdecimal">¶</a> – the same flag as that of <a class="reference internal" href="../core/types.html#sqlalchemy.types.Numeric" title="sqlalchemy.types.Numeric"><tt class="xref py py-class docutils literal"><span class="pre">Numeric</span></tt></a>, but
defaults to <tt class="docutils literal"><span class="pre">False</span></tt>. Note that setting this flag to <tt class="docutils literal"><span class="pre">True</span></tt>
results in floating point conversion.</li>
<li><span class="target" id="sqlalchemy.dialects.postgresql.DOUBLE_PRECISION.params.decimal_return_scale"></span><strong>decimal_return_scale</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.postgresql.DOUBLE_PRECISION.params.decimal_return_scale">¶</a> – <p>Default scale to use when converting
from floats to Python decimals. Floating point values will typically
be much longer due to decimal inaccuracy, and most floating point
database types don’t have a notion of “scale”, so by default the
float type looks for the first ten decimal places when converting.
Specfiying this value will override that length. Note that the
MySQL float types, which do include “scale”, will use “scale”
as the default for decimal_return_scale, if not otherwise specified.</p>
<div class="versionadded">
<p><span>New in version 0.9.0.</span></p>
</div>
</li>
<li><span class="target" id="sqlalchemy.dialects.postgresql.DOUBLE_PRECISION.params.**kwargs"></span><strong>**kwargs</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.postgresql.DOUBLE_PRECISION.params.**kwargs">¶</a> – deprecated. Additional arguments here are ignored
by the default <a class="reference internal" href="../core/types.html#sqlalchemy.types.Float" title="sqlalchemy.types.Float"><tt class="xref py py-class docutils literal"><span class="pre">Float</span></tt></a> type. For database specific
floats that support additional arguments, see that dialect’s
documentation for details, such as
<a class="reference internal" href="mysql.html#sqlalchemy.dialects.mysql.FLOAT" title="sqlalchemy.dialects.mysql.FLOAT"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.dialects.mysql.FLOAT</span></tt></a>.</li>
</ul>
</td>
</tr>
</tbody>
</table>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.ENUM">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.postgresql.</tt><tt class="descname">ENUM</tt><big>(</big><em>*enums</em>, <em>**kw</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.ENUM" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/types.html#sqlalchemy.types.Enum" title="sqlalchemy.types.Enum"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.Enum</span></tt></a></p>
<p>Postgresql ENUM type.</p>
<p>This is a subclass of <a class="reference internal" href="../core/types.html#sqlalchemy.types.Enum" title="sqlalchemy.types.Enum"><tt class="xref py py-class docutils literal"><span class="pre">types.Enum</span></tt></a> which includes
support for PG’s <tt class="docutils literal"><span class="pre">CREATE</span> <span class="pre">TYPE</span></tt>.</p>
<p><a class="reference internal" href="#sqlalchemy.dialects.postgresql.ENUM" title="sqlalchemy.dialects.postgresql.ENUM"><tt class="xref py py-class docutils literal"><span class="pre">ENUM</span></tt></a> is used automatically when
using the <a class="reference internal" href="../core/types.html#sqlalchemy.types.Enum" title="sqlalchemy.types.Enum"><tt class="xref py py-class docutils literal"><span class="pre">types.Enum</span></tt></a> type on PG assuming
the <tt class="docutils literal"><span class="pre">native_enum</span></tt> is left as <tt class="docutils literal"><span class="pre">True</span></tt>. However, the
<a class="reference internal" href="#sqlalchemy.dialects.postgresql.ENUM" title="sqlalchemy.dialects.postgresql.ENUM"><tt class="xref py py-class docutils literal"><span class="pre">ENUM</span></tt></a> class can also be instantiated
directly in order to access some additional Postgresql-specific
options, namely finer control over whether or not
<tt class="docutils literal"><span class="pre">CREATE</span> <span class="pre">TYPE</span></tt> should be emitted.</p>
<p>Note that both <a class="reference internal" href="../core/types.html#sqlalchemy.types.Enum" title="sqlalchemy.types.Enum"><tt class="xref py py-class docutils literal"><span class="pre">types.Enum</span></tt></a> as well as
<a class="reference internal" href="#sqlalchemy.dialects.postgresql.ENUM" title="sqlalchemy.dialects.postgresql.ENUM"><tt class="xref py py-class docutils literal"><span class="pre">ENUM</span></tt></a> feature create/drop
methods; the base <a class="reference internal" href="../core/types.html#sqlalchemy.types.Enum" title="sqlalchemy.types.Enum"><tt class="xref py py-class docutils literal"><span class="pre">types.Enum</span></tt></a> type ultimately
delegates to the <a class="reference internal" href="#sqlalchemy.dialects.postgresql.ENUM.create" title="sqlalchemy.dialects.postgresql.ENUM.create"><tt class="xref py py-meth docutils literal"><span class="pre">create()</span></tt></a> and
<a class="reference internal" href="#sqlalchemy.dialects.postgresql.ENUM.drop" title="sqlalchemy.dialects.postgresql.ENUM.drop"><tt class="xref py py-meth docutils literal"><span class="pre">drop()</span></tt></a> methods present here.</p>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.ENUM.__init__">
<tt class="descname">__init__</tt><big>(</big><em>*enums</em>, <em>**kw</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.ENUM.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Construct an <a class="reference internal" href="#sqlalchemy.dialects.postgresql.ENUM" title="sqlalchemy.dialects.postgresql.ENUM"><tt class="xref py py-class docutils literal"><span class="pre">ENUM</span></tt></a>.</p>
<p>Arguments are the same as that of
<a class="reference internal" href="../core/types.html#sqlalchemy.types.Enum" title="sqlalchemy.types.Enum"><tt class="xref py py-class docutils literal"><span class="pre">types.Enum</span></tt></a>, but also including
the following parameters.</p>
<table class="docutils field-list" frame="void" rules="none">
<col class="field-name" />
<col class="field-body" />
<tbody valign="top">
<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body"><span class="target" id="sqlalchemy.dialects.postgresql.ENUM.params.create_type"></span><strong>create_type</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.postgresql.ENUM.params.create_type">¶</a> – <p>Defaults to True.
Indicates that <tt class="docutils literal"><span class="pre">CREATE</span> <span class="pre">TYPE</span></tt> should be
emitted, after optionally checking for the
presence of the type, when the parent
table is being created; and additionally
that <tt class="docutils literal"><span class="pre">DROP</span> <span class="pre">TYPE</span></tt> is called when the table
is dropped. When <tt class="docutils literal"><span class="pre">False</span></tt>, no check
will be performed and no <tt class="docutils literal"><span class="pre">CREATE</span> <span class="pre">TYPE</span></tt>
or <tt class="docutils literal"><span class="pre">DROP</span> <span class="pre">TYPE</span></tt> is emitted, unless
<a class="reference internal" href="#sqlalchemy.dialects.postgresql.ENUM.create" title="sqlalchemy.dialects.postgresql.ENUM.create"><tt class="xref py py-meth docutils literal"><span class="pre">create()</span></tt></a>
or <a class="reference internal" href="#sqlalchemy.dialects.postgresql.ENUM.drop" title="sqlalchemy.dialects.postgresql.ENUM.drop"><tt class="xref py py-meth docutils literal"><span class="pre">drop()</span></tt></a>
are called directly.
Setting to <tt class="docutils literal"><span class="pre">False</span></tt> is helpful
when invoking a creation scheme to a SQL file
without access to the actual database -
the <a class="reference internal" href="#sqlalchemy.dialects.postgresql.ENUM.create" title="sqlalchemy.dialects.postgresql.ENUM.create"><tt class="xref py py-meth docutils literal"><span class="pre">create()</span></tt></a> and
<a class="reference internal" href="#sqlalchemy.dialects.postgresql.ENUM.drop" title="sqlalchemy.dialects.postgresql.ENUM.drop"><tt class="xref py py-meth docutils literal"><span class="pre">drop()</span></tt></a> methods can
be used to emit SQL to a target bind.</p>
<div class="versionadded">
<p><span>New in version 0.7.4.</span></p>
</div>
</td>
</tr>
</tbody>
</table>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.ENUM.create">
<tt class="descname">create</tt><big>(</big><em>bind=None</em>, <em>checkfirst=True</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.ENUM.create" title="Permalink to this definition">¶</a></dt>
<dd><p>Emit <tt class="docutils literal"><span class="pre">CREATE</span> <span class="pre">TYPE</span></tt> for this
<a class="reference internal" href="#sqlalchemy.dialects.postgresql.ENUM" title="sqlalchemy.dialects.postgresql.ENUM"><tt class="xref py py-class docutils literal"><span class="pre">ENUM</span></tt></a>.</p>
<p>If the underlying dialect does not support
Postgresql CREATE TYPE, no action is taken.</p>
<table class="docutils field-list" frame="void" rules="none">
<col class="field-name" />
<col class="field-body" />
<tbody valign="top">
<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body"><ul class="first last simple">
<li><span class="target" id="sqlalchemy.dialects.postgresql.ENUM.create.params.bind"></span><strong>bind</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.postgresql.ENUM.create.params.bind">¶</a> – a connectable <a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><tt class="xref py py-class docutils literal"><span class="pre">Engine</span></tt></a>,
<a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><tt class="xref py py-class docutils literal"><span class="pre">Connection</span></tt></a>, or similar object to emit
SQL.</li>
<li><span class="target" id="sqlalchemy.dialects.postgresql.ENUM.create.params.checkfirst"></span><strong>checkfirst</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.postgresql.ENUM.create.params.checkfirst">¶</a> – if <tt class="docutils literal"><span class="pre">True</span></tt>, a query against
the PG catalog will be first performed to see
if the type does not exist already before
creating.</li>
</ul>
</td>
</tr>
</tbody>
</table>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.ENUM.drop">
<tt class="descname">drop</tt><big>(</big><em>bind=None</em>, <em>checkfirst=True</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.ENUM.drop" title="Permalink to this definition">¶</a></dt>
<dd><p>Emit <tt class="docutils literal"><span class="pre">DROP</span> <span class="pre">TYPE</span></tt> for this
<a class="reference internal" href="#sqlalchemy.dialects.postgresql.ENUM" title="sqlalchemy.dialects.postgresql.ENUM"><tt class="xref py py-class docutils literal"><span class="pre">ENUM</span></tt></a>.</p>
<p>If the underlying dialect does not support
Postgresql DROP TYPE, no action is taken.</p>
<table class="docutils field-list" frame="void" rules="none">
<col class="field-name" />
<col class="field-body" />
<tbody valign="top">
<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body"><ul class="first last simple">
<li><span class="target" id="sqlalchemy.dialects.postgresql.ENUM.drop.params.bind"></span><strong>bind</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.postgresql.ENUM.drop.params.bind">¶</a> – a connectable <a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><tt class="xref py py-class docutils literal"><span class="pre">Engine</span></tt></a>,
<a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><tt class="xref py py-class docutils literal"><span class="pre">Connection</span></tt></a>, or similar object to emit
SQL.</li>
<li><span class="target" id="sqlalchemy.dialects.postgresql.ENUM.drop.params.checkfirst"></span><strong>checkfirst</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.postgresql.ENUM.drop.params.checkfirst">¶</a> – if <tt class="docutils literal"><span class="pre">True</span></tt>, a query against
the PG catalog will be first performed to see
if the type actually exists before dropping.</li>
</ul>
</td>
</tr>
</tbody>
</table>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.HSTORE">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.postgresql.</tt><tt class="descname">HSTORE</tt><a class="headerlink" href="#sqlalchemy.dialects.postgresql.HSTORE" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/types.html#sqlalchemy.types.Concatenable" title="sqlalchemy.types.Concatenable"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.Concatenable</span></tt></a>, <a class="reference internal" href="../core/types.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.TypeEngine</span></tt></a></p>
<p>Represent the Postgresql HSTORE type.</p>
<p>The <a class="reference internal" href="#sqlalchemy.dialects.postgresql.HSTORE" title="sqlalchemy.dialects.postgresql.HSTORE"><tt class="xref py py-class docutils literal"><span class="pre">HSTORE</span></tt></a> type stores dictionaries containing strings, e.g.:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">data_table</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'data_table'</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'data'</span><span class="p">,</span> <span class="n">HSTORE</span><span class="p">)</span>
<span class="p">)</span>
<span class="k">with</span> <span class="n">engine</span><span class="o">.</span><span class="n">connect</span><span class="p">()</span> <span class="k">as</span> <span class="n">conn</span><span class="p">:</span>
<span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span>
<span class="n">data_table</span><span class="o">.</span><span class="n">insert</span><span class="p">(),</span>
<span class="n">data</span> <span class="o">=</span> <span class="p">{</span><span class="s">"key1"</span><span class="p">:</span> <span class="s">"value1"</span><span class="p">,</span> <span class="s">"key2"</span><span class="p">:</span> <span class="s">"value2"</span><span class="p">}</span>
<span class="p">)</span></pre></div>
</div>
<p><a class="reference internal" href="#sqlalchemy.dialects.postgresql.HSTORE" title="sqlalchemy.dialects.postgresql.HSTORE"><tt class="xref py py-class docutils literal"><span class="pre">HSTORE</span></tt></a> provides for a wide range of operations, including:</p>
<ul>
<li><p class="first">Index operations:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">data_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data</span><span class="p">[</span><span class="s">'some key'</span><span class="p">]</span> <span class="o">==</span> <span class="s">'some value'</span></pre></div>
</div>
</li>
<li><p class="first">Containment operations:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">data_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data</span><span class="o">.</span><span class="n">has_key</span><span class="p">(</span><span class="s">'some key'</span><span class="p">)</span>
<span class="n">data_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data</span><span class="o">.</span><span class="n">has_all</span><span class="p">([</span><span class="s">'one'</span><span class="p">,</span> <span class="s">'two'</span><span class="p">,</span> <span class="s">'three'</span><span class="p">])</span></pre></div>
</div>
</li>
<li><p class="first">Concatenation:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">data_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data</span> <span class="o">+</span> <span class="p">{</span><span class="s">"k1"</span><span class="p">:</span> <span class="s">"v1"</span><span class="p">}</span></pre></div>
</div>
</li>
</ul>
<p>For a full list of special methods see
<a class="reference internal" href="#sqlalchemy.dialects.postgresql.HSTORE.comparator_factory" title="sqlalchemy.dialects.postgresql.HSTORE.comparator_factory"><tt class="xref py py-class docutils literal"><span class="pre">HSTORE.comparator_factory</span></tt></a>.</p>
<p>For usage with the SQLAlchemy ORM, it may be desirable to combine
the usage of <a class="reference internal" href="#sqlalchemy.dialects.postgresql.HSTORE" title="sqlalchemy.dialects.postgresql.HSTORE"><tt class="xref py py-class docutils literal"><span class="pre">HSTORE</span></tt></a> with <a class="reference internal" href="../orm/extensions/mutable.html#sqlalchemy.ext.mutable.MutableDict" title="sqlalchemy.ext.mutable.MutableDict"><tt class="xref py py-class docutils literal"><span class="pre">MutableDict</span></tt></a> dictionary
now part of the <a class="reference internal" href="../orm/extensions/mutable.html#module-sqlalchemy.ext.mutable" title="sqlalchemy.ext.mutable"><tt class="xref py py-mod docutils literal"><span class="pre">sqlalchemy.ext.mutable</span></tt></a>
extension. This extension will allow “in-place” changes to the
dictionary, e.g. addition of new keys or replacement/removal of existing
keys to/from the current dictionary, to produce events which will be
detected by the unit of work:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy.ext.mutable</span> <span class="kn">import</span> <span class="n">MutableDict</span>
<span class="k">class</span> <span class="nc">MyClass</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="n">__tablename__</span> <span class="o">=</span> <span class="s">'data_table'</span>
<span class="nb">id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
<span class="n">data</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">MutableDict</span><span class="o">.</span><span class="n">as_mutable</span><span class="p">(</span><span class="n">HSTORE</span><span class="p">))</span>
<span class="n">my_object</span> <span class="o">=</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">MyClass</span><span class="p">)</span><span class="o">.</span><span class="n">one</span><span class="p">()</span>
<span class="c"># in-place mutation, requires Mutable extension</span>
<span class="c"># in order for the ORM to detect</span>
<span class="n">my_object</span><span class="o">.</span><span class="n">data</span><span class="p">[</span><span class="s">'some_key'</span><span class="p">]</span> <span class="o">=</span> <span class="s">'some value'</span>
<span class="n">session</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span></pre></div>
</div>
<p>When the <a class="reference internal" href="../orm/extensions/mutable.html#module-sqlalchemy.ext.mutable" title="sqlalchemy.ext.mutable"><tt class="xref py py-mod docutils literal"><span class="pre">sqlalchemy.ext.mutable</span></tt></a> extension is not used, the ORM
will not be alerted to any changes to the contents of an existing
dictionary, unless that dictionary value is re-assigned to the
HSTORE-attribute itself, thus generating a change event.</p>
<div class="versionadded">
<p><span>New in version 0.8.</span></p>
</div>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p class="last"><a class="reference internal" href="#sqlalchemy.dialects.postgresql.hstore" title="sqlalchemy.dialects.postgresql.hstore"><tt class="xref py py-class docutils literal"><span class="pre">hstore</span></tt></a> - render the Postgresql <tt class="docutils literal"><span class="pre">hstore()</span></tt> function.</p>
</div>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.HSTORE.comparator_factory">
<em class="property">class </em><tt class="descname">comparator_factory</tt><big>(</big><em>expr</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.HSTORE.comparator_factory" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.Comparator</span></tt></p>
<p>Define comparison operations for <a class="reference internal" href="#sqlalchemy.dialects.postgresql.HSTORE" title="sqlalchemy.dialects.postgresql.HSTORE"><tt class="xref py py-class docutils literal"><span class="pre">HSTORE</span></tt></a>.</p>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.HSTORE.comparator_factory.array">
<tt class="descname">array</tt><big>(</big><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.HSTORE.comparator_factory.array" title="Permalink to this definition">¶</a></dt>
<dd><p>Text array expression. Returns array of alternating keys and
values.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.HSTORE.comparator_factory.contained_by">
<tt class="descname">contained_by</tt><big>(</big><em>other</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.HSTORE.comparator_factory.contained_by" title="Permalink to this definition">¶</a></dt>
<dd><p>Boolean expression. Test if keys are a proper subset of the
keys of the argument hstore expression.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.HSTORE.comparator_factory.contains">
<tt class="descname">contains</tt><big>(</big><em>other</em>, <em>**kwargs</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.HSTORE.comparator_factory.contains" title="Permalink to this definition">¶</a></dt>
<dd><p>Boolean expression. Test if keys are a superset of the keys of
the argument hstore expression.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.HSTORE.comparator_factory.defined">
<tt class="descname">defined</tt><big>(</big><em>key</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.HSTORE.comparator_factory.defined" title="Permalink to this definition">¶</a></dt>
<dd><p>Boolean expression. Test for presence of a non-NULL value for
the key. Note that the key may be a SQLA expression.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.HSTORE.comparator_factory.delete">
<tt class="descname">delete</tt><big>(</big><em>key</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.HSTORE.comparator_factory.delete" title="Permalink to this definition">¶</a></dt>
<dd><p>HStore expression. Returns the contents of this hstore with the
given key deleted. Note that the key may be a SQLA expression.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.HSTORE.comparator_factory.has_all">
<tt class="descname">has_all</tt><big>(</big><em>other</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.HSTORE.comparator_factory.has_all" title="Permalink to this definition">¶</a></dt>
<dd><p>Boolean expression. Test for presence of all keys in the PG
array.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.HSTORE.comparator_factory.has_any">
<tt class="descname">has_any</tt><big>(</big><em>other</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.HSTORE.comparator_factory.has_any" title="Permalink to this definition">¶</a></dt>
<dd><p>Boolean expression. Test for presence of any key in the PG
array.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.HSTORE.comparator_factory.has_key">
<tt class="descname">has_key</tt><big>(</big><em>other</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.HSTORE.comparator_factory.has_key" title="Permalink to this definition">¶</a></dt>
<dd><p>Boolean expression. Test for presence of a key. Note that the
key may be a SQLA expression.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.HSTORE.comparator_factory.keys">
<tt class="descname">keys</tt><big>(</big><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.HSTORE.comparator_factory.keys" title="Permalink to this definition">¶</a></dt>
<dd><p>Text array expression. Returns array of keys.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.HSTORE.comparator_factory.matrix">
<tt class="descname">matrix</tt><big>(</big><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.HSTORE.comparator_factory.matrix" title="Permalink to this definition">¶</a></dt>
<dd><p>Text array expression. Returns array of [key, value] pairs.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.HSTORE.comparator_factory.slice">
<tt class="descname">slice</tt><big>(</big><em>array</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.HSTORE.comparator_factory.slice" title="Permalink to this definition">¶</a></dt>
<dd><p>HStore expression. Returns a subset of an hstore defined by
array of keys.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.HSTORE.comparator_factory.vals">
<tt class="descname">vals</tt><big>(</big><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.HSTORE.comparator_factory.vals" title="Permalink to this definition">¶</a></dt>
<dd><p>Text array expression. Returns array of values.</p>
</dd></dl>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.hstore">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.postgresql.</tt><tt class="descname">hstore</tt><big>(</big><em>*args</em>, <em>**kwargs</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.hstore" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/functions.html#sqlalchemy.sql.functions.GenericFunction" title="sqlalchemy.sql.functions.GenericFunction"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.sql.functions.GenericFunction</span></tt></a></p>
<p>Construct an hstore value within a SQL expression using the
Postgresql <tt class="docutils literal"><span class="pre">hstore()</span></tt> function.</p>
<p>The <a class="reference internal" href="#sqlalchemy.dialects.postgresql.hstore" title="sqlalchemy.dialects.postgresql.hstore"><tt class="xref py py-class docutils literal"><span class="pre">hstore</span></tt></a> function accepts one or two arguments as described
in the Postgresql documentation.</p>
<p>E.g.:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy.dialects.postgresql</span> <span class="kn">import</span> <span class="n">array</span><span class="p">,</span> <span class="n">hstore</span>
<span class="n">select</span><span class="p">([</span><span class="n">hstore</span><span class="p">(</span><span class="s">'key1'</span><span class="p">,</span> <span class="s">'value1'</span><span class="p">)])</span>
<span class="n">select</span><span class="p">([</span>
<span class="n">hstore</span><span class="p">(</span>
<span class="n">array</span><span class="p">([</span><span class="s">'key1'</span><span class="p">,</span> <span class="s">'key2'</span><span class="p">,</span> <span class="s">'key3'</span><span class="p">]),</span>
<span class="n">array</span><span class="p">([</span><span class="s">'value1'</span><span class="p">,</span> <span class="s">'value2'</span><span class="p">,</span> <span class="s">'value3'</span><span class="p">])</span>
<span class="p">)</span>
<span class="p">])</span></pre></div>
</div>
<div class="versionadded">
<p><span>New in version 0.8.</span></p>
</div>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p class="last"><a class="reference internal" href="#sqlalchemy.dialects.postgresql.HSTORE" title="sqlalchemy.dialects.postgresql.HSTORE"><tt class="xref py py-class docutils literal"><span class="pre">HSTORE</span></tt></a> - the Postgresql <tt class="docutils literal"><span class="pre">HSTORE</span></tt> datatype.</p>
</div>
<dl class="attribute">
<dt id="sqlalchemy.dialects.postgresql.hstore.type">
<tt class="descname">type</tt><a class="headerlink" href="#sqlalchemy.dialects.postgresql.hstore.type" title="Permalink to this definition">¶</a></dt>
<dd><p>alias of <a class="reference internal" href="#sqlalchemy.dialects.postgresql.HSTORE" title="sqlalchemy.dialects.postgresql.HSTORE"><tt class="xref py py-class docutils literal"><span class="pre">HSTORE</span></tt></a></p>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.INET">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.postgresql.</tt><tt class="descname">INET</tt><a class="headerlink" href="#sqlalchemy.dialects.postgresql.INET" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/types.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.TypeEngine</span></tt></a></p>
<dl class="attribute">
<dt id="sqlalchemy.dialects.postgresql.INET.__init__">
<tt class="descname">__init__</tt><a class="headerlink" href="#sqlalchemy.dialects.postgresql.INET.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>x.__init__(...) initializes x; see help(type(x)) for signature</p>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.INTERVAL">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.postgresql.</tt><tt class="descname">INTERVAL</tt><big>(</big><em>precision=None</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.INTERVAL" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/types.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.TypeEngine</span></tt></a></p>
<p>Postgresql INTERVAL type.</p>
<p>The INTERVAL type may not be supported on all DBAPIs.
It is known to work on psycopg2 and not pg8000 or zxjdbc.</p>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.JSON">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.postgresql.</tt><tt class="descname">JSON</tt><big>(</big><em>none_as_null=False</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.JSON" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/types.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.TypeEngine</span></tt></a></p>
<p>Represent the Postgresql JSON type.</p>
<p>The <a class="reference internal" href="#sqlalchemy.dialects.postgresql.JSON" title="sqlalchemy.dialects.postgresql.JSON"><tt class="xref py py-class docutils literal"><span class="pre">JSON</span></tt></a> type stores arbitrary JSON format data, e.g.:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">data_table</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'data_table'</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'data'</span><span class="p">,</span> <span class="n">JSON</span><span class="p">)</span>
<span class="p">)</span>
<span class="k">with</span> <span class="n">engine</span><span class="o">.</span><span class="n">connect</span><span class="p">()</span> <span class="k">as</span> <span class="n">conn</span><span class="p">:</span>
<span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span>
<span class="n">data_table</span><span class="o">.</span><span class="n">insert</span><span class="p">(),</span>
<span class="n">data</span> <span class="o">=</span> <span class="p">{</span><span class="s">"key1"</span><span class="p">:</span> <span class="s">"value1"</span><span class="p">,</span> <span class="s">"key2"</span><span class="p">:</span> <span class="s">"value2"</span><span class="p">}</span>
<span class="p">)</span></pre></div>
</div>
<p><a class="reference internal" href="#sqlalchemy.dialects.postgresql.JSON" title="sqlalchemy.dialects.postgresql.JSON"><tt class="xref py py-class docutils literal"><span class="pre">JSON</span></tt></a> provides several operations:</p>
<ul>
<li><p class="first">Index operations:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">data_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data</span><span class="p">[</span><span class="s">'some key'</span><span class="p">]</span></pre></div>
</div>
</li>
<li><p class="first">Index operations returning text (required for text comparison):</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">data_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data</span><span class="p">[</span><span class="s">'some key'</span><span class="p">]</span><span class="o">.</span><span class="n">astext</span> <span class="o">==</span> <span class="s">'some value'</span></pre></div>
</div>
</li>
<li><p class="first">Index operations with a built-in CAST call:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">data_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data</span><span class="p">[</span><span class="s">'some key'</span><span class="p">]</span><span class="o">.</span><span class="n">cast</span><span class="p">(</span><span class="n">Integer</span><span class="p">)</span> <span class="o">==</span> <span class="mi">5</span></pre></div>
</div>
</li>
<li><p class="first">Path index operations:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">data_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data</span><span class="p">[(</span><span class="s">'key_1'</span><span class="p">,</span> <span class="s">'key_2'</span><span class="p">,</span> <span class="o">...</span><span class="p">,</span> <span class="s">'key_n'</span><span class="p">)]</span></pre></div>
</div>
</li>
<li><p class="first">Path index operations returning text (required for text comparison):</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">data_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data</span><span class="p">[(</span><span class="s">'key_1'</span><span class="p">,</span> <span class="s">'key_2'</span><span class="p">,</span> <span class="o">...</span><span class="p">,</span> <span class="s">'key_n'</span><span class="p">)]</span><span class="o">.</span><span class="n">astext</span> <span class="o">==</span> \
<span class="s">'some value'</span></pre></div>
</div>
</li>
</ul>
<p>Index operations return an instance of <a class="reference internal" href="#sqlalchemy.dialects.postgresql.JSONElement" title="sqlalchemy.dialects.postgresql.JSONElement"><tt class="xref py py-class docutils literal"><span class="pre">JSONElement</span></tt></a>, which
represents an expression such as <tt class="docutils literal"><span class="pre">column</span> <span class="pre">-></span> <span class="pre">index</span></tt>. This element then
defines methods such as <a class="reference internal" href="#sqlalchemy.dialects.postgresql.JSONElement.astext" title="sqlalchemy.dialects.postgresql.JSONElement.astext"><tt class="xref py py-attr docutils literal"><span class="pre">JSONElement.astext</span></tt></a> and
<a class="reference internal" href="#sqlalchemy.dialects.postgresql.JSONElement.cast" title="sqlalchemy.dialects.postgresql.JSONElement.cast"><tt class="xref py py-meth docutils literal"><span class="pre">JSONElement.cast()</span></tt></a> for setting up type behavior.</p>
<p>The <a class="reference internal" href="#sqlalchemy.dialects.postgresql.JSON" title="sqlalchemy.dialects.postgresql.JSON"><tt class="xref py py-class docutils literal"><span class="pre">JSON</span></tt></a> type, when used with the SQLAlchemy ORM, does not
detect in-place mutations to the structure. In order to detect these, the
<a class="reference internal" href="../orm/extensions/mutable.html#module-sqlalchemy.ext.mutable" title="sqlalchemy.ext.mutable"><tt class="xref py py-mod docutils literal"><span class="pre">sqlalchemy.ext.mutable</span></tt></a> extension must be used. This extension will
allow “in-place” changes to the datastructure to produce events which
will be detected by the unit of work. See the example at <a class="reference internal" href="#sqlalchemy.dialects.postgresql.HSTORE" title="sqlalchemy.dialects.postgresql.HSTORE"><tt class="xref py py-class docutils literal"><span class="pre">HSTORE</span></tt></a>
for a simple example involving a dictionary.</p>
<p>Custom serializers and deserializers are specified at the dialect level,
that is using <a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><tt class="xref py py-func docutils literal"><span class="pre">create_engine()</span></tt></a>. The reason for this is that when
using psycopg2, the DBAPI only allows serializers at the per-cursor
or per-connection level. E.g.:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">"postgresql://scott:tiger@localhost/test"</span><span class="p">,</span>
<span class="n">json_serializer</span><span class="o">=</span><span class="n">my_serialize_fn</span><span class="p">,</span>
<span class="n">json_deserializer</span><span class="o">=</span><span class="n">my_deserialize_fn</span>
<span class="p">)</span></pre></div>
</div>
<p>When using the psycopg2 dialect, the json_deserializer is registered
against the database using <tt class="docutils literal"><span class="pre">psycopg2.extras.register_default_json</span></tt>.</p>
<div class="versionadded">
<p><span>New in version 0.9.</span></p>
</div>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.JSON.__init__">
<tt class="descname">__init__</tt><big>(</big><em>none_as_null=False</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.JSON.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Construct a <a class="reference internal" href="#sqlalchemy.dialects.postgresql.JSON" title="sqlalchemy.dialects.postgresql.JSON"><tt class="xref py py-class docutils literal"><span class="pre">JSON</span></tt></a> type.</p>
<table class="docutils field-list" frame="void" rules="none">
<col class="field-name" />
<col class="field-body" />
<tbody valign="top">
<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body"><span class="target" id="sqlalchemy.dialects.postgresql.JSON.params.none_as_null"></span><strong>none_as_null</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.postgresql.JSON.params.none_as_null">¶</a> – <p>if True, persist the value <tt class="docutils literal"><span class="pre">None</span></tt> as a
SQL NULL value, not the JSON encoding of <tt class="docutils literal"><span class="pre">null</span></tt>. Note that
when this flag is False, the <a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.expression.null" title="sqlalchemy.sql.expression.null"><tt class="xref py py-func docutils literal"><span class="pre">null()</span></tt></a> construct can still
be used to persist a NULL value:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">null</span>
<span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">table</span><span class="o">.</span><span class="n">insert</span><span class="p">(),</span> <span class="n">data</span><span class="o">=</span><span class="n">null</span><span class="p">())</span></pre></div>
</div>
<div class="versionchanged">
<p><span>Changed in version 0.9.8: </span>- Added <tt class="docutils literal"><span class="pre">none_as_null</span></tt>, and <a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.expression.null" title="sqlalchemy.sql.expression.null"><tt class="xref py py-func docutils literal"><span class="pre">null()</span></tt></a>
is now supported in order to persist a NULL value.</p>
</div>
</td>
</tr>
</tbody>
</table>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.JSON.comparator_factory">
<em class="property">class </em><tt class="descname">comparator_factory</tt><big>(</big><em>expr</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.JSON.comparator_factory" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.Comparator</span></tt></p>
<p>Define comparison operations for <a class="reference internal" href="#sqlalchemy.dialects.postgresql.JSON" title="sqlalchemy.dialects.postgresql.JSON"><tt class="xref py py-class docutils literal"><span class="pre">JSON</span></tt></a>.</p>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.JSONB">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.postgresql.</tt><tt class="descname">JSONB</tt><big>(</big><em>none_as_null=False</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.JSONB" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.dialects.postgresql.json.JSON</span></tt></p>
<p>Represent the Postgresql JSONB type.</p>
<p>The <a class="reference internal" href="#sqlalchemy.dialects.postgresql.JSONB" title="sqlalchemy.dialects.postgresql.JSONB"><tt class="xref py py-class docutils literal"><span class="pre">JSONB</span></tt></a> type stores arbitrary JSONB format data, e.g.:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">data_table</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'data_table'</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'data'</span><span class="p">,</span> <span class="n">JSONB</span><span class="p">)</span>
<span class="p">)</span>
<span class="k">with</span> <span class="n">engine</span><span class="o">.</span><span class="n">connect</span><span class="p">()</span> <span class="k">as</span> <span class="n">conn</span><span class="p">:</span>
<span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span>
<span class="n">data_table</span><span class="o">.</span><span class="n">insert</span><span class="p">(),</span>
<span class="n">data</span> <span class="o">=</span> <span class="p">{</span><span class="s">"key1"</span><span class="p">:</span> <span class="s">"value1"</span><span class="p">,</span> <span class="s">"key2"</span><span class="p">:</span> <span class="s">"value2"</span><span class="p">}</span>
<span class="p">)</span></pre></div>
</div>
<p><a class="reference internal" href="#sqlalchemy.dialects.postgresql.JSONB" title="sqlalchemy.dialects.postgresql.JSONB"><tt class="xref py py-class docutils literal"><span class="pre">JSONB</span></tt></a> provides several operations:</p>
<ul>
<li><p class="first">Index operations:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">data_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data</span><span class="p">[</span><span class="s">'some key'</span><span class="p">]</span></pre></div>
</div>
</li>
<li><p class="first">Index operations returning text (required for text comparison):</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">data_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data</span><span class="p">[</span><span class="s">'some key'</span><span class="p">]</span><span class="o">.</span><span class="n">astext</span> <span class="o">==</span> <span class="s">'some value'</span></pre></div>
</div>
</li>
<li><p class="first">Index operations with a built-in CAST call:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">data_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data</span><span class="p">[</span><span class="s">'some key'</span><span class="p">]</span><span class="o">.</span><span class="n">cast</span><span class="p">(</span><span class="n">Integer</span><span class="p">)</span> <span class="o">==</span> <span class="mi">5</span></pre></div>
</div>
</li>
<li><p class="first">Path index operations:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">data_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data</span><span class="p">[(</span><span class="s">'key_1'</span><span class="p">,</span> <span class="s">'key_2'</span><span class="p">,</span> <span class="o">...</span><span class="p">,</span> <span class="s">'key_n'</span><span class="p">)]</span></pre></div>
</div>
</li>
<li><p class="first">Path index operations returning text (required for text comparison):</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">data_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data</span><span class="p">[(</span><span class="s">'key_1'</span><span class="p">,</span> <span class="s">'key_2'</span><span class="p">,</span> <span class="o">...</span><span class="p">,</span> <span class="s">'key_n'</span><span class="p">)]</span><span class="o">.</span><span class="n">astext</span> <span class="o">==</span> \
<span class="s">'some value'</span></pre></div>
</div>
</li>
</ul>
<p>Index operations return an instance of <a class="reference internal" href="#sqlalchemy.dialects.postgresql.JSONElement" title="sqlalchemy.dialects.postgresql.JSONElement"><tt class="xref py py-class docutils literal"><span class="pre">JSONElement</span></tt></a>, which
represents an expression such as <tt class="docutils literal"><span class="pre">column</span> <span class="pre">-></span> <span class="pre">index</span></tt>. This element then
defines methods such as <a class="reference internal" href="#sqlalchemy.dialects.postgresql.JSONElement.astext" title="sqlalchemy.dialects.postgresql.JSONElement.astext"><tt class="xref py py-attr docutils literal"><span class="pre">JSONElement.astext</span></tt></a> and
<a class="reference internal" href="#sqlalchemy.dialects.postgresql.JSONElement.cast" title="sqlalchemy.dialects.postgresql.JSONElement.cast"><tt class="xref py py-meth docutils literal"><span class="pre">JSONElement.cast()</span></tt></a> for setting up type behavior.</p>
<p>The <a class="reference internal" href="#sqlalchemy.dialects.postgresql.JSON" title="sqlalchemy.dialects.postgresql.JSON"><tt class="xref py py-class docutils literal"><span class="pre">JSON</span></tt></a> type, when used with the SQLAlchemy ORM, does not
detect in-place mutations to the structure. In order to detect these, the
<a class="reference internal" href="../orm/extensions/mutable.html#module-sqlalchemy.ext.mutable" title="sqlalchemy.ext.mutable"><tt class="xref py py-mod docutils literal"><span class="pre">sqlalchemy.ext.mutable</span></tt></a> extension must be used. This extension will
allow “in-place” changes to the datastructure to produce events which
will be detected by the unit of work. See the example at <a class="reference internal" href="#sqlalchemy.dialects.postgresql.HSTORE" title="sqlalchemy.dialects.postgresql.HSTORE"><tt class="xref py py-class docutils literal"><span class="pre">HSTORE</span></tt></a>
for a simple example involving a dictionary.</p>
<p>Custom serializers and deserializers are specified at the dialect level,
that is using <a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><tt class="xref py py-func docutils literal"><span class="pre">create_engine()</span></tt></a>. The reason for this is that when
using psycopg2, the DBAPI only allows serializers at the per-cursor
or per-connection level. E.g.:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">"postgresql://scott:tiger@localhost/test"</span><span class="p">,</span>
<span class="n">json_serializer</span><span class="o">=</span><span class="n">my_serialize_fn</span><span class="p">,</span>
<span class="n">json_deserializer</span><span class="o">=</span><span class="n">my_deserialize_fn</span>
<span class="p">)</span></pre></div>
</div>
<p>When using the psycopg2 dialect, the json_deserializer is registered
against the database using <tt class="docutils literal"><span class="pre">psycopg2.extras.register_default_json</span></tt>.</p>
<div class="versionadded">
<p><span>New in version 0.9.7.</span></p>
</div>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.JSONB.comparator_factory">
<em class="property">class </em><tt class="descname">comparator_factory</tt><big>(</big><em>expr</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.JSONB.comparator_factory" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.Comparator</span></tt></p>
<p>Define comparison operations for <a class="reference internal" href="#sqlalchemy.dialects.postgresql.JSON" title="sqlalchemy.dialects.postgresql.JSON"><tt class="xref py py-class docutils literal"><span class="pre">JSON</span></tt></a>.</p>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.JSONB.comparator_factory.contained_by">
<tt class="descname">contained_by</tt><big>(</big><em>other</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.JSONB.comparator_factory.contained_by" title="Permalink to this definition">¶</a></dt>
<dd><p>Boolean expression. Test if keys are a proper subset of the
keys of the argument jsonb expression.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.JSONB.comparator_factory.contains">
<tt class="descname">contains</tt><big>(</big><em>other</em>, <em>**kwargs</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.JSONB.comparator_factory.contains" title="Permalink to this definition">¶</a></dt>
<dd><p>Boolean expression. Test if keys (or array) are a superset of/contained
the keys of the argument jsonb expression.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.JSONB.comparator_factory.has_all">
<tt class="descname">has_all</tt><big>(</big><em>other</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.JSONB.comparator_factory.has_all" title="Permalink to this definition">¶</a></dt>
<dd><p>Boolean expression. Test for presence of all keys in jsonb</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.JSONB.comparator_factory.has_any">
<tt class="descname">has_any</tt><big>(</big><em>other</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.JSONB.comparator_factory.has_any" title="Permalink to this definition">¶</a></dt>
<dd><p>Boolean expression. Test for presence of any key in jsonb</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.JSONB.comparator_factory.has_key">
<tt class="descname">has_key</tt><big>(</big><em>other</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.JSONB.comparator_factory.has_key" title="Permalink to this definition">¶</a></dt>
<dd><p>Boolean expression. Test for presence of a key. Note that the
key may be a SQLA expression.</p>
</dd></dl>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.JSONElement">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.postgresql.</tt><tt class="descname">JSONElement</tt><big>(</big><em>left</em>, <em>right</em>, <em>astext=False</em>, <em>opstring=None</em>, <em>result_type=None</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.JSONElement" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.expression.BinaryExpression" title="sqlalchemy.sql.expression.BinaryExpression"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.sql.expression.BinaryExpression</span></tt></a></p>
<p>Represents accessing an element of a <a class="reference internal" href="#sqlalchemy.dialects.postgresql.JSON" title="sqlalchemy.dialects.postgresql.JSON"><tt class="xref py py-class docutils literal"><span class="pre">JSON</span></tt></a> value.</p>
<p>The <a class="reference internal" href="#sqlalchemy.dialects.postgresql.JSONElement" title="sqlalchemy.dialects.postgresql.JSONElement"><tt class="xref py py-class docutils literal"><span class="pre">JSONElement</span></tt></a> is produced whenever using the Python index
operator on an expression that has the type <a class="reference internal" href="#sqlalchemy.dialects.postgresql.JSON" title="sqlalchemy.dialects.postgresql.JSON"><tt class="xref py py-class docutils literal"><span class="pre">JSON</span></tt></a>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">expr</span> <span class="o">=</span> <span class="n">mytable</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">json_data</span><span class="p">[</span><span class="s">'some_key'</span><span class="p">]</span></pre></div>
</div>
<p>The expression typically compiles to a JSON access such as <tt class="docutils literal"><span class="pre">col</span> <span class="pre">-></span> <span class="pre">key</span></tt>.
Modifiers are then available for typing behavior, including
<a class="reference internal" href="#sqlalchemy.dialects.postgresql.JSONElement.cast" title="sqlalchemy.dialects.postgresql.JSONElement.cast"><tt class="xref py py-meth docutils literal"><span class="pre">JSONElement.cast()</span></tt></a> and <a class="reference internal" href="#sqlalchemy.dialects.postgresql.JSONElement.astext" title="sqlalchemy.dialects.postgresql.JSONElement.astext"><tt class="xref py py-attr docutils literal"><span class="pre">JSONElement.astext</span></tt></a>.</p>
<dl class="attribute">
<dt id="sqlalchemy.dialects.postgresql.JSONElement.astext">
<tt class="descname">astext</tt><a class="headerlink" href="#sqlalchemy.dialects.postgresql.JSONElement.astext" title="Permalink to this definition">¶</a></dt>
<dd><p>Convert this <a class="reference internal" href="#sqlalchemy.dialects.postgresql.JSONElement" title="sqlalchemy.dialects.postgresql.JSONElement"><tt class="xref py py-class docutils literal"><span class="pre">JSONElement</span></tt></a> to use the ‘astext’ operator
when evaluated.</p>
<p>E.g.:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">select</span><span class="p">([</span><span class="n">data_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data</span><span class="p">[</span><span class="s">'some key'</span><span class="p">]</span><span class="o">.</span><span class="n">astext</span><span class="p">])</span></pre></div>
</div>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p class="last"><a class="reference internal" href="#sqlalchemy.dialects.postgresql.JSONElement.cast" title="sqlalchemy.dialects.postgresql.JSONElement.cast"><tt class="xref py py-meth docutils literal"><span class="pre">JSONElement.cast()</span></tt></a></p>
</div>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.JSONElement.cast">
<tt class="descname">cast</tt><big>(</big><em>type_</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.JSONElement.cast" title="Permalink to this definition">¶</a></dt>
<dd><p>Convert this <a class="reference internal" href="#sqlalchemy.dialects.postgresql.JSONElement" title="sqlalchemy.dialects.postgresql.JSONElement"><tt class="xref py py-class docutils literal"><span class="pre">JSONElement</span></tt></a> to apply both the ‘astext’ operator
as well as an explicit type cast when evaulated.</p>
<p>E.g.:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">select</span><span class="p">([</span><span class="n">data_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data</span><span class="p">[</span><span class="s">'some key'</span><span class="p">]</span><span class="o">.</span><span class="n">cast</span><span class="p">(</span><span class="n">Integer</span><span class="p">)])</span></pre></div>
</div>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p class="last"><a class="reference internal" href="#sqlalchemy.dialects.postgresql.JSONElement.astext" title="sqlalchemy.dialects.postgresql.JSONElement.astext"><tt class="xref py py-attr docutils literal"><span class="pre">JSONElement.astext</span></tt></a></p>
</div>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.MACADDR">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.postgresql.</tt><tt class="descname">MACADDR</tt><a class="headerlink" href="#sqlalchemy.dialects.postgresql.MACADDR" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/types.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.TypeEngine</span></tt></a></p>
<dl class="attribute">
<dt id="sqlalchemy.dialects.postgresql.MACADDR.__init__">
<tt class="descname">__init__</tt><a class="headerlink" href="#sqlalchemy.dialects.postgresql.MACADDR.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>x.__init__(...) initializes x; see help(type(x)) for signature</p>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.OID">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.postgresql.</tt><tt class="descname">OID</tt><a class="headerlink" href="#sqlalchemy.dialects.postgresql.OID" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/types.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.TypeEngine</span></tt></a></p>
<p>Provide the Postgresql OID type.</p>
<div class="versionadded">
<p><span>New in version 0.9.5.</span></p>
</div>
<dl class="attribute">
<dt id="sqlalchemy.dialects.postgresql.OID.__init__">
<tt class="descname">__init__</tt><a class="headerlink" href="#sqlalchemy.dialects.postgresql.OID.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>x.__init__(...) initializes x; see help(type(x)) for signature</p>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.REAL">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.postgresql.</tt><tt class="descname">REAL</tt><big>(</big><em>precision=None</em>, <em>asdecimal=False</em>, <em>decimal_return_scale=None</em>, <em>**kwargs</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.REAL" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/types.html#sqlalchemy.types.Float" title="sqlalchemy.types.Float"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.Float</span></tt></a></p>
<p>The SQL REAL type.</p>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.REAL.__init__">
<tt class="descname">__init__</tt><big>(</big><em>precision=None</em>, <em>asdecimal=False</em>, <em>decimal_return_scale=None</em>, <em>**kwargs</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.REAL.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Construct a Float.</p>
<table class="docutils field-list" frame="void" rules="none">
<col class="field-name" />
<col class="field-body" />
<tbody valign="top">
<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body"><ul class="first last simple">
<li><span class="target" id="sqlalchemy.dialects.postgresql.REAL.params.precision"></span><strong>precision</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.postgresql.REAL.params.precision">¶</a> – the numeric precision for use in DDL <tt class="docutils literal"><span class="pre">CREATE</span>
<span class="pre">TABLE</span></tt>.</li>
<li><span class="target" id="sqlalchemy.dialects.postgresql.REAL.params.asdecimal"></span><strong>asdecimal</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.postgresql.REAL.params.asdecimal">¶</a> – the same flag as that of <a class="reference internal" href="../core/types.html#sqlalchemy.types.Numeric" title="sqlalchemy.types.Numeric"><tt class="xref py py-class docutils literal"><span class="pre">Numeric</span></tt></a>, but
defaults to <tt class="docutils literal"><span class="pre">False</span></tt>. Note that setting this flag to <tt class="docutils literal"><span class="pre">True</span></tt>
results in floating point conversion.</li>
<li><span class="target" id="sqlalchemy.dialects.postgresql.REAL.params.decimal_return_scale"></span><strong>decimal_return_scale</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.postgresql.REAL.params.decimal_return_scale">¶</a> – <p>Default scale to use when converting
from floats to Python decimals. Floating point values will typically
be much longer due to decimal inaccuracy, and most floating point
database types don’t have a notion of “scale”, so by default the
float type looks for the first ten decimal places when converting.
Specfiying this value will override that length. Note that the
MySQL float types, which do include “scale”, will use “scale”
as the default for decimal_return_scale, if not otherwise specified.</p>
<div class="versionadded">
<p><span>New in version 0.9.0.</span></p>
</div>
</li>
<li><span class="target" id="sqlalchemy.dialects.postgresql.REAL.params.**kwargs"></span><strong>**kwargs</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.postgresql.REAL.params.**kwargs">¶</a> – deprecated. Additional arguments here are ignored
by the default <a class="reference internal" href="../core/types.html#sqlalchemy.types.Float" title="sqlalchemy.types.Float"><tt class="xref py py-class docutils literal"><span class="pre">Float</span></tt></a> type. For database specific
floats that support additional arguments, see that dialect’s
documentation for details, such as
<a class="reference internal" href="mysql.html#sqlalchemy.dialects.mysql.FLOAT" title="sqlalchemy.dialects.mysql.FLOAT"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.dialects.mysql.FLOAT</span></tt></a>.</li>
</ul>
</td>
</tr>
</tbody>
</table>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.TSVECTOR">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.postgresql.</tt><tt class="descname">TSVECTOR</tt><a class="headerlink" href="#sqlalchemy.dialects.postgresql.TSVECTOR" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/types.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.TypeEngine</span></tt></a></p>
<p>The <a class="reference internal" href="#sqlalchemy.dialects.postgresql.TSVECTOR" title="sqlalchemy.dialects.postgresql.TSVECTOR"><tt class="xref py py-class docutils literal"><span class="pre">postgresql.TSVECTOR</span></tt></a> type implements the Postgresql
text search type TSVECTOR.</p>
<p>It can be used to do full text queries on natural language
documents.</p>
<div class="versionadded">
<p><span>New in version 0.9.0.</span></p>
</div>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p class="last"><a class="reference internal" href="#postgresql-match"><em>Full Text Search</em></a></p>
</div>
<dl class="attribute">
<dt id="sqlalchemy.dialects.postgresql.TSVECTOR.__init__">
<tt class="descname">__init__</tt><a class="headerlink" href="#sqlalchemy.dialects.postgresql.TSVECTOR.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>x.__init__(...) initializes x; see help(type(x)) for signature</p>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.UUID">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.postgresql.</tt><tt class="descname">UUID</tt><big>(</big><em>as_uuid=False</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.UUID" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/types.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.TypeEngine</span></tt></a></p>
<p>Postgresql UUID type.</p>
<p>Represents the UUID column type, interpreting
data either as natively returned by the DBAPI
or as Python uuid objects.</p>
<p>The UUID type may not be supported on all DBAPIs.
It is known to work on psycopg2 and not pg8000.</p>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.UUID.__init__">
<tt class="descname">__init__</tt><big>(</big><em>as_uuid=False</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.UUID.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Construct a UUID type.</p>
<table class="docutils field-list" frame="void" rules="none">
<col class="field-name" />
<col class="field-body" />
<tbody valign="top">
<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body"><span class="target" id="sqlalchemy.dialects.postgresql.UUID.params.as_uuid"></span><strong>as_uuid=False</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.postgresql.UUID.params.as_uuid">¶</a> – if True, values will be interpreted
as Python uuid objects, converting to/from string via the
DBAPI.</td>
</tr>
</tbody>
</table>
</dd></dl>
</dd></dl>
<div class="section" id="range-types">
<h3>Range Types<a class="headerlink" href="#range-types" title="Permalink to this headline">¶</a></h3>
<p>The new range column types found in PostgreSQL 9.2 onwards are
catered for by the following types:</p>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.INT4RANGE">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.postgresql.</tt><tt class="descname">INT4RANGE</tt><a class="headerlink" href="#sqlalchemy.dialects.postgresql.INT4RANGE" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="#sqlalchemy.dialects.postgresql.ranges.RangeOperators" title="sqlalchemy.dialects.postgresql.ranges.RangeOperators"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.dialects.postgresql.ranges.RangeOperators</span></tt></a>, <a class="reference internal" href="../core/types.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.TypeEngine</span></tt></a></p>
<p>Represent the Postgresql INT4RANGE type.</p>
<div class="versionadded">
<p><span>New in version 0.8.2.</span></p>
</div>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.INT8RANGE">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.postgresql.</tt><tt class="descname">INT8RANGE</tt><a class="headerlink" href="#sqlalchemy.dialects.postgresql.INT8RANGE" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="#sqlalchemy.dialects.postgresql.ranges.RangeOperators" title="sqlalchemy.dialects.postgresql.ranges.RangeOperators"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.dialects.postgresql.ranges.RangeOperators</span></tt></a>, <a class="reference internal" href="../core/types.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.TypeEngine</span></tt></a></p>
<p>Represent the Postgresql INT8RANGE type.</p>
<div class="versionadded">
<p><span>New in version 0.8.2.</span></p>
</div>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.NUMRANGE">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.postgresql.</tt><tt class="descname">NUMRANGE</tt><a class="headerlink" href="#sqlalchemy.dialects.postgresql.NUMRANGE" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="#sqlalchemy.dialects.postgresql.ranges.RangeOperators" title="sqlalchemy.dialects.postgresql.ranges.RangeOperators"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.dialects.postgresql.ranges.RangeOperators</span></tt></a>, <a class="reference internal" href="../core/types.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.TypeEngine</span></tt></a></p>
<p>Represent the Postgresql NUMRANGE type.</p>
<div class="versionadded">
<p><span>New in version 0.8.2.</span></p>
</div>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.DATERANGE">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.postgresql.</tt><tt class="descname">DATERANGE</tt><a class="headerlink" href="#sqlalchemy.dialects.postgresql.DATERANGE" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="#sqlalchemy.dialects.postgresql.ranges.RangeOperators" title="sqlalchemy.dialects.postgresql.ranges.RangeOperators"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.dialects.postgresql.ranges.RangeOperators</span></tt></a>, <a class="reference internal" href="../core/types.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.TypeEngine</span></tt></a></p>
<p>Represent the Postgresql DATERANGE type.</p>
<div class="versionadded">
<p><span>New in version 0.8.2.</span></p>
</div>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.TSRANGE">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.postgresql.</tt><tt class="descname">TSRANGE</tt><a class="headerlink" href="#sqlalchemy.dialects.postgresql.TSRANGE" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="#sqlalchemy.dialects.postgresql.ranges.RangeOperators" title="sqlalchemy.dialects.postgresql.ranges.RangeOperators"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.dialects.postgresql.ranges.RangeOperators</span></tt></a>, <a class="reference internal" href="../core/types.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.TypeEngine</span></tt></a></p>
<p>Represent the Postgresql TSRANGE type.</p>
<div class="versionadded">
<p><span>New in version 0.8.2.</span></p>
</div>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.TSTZRANGE">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.postgresql.</tt><tt class="descname">TSTZRANGE</tt><a class="headerlink" href="#sqlalchemy.dialects.postgresql.TSTZRANGE" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="#sqlalchemy.dialects.postgresql.ranges.RangeOperators" title="sqlalchemy.dialects.postgresql.ranges.RangeOperators"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.dialects.postgresql.ranges.RangeOperators</span></tt></a>, <a class="reference internal" href="../core/types.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.TypeEngine</span></tt></a></p>
<p>Represent the Postgresql TSTZRANGE type.</p>
<div class="versionadded">
<p><span>New in version 0.8.2.</span></p>
</div>
</dd></dl>
<p>The types above get most of their functionality from the following
mixin:</p>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.ranges.RangeOperators">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.postgresql.ranges.</tt><tt class="descname">RangeOperators</tt><a class="headerlink" href="#sqlalchemy.dialects.postgresql.ranges.RangeOperators" title="Permalink to this definition">¶</a></dt>
<dd><p>This mixin provides functionality for the Range Operators
listed in Table 9-44 of the <a class="reference external" href="http://www.postgresql.org/docs/devel/static/functions-range.html">postgres documentation</a> for Range
Functions and Operators. It is used by all the range types
provided in the <tt class="docutils literal"><span class="pre">postgres</span></tt> dialect and can likely be used for
any range types you create yourself.</p>
<p>No extra support is provided for the Range Functions listed in
Table 9-45 of the postgres documentation. For these, the normal
<a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.expression.func" title="sqlalchemy.sql.expression.func"><tt class="xref py py-func docutils literal"><span class="pre">func()</span></tt></a> object should be used.</p>
<div class="versionadded">
<p><span>New in version 0.8.2: </span>Support for Postgresql RANGE operations.</p>
</div>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.ranges.RangeOperators.comparator_factory">
<em class="property">class </em><tt class="descname">comparator_factory</tt><big>(</big><em>expr</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.ranges.RangeOperators.comparator_factory" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.Comparator</span></tt></p>
<p>Define comparison operations for range types.</p>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.ranges.RangeOperators.comparator_factory.__ne__">
<tt class="descname">__ne__</tt><big>(</big><em>other</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.ranges.RangeOperators.comparator_factory.__ne__" title="Permalink to this definition">¶</a></dt>
<dd><p>Boolean expression. Returns true if two ranges are not equal</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.ranges.RangeOperators.comparator_factory.adjacent_to">
<tt class="descname">adjacent_to</tt><big>(</big><em>other</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.ranges.RangeOperators.comparator_factory.adjacent_to" title="Permalink to this definition">¶</a></dt>
<dd><p>Boolean expression. Returns true if the range in the column
is adjacent to the range in the operand.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.ranges.RangeOperators.comparator_factory.contained_by">
<tt class="descname">contained_by</tt><big>(</big><em>other</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.ranges.RangeOperators.comparator_factory.contained_by" title="Permalink to this definition">¶</a></dt>
<dd><p>Boolean expression. Returns true if the column is contained
within the right hand operand.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.ranges.RangeOperators.comparator_factory.contains">
<tt class="descname">contains</tt><big>(</big><em>other</em>, <em>**kw</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.ranges.RangeOperators.comparator_factory.contains" title="Permalink to this definition">¶</a></dt>
<dd><p>Boolean expression. Returns true if the right hand operand,
which can be an element or a range, is contained within the
column.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.ranges.RangeOperators.comparator_factory.not_extend_left_of">
<tt class="descname">not_extend_left_of</tt><big>(</big><em>other</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.ranges.RangeOperators.comparator_factory.not_extend_left_of" title="Permalink to this definition">¶</a></dt>
<dd><p>Boolean expression. Returns true if the range in the column
does not extend left of the range in the operand.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.ranges.RangeOperators.comparator_factory.not_extend_right_of">
<tt class="descname">not_extend_right_of</tt><big>(</big><em>other</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.ranges.RangeOperators.comparator_factory.not_extend_right_of" title="Permalink to this definition">¶</a></dt>
<dd><p>Boolean expression. Returns true if the range in the column
does not extend right of the range in the operand.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.ranges.RangeOperators.comparator_factory.overlaps">
<tt class="descname">overlaps</tt><big>(</big><em>other</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.ranges.RangeOperators.comparator_factory.overlaps" title="Permalink to this definition">¶</a></dt>
<dd><p>Boolean expression. Returns true if the column overlaps
(has points in common with) the right hand operand.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.ranges.RangeOperators.comparator_factory.strictly_left_of">
<tt class="descname">strictly_left_of</tt><big>(</big><em>other</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.ranges.RangeOperators.comparator_factory.strictly_left_of" title="Permalink to this definition">¶</a></dt>
<dd><p>Boolean expression. Returns true if the column is strictly
left of the right hand operand.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.ranges.RangeOperators.comparator_factory.strictly_right_of">
<tt class="descname">strictly_right_of</tt><big>(</big><em>other</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.ranges.RangeOperators.comparator_factory.strictly_right_of" title="Permalink to this definition">¶</a></dt>
<dd><p>Boolean expression. Returns true if the column is strictly
right of the right hand operand.</p>
</dd></dl>
</dd></dl>
</dd></dl>
<div class="admonition warning">
<p class="first admonition-title">Warning</p>
<p class="last">The range type DDL support should work with any Postgres DBAPI
driver, however the data types returned may vary. If you are using
<tt class="docutils literal"><span class="pre">psycopg2</span></tt>, it’s recommended to upgrade to version 2.5 or later
before using these column types.</p>
</div>
<p>When instantiating models that use these column types, you should pass
whatever data type is expected by the DBAPI driver you’re using for
the column type. For <a class="reference external" href="http://pythonhosted.org/psycopg2/module.html#module-psycopg2" title="(in Psycopg v2.5)"><tt class="xref py py-mod docutils literal"><span class="pre">psycopg2</span></tt></a> these are
<a class="reference external" href="http://pythonhosted.org/psycopg2/extras.html#psycopg2.extras.NumericRange" title="(in Psycopg v2.5)"><tt class="xref py py-class docutils literal"><span class="pre">NumericRange</span></tt></a>,
<a class="reference external" href="http://pythonhosted.org/psycopg2/extras.html#psycopg2.extras.DateRange" title="(in Psycopg v2.5)"><tt class="xref py py-class docutils literal"><span class="pre">DateRange</span></tt></a>,
<a class="reference external" href="http://pythonhosted.org/psycopg2/extras.html#psycopg2.extras.DateTimeRange" title="(in Psycopg v2.5)"><tt class="xref py py-class docutils literal"><span class="pre">DateTimeRange</span></tt></a> and
<a class="reference external" href="http://pythonhosted.org/psycopg2/extras.html#psycopg2.extras.DateTimeTZRange" title="(in Psycopg v2.5)"><tt class="xref py py-class docutils literal"><span class="pre">DateTimeTZRange</span></tt></a> or the class you’ve
registered with <a class="reference external" href="http://pythonhosted.org/psycopg2/extras.html#psycopg2.extras.register_range" title="(in Psycopg v2.5)"><tt class="xref py py-func docutils literal"><span class="pre">register_range()</span></tt></a>.</p>
<p>For example:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">psycopg2.extras</span> <span class="kn">import</span> <span class="n">DateTimeRange</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.dialects.postgresql</span> <span class="kn">import</span> <span class="n">TSRANGE</span>
<span class="k">class</span> <span class="nc">RoomBooking</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="n">__tablename__</span> <span class="o">=</span> <span class="s">'room_booking'</span>
<span class="n">room</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">(),</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
<span class="n">during</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">TSRANGE</span><span class="p">())</span>
<span class="n">booking</span> <span class="o">=</span> <span class="n">RoomBooking</span><span class="p">(</span>
<span class="n">room</span><span class="o">=</span><span class="mi">101</span><span class="p">,</span>
<span class="n">during</span><span class="o">=</span><span class="n">DateTimeRange</span><span class="p">(</span><span class="n">datetime</span><span class="p">(</span><span class="mi">2013</span><span class="p">,</span> <span class="mi">3</span><span class="p">,</span> <span class="mi">23</span><span class="p">),</span> <span class="bp">None</span><span class="p">)</span>
<span class="p">)</span></pre></div>
</div>
</div>
</div>
<div class="section" id="postgresql-constraint-types">
<h2>PostgreSQL Constraint Types<a class="headerlink" href="#postgresql-constraint-types" title="Permalink to this headline">¶</a></h2>
<p>SQLAlchemy supports Postgresql EXCLUDE constraints via the
<a class="reference internal" href="#sqlalchemy.dialects.postgresql.ExcludeConstraint" title="sqlalchemy.dialects.postgresql.ExcludeConstraint"><tt class="xref py py-class docutils literal"><span class="pre">ExcludeConstraint</span></tt></a> class:</p>
<dl class="class">
<dt id="sqlalchemy.dialects.postgresql.ExcludeConstraint">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.postgresql.</tt><tt class="descname">ExcludeConstraint</tt><big>(</big><em>*elements</em>, <em>**kw</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.ExcludeConstraint" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/constraints.html#sqlalchemy.schema.ColumnCollectionConstraint" title="sqlalchemy.schema.ColumnCollectionConstraint"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.schema.ColumnCollectionConstraint</span></tt></a></p>
<p>A table-level EXCLUDE constraint.</p>
<p>Defines an EXCLUDE constraint as described in the <a class="reference external" href="http://www.postgresql.org/docs/9.0/static/sql-createtable.html#SQL-CREATETABLE-EXCLUDE">postgres
documentation</a>.</p>
<dl class="method">
<dt id="sqlalchemy.dialects.postgresql.ExcludeConstraint.__init__">
<tt class="descname">__init__</tt><big>(</big><em>*elements</em>, <em>**kw</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.postgresql.ExcludeConstraint.__init__" title="Permalink to this definition">¶</a></dt>
<dd><table class="docutils field-list" frame="void" rules="none">
<col class="field-name" />
<col class="field-body" />
<tbody valign="top">
<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body"><ul class="first last simple">
<li><span class="target" id="sqlalchemy.dialects.postgresql.ExcludeConstraint.params.*elements"></span><strong>*elements</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.postgresql.ExcludeConstraint.params.*elements">¶</a> – A sequence of two tuples of the form <tt class="docutils literal"><span class="pre">(column,</span> <span class="pre">operator)</span></tt> where
column must be a column name or Column object and operator must
be a string containing the operator to use.</li>
<li><span class="target" id="sqlalchemy.dialects.postgresql.ExcludeConstraint.params.name"></span><strong>name</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.postgresql.ExcludeConstraint.params.name">¶</a> – Optional, the in-database name of this constraint.</li>
<li><span class="target" id="sqlalchemy.dialects.postgresql.ExcludeConstraint.params.deferrable"></span><strong>deferrable</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.postgresql.ExcludeConstraint.params.deferrable">¶</a> – Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when
issuing DDL for this constraint.</li>
<li><span class="target" id="sqlalchemy.dialects.postgresql.ExcludeConstraint.params.initially"></span><strong>initially</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.postgresql.ExcludeConstraint.params.initially">¶</a> – Optional string. If set, emit INITIALLY <value> when issuing DDL
for this constraint.</li>
<li><span class="target" id="sqlalchemy.dialects.postgresql.ExcludeConstraint.params.using"></span><strong>using</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.postgresql.ExcludeConstraint.params.using">¶</a> – Optional string. If set, emit USING <index_method> when issuing DDL
for this constraint. Defaults to ‘gist’.</li>
<li><span class="target" id="sqlalchemy.dialects.postgresql.ExcludeConstraint.params.where"></span><strong>where</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.postgresql.ExcludeConstraint.params.where">¶</a> – Optional string. If set, emit WHERE <predicate> when issuing DDL
for this constraint.</li>
</ul>
</td>
</tr>
</tbody>
</table>
</dd></dl>
</dd></dl>
<p>For example:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy.dialects.postgresql</span> <span class="kn">import</span> <span class="n">ExcludeConstraint</span><span class="p">,</span> <span class="n">TSRANGE</span>
<span class="k">class</span> <span class="nc">RoomBooking</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="n">__tablename__</span> <span class="o">=</span> <span class="s">'room_booking'</span>
<span class="n">room</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">(),</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
<span class="n">during</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">TSRANGE</span><span class="p">())</span>
<span class="n">__table_args__</span> <span class="o">=</span> <span class="p">(</span>
<span class="n">ExcludeConstraint</span><span class="p">((</span><span class="s">'room'</span><span class="p">,</span> <span class="s">'='</span><span class="p">),</span> <span class="p">(</span><span class="s">'during'</span><span class="p">,</span> <span class="s">'&&'</span><span class="p">)),</span>
<span class="p">)</span></pre></div>
</div>
</div>
<div class="section" id="module-sqlalchemy.dialects.postgresql.psycopg2">
<span id="psycopg2"></span><h2>psycopg2<a class="headerlink" href="#module-sqlalchemy.dialects.postgresql.psycopg2" title="Permalink to this headline">¶</a></h2>
<p>Support for the PostgreSQL database via the psycopg2 driver.</p>
<div class="section" id="dialect-postgresql-psycopg2-url">
<h3>DBAPI<a class="headerlink" href="#dialect-postgresql-psycopg2-url" title="Permalink to this headline">¶</a></h3>
<p>Documentation and download information (if applicable) for psycopg2 is available at:
<a class="reference external" href="http://pypi.python.org/pypi/psycopg2/">http://pypi.python.org/pypi/psycopg2/</a></p>
</div>
<div class="section" id="dialect-postgresql-psycopg2-connect">
<h3>Connecting<a class="headerlink" href="#dialect-postgresql-psycopg2-connect" title="Permalink to this headline">¶</a></h3>
<p>Connect String:<div class="highlight-python"><pre>postgresql+psycopg2://user:password@host:port/dbname[?key=value&key=value...]</pre>
</div>
</p>
</div>
<div class="section" id="psycopg2-connect-arguments">
<h3>psycopg2 Connect Arguments<a class="headerlink" href="#psycopg2-connect-arguments" title="Permalink to this headline">¶</a></h3>
<p>psycopg2-specific keyword arguments which are accepted by
<a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><tt class="xref py py-func docutils literal"><span class="pre">create_engine()</span></tt></a> are:</p>
<ul>
<li><p class="first"><tt class="docutils literal"><span class="pre">server_side_cursors</span></tt>: Enable the usage of “server side cursors” for SQL
statements which support this feature. What this essentially means from a
psycopg2 point of view is that the cursor is created using a name, e.g.
<tt class="docutils literal"><span class="pre">connection.cursor('some</span> <span class="pre">name')</span></tt>, which has the effect that result rows
are not immediately pre-fetched and buffered after statement execution, but
are instead left on the server and only retrieved as needed. SQLAlchemy’s
<a class="reference internal" href="../core/connections.html#sqlalchemy.engine.ResultProxy" title="sqlalchemy.engine.ResultProxy"><tt class="xref py py-class docutils literal"><span class="pre">ResultProxy</span></tt></a> uses special row-buffering
behavior when this feature is enabled, such that groups of 100 rows at a
time are fetched over the wire to reduce conversational overhead.
Note that the <tt class="docutils literal"><span class="pre">stream_results=True</span></tt> execution option is a more targeted
way of enabling this mode on a per-execution basis.</p>
</li>
<li><p class="first"><tt class="docutils literal"><span class="pre">use_native_unicode</span></tt>: Enable the usage of Psycopg2 “native unicode” mode
per connection. True by default.</p>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p class="last"><a class="reference internal" href="#psycopg2-disable-native-unicode"><em>Disabling Native Unicode</em></a></p>
</div>
</li>
<li><p class="first"><tt class="docutils literal"><span class="pre">isolation_level</span></tt>: This option, available for all PostgreSQL dialects,
includes the <tt class="docutils literal"><span class="pre">AUTOCOMMIT</span></tt> isolation level when using the psycopg2
dialect.</p>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p class="last"><a class="reference internal" href="#psycopg2-isolation-level"><em>Psycopg2 Transaction Isolation Level</em></a></p>
</div>
</li>
<li><p class="first"><tt class="docutils literal"><span class="pre">client_encoding</span></tt>: sets the client encoding in a libpq-agnostic way,
using psycopg2’s <tt class="docutils literal"><span class="pre">set_client_encoding()</span></tt> method.</p>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p class="last"><a class="reference internal" href="#psycopg2-unicode"><em>Unicode with Psycopg2</em></a></p>
</div>
</li>
</ul>
</div>
<div class="section" id="unix-domain-connections">
<h3>Unix Domain Connections<a class="headerlink" href="#unix-domain-connections" title="Permalink to this headline">¶</a></h3>
<p>psycopg2 supports connecting via Unix domain connections. When the <tt class="docutils literal"><span class="pre">host</span></tt>
portion of the URL is omitted, SQLAlchemy passes <tt class="docutils literal"><span class="pre">None</span></tt> to psycopg2,
which specifies Unix-domain communication rather than TCP/IP communication:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">create_engine</span><span class="p">(</span><span class="s">"postgresql+psycopg2://user:password@/dbname"</span><span class="p">)</span></pre></div>
</div>
<p>By default, the socket file used is to connect to a Unix-domain socket
in <tt class="docutils literal"><span class="pre">/tmp</span></tt>, or whatever socket directory was specified when PostgreSQL
was built. This value can be overridden by passing a pathname to psycopg2,
using <tt class="docutils literal"><span class="pre">host</span></tt> as an additional keyword argument:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">create_engine</span><span class="p">(</span><span class="s">"postgresql+psycopg2://user:password@/dbname?host=/var/lib/postgresql"</span><span class="p">)</span></pre></div>
</div>
<p>See also:</p>
<p><a class="reference external" href="http://www.postgresql.org/docs/9.1/static/libpq-connect.html#LIBPQ-PQCONNECTDBPARAMS">PQconnectdbParams</a></p>
</div>
<div class="section" id="per-statement-connection-execution-options">
<h3>Per-Statement/Connection Execution Options<a class="headerlink" href="#per-statement-connection-execution-options" title="Permalink to this headline">¶</a></h3>
<p>The following DBAPI-specific options are respected when used with
<a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Connection.execution_options" title="sqlalchemy.engine.Connection.execution_options"><tt class="xref py py-meth docutils literal"><span class="pre">Connection.execution_options()</span></tt></a>, <a class="reference internal" href="../core/selectable.html#sqlalchemy.sql.expression.Executable.execution_options" title="sqlalchemy.sql.expression.Executable.execution_options"><tt class="xref py py-meth docutils literal"><span class="pre">Executable.execution_options()</span></tt></a>,
<a class="reference internal" href="../orm/query.html#sqlalchemy.orm.query.Query.execution_options" title="sqlalchemy.orm.query.Query.execution_options"><tt class="xref py py-meth docutils literal"><span class="pre">Query.execution_options()</span></tt></a>, in addition to those not specific to DBAPIs:</p>
<ul class="simple">
<li>isolation_level - Set the transaction isolation level for the lifespan of a
<a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><tt class="xref py py-class docutils literal"><span class="pre">Connection</span></tt></a> (can only be set on a connection, not a statement
or query). See <a class="reference internal" href="#psycopg2-isolation-level"><em>Psycopg2 Transaction Isolation Level</em></a>.</li>
<li>stream_results - Enable or disable usage of psycopg2 server side cursors -
this feature makes use of “named” cursors in combination with special
result handling methods so that result rows are not fully buffered.
If <tt class="docutils literal"><span class="pre">None</span></tt> or not set, the <tt class="docutils literal"><span class="pre">server_side_cursors</span></tt> option of the
<a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><tt class="xref py py-class docutils literal"><span class="pre">Engine</span></tt></a> is used.</li>
</ul>
</div>
<div class="section" id="unicode-with-psycopg2">
<span id="psycopg2-unicode"></span><h3>Unicode with Psycopg2<a class="headerlink" href="#unicode-with-psycopg2" title="Permalink to this headline">¶</a></h3>
<p>By default, the psycopg2 driver uses the <tt class="docutils literal"><span class="pre">psycopg2.extensions.UNICODE</span></tt>
extension, such that the DBAPI receives and returns all strings as Python
Unicode objects directly - SQLAlchemy passes these values through without
change. Psycopg2 here will encode/decode string values based on the
current “client encoding” setting; by default this is the value in
the <tt class="docutils literal"><span class="pre">postgresql.conf</span></tt> file, which often defaults to <tt class="docutils literal"><span class="pre">SQL_ASCII</span></tt>.
Typically, this can be changed to <tt class="docutils literal"><span class="pre">utf8</span></tt>, as a more useful default:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="c"># postgresql.conf file</span>
<span class="c"># client_encoding = sql_ascii # actually, defaults to database</span>
<span class="c"># encoding</span>
<span class="n">client_encoding</span> <span class="o">=</span> <span class="n">utf8</span></pre></div>
</div>
<p>A second way to affect the client encoding is to set it within Psycopg2
locally. SQLAlchemy will call psycopg2’s
<a class="reference external" href="http://pythonhosted.org/psycopg2/connection.html#connection.set_client_encoding" title="(in Psycopg v2.5)"><tt class="docutils literal"><span class="pre">connection.set_client_encoding()</span></tt></a> method
on all new connections based on the value passed to
<a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><tt class="xref py py-func docutils literal"><span class="pre">create_engine()</span></tt></a> using the <tt class="docutils literal"><span class="pre">client_encoding</span></tt> parameter:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="c"># set_client_encoding() setting;</span>
<span class="c"># works for *all* Postgresql versions</span>
<span class="n">engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">"postgresql://user:pass@host/dbname"</span><span class="p">,</span>
<span class="n">client_encoding</span><span class="o">=</span><span class="s">'utf8'</span><span class="p">)</span></pre></div>
</div>
<p>This overrides the encoding specified in the Postgresql client configuration.
When using the parameter in this way, the psycopg2 driver emits
<tt class="docutils literal"><span class="pre">SET</span> <span class="pre">client_encoding</span> <span class="pre">TO</span> <span class="pre">'utf8'</span></tt> on the connection explicitly, and works
in all Postgresql versions.</p>
<p>Note that the <tt class="docutils literal"><span class="pre">client_encoding</span></tt> setting as passed to <a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><tt class="xref py py-func docutils literal"><span class="pre">create_engine()</span></tt></a>
is <strong>not the same</strong> as the more recently added <tt class="docutils literal"><span class="pre">client_encoding</span></tt> parameter
now supported by libpq directly. This is enabled when <tt class="docutils literal"><span class="pre">client_encoding</span></tt>
is passed directly to <tt class="docutils literal"><span class="pre">psycopg2.connect()</span></tt>, and from SQLAlchemy is passed
using the <a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine.params.connect_args" title="sqlalchemy.create_engine"><tt class="xref py py-paramref docutils literal"><span class="pre">create_engine.connect_args</span></tt></a> parameter:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="c"># libpq direct parameter setting;</span>
<span class="c"># only works for Postgresql **9.1 and above**</span>
<span class="n">engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">"postgresql://user:pass@host/dbname"</span><span class="p">,</span>
<span class="n">connect_args</span><span class="o">=</span><span class="p">{</span><span class="s">'client_encoding'</span><span class="p">:</span> <span class="s">'utf8'</span><span class="p">})</span>
<span class="c"># using the query string is equivalent</span>
<span class="n">engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">"postgresql://user:pass@host/dbname?client_encoding=utf8"</span><span class="p">)</span></pre></div>
</div>
<p>The above parameter was only added to libpq as of version 9.1 of Postgresql,
so using the previous method is better for cross-version support.</p>
<div class="section" id="disabling-native-unicode">
<span id="psycopg2-disable-native-unicode"></span><h4>Disabling Native Unicode<a class="headerlink" href="#disabling-native-unicode" title="Permalink to this headline">¶</a></h4>
<p>SQLAlchemy can also be instructed to skip the usage of the psycopg2
<tt class="docutils literal"><span class="pre">UNICODE</span></tt> extension and to instead utilize its own unicode encode/decode
services, which are normally reserved only for those DBAPIs that don’t
fully support unicode directly. Passing <tt class="docutils literal"><span class="pre">use_native_unicode=False</span></tt> to
<a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><tt class="xref py py-func docutils literal"><span class="pre">create_engine()</span></tt></a> will disable usage of <tt class="docutils literal"><span class="pre">psycopg2.extensions.UNICODE</span></tt>.
SQLAlchemy will instead encode data itself into Python bytestrings on the way
in and coerce from bytes on the way back,
using the value of the <a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><tt class="xref py py-func docutils literal"><span class="pre">create_engine()</span></tt></a> <tt class="docutils literal"><span class="pre">encoding</span></tt> parameter, which
defaults to <tt class="docutils literal"><span class="pre">utf-8</span></tt>.
SQLAlchemy’s own unicode encode/decode functionality is steadily becoming
obsolete as most DBAPIs now support unicode fully.</p>
</div>
</div>
<div class="section" id="transactions">
<h3>Transactions<a class="headerlink" href="#transactions" title="Permalink to this headline">¶</a></h3>
<p>The psycopg2 dialect fully supports SAVEPOINT and two-phase commit operations.</p>
</div>
<div class="section" id="psycopg2-transaction-isolation-level">
<span id="psycopg2-isolation-level"></span><h3>Psycopg2 Transaction Isolation Level<a class="headerlink" href="#psycopg2-transaction-isolation-level" title="Permalink to this headline">¶</a></h3>
<p>As discussed in <a class="reference internal" href="#postgresql-isolation-level"><em>Transaction Isolation Level</em></a>,
all Postgresql dialects support setting of transaction isolation level
both via the <tt class="docutils literal"><span class="pre">isolation_level</span></tt> parameter passed to <a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><tt class="xref py py-func docutils literal"><span class="pre">create_engine()</span></tt></a>,
as well as the <tt class="docutils literal"><span class="pre">isolation_level</span></tt> argument used by
<a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Connection.execution_options" title="sqlalchemy.engine.Connection.execution_options"><tt class="xref py py-meth docutils literal"><span class="pre">Connection.execution_options()</span></tt></a>. When using the psycopg2 dialect, these
options make use of psycopg2’s <tt class="docutils literal"><span class="pre">set_isolation_level()</span></tt> connection method,
rather than emitting a Postgresql directive; this is because psycopg2’s
API-level setting is always emitted at the start of each transaction in any
case.</p>
<p>The psycopg2 dialect supports these constants for isolation level:</p>
<ul class="simple">
<li><tt class="docutils literal"><span class="pre">READ</span> <span class="pre">COMMITTED</span></tt></li>
<li><tt class="docutils literal"><span class="pre">READ</span> <span class="pre">UNCOMMITTED</span></tt></li>
<li><tt class="docutils literal"><span class="pre">REPEATABLE</span> <span class="pre">READ</span></tt></li>
<li><tt class="docutils literal"><span class="pre">SERIALIZABLE</span></tt></li>
<li><tt class="docutils literal"><span class="pre">AUTOCOMMIT</span></tt></li>
</ul>
<div class="versionadded">
<p><span>New in version 0.8.2: </span>support for AUTOCOMMIT isolation level when using
psycopg2.</p>
</div>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p><a class="reference internal" href="#postgresql-isolation-level"><em>Transaction Isolation Level</em></a></p>
<p class="last"><a class="reference internal" href="#pg8000-isolation-level"><em>pg8000 Transaction Isolation Level</em></a></p>
</div>
</div>
<div class="section" id="notice-logging">
<h3>NOTICE logging<a class="headerlink" href="#notice-logging" title="Permalink to this headline">¶</a></h3>
<p>The psycopg2 dialect will log Postgresql NOTICE messages via the
<tt class="docutils literal"><span class="pre">sqlalchemy.dialects.postgresql</span></tt> logger:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">import</span> <span class="nn">logging</span>
<span class="n">logging</span><span class="o">.</span><span class="n">getLogger</span><span class="p">(</span><span class="s">'sqlalchemy.dialects.postgresql'</span><span class="p">)</span><span class="o">.</span><span class="n">setLevel</span><span class="p">(</span><span class="n">logging</span><span class="o">.</span><span class="n">INFO</span><span class="p">)</span></pre></div>
</div>
</div>
<div class="section" id="hstore-type">
<h3>HSTORE type<a class="headerlink" href="#hstore-type" title="Permalink to this headline">¶</a></h3>
<p>The <tt class="docutils literal"><span class="pre">psycopg2</span></tt> DBAPI includes an extension to natively handle marshalling of
the HSTORE type. The SQLAlchemy psycopg2 dialect will enable this extension
by default when it is detected that the target database has the HSTORE
type set up for use. In other words, when the dialect makes the first
connection, a sequence like the following is performed:</p>
<ol class="arabic simple">
<li>Request the available HSTORE oids using
<tt class="docutils literal"><span class="pre">psycopg2.extras.HstoreAdapter.get_oids()</span></tt>.
If this function returns a list of HSTORE identifiers, we then determine
that the <tt class="docutils literal"><span class="pre">HSTORE</span></tt> extension is present.</li>
<li>If the <tt class="docutils literal"><span class="pre">use_native_hstore</span></tt> flag is at its default of <tt class="docutils literal"><span class="pre">True</span></tt>, and
we’ve detected that <tt class="docutils literal"><span class="pre">HSTORE</span></tt> oids are available, the
<tt class="docutils literal"><span class="pre">psycopg2.extensions.register_hstore()</span></tt> extension is invoked for all
connections.</li>
</ol>
<p>The <tt class="docutils literal"><span class="pre">register_hstore()</span></tt> extension has the effect of <strong>all Python
dictionaries being accepted as parameters regardless of the type of target
column in SQL</strong>. The dictionaries are converted by this extension into a
textual HSTORE expression. If this behavior is not desired, disable the
use of the hstore extension by setting <tt class="docutils literal"><span class="pre">use_native_hstore</span></tt> to <tt class="docutils literal"><span class="pre">False</span></tt> as
follows:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">"postgresql+psycopg2://scott:tiger@localhost/test"</span><span class="p">,</span>
<span class="n">use_native_hstore</span><span class="o">=</span><span class="bp">False</span><span class="p">)</span></pre></div>
</div>
<p>The <tt class="docutils literal"><span class="pre">HSTORE</span></tt> type is <strong>still supported</strong> when the
<tt class="docutils literal"><span class="pre">psycopg2.extensions.register_hstore()</span></tt> extension is not used. It merely
means that the coercion between Python dictionaries and the HSTORE
string format, on both the parameter side and the result side, will take
place within SQLAlchemy’s own marshalling logic, and not that of <tt class="docutils literal"><span class="pre">psycopg2</span></tt>
which may be more performant.</p>
</div>
</div>
<div class="section" id="module-sqlalchemy.dialects.postgresql.pypostgresql">
<span id="py-postgresql"></span><h2>py-postgresql<a class="headerlink" href="#module-sqlalchemy.dialects.postgresql.pypostgresql" title="Permalink to this headline">¶</a></h2>
<p>Support for the PostgreSQL database via the py-postgresql driver.</p>
<div class="section" id="dialect-postgresql-pypostgresql-url">
<h3>DBAPI<a class="headerlink" href="#dialect-postgresql-pypostgresql-url" title="Permalink to this headline">¶</a></h3>
<p>Documentation and download information (if applicable) for py-postgresql is available at:
<a class="reference external" href="http://python.projects.pgfoundry.org/">http://python.projects.pgfoundry.org/</a></p>
</div>
<div class="section" id="dialect-postgresql-pypostgresql-connect">
<h3>Connecting<a class="headerlink" href="#dialect-postgresql-pypostgresql-connect" title="Permalink to this headline">¶</a></h3>
<p>Connect String:<div class="highlight-python"><pre>postgresql+pypostgresql://user:password@host:port/dbname[?key=value&key=value...]</pre>
</div>
</p>
</div>
</div>
<div class="section" id="module-sqlalchemy.dialects.postgresql.pg8000">
<span id="pg8000"></span><h2>pg8000<a class="headerlink" href="#module-sqlalchemy.dialects.postgresql.pg8000" title="Permalink to this headline">¶</a></h2>
<p>Support for the PostgreSQL database via the pg8000 driver.</p>
<div class="section" id="dialect-postgresql-pg8000-url">
<h3>DBAPI<a class="headerlink" href="#dialect-postgresql-pg8000-url" title="Permalink to this headline">¶</a></h3>
<p>Documentation and download information (if applicable) for pg8000 is available at:
<a class="reference external" href="https://pythonhosted.org/pg8000/">https://pythonhosted.org/pg8000/</a></p>
</div>
<div class="section" id="dialect-postgresql-pg8000-connect">
<h3>Connecting<a class="headerlink" href="#dialect-postgresql-pg8000-connect" title="Permalink to this headline">¶</a></h3>
<p>Connect String:<div class="highlight-python"><pre>postgresql+pg8000://user:password@host:port/dbname[?key=value&key=value...]</pre>
</div>
</p>
</div>
<div class="section" id="unicode">
<h3>Unicode<a class="headerlink" href="#unicode" title="Permalink to this headline">¶</a></h3>
<p>When communicating with the server, pg8000 <strong>always uses the server-side
character set</strong>. SQLAlchemy has no ability to modify what character set
pg8000 chooses to use, and additionally SQLAlchemy does no unicode conversion
of any kind with the pg8000 backend. The origin of the client encoding setting
is ultimately the CLIENT_ENCODING setting in postgresql.conf.</p>
<p>It is not necessary, though is also harmless, to pass the “encoding” parameter
to <a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><tt class="xref py py-func docutils literal"><span class="pre">create_engine()</span></tt></a> when using pg8000.</p>
</div>
<div class="section" id="pg8000-transaction-isolation-level">
<span id="pg8000-isolation-level"></span><h3>pg8000 Transaction Isolation Level<a class="headerlink" href="#pg8000-transaction-isolation-level" title="Permalink to this headline">¶</a></h3>
<p>The pg8000 dialect offers the same isolation level settings as that
of the <a class="reference internal" href="#psycopg2-isolation-level"><em>psycopg2</em></a> dialect:</p>
<ul class="simple">
<li><tt class="docutils literal"><span class="pre">READ</span> <span class="pre">COMMITTED</span></tt></li>
<li><tt class="docutils literal"><span class="pre">READ</span> <span class="pre">UNCOMMITTED</span></tt></li>
<li><tt class="docutils literal"><span class="pre">REPEATABLE</span> <span class="pre">READ</span></tt></li>
<li><tt class="docutils literal"><span class="pre">SERIALIZABLE</span></tt></li>
<li><tt class="docutils literal"><span class="pre">AUTOCOMMIT</span></tt></li>
</ul>
<div class="versionadded">
<p><span>New in version 0.9.5: </span>support for AUTOCOMMIT isolation level when using
pg8000.</p>
</div>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p><a class="reference internal" href="#postgresql-isolation-level"><em>Transaction Isolation Level</em></a></p>
<p class="last"><a class="reference internal" href="#psycopg2-isolation-level"><em>Psycopg2 Transaction Isolation Level</em></a></p>
</div>
</div>
</div>
<div class="section" id="module-sqlalchemy.dialects.postgresql.zxjdbc">
<span id="zxjdbc"></span><h2>zxjdbc<a class="headerlink" href="#module-sqlalchemy.dialects.postgresql.zxjdbc" title="Permalink to this headline">¶</a></h2>
<p>Support for the PostgreSQL database via the zxJDBC for Jython driver.</p>
<div class="section" id="dialect-postgresql-zxjdbc-url">
<h3>DBAPI<a class="headerlink" href="#dialect-postgresql-zxjdbc-url" title="Permalink to this headline">¶</a></h3>
<p>Drivers for this database are available at:
<a class="reference external" href="http://jdbc.postgresql.org/">http://jdbc.postgresql.org/</a></p>
</div>
<div class="section" id="dialect-postgresql-zxjdbc-connect">
<h3>Connecting<a class="headerlink" href="#dialect-postgresql-zxjdbc-connect" title="Permalink to this headline">¶</a></h3>
<p>Connect String:<div class="highlight-python"><pre>postgresql+zxjdbc://scott:tiger@localhost/db</pre>
</div>
</p>
</div>
</div>
</div>
</div>
</div>
<div id="docs-bottom-navigation" class="docs-navigation-links">
Previous:
<a href="oracle.html" title="previous chapter">Oracle</a>
Next:
<a href="sqlite.html" title="next chapter">SQLite</a>
<div id="docs-copyright">
© <a href="../copyright.html">Copyright</a> 2007-2014, the SQLAlchemy authors and contributors.
Created using <a href="http://sphinx.pocoo.org/">Sphinx</a> 1.2b1.
</div>
</div>
</div>
</body>
</html>