Merge branch 'master' into ticket_2501ticket_2501

Conflicts:
	lib/sqlalchemy/orm/mapper.py

10 files changed, 444 insertions, 98 deletions

diff --git a/lib/sqlalchemy/dialects/postgresql/__init__.py b/lib/sqlalchemy/dialects/postgresql/__init__.py
index 408b67846..180e9fc7e 100644
--- a/lib/sqlalchemy/dialects/postgresql/__init__.py
+++ b/lib/sqlalchemy/dialects/postgresql/__init__.py
@@ -1,5 +1,5 @@
 # postgresql/__init__.py
-# Copyright (C) 2005-2013 the SQLAlchemy authors and contributors <see AUTHORS file>
+# Copyright (C) 2005-2014 the SQLAlchemy authors and contributors <see AUTHORS file>
 #
 # This module is part of SQLAlchemy and is released under
 # the MIT License: http://www.opensource.org/licenses/mit-license.php
@@ -11,9 +11,11 @@ base.dialect = psycopg2.dialect
 from .base import \
     INTEGER, BIGINT, SMALLINT, VARCHAR, CHAR, TEXT, NUMERIC, FLOAT, REAL, \
     INET, CIDR, UUID, BIT, MACADDR, DOUBLE_PRECISION, TIMESTAMP, TIME, \
-    DATE, BYTEA, BOOLEAN, INTERVAL, ARRAY, ENUM, dialect, array, Any, All
+    DATE, BYTEA, BOOLEAN, INTERVAL, ARRAY, ENUM, dialect, array, Any, All, \
+    TSVECTOR
 from .constraints import ExcludeConstraint
 from .hstore import HSTORE, hstore
+from .json import JSON, JSONElement
 from .ranges import INT4RANGE, INT8RANGE, NUMRANGE, DATERANGE, TSRANGE, \
     TSTZRANGE
 
@@ -23,5 +25,5 @@ __all__ = (
     'DOUBLE_PRECISION', 'TIMESTAMP', 'TIME', 'DATE', 'BYTEA', 'BOOLEAN',
     'INTERVAL', 'ARRAY', 'ENUM', 'dialect', 'Any', 'All', 'array', 'HSTORE',
     'hstore', 'INT4RANGE', 'INT8RANGE', 'NUMRANGE', 'DATERANGE',
-    'TSRANGE', 'TSTZRANGE'
+    'TSRANGE', 'TSTZRANGE', 'json', 'JSON', 'JSONElement'
 )
diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index 6ccf7190e..11bd3830d 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -1,5 +1,5 @@
 # postgresql/base.py
-# Copyright (C) 2005-2013 the SQLAlchemy authors and contributors <see AUTHORS file>
+# Copyright (C) 2005-2014 the SQLAlchemy authors and contributors <see AUTHORS file>
 #
 # This module is part of SQLAlchemy and is released under
 # the MIT License: http://www.opensource.org/licenses/mit-license.php
@@ -131,6 +131,44 @@ use the :meth:`._UpdateBase.returning` method on a per-statement basis::
         where(table.c.name=='foo')
     print result.fetchall()
 
+.. _postgresql_match:
+
+Full Text Search
+----------------
+
+SQLAlchemy makes available the Postgresql ``@@`` operator via the
+:meth:`.ColumnElement.match` method on any textual column expression.
+On a Postgresql dialect, an expression like the following::
+
+    select([sometable.c.text.match("search string")])
+
+will emit to the database::
+
+    SELECT text @@ to_tsquery('search string') FROM table
+
+The Postgresql text search functions such as ``to_tsquery()``
+and ``to_tsvector()`` are available
+explicitly using the standard :attr:`.func` construct.  For example::
+
+    select([
+        func.to_tsvector('fat cats ate rats').match('cat & rat')
+    ])
+
+Emits the equivalent of::
+
+    SELECT to_tsvector('fat cats ate rats') @@ to_tsquery('cat & rat')
+
+The :class:`.postgresql.TSVECTOR` type can provide for explicit CAST::
+
+    from sqlalchemy.dialects.postgresql import TSVECTOR
+    from sqlalchemy import select, cast
+    select([cast("some text", TSVECTOR)])
+
+produces a statement equivalent to::
+
+    SELECT CAST('some text' AS TSVECTOR) AS anon_1
+
+
 FROM ONLY ...
 ------------------------
 
@@ -210,7 +248,7 @@ import re
 
 from ... import sql, schema, exc, util
 from ...engine import default, reflection
-from ...sql import compiler, expression, util as sql_util, operators
+from ...sql import compiler, expression, operators
 from ... import types as sqltypes
 
 try:
