- Added support for the :class:`postgresql.JSONB` datatype when

using psycopg2 2.5.4 or greater, which features native conversion
of JSONB data so that SQLAlchemy's converters must be disabled;
additionally, the newly added psycopg2 extension
``extras.register_default_jsonb`` is used to establish a JSON
deserializer passed to the dialect via the ``json_deserializer``
argument.  Also repaired the Postgresql integration tests which
weren't actually round-tripping the JSONB type as opposed to the
JSON type.  Pull request courtesy Mateusz Susik.

- Repaired the use of the "array_oid" flag when registering the
HSTORE type with older psycopg2 versions < 2.4.3, which does not
support this flag, as well as use of the native json serializer
hook "register_default_json" with user-defined ``json_deserializer``
on psycopg2 versions < 2.5, which does not include native json.

1 files changed, 16 insertions, 10 deletions

diff --git a/lib/sqlalchemy/dialects/postgresql/psycopg2.py b/lib/sqlalchemy/dialects/postgresql/psycopg2.py
index 21011b68b..26e45fed2 100644
--- a/lib/sqlalchemy/dialects/postgresql/psycopg2.py
+++ b/lib/sqlalchemy/dialects/postgresql/psycopg2.py
@@ -263,14 +263,17 @@ HSTORE type
 
 The ``psycopg2`` 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
+by default when psycopg2 version 2.4 or greater is used, and
+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:
 
 1. Request the available HSTORE oids using
    ``psycopg2.extras.HstoreAdapter.get_oids()``.
    If this function returns a list of HSTORE identifiers, we then determine
    that the ``HSTORE`` extension is present.
+   This function is **skipped** if the version of psycopg2 installed is
+   less than version 2.4.
 
 2. If the ``use_native_hstore`` flag is at its default of ``True``, and
    we've detected that ``HSTORE`` oids are available, the
@@ -583,19 +586,22 @@ class PGDialect_psycopg2(PGDialect):
                 hstore_oids = self._hstore_oids(conn)
                 if hstore_oids is not None:
                     oid, array_oid = hstore_oids
+                    kw = {'oid': oid}
                     if util.py2k:
-                        extras.register_hstore(conn, oid=oid,
-                                               array_oid=array_oid,
-                                               unicode=True)
-                    else:
-                        extras.register_hstore(conn, oid=oid,
-                                               array_oid=array_oid)
+                        kw['unicode'] = True
+                    if self.psycopg2_version >= (2, 4, 3):
+                        kw['array_oid'] = array_oid
+                    extras.register_hstore(conn, **kw)
             fns.append(on_connect)
 
         if self.dbapi and self._json_deserializer:
             def on_connect(conn):
-                extras.register_default_json(
-                    conn, loads=self._json_deserializer)
+                if self._has_native_json:
+                    extras.register_default_json(
+                        conn, loads=self._json_deserializer)
+                if self._has_native_jsonb:
+                    extras.register_default_jsonb(
+                        conn, loads=self._json_deserializer)
             fns.append(on_connect)
 
         if fns: