The presentation “PostgreSQL and JSON with Python” is part of Sunscrapers’ weekly talks. The main talking points include: PostgreSQL types, HStore vs JSON vs JSONB, SQLAlchemy, Django.
1. Why?
- Schema-less data
- Schema-unknown data
- Document storage
- User-defined schema
2. PostgreSQL types:
- XML (since 8.2)
- HStore (since 8.2)
- JSON (since 9.2)
- JSONB (since 9.4)
3. HStore vs JSON vs JSONB
- Only simple key/value pairs
- No nesting
- Strings only
- Indexing
- Many functions and operators
4. SQLAlchemy
- Simple validation on input
- Stored as text (like XML)
- Preserves key order and duplicates
- Indexes (only expression index)
5. JSONB
- Full JSON implementation
- Binary storage
- No key order or duplicate preservation
- Fast access operations
- Indexing
- No date type!
6. SQLAlchemy
- PostgreSQL dialect
- Close to database
- HStore, JSON, JSONB data types and much more
7. Django ORM
- HStoreField since 1.8
- JSONField since 1.9 (uses JSONB)
- Support for migrations
- Third party libs like: django-hstore
Slides available here:
https://www.slideshare.net/sunscrapers/postgresql-and-json-with-python-przemek-lewandowski