@@ -230,7 +268,7 @@ RESERVED_WORDS = set(
     "default", "deferrable", "desc", "distinct", "do", "else", "end",
     "except", "false", "fetch", "for", "foreign", "from", "grant", "group",
     "having", "in", "initially", "intersect", "into", "leading", "limit",
-    "localtime", "localtimestamp", "new", "not", "null", "off", "offset",
+    "localtime", "localtimestamp", "new", "not", "null", "of", "off", "offset",
     "old", "on", "only", "or", "order", "placing", "primary", "references",
     "returning", "select", "session_user", "some", "symmetric", "table",
     "then", "to", "trailing", "true", "union", "unique", "user", "using",
@@ -368,6 +406,23 @@ class UUID(sqltypes.TypeEngine):
 
 PGUuid = UUID
 
+class TSVECTOR(sqltypes.TypeEngine):
+    """The :class:`.postgresql.TSVECTOR` type implements the Postgresql
+    text search type TSVECTOR.
+
+    It can be used to do full text queries on natural language
+    documents.
+
+    .. versionadded:: 0.9.0
+
+    .. seealso::
+
+        :ref:`postgresql_match`
+
+    """
+    __visit_name__ = 'TSVECTOR'
+
+
 
 class _Slice(expression.ColumnElement):
     __visit_name__ = 'slice'
@@ -913,6 +968,7 @@ ischema_names = {
     'interval': INTERVAL,
     'interval year to month': INTERVAL,
     'interval day to second': INTERVAL,
+    'tsvector' : TSVECTOR
 }
 
 
@@ -954,25 +1010,30 @@ class PGCompiler(compiler.SQLCompiler):
 
     def visit_ilike_op_binary(self, binary, operator, **kw):
         escape = binary.modifiers.get("escape", None)
+
         return '%s ILIKE %s' % \
                 (self.process(binary.left, **kw),
                     self.process(binary.right, **kw)) \
-                + (escape and
-                        (' ESCAPE ' + self.render_literal_value(escape, None))
-                        or '')
+            + (
+                ' ESCAPE ' +
+                self.render_literal_value(escape, sqltypes.STRINGTYPE)
+                if escape else ''
+            )
 
     def visit_notilike_op_binary(self, binary, operator, **kw):
         escape = binary.modifiers.get("escape", None)
         return '%s NOT ILIKE %s' % \
                 (self.process(binary.left, **kw),
                     self.process(binary.right, **kw)) \
-                + (escape and
-                        (' ESCAPE ' + self.render_literal_value(escape, None))
-                        or '')
+            + (
+                ' ESCAPE ' +
+                self.render_literal_value(escape, sqltypes.STRINGTYPE)
+                if escape else ''
+            )
 
     def render_literal_value(self, value, type_):
         value = super(PGCompiler, self).render_literal_value(value, type_)
-        # TODO: need to inspect "standard_conforming_strings"
+
         if self.dialect._backslash_escapes:
             value = value.replace('\\', '\\\\')
         return value
@@ -1009,14 +1070,25 @@ class PGCompiler(compiler.SQLCompiler):
             return ""
 
     def for_update_clause(self, select):
-        if select.for_update == 'nowait':
-            return " FOR UPDATE NOWAIT"
-        elif select.for_update == 'read':
-            return " FOR SHARE"
-        elif select.for_update == 'read_nowait':
-            return " FOR SHARE NOWAIT"
+
+        if select._for_update_arg.read:
+            tmp = " FOR SHARE"
         else:
-            return super(PGCompiler, self).for_update_clause(select)
+            tmp = " FOR UPDATE"
+
+        if select._for_update_arg.of:
+            tables = util.OrderedSet(
+                            c.table if isinstance(c, expression.ColumnClause)
+                            else c for c in select._for_update_arg.of)
+            tmp += " OF " + ", ".join(
+                                self.process(table, ashint=True)
+                                for table in tables
+                            )
+
+        if select._for_update_arg.nowait:
+            tmp += " NOWAIT"
+
+        return tmp
 
     def returning_clause(self, stmt, returning_cols):
 
@@ -1039,12 +1111,15 @@ class PGCompiler(compiler.SQLCompiler):
 
 class PGDDLCompiler(compiler.DDLCompiler):
     def get_column_specification(self, column, **kwargs):
+
         colspec = self.preparer.format_column(column)
         impl_type = column.type.dialect_impl(self.dialect)
         if column.primary_key and \
             column is column.table._autoincrement_column and \
-            not isinstance(impl_type, sqltypes.SmallInteger) and \
             (
+                self.dialect.supports_smallserial or
+                not isinstance(impl_type, sqltypes.SmallInteger)
+            ) and (
                 column.default is None or
                 (
                     isinstance(column.default, schema.Sequence) and
@@ -1052,6 +1127,8 @@ class PGDDLCompiler(compiler.DDLCompiler):
                 )):
             if isinstance(impl_type, sqltypes.BigInteger):
                 colspec += " BIGSERIAL"
+            elif isinstance(impl_type, sqltypes.SmallInteger):
+                colspec += " SMALLSERIAL"
             else:
                 colspec += " SERIAL"
         else:
@@ -1069,7 +1146,9 @@ class PGDDLCompiler(compiler.DDLCompiler):
 
         return "CREATE TYPE %s AS ENUM (%s)" % (
             self.preparer.format_type(type_),
-            ",".join("'%s'" % e for e in type_.enums)
+            ", ".join(
+                self.sql_compiler.process(sql.literal(e), literal_binds=True)
+                        for e in type_.enums)
         )
 
     def visit_drop_enum_type(self, drop):
@@ -1092,31 +1171,29 @@ class PGDDLCompiler(compiler.DDLCompiler):
                     preparer.format_table(index.table)
                 )
 
