{"action":"created","issue":{"url":"https://api.github.com/repos/tminglei/slick-pg/issues/141","labels_url":"https://api.github.com/repos/tminglei/slick-pg/issues/141/labels{/name}","comments_url":"https://api.github.com/repos/tminglei/slick-pg/issues/141/comments","events_url":"https://api.github.com/repos/tminglei/slick-pg/issues/141/events","html_url":"https://github.com/tminglei/slick-pg/issues/141","id":64038860,"number":141,"title":"Json works, jsonb doesnt, may be due to the type mapper","user":{"login":"yvesnyc","id":2157722,"avatar_url":"https://avatars.githubusercontent.com/u/2157722?v=3","gravatar_id":"","url":"https://api.github.com/users/yvesnyc","html_url":"https://github.com/yvesnyc","followers_url":"https://api.github.com/users/yvesnyc/followers","following_url":"https://api.github.com/users/yvesnyc/following{/other_user}","gists_url":"https://api.github.com/users/yvesnyc/gists{/gist_id}","starred_url":"https://api.github.com/users/yvesnyc/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/yvesnyc/subscriptions","organizations_url":"https://api.github.com/users/yvesnyc/orgs","repos_url":"https://api.github.com/users/yvesnyc/repos","events_url":"https://api.github.com/users/yvesnyc/events{/privacy}","received_events_url":"https://api.github.com/users/yvesnyc/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":5,"created_at":"2015-03-24T16:13:07Z","updated_at":"2015-03-25T13:29:56Z","closed_at":null,"body":"Thanks for slick-pg.\r\n\r\nIt works for json tables. However, I've been unable to get the jsonb support to work. It seems the type mapper fails when using jsonb. \r\n\r\nFor json I see the type map: com.github.tminglei.slickpg.utils.PgCommonJdbcTypes$Generic/json. \r\nFor jsonb I see the type map: com.github.tminglei.slickpg.utils.PgCommonJdbcTypes$Generic/null. The Postgres driver then fails on the \"null\" type. Below is more data.\r\n\r\nHere is my Driver which works when using json. Followed by the build.sbt configuration. I'm using slick-pg 0.84.\r\n\r\n```\r\n------------slickExtendedDriver----\r\n\r\nimport com.github.tminglei.slickpg._\r\nimport slick.driver.PostgresDriver\r\n\r\ntrait slickExtendedPostgresDriver extends PostgresDriver with PgJson4sSupport {\r\n\r\noverride lazy val Implicit = new ImplicitsPlus {}\r\noverride val simple = new SimpleQLPlus {}\r\n\r\n// JsonB\r\n// override val pgjson = \"jsonb\" //to keep back compatibility, pgjson's value was \"json\" by default\r\n\r\ntrait ImplicitsPlus extends Implicits with JsonImplicits\r\ntrait SimpleQLPlus extends SimpleQL with ImplicitsPlus\r\n/// for json support\r\ntype DOCType = text.Document\r\noverride val jsonMethods = org.json4s.native.JsonMethods\r\n}\r\nobject myDriverPostgresDriver extends slickExtendedPostgresDriver\r\n-----------------------------------------\r\n```\r\n\r\n```\r\n-------------build.sbt-----------------\r\n \"org.json4s\" %% \"json4s-native\" % \"3.2.10\",\r\n  \"org.json4s\" %% \"json4s-jackson\" % \"3.2.10\",\r\n  \"org.json4s\" %% \"json4s-ast\" % \"3.2.10\",\r\n\"com.typesafe.slick\" %% \"slick\" % \"2.1.0\",\r\n\"org.postgresql\" % \"postgresql\" % \"9.4-1201-jdbc4\",\r\n\"com.github.tminglei\" %% \"slick-pg\" % \"0.8.4\"\r\n-----------------------------------------\r\n```\r\n\r\nIn debug mode I see messages from the slick compiler\r\n```\r\n11:37:32.842 TKD [main] DEBUG scala.slick.compiler.QueryCompiler - After phase assignUniqueSymbols:\r\n| TableExpansion\r\n| table s2: Table repository : Coll[scala.collection.immutable.Vector][NominalType(@(t3))(UnassignedStructuralType(@(t3)))]\r\n| columns: TypeMapping\r\n| 0: ProductNode\r\n| 1: Path s2.url : String/VARCHAR(254)\r\n| 2: Path s2.id : Int/INTEGER\r\n| 3: Path s2.branch : Option[String/VARCHAR(254)]\r\n| 4: Path s2.scmversion : com.github.tminglei.slickpg.utils.PgCommonJdbcTypes$Generic/json\r\n```\r\n\r\nWhen I use jsonb by uncommenting the 'override val pgjson = \"jsonb\"', I see the following debug messages. Notice the null at the end. \r\n```\r\n11:28:07.993 TKD [main] DEBUG scala.slick.compiler.QueryCompiler - Source:\r\n| TableExpansion\r\n| table s2: Table repository : Coll[scala.collection.immutable.Vector][NominalType(@(PostgresDriver._.repository))(UnassignedStructuralType(@(PostgresDriver._.repository)))]\r\n| columns: TypeMapping\r\n| 0: ProductNode\r\n| 1: Path s2.url : String/VARCHAR(254)\r\n| 2: Path s2.id : Int/INTEGER\r\n| 3: Path s2.branch : Option[String/VARCHAR(254)]\r\n| 4: Path s2.scmversion : com.github.tminglei.slickpg.utils.PgCommonJdbcTypes$Generic/null\r\n```\r\n\r\nI even tried using json4s 3.2.11, but no change. \r\n\r\nAny Ideas?\r\n\r\nHere is the Exception:\r\n\r\n```\r\nException in thread \"main\" org.postgresql.util.PSQLException: Unknown type null.\r\nat org.postgresql.jdbc2.AbstractJdbc2Statement.setPGobject(AbstractJdbc2Statement.java:1703)\r\nat org.postgresql.jdbc2.AbstractJdbc2Statement.setObject(AbstractJdbc2Statement.java:1926)\r\nat org.postgresql.jdbc3g.AbstractJdbc3gStatement.setObject(AbstractJdbc3gStatement.java:36)\r\nat org.postgresql.jdbc4.AbstractJdbc4Statement.setObject(AbstractJdbc4Statement.java:48)\r\nat scala.slick.jdbc.JdbcBackend$SessionDef$LoggingPreparedStatement.setObject(JdbcBackend.scala:372)\r\nat com.github.tminglei.slickpg.utils.PgCommonJdbcTypes$GenericJdbcType.setValue(PgCommonJdbcTypes.scala:23)\r\nat scala.slick.jdbc.BaseResultConverter.set(JdbcResultConverter.scala:19)\r\nat scala.slick.jdbc.BaseResultConverter.set(JdbcResultConverter.scala:11)\r\nat scala.slick.relational.ProductResultConverter.set(ResultConverter.scala:71)\r\nat scala.slick.relational.ProductResultConverter.set(ResultConverter.scala:46)\r\nat scala.slick.relational.TypeMappingResultConverter.set(ResultConverter.scala:127)\r\nat scala.slick.driver.JdbcInsertInvokerComponent$BaseInsertInvoker$$anonfun$internalInsert$1.apply(JdbcInsertInvokerComponent.scala:182)\r\nat scala.slick.driver.JdbcInsertInvokerComponent$BaseInsertInvoker$$anonfun$internalInsert$1.apply(JdbcInsertInvokerComponent.scala:180)\r\nat scala.slick.jdbc.JdbcBackend$SessionDef$class.withPreparedInsertStatement(JdbcBackend.scala:198)\r\nat scala.slick.jdbc.JdbcBackend$BaseSession.withPreparedInsertStatement(JdbcBackend.scala:389)\r\nat scala.slick.driver.JdbcInsertInvokerComponent$ReturningInsertInvoker.preparedInsert(JdbcInsertInvokerComponent.scala:301)\r\nat scala.slick.driver.JdbcInsertInvokerComponent$BaseInsertInvoker.internalInsert(JdbcInsertInvokerComponent.scala:180)\r\nat scala.slick.driver.JdbcInsertInvokerComponent$BaseInsertInvoker.insert(JdbcInsertInvokerComponent.scala:175)\r\nat scala.slick.driver.JdbcInsertInvokerComponent$InsertInvokerDef$class.$plus$eq(JdbcInsertInvokerComponent.scala:70)\r\nat scala.slick.driver.JdbcInsertInvokerComponent$BaseInsertInvoker.$plus$eq(JdbcInsertInvokerComponent.scala:145)\r\n```"},"comment":{"url":"https://api.github.com/repos/tminglei/slick-pg/issues/comments/86023909","html_url":"https://github.com/tminglei/slick-pg/issues/141#issuecomment-86023909","issue_url":"https://api.github.com/repos/tminglei/slick-pg/issues/141","id":86023909,"user":{"login":"yvesnyc","id":2157722,"avatar_url":"https://avatars.githubusercontent.com/u/2157722?v=3","gravatar_id":"","url":"https://api.github.com/users/yvesnyc","html_url":"https://github.com/yvesnyc","followers_url":"https://api.github.com/users/yvesnyc/followers","following_url":"https://api.github.com/users/yvesnyc/following{/other_user}","gists_url":"https://api.github.com/users/yvesnyc/gists{/gist_id}","starred_url":"https://api.github.com/users/yvesnyc/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/yvesnyc/subscriptions","organizations_url":"https://api.github.com/users/yvesnyc/orgs","repos_url":"https://api.github.com/users/yvesnyc/repos","events_url":"https://api.github.com/users/yvesnyc/events{/privacy}","received_events_url":"https://api.github.com/users/yvesnyc/received_events","type":"User","site_admin":false},"created_at":"2015-03-25T13:29:56Z","updated_at":"2015-03-25T13:29:56Z","body":"I ran the query on the 9.4.1 PostgreSQL DB. Here is the result with column names in the first row:\n\noid,typname,typnamespace,typowner,typlen,typbyval,typtype,typcategory,typispreferred,typisdefined,typdelim,typrelid,typelem,typarray,typinput,typoutput,typreceive,typsend,typmodin,typmodout,typanalyze,typalign,typstorage,typnotnull,typbasetype,typtypmod,typndims,typcollation,typdefaultbin,typdefault,typacl\n3802,jsonb,11,10,-1,f,b,U,f,t,\",\",0,0,3807,jsonb_in,jsonb_out,jsonb_recv,jsonb_send,-,-,-,i,x,f,0,-1,0,0,,,\n\n\n\nOn Mar 25, 2015, at 9:08 AM, 涂名雷 <notifications@github.com> wrote:\n\n> Well, you need override pgjson=jsonb, to keep the type consistent with db column.\n> \n> Let's continue to analyze the cause of first exception.\n> \n> Here's the codes in AbstractJdbc2Statement.java of pg driver where throw the exception:\n> \n>     // Helper method for setting parameters to PGobject subclasses.\n>     private void setPGobject(int parameterIndex, PGobject x) throws SQLException {\n>         String typename = x.getType();\n>         int oid = connection.getTypeInfo().getPGType(typename);\n>         if (oid == Oid.UNSPECIFIED)\n>             throw new PSQLException(GT.tr(\"Unknown type {0}.\", typename), PSQLState.INVALID_PARAMETER_TYPE);\n> ...\n> And here's the codes in TypeInfoCache.java of pg driver that fetch the pg type:\n> \n>     public synchronized int getPGType(String pgTypeName) throws SQLException\n>     {\n>         Integer oid = (Integer)_pgNameToOid.get(pgTypeName);\n>         if (oid != null)\n>             return oid.intValue();\n> \n>         if (_getOidStatement == null) {\n>             String sql;\n>             if (_conn.haveMinimumServerVersion(\"8.0\")) {\n>                 // see comments in @getSQLType()\n>                 sql = \"SELECT pg_type.oid \" +\n>                       \"  FROM pg_catalog.pg_type \" +\n>                       \"  LEFT \" +\n>                       \"  JOIN (select ns.oid as nspoid, ns.nspname, r.r \" +\n>                       \"          from pg_namespace as ns \" +\n>                       \"          join ( select s.r, (current_schemas(false))[s.r] as nspname \" +\n>                       //                  -- go with older way of unnesting array to be compatible with 8.0\n>                       \"                   from generate_series(1, array_upper(current_schemas(false), 1)) as s(r) ) as r \" +\n>                       \"         using ( nspname ) \" +\n>                       \"       ) as sp \" +\n>                       \"    ON sp.nspoid = typnamespace \" +\n>                       \" WHERE typname = ? \" +\n>                       \" ORDER BY sp.r, pg_type.oid DESC LIMIT 1;\";\n>             } else if (_conn.haveMinimumServerVersion(\"7.3\")) {\n>                 sql = \"SELECT oid FROM pg_catalog.pg_type WHERE typname = ? ORDER BY oid DESC LIMIT 1\";\n>             } else {\n>                 sql = \"SELECT oid FROM pg_type WHERE typname = ? ORDER BY oid DESC LIMIT 1\";\n>             }\n> \n>             _getOidStatement = _conn.prepareStatement(sql);\n>         }\n> \n>         _getOidStatement.setString(1, pgTypeName);\n> \n>         // Go through BaseStatement to avoid transaction start.\n>         if (!((BaseStatement)_getOidStatement).executeWithFlags(QueryExecutor.QUERY_SUPPRESS_BEGIN))\n>             throw new PSQLException(GT.tr(\"No results were returned by the query.\"), PSQLState.NO_DATA);\n> \n>         oid = new Integer(Oid.UNSPECIFIED);\n>         ResultSet rs = _getOidStatement.getResultSet();\n>         if (rs.next()) {\n>             oid = new Integer((int)rs.getLong(1));\n>             _oidToPgName.put(oid, pgTypeName);\n>         }\n>         _pgNameToOid.put(pgTypeName, oid);\n>         rs.close();\n> \n>         return oid.intValue();\n>     }\n> I suspect no results returned for the sql, pls help confirm:\n> \n> SELECT pg_type.oid , pg_type.*\n> FROM pg_catalog.pg_type \n>    LEFT \n>    JOIN (select ns.oid as nspoid, ns.nspname, r.r \n>          from pg_namespace as ns\n>            join ( select s.r, (current_schemas(false))[s.r] as nspname \n>            -- go with older way of unnesting array to be compatible with 8.0\n>                 from generate_series(1, array_upper(current_schemas(false), 1)) as s(r) ) as r \n>           using ( nspname ) \n>          ) as sp\n>     ON sp.nspoid = typnamespace \n> WHERE typname = 'jsonb'   -- this is the parameter\n> ORDER BY sp.r, pg_type.oid DESC;\n> —\n> Reply to this email directly or view it on GitHub.\n> \n\n"}}