Hi, Thanks for SQLAlchemy. I'm trying to insert some JSONB into a PostgreSQL database. My insert succeeds if the column type is JSON but fails if the type is JSONB.
SQLAlchemy version: sqlalchemy-1.0.13 PostgreSQL: 9.5.3 DBAPI: py-postgresql Python: 3.4 OS: Windows 8.1 64-bit Here's my test code: import postgresql import sqlalchemy from sqlalchemy import * from sqlalchemy.orm import * from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.schema import MetaData from sqlalchemy.dialects.postgresql import JSON, JSONB from datetime import datetime, tzinfo, timedelta engine=create_engine("postgresql+pypostgresql://postgres:*******@localhost:5432/dbname") NAMING_CONVENTION = { "ix": 'ix_%(column_0_label)s', "uq":"uq_%(table_name)s_%(column_0_name)s", "ck":"ck_%(table_name)s_%(constraint_name)s", "fk":"fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s", "pk":"pk_%(table_name)s" } metadata = MetaData(naming_convention=NAMING_CONVENTION) Base = declarative_base(metadata=metadata) class JSON_test(Base): __tablename__ = 'json_test' json_id = Column(Integer, primary_key=True) history = Column(JSON) class JSONB_test(Base): __tablename__ = 'jsonb_test' jsonb_id = Column(Integer, primary_key=True) history = Column(JSONB) Base.metadata.create_all(engine) Session = sessionmaker(bind=engine) session = Session() session.add(JSON_test(history={"entry":{"user":"andrew","datetime" : "{datetime}".format(datetime=datetime.now()),"reason":"init","description":"init","source":"andrew"}})) session.commit() # This JSON object is identical to the last session.add(JSONB_test(history={"entry":{"user":"andrew","datetime" : "{datetime}".format(datetime=datetime.now()),"reason":"init","description":"init","source":"andrew"}})) session.commit() After running this code, the JSON_test commit succeeds but the JSONB_test commit throws this error: >>> session.add(JSONB_test(history={"entry":{"user":"andrew","datetime" : "{datetime}".format(datetime=datetim e.now()),"reason":"init","description":"init","source":"andrew"}})) >>> session.commit() Traceback (most recent call last): File "C:\Python34\lib\site-packages\postgresql\driver\dbapi20.py", line 92, in __next__ r = self.buf[self.pos] IndexError: list index out of range During handling of the above exception, another exception occurred: Traceback (most recent call last): File "C:\Python34\lib\site-packages\sqlalchemy\engine\result.py", line 1019, in fetchone row = self._fetchone_impl() File "C:\Python34\lib\site-packages\sqlalchemy\engine\result.py", line 900, in _fetchone_impl return self.cursor.fetchone() File "C:\Python34\lib\site-packages\postgresql\driver\dbapi20.py", line 187, in fetchone return next(self._portal) File "C:\Python34\lib\site-packages\postgresql\driver\dbapi20.py", line 100, in __next__ self.buf = next(self.chunks) File "C:\Python34\lib\site-packages\postgresql\driver\pq3.py", line 1009, in __next__ self.database._pq_complete() File "C:\Python34\lib\site-packages\postgresql\driver\pq3.py", line 2604, in _pq_complete self.typio.raise_error(x.error_message, cause = getattr(x, 'exception', None)) File "C:\Python34\lib\site-packages\postgresql\driver\pq3.py", line 541, in raise_error self.raise_server_error(error_message, **kw) File "C:\Python34\lib\site-packages\postgresql\driver\pq3.py", line 532, in raise_server_error raise server_error postgresql.exceptions.InternalError: unsupported jsonb version number 123 CODE: XX000 LOCATION: File 'jsonb.c', line 122, in jsonb_recv from SERVER RESULT: chunksize: 4096 type: MultiXactInsideBlock parameters: ('{"entry": {"user": "andrew", "datetime": "2016-06-01 09:07:30.502703", "reason": "init", "description": "init", "source": "andrew"}}',) cursor_id: py:0x4289550 STATEMENT: [prepared] sql_parameter_types: ['"pg_catalog"."jsonb"'] results: ('jsonb_id' 'INTEGER') statement_id: py:0x42d16a0 string: INSERT INTO jsonb_test (history) VALUES ($1) RETURNING jsonb_test.jsonb_id CONNECTION: [idle in block] client_address: 127.0.0.1/32 client_port: 64593 version: PostgreSQL 9.5.3, compiled by Visual C++ build 1800, 64-bit CONNECTOR: [Host] pq://postgres:***@localhost:5432/products category: None DRIVER: postgresql.driver.pq3.Driver The above exception was the direct cause of the following exception: Traceback (most recent call last): File "<stdin>", line 1, in <module> File "C:\Python34\lib\site-packages\sqlalchemy\orm\session.py", line 801, in commit self.transaction.commit() File "C:\Python34\lib\site-packages\sqlalchemy\orm\session.py", line 392, in commit self._prepare_impl() File "C:\Python34\lib\site-packages\sqlalchemy\orm\session.py", line 372, in _prepare_impl self.session.flush() File "C:\Python34\lib\site-packages\sqlalchemy\orm\session.py", line 2019, in flush self._flush(objects) File "C:\Python34\lib\site-packages\sqlalchemy\orm\session.py", line 2137, in _flush transaction.rollback(_capture_exception=True) File "C:\Python34\lib\site-packages\sqlalchemy\util\langhelpers.py", line 60, in __exit__ compat.reraise(exc_type, exc_value, exc_tb) File "C:\Python34\lib\site-packages\sqlalchemy\util\compat.py", line 186, in reraise raise value File "C:\Python34\lib\site-packages\sqlalchemy\orm\session.py", line 2101, in _flush flush_context.execute() File "C:\Python34\lib\site-packages\sqlalchemy\orm\unitofwork.py", line 373, in execute rec.execute(self) File "C:\Python34\lib\site-packages\sqlalchemy\orm\unitofwork.py", line 532, in execute uow File "C:\Python34\lib\site-packages\sqlalchemy\orm\persistence.py", line 174, in save_obj mapper, table, insert) File "C:\Python34\lib\site-packages\sqlalchemy\orm\persistence.py", line 800, in _emit_insert_statements execute(statement, params) File "C:\Python34\lib\site-packages\sqlalchemy\engine\base.py", line 914, in execute return meth(self, multiparams, params) File "C:\Python34\lib\site-packages\sqlalchemy\sql\elements.py", line 323, in _execute_on_connection return connection._execute_clauseelement(self, multiparams, params) File "C:\Python34\lib\site-packages\sqlalchemy\engine\base.py", line 1010, in _execute_clauseelement compiled_sql, distilled_params File "C:\Python34\lib\site-packages\sqlalchemy\engine\base.py", line 1159, in _execute_context result = context._setup_crud_result_proxy() File "C:\Python34\lib\site-packages\sqlalchemy\engine\default.py", line 826, in _setup_crud_result_proxy row = result.fetchone() File "C:\Python34\lib\site-packages\sqlalchemy\engine\result.py", line 1028, in fetchone self.cursor, self.context) File "C:\Python34\lib\site-packages\sqlalchemy\engine\base.py", line 1341, in _handle_dbapi_exception exc_info File "C:\Python34\lib\site-packages\sqlalchemy\util\compat.py", line 202, in raise_from_cause reraise(type(exception), exception, tb=exc_tb, cause=cause) File "C:\Python34\lib\site-packages\sqlalchemy\util\compat.py", line 185, in reraise raise value.with_traceback(tb) File "C:\Python34\lib\site-packages\sqlalchemy\engine\result.py", line 1019, in fetchone row = self._fetchone_impl() File "C:\Python34\lib\site-packages\sqlalchemy\engine\result.py", line 900, in _fetchone_impl return self.cursor.fetchone() File "C:\Python34\lib\site-packages\postgresql\driver\dbapi20.py", line 187, in fetchone return next(self._portal) File "C:\Python34\lib\site-packages\postgresql\driver\dbapi20.py", line 100, in __next__ self.buf = next(self.chunks) File "C:\Python34\lib\site-packages\postgresql\driver\pq3.py", line 1009, in __next__ self.database._pq_complete() File "C:\Python34\lib\site-packages\postgresql\driver\pq3.py", line 2604, in _pq_complete self.typio.raise_error(x.error_message, cause = getattr(x, 'exception', None)) File "C:\Python34\lib\site-packages\postgresql\driver\pq3.py", line 541, in raise_error self.raise_server_error(error_message, **kw) File "C:\Python34\lib\site-packages\postgresql\driver\pq3.py", line 532, in raise_server_error raise server_error sqlalchemy.exc.InternalError: (postgresql.exceptions.InternalError) unsupported jsonb version number 123 CODE: XX000 LOCATION: File 'jsonb.c', line 122, in jsonb_recv from SERVER RESULT: chunksize: 4096 type: MultiXactInsideBlock parameters: ('{"entry": {"user": "andrew", "datetime": "2016-06-01 09:07:30.502703", "reason": "init", "description": "init", "source": "andrew"}}',) cursor_id: py:0x4289550 STATEMENT: [prepared] sql_parameter_types: ['"pg_catalog"."jsonb"'] results: ('jsonb_id' 'INTEGER') statement_id: py:0x42d16a0 string: INSERT INTO jsonb_test (history) VALUES ($1) RETURNING jsonb_test.jsonb_id CONNECTION: [failed block] client_address: 127.0.0.1/32 client_port: 64593 version: PostgreSQL 9.5.3, compiled by Visual C++ build 1800, 64-bit CONNECTOR: [Host] pq://postgres:***@localhost:5432/products category: None DRIVER: postgresql.driver.pq3.Driver My apologies if I'm handling the JSONB insert incorrectly, but from the docs <http://docs.sqlalchemy.org/en/rel_1_0/dialects/postgresql.html#postgresql-data-types>, it seems that the JSON and JSONB inserts are exactly the same syntax? Thanks, Andrew -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.