-        if 'postgresql_using' in index.kwargs:
-            using = index.kwargs['postgresql_using']
-            text += "USING %s " % preparer.quote(using, index.quote)
+        using = index.dialect_options['postgresql']['using']
+        if using:
+            text += "USING %s " % preparer.quote(using)
 
-        ops = index.kwargs.get('postgresql_ops', {})
+        ops = index.dialect_options["postgresql"]["ops"]
         text += "(%s)" \
                 % (
                     ', '.join([
-                        self.sql_compiler.process(expr, include_table=False) +
-
-
+                        self.sql_compiler.process(
+                                expr.self_group()
+                                if not isinstance(expr, expression.ColumnClause)
+                                    else expr,
+                                include_table=False, literal_binds=True) +
                         (c.key in ops and (' ' + ops[c.key]) or '')
-
-
                         for expr, c in zip(index.expressions, index.columns)])
                     )
 
-        if 'postgresql_where' in index.kwargs:
-            whereclause = index.kwargs['postgresql_where']
-        else:
-            whereclause = None
+        whereclause = index.dialect_options["postgresql"]["where"]
 
         if whereclause is not None:
-            whereclause = sql_util.expression_as_ddl(whereclause)
-            where_compiled = self.sql_compiler.process(whereclause)
+            where_compiled = self.sql_compiler.process(
+                                    whereclause, include_table=False,
+                                    literal_binds=True)
             text += " WHERE " + where_compiled
         return text
 
@@ -1128,16 +1205,20 @@ class PGDDLCompiler(compiler.DDLCompiler):
         elements = []
         for c in constraint.columns:
             op = constraint.operators[c.name]
-            elements.append(self.preparer.quote(c.name, c.quote)+' WITH '+op)
+            elements.append(self.preparer.quote(c.name) + ' WITH '+op)
         text += "EXCLUDE USING %s (%s)" % (constraint.using, ', '.join(elements))
         if constraint.where is not None:
-            sqltext = sql_util.expression_as_ddl(constraint.where)
-            text += ' WHERE (%s)' % self.sql_compiler.process(sqltext)
+            text += ' WHERE (%s)' % self.sql_compiler.process(
+                                            constraint.where,
+                                            literal_binds=True)
         text += self.define_constraint_deferrability(constraint)
         return text
 
 
 class PGTypeCompiler(compiler.GenericTypeCompiler):
+    def visit_TSVECTOR(self, type):
+        return "TSVECTOR"
+
     def visit_INET(self, type_):
         return "INET"
 
@@ -1162,6 +1243,9 @@ class PGTypeCompiler(compiler.GenericTypeCompiler):
     def visit_HSTORE(self, type_):
         return "HSTORE"
 
+    def visit_JSON(self, type_):
+        return "JSON"
+
     def visit_INT4RANGE(self, type_):
         return "INT4RANGE"
 
@@ -1250,9 +1334,9 @@ class PGIdentifierPreparer(compiler.IdentifierPreparer):
         if not type_.name:
             raise exc.CompileError("Postgresql ENUM type requires a name.")
 
-        name = self.quote(type_.name, type_.quote)
+        name = self.quote(type_.name)
         if not self.omit_schema and use_schema and type_.schema is not None:
-            name = self.quote_schema(type_.schema, type_.quote) + "." + name
+            name = self.quote_schema(type_.schema) + "." + name
         return name
 
 
@@ -1328,6 +1412,7 @@ class PGDialect(default.DefaultDialect):
 
     supports_native_enum = True
     supports_native_boolean = True
+    supports_smallserial = True
 
     supports_sequences = True
     sequences_optional = True
@@ -1349,12 +1434,22 @@ class PGDialect(default.DefaultDialect):
     inspector = PGInspector
     isolation_level = None
 
-    # TODO: need to inspect "standard_conforming_strings"
+    construct_arguments = [
+        (schema.Index, {
+            "using": False,
+            "where": None,
+            "ops": {}
+        })
+    ]
+
     _backslash_escapes = True
 
-    def __init__(self, isolation_level=None, **kwargs):
+    def __init__(self, isolation_level=None, json_serializer=None,
+                    json_deserializer=None, **kwargs):
         default.DefaultDialect.__init__(self, **kwargs)
         self.isolation_level = isolation_level
+        self._json_deserializer = json_deserializer
+        self._json_serializer = json_serializer
 
     def initialize(self, connection):
         super(PGDialect, self).initialize(connection)
@@ -1368,6 +1463,13 @@ class PGDialect(default.DefaultDialect):
             # psycopg2, others may have placed ENUM here as well
             self.colspecs.pop(ENUM, None)
 
+        # http://www.postgresql.org/docs/9.3/static/release-9-2.html#AEN116689
+        self.supports_smallserial = self.server_version_info >= (9, 2)
+
+        self._backslash_escapes = connection.scalar(
+                                    "show standard_conforming_strings"
+                                    ) == 'off'
+
     def on_connect(self):
         if self.isolation_level is not None:
             def connect(conn):
@@ -1515,12 +1617,6 @@ class PGDialect(default.DefaultDialect):
         return bool(cursor.first())
 
     def has_type(self, connection, type_name, schema=None):
-        bindparams = [
-            sql.bindparam('typname',
-                util.text_type(type_name), type_=sqltypes.Unicode),
-            sql.bindparam('nspname',
-                util.text_type(schema), type_=sqltypes.Unicode),
-            ]
         if schema is not None:
             query = """
             SELECT EXISTS (
@@ -1530,6 +1626,7 @@ class PGDialect(default.DefaultDialect):
                 AND n.nspname = :nspname
                 )
                 """
+            query = sql.text(query)
         else:
             query = """
             SELECT EXISTS (
@@ -1538,13 +1635,23 @@ class PGDialect(default.DefaultDialect):
                 AND pg_type_is_visible(t.oid)
                 )
                 """
-        cursor = connection.execute(sql.text(query, bindparams=bindparams))
+            query = sql.text(query)
+        query = query.bindparams(
+                sql.bindparam('typname',
+                    util.text_type(type_name), type_=sqltypes.Unicode),
+                )
+        if schema is not None:
+            query = query.bindparams(
+                    sql.bindparam('nspname',
+                        util.text_type(schema), type_=sqltypes.Unicode),
+                    )
+        cursor = connection.execute(query)
         return bool(cursor.scalar())
 
     def _get_server_version_info(self, connection):
         v = connection.execute("select version()").scalar()
         m = re.match(
-            '(?:PostgreSQL|EnterpriseDB) '
+            '.*(?:PostgreSQL|EnterpriseDB) '
             '(\d+)\.(\d+)(?:\.(\d+))?(?:\.\d+)?(?:devel)?',
             v)
         if not m:
@@ -1578,12 +1685,10 @@ class PGDialect(default.DefaultDialect):
         table_name = util.text_type(table_name)
         if schema is not None:
             schema = util.text_type(schema)
-        s = sql.text(query, bindparams=[
-            sql.bindparam('table_name', type_=sqltypes.Unicode),
-            sql.bindparam('schema', type_=sqltypes.Unicode)
-            ],
-            typemap={'oid': sqltypes.Integer}
-        )
+        s = sql.text(query).bindparams(table_name=sqltypes.Unicode)
+        s = s.columns(oid=sqltypes.Integer)
+        if schema:
+            s = s.bindparams(sql.bindparam('schema', type_=sqltypes.Unicode))
         c = connection.execute(s, table_name=table_name, schema=schema)
         table_oid = c.scalar()
         if table_oid is None:
@@ -1675,8 +1780,7 @@ class PGDialect(default.DefaultDialect):
         SQL_COLS = """
             SELECT a.attname,
               pg_catalog.format_type(a.atttypid, a.atttypmod),
-              (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid)
-                for 128)
+              (SELECT pg_catalog.pg_get_expr(d.adbin, d.adrelid)
                 FROM pg_catalog.pg_attrdef d
                WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum
                AND a.atthasdef)
@@ -1883,6 +1987,15 @@ class PGDialect(default.DefaultDialect):
                 n.oid = c.relnamespace
           ORDER BY 1
         """
+        # http://www.postgresql.org/docs/9.0/static/sql-createtable.html
+        FK_REGEX = re.compile(
+            r'FOREIGN KEY \((.*?)\) REFERENCES (?:(.*?)\.)?(.*?)\((.*?)\)'
+            r'[\s]?(MATCH (FULL|PARTIAL|SIMPLE)+)?'
+            r'[\s]?(ON UPDATE (CASCADE|RESTRICT|NO ACTION|SET NULL|SET DEFAULT)+)?'
+            r'[\s]?(ON DELETE (CASCADE|RESTRICT|NO ACTION|SET NULL|SET DEFAULT)+)?'
+            r'[\s]?(DEFERRABLE|NOT DEFERRABLE)?'
+            r'[\s]?(INITIALLY (DEFERRED|IMMEDIATE)+)?'
+        )
 
         t = sql.text(FK_SQL, typemap={
                                 'conname': sqltypes.Unicode,
@@ -1890,15 +2003,18 @@ class PGDialect(default.DefaultDialect):
         c = connection.execute(t, table=table_oid)
         fkeys = []
         for conname, condef, conschema in c.fetchall():
-            m = re.search('FOREIGN KEY \((.*?)\) REFERENCES '
-                            '(?:(.*?)\.)?(.*?)\((.*?)\)', condef).groups()
+            m = re.search(FK_REGEX, condef).groups()
             constrained_columns, referred_schema, \
-                    referred_table, referred_columns = m
+                    referred_table, referred_columns, \
+                    _, match, _, onupdate, _, ondelete, \
+                    deferrable, _, initially = m
+            if deferrable is not None:
+                deferrable = True if deferrable == 'DEFERRABLE' else False
             constrained_columns = [preparer._unquote_identifier(x)
                         for x in re.split(r'\s*,\s*', constrained_columns)]
 
             if referred_schema:
-                referred_schema =\
+                referred_schema = \
                                 preparer._unquote_identifier(referred_schema)
             elif schema is not None and schema == conschema:
                 # no schema was returned by pg_get_constraintdef().  This
@@ -1916,7 +2032,14 @@ class PGDialect(default.DefaultDialect):
                 'constrained_columns': constrained_columns,
                 'referred_schema': referred_schema,
                 'referred_table': referred_table,
-                'referred_columns': referred_columns
+                'referred_columns': referred_columns,
+                'options': {
+                    'onupdate': onupdate,
+                    'ondelete': ondelete,
+                    'deferrable': deferrable,
+                    'initially': initially,
+                    'match': match
+                }
             }
             fkeys.append(fkey_d)
         return fkeys
@@ -1926,11 +2049,14 @@ class PGDialect(default.DefaultDialect):
         table_oid = self.get_table_oid(connection, table_name, schema,
                                        info_cache=kw.get('info_cache'))
 
+        # cast indkey as varchar since it's an int2vector,
+        # returned as a list by some drivers such as pypostgresql
+
         IDX_SQL = """
           SELECT
               i.relname as relname,
               ix.indisunique, ix.indexprs, ix.indpred,
-              a.attname, a.attnum, ix.indkey
+              a.attname, a.attnum, ix.indkey::varchar
           FROM
               pg_class t
                     join pg_index ix on t.oid = ix.indrelid
diff --git a/lib/sqlalchemy/dialects/postgresql/constraints.py b/lib/sqlalchemy/dialects/postgresql/constraints.py
index 5b8bbe643..f45cef1a2 100644
--- a/lib/sqlalchemy/dialects/postgresql/constraints.py
+++ b/lib/sqlalchemy/dialects/postgresql/constraints.py
@@ -1,4 +1,4 @@
-# Copyright (C) 2013 the SQLAlchemy authors and contributors <see AUTHORS file>
+# Copyright (C) 2013-2014 the SQLAlchemy authors and contributors <see AUTHORS file>
 #
 # This module is part of SQLAlchemy and is released under
 # the MIT License: http://www.opensource.org/licenses/mit-license.php
@@ -39,7 +39,7 @@ class ExcludeConstraint(ColumnCollectionConstraint):
         :param using:
           Optional string.  If set, emit USING <index_method> when issuing DDL
           for this constraint. Defaults to 'gist'.
-          
+
         :param where:
           Optional string.  If set, emit WHERE <predicate> when issuing DDL
           for this constraint.
@@ -60,7 +60,7 @@ class ExcludeConstraint(ColumnCollectionConstraint):
         where = kw.get('where')
         if where:
             self.where =  expression._literal_as_text(where)
-            
+
     def copy(self, **kw):
         elements = [(col, self.operators[col])
                     for col in self.columns.keys()]
diff --git a/lib/sqlalchemy/dialects/postgresql/hstore.py b/lib/sqlalchemy/dialects/postgresql/hstore.py
index c645e25d2..76562088d 100644
--- a/lib/sqlalchemy/dialects/postgresql/hstore.py
+++ b/lib/sqlalchemy/dialects/postgresql/hstore.py
@@ -1,5 +1,5 @@
 # postgresql/hstore.py
-# Copyright (C) 2005-2013 the SQLAlchemy authors and contributors <see AUTHORS file>
+# Copyright (C) 2005-2014 the SQLAlchemy authors and contributors <see AUTHORS file>
 #
 # This module is part of SQLAlchemy and is released under
 # the MIT License: http://www.opensource.org/licenses/mit-license.php
@@ -144,8 +144,10 @@ class HSTORE(sqltypes.Concatenable, sqltypes.TypeEngine):
     For usage with the SQLAlchemy ORM, it may be desirable to combine
     the usage of :class:`.HSTORE` with :class:`.MutableDict` dictionary
     now part of the :mod:`sqlalchemy.ext.mutable`
-    extension.  This extension will allow in-place changes to dictionary
-    values to be detected by the unit of work::
+    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::
 
         from sqlalchemy.ext.mutable import MutableDict
 
@@ -163,6 +165,11 @@ class HSTORE(sqltypes.Concatenable, sqltypes.TypeEngine):
 
         session.commit()
 
+    When the :mod:`sqlalchemy.ext.mutable` 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.
+
     .. versionadded:: 0.8
 
     .. seealso::
diff --git a/lib/sqlalchemy/dialects/postgresql/json.py b/lib/sqlalchemy/dialects/postgresql/json.py
new file mode 100644
index 000000000..2e29185e8
--- /dev/null
+++ b/lib/sqlalchemy/dialects/postgresql/json.py
@@ -0,0 +1,199 @@
+# postgresql/json.py
+# Copyright (C) 2005-2014 the SQLAlchemy authors and contributors <see AUTHORS file>
+#
+# This module is part of SQLAlchemy and is released under
+# the MIT License: http://www.opensource.org/licenses/mit-license.php
+from __future__ import absolute_import
+
+import json
+
+from .base import ischema_names
+from ... import types as sqltypes
+from ...sql.operators import custom_op
+from ... import sql
+from ...sql import elements
+from ... import util
+
+__all__ = ('JSON', 'JSONElement')
+
+
+class JSONElement(elements.BinaryExpression):
+    """Represents accessing an element of a :class:`.JSON` value.
+
+    The :class:`.JSONElement` is produced whenever using the Python index
+    operator on an expression that has the type :class:`.JSON`::
+
+        expr = mytable.c.json_data['some_key']
+
+    The expression typically compiles to a JSON access such as ``col -> key``.
+    Modifiers are then available for typing behavior, including :meth:`.JSONElement.cast`
+    and :attr:`.JSONElement.astext`.
+
+    """
+    def __init__(self, left, right, astext=False, opstring=None, result_type=None):
+        self._astext = astext
+        if opstring is None:
+            if hasattr(right, '__iter__') and \
+                not isinstance(right, util.string_types):
+                opstring = "#>"
+                right = "{%s}" % (", ".join(util.text_type(elem) for elem in right))
+            else:
+                opstring = "->"
+
+        self._json_opstring = opstring
+        operator = custom_op(opstring, precedence=5)
+        right = left._check_literal(left, operator, right)
+        super(JSONElement, self).__init__(left, right, operator, type_=result_type)
+
+    @property
+    def astext(self):
+        """Convert this :class:`.JSONElement` to use the 'astext' operator
+        when evaluated.
+
+        E.g.::
+
+            select([data_table.c.data['some key'].astext])
+
+        .. seealso::
+
+            :meth:`.JSONElement.cast`
+
+        """
+        if self._astext:
+            return self
+        else:
+            return JSONElement(
+                    self.left,
+                    self.right,
+                    astext=True,
+                    opstring=self._json_opstring + ">",
+                    result_type=sqltypes.String(convert_unicode=True)
+                )
+
+    def cast(self, type_):
+        """Convert this :class:`.JSONElement` to apply both the 'astext' operator
+        as well as an explicit type cast when evaulated.
+
+        E.g.::
+
+            select([data_table.c.data['some key'].cast(Integer)])
+
+        .. seealso::
+
+            :attr:`.JSONElement.astext`
+
+        """
+        if not self._astext:
+            return self.astext.cast(type_)
+        else:
+            return sql.cast(self, type_)
+
+
+class JSON(sqltypes.TypeEngine):
+    """Represent the Postgresql JSON type.
+
+    The :class:`.JSON` type stores arbitrary JSON format data, e.g.::
+
+        data_table = Table('data_table', metadata,
+            Column('id', Integer, primary_key=True),
+            Column('data', JSON)
+        )
+
+        with engine.connect() as conn:
+            conn.execute(
+                data_table.insert(),
+                data = {"key1": "value1", "key2": "value2"}
+            )
+
+    :class:`.JSON` provides several operations:
+
+    * Index operations::
+
+        data_table.c.data['some key']
+
+    * Index operations returning text (required for text comparison)::
+
+        data_table.c.data['some key'].astext == 'some value'
+
+    * Index operations with a built-in CAST call::
+
+        data_table.c.data['some key'].cast(Integer) == 5
+
+    * Path index operations::
+
+        data_table.c.data[('key_1', 'key_2', ..., 'key_n')]
+
+    * Path index operations returning text (required for text comparison)::
+
+        data_table.c.data[('key_1', 'key_2', ..., 'key_n')].astext == 'some value'
+
+    Index operations return an instance of :class:`.JSONElement`, which represents
+    an expression such as ``column -> index``.  This element then defines
+    methods such as :attr:`.JSONElement.astext` and :meth:`.JSONElement.cast`
+    for setting up type behavior.
+
+    The :class:`.JSON` type, when used with the SQLAlchemy ORM, does not detect
+    in-place mutations to the structure.  In order to detect these, the
+    :mod:`sqlalchemy.ext.mutable` 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 :class:`.HSTORE`
+    for a simple example involving a dictionary.
+
+    Custom serializers and deserializers are specified at the dialect level,
+    that is using :func:`.create_engine`.  The reason for this is that when
+    using psycopg2, the DBAPI only allows serializers at the per-cursor
+    or per-connection level.   E.g.::
+
+        engine = create_engine("postgresql://scott:tiger@localhost/test",
+                                json_serializer=my_serialize_fn,
+                                json_deserializer=my_deserialize_fn
+                        )
+
+    When using the psycopg2 dialect, the json_deserializer is registered
+    against the database using ``psycopg2.extras.register_default_json``.
+
+    .. versionadded:: 0.9
+
+    """
+
+    __visit_name__ = 'JSON'
+
+    class comparator_factory(sqltypes.Concatenable.Comparator):
+        """Define comparison operations for :class:`.JSON`."""
+
+        def __getitem__(self, other):
+            """Get the value at a given key."""
+
+            return JSONElement(self.expr, other)
+
+        def _adapt_expression(self, op, other_comparator):
+            if isinstance(op, custom_op):
+                if op.opstring == '->':
+                    return op, sqltypes.Text
+            return sqltypes.Concatenable.Comparator.\
+                _adapt_expression(self, op, other_comparator)
+
+    def bind_processor(self, dialect):
+        json_serializer = dialect._json_serializer or json.dumps
+        if util.py2k:
+            encoding = dialect.encoding
+            def process(value):
+                return json_serializer(value).encode(encoding)
+        else:
+            def process(value):
+                return json_serializer(value)
+        return process
+
+    def result_processor(self, dialect, coltype):
+        json_deserializer = dialect._json_deserializer or json.loads
+        if util.py2k:
+            encoding = dialect.encoding
+            def process(value):
+                return json_deserializer(value.decode(encoding))
+        else:
+            def process(value):
+                return json_deserializer(value)
+        return process
+
+
+ischema_names['json'] = JSON
diff --git a/lib/sqlalchemy/dialects/postgresql/pg8000.py b/lib/sqlalchemy/dialects/postgresql/pg8000.py
index 0e503746c..bc73f9757 100644
--- a/lib/sqlalchemy/dialects/postgresql/pg8000.py
+++ b/lib/sqlalchemy/dialects/postgresql/pg8000.py
@@ -1,5 +1,5 @@
 # postgresql/pg8000.py
-# Copyright (C) 2005-2013 the SQLAlchemy authors and contributors <see AUTHORS file>
+# Copyright (C) 2005-2014 the SQLAlchemy authors and contributors <see AUTHORS file>
 #
 # This module is part of SQLAlchemy and is released under
 # the MIT License: http://www.opensource.org/licenses/mit-license.php
@@ -39,7 +39,9 @@ class _PGNumeric(sqltypes.Numeric):
     def result_processor(self, dialect, coltype):
         if self.asdecimal:
             if coltype in _FLOAT_TYPES:
-                return processors.to_decimal_processor_factory(decimal.Decimal)
+                return processors.to_decimal_processor_factory(
+                                    decimal.Decimal,
+                                    self._effective_decimal_return_scale)
             elif coltype in _DECIMAL_TYPES or coltype in _INT_TYPES:
                 # pg8000 returns Decimal natively for 1700
                 return None
diff --git a/lib/sqlalchemy/dialects/postgresql/psycopg2.py b/lib/sqlalchemy/dialects/postgresql/psycopg2.py
index 02eda094e..e9f64f829 100644
--- a/lib/sqlalchemy/dialects/postgresql/psycopg2.py
+++ b/lib/sqlalchemy/dialects/postgresql/psycopg2.py
@@ -1,5 +1,5 @@
 # postgresql/psycopg2.py
-# Copyright (C) 2005-2013 the SQLAlchemy authors and contributors <see AUTHORS file>
+# Copyright (C) 2005-2014 the SQLAlchemy authors and contributors <see AUTHORS file>
 #
 # This module is part of SQLAlchemy and is released under
 # the MIT License: http://www.opensource.org/licenses/mit-license.php
@@ -179,6 +179,7 @@ from .base import PGDialect, PGCompiler, \
                                 ENUM, ARRAY, _DECIMAL_TYPES, _FLOAT_TYPES,\
                                 _INT_TYPES
 from .hstore import HSTORE
+from .json import JSON
 
 
 logger = logging.getLogger('sqlalchemy.dialects.postgresql')
@@ -191,7 +192,9 @@ class _PGNumeric(sqltypes.Numeric):
     def result_processor(self, dialect, coltype):
         if self.asdecimal:
             if coltype in _FLOAT_TYPES:
-                return processors.to_decimal_processor_factory(decimal.Decimal)
+                return processors.to_decimal_processor_factory(
+                                decimal.Decimal,
+                                self._effective_decimal_return_scale)
             elif coltype in _DECIMAL_TYPES or coltype in _INT_TYPES:
                 # pg8000 returns Decimal natively for 1700
                 return None
@@ -210,23 +213,13 @@ class _PGNumeric(sqltypes.Numeric):
 
 
 class _PGEnum(ENUM):
-    def __init__(self, *arg, **kw):
-        super(_PGEnum, self).__init__(*arg, **kw)
-        if util.py2k:
-            if self.convert_unicode:
-                self.convert_unicode = "force"
-
-
-class _PGArray(ARRAY):
-    def __init__(self, *arg, **kw):
-        super(_PGArray, self).__init__(*arg, **kw)
-        if util.py2k:
-            # FIXME: this check won't work for setups that
-            # have convert_unicode only on their create_engine().
-            if isinstance(self.item_type, sqltypes.String) and \
-                        self.item_type.convert_unicode:
-                self.item_type.convert_unicode = "force"
-
+    def result_processor(self, dialect, coltype):
+        if util.py2k and self.convert_unicode is True:
+            # we can't easily use PG's extensions here because
+            # the OID is on the fly, and we need to give it a python
+            # function anyway - not really worth it.
+            self.convert_unicode = "force_nocheck"
+        return super(_PGEnum, self).result_processor(dialect, coltype)
 
 class _PGHStore(HSTORE):
     def bind_processor(self, dialect):
@@ -241,6 +234,15 @@ class _PGHStore(HSTORE):
         else:
             return super(_PGHStore, self).result_processor(dialect, coltype)
 
+
+class _PGJSON(JSON):
+
+    def result_processor(self, dialect, coltype):
+        if dialect._has_native_json:
+            return None
+        else:
+            return super(_PGJSON, self).result_processor(dialect, coltype)
+
 # When we're handed literal SQL, ensure it's a SELECT-query. Since
 # 8.3, combining cursors and "FOR UPDATE" has been fine.
 SERVER_SIDE_CURSOR_RE = re.compile(
@@ -325,6 +327,7 @@ class PGDialect_psycopg2(PGDialect):
     psycopg2_version = (0, 0)
 
     _has_native_hstore = False
+    _has_native_json = False
 
     colspecs = util.update_copy(
         PGDialect.colspecs,
@@ -332,8 +335,8 @@ class PGDialect_psycopg2(PGDialect):
             sqltypes.Numeric: _PGNumeric,
             ENUM: _PGEnum,  # needs force_unicode
             sqltypes.Enum: _PGEnum,  # needs force_unicode
-            ARRAY: _PGArray,  # needs force_unicode
             HSTORE: _PGHStore,
+            JSON: _PGJSON
         }
     )
 
@@ -361,6 +364,7 @@ class PGDialect_psycopg2(PGDialect):
         self._has_native_hstore = self.use_native_hstore and \
                         self._hstore_oids(connection.connection) \
                             is not None
+        self._has_native_json = self.psycopg2_version >= (2, 5)
 
     @classmethod
     def dbapi(cls):
@@ -369,7 +373,7 @@ class PGDialect_psycopg2(PGDialect):
 
     @util.memoized_property
     def _isolation_lookup(self):
-        extensions = __import__('psycopg2.extensions').extensions
+        from psycopg2 import extensions
         return {
             'AUTOCOMMIT': extensions.ISOLATION_LEVEL_AUTOCOMMIT,
             'READ COMMITTED': extensions.ISOLATION_LEVEL_READ_COMMITTED,
@@ -407,6 +411,7 @@ class PGDialect_psycopg2(PGDialect):
         if self.dbapi and self.use_native_unicode:
             def on_connect(conn):
                 extensions.register_type(extensions.UNICODE, conn)
+                extensions.register_type(extensions.UNICODEARRAY, conn)
             fns.append(on_connect)
 
         if self.dbapi and self.use_native_hstore:
@@ -423,6 +428,11 @@ class PGDialect_psycopg2(PGDialect):
                                         array_oid=array_oid)
             fns.append(on_connect)
 
+        if self.dbapi and self._json_deserializer:
+            def on_connect(conn):
+                extras.register_default_json(conn, loads=self._json_deserializer)
+            fns.append(on_connect)
+
         if fns:
             def on_connect(conn):
                 for fn in fns:
diff --git a/lib/sqlalchemy/dialects/postgresql/pypostgresql.py b/lib/sqlalchemy/dialects/postgresql/pypostgresql.py
index 289bef114..f030d2c1b 100644
--- a/lib/sqlalchemy/dialects/postgresql/pypostgresql.py
+++ b/lib/sqlalchemy/dialects/postgresql/pypostgresql.py
@@ -1,5 +1,5 @@
 # postgresql/pypostgresql.py
-# Copyright (C) 2005-2013 the SQLAlchemy authors and contributors <see AUTHORS file>
+# Copyright (C) 2005-2014 the SQLAlchemy authors and contributors <see AUTHORS file>
 #
 # This module is part of SQLAlchemy and is released under
 # the MIT License: http://www.opensource.org/licenses/mit-license.php
diff --git a/lib/sqlalchemy/dialects/postgresql/ranges.py b/lib/sqlalchemy/dialects/postgresql/ranges.py
index d03f948a7..57b0c4c30 100644
--- a/lib/sqlalchemy/dialects/postgresql/ranges.py
+++ b/lib/sqlalchemy/dialects/postgresql/ranges.py
@@ -1,4 +1,4 @@
-# Copyright (C) 2013 the SQLAlchemy authors and contributors <see AUTHORS file>
+# Copyright (C) 2013-2014 the SQLAlchemy authors and contributors <see AUTHORS file>
 #
 # This module is part of SQLAlchemy and is released under
 # the MIT License: http://www.opensource.org/licenses/mit-license.php
diff --git a/lib/sqlalchemy/dialects/postgresql/zxjdbc.py b/lib/sqlalchemy/dialects/postgresql/zxjdbc.py
index 583afc23f..67e7d53e6 100644
--- a/lib/sqlalchemy/dialects/postgresql/zxjdbc.py
+++ b/lib/sqlalchemy/dialects/postgresql/zxjdbc.py
@@ -1,5 +1,5 @@
 # postgresql/zxjdbc.py
-# Copyright (C) 2005-2013 the SQLAlchemy authors and contributors <see AUTHORS file>
+# Copyright (C) 2005-2014 the SQLAlchemy authors and contributors <see AUTHORS file>
 #
 # This module is part of SQLAlchemy and is released under
 # the MIT License: http://www.opensource.org/licenses/mit-license.php