postgres / jsonb support request

[kirsn]

Details in this discussion thread: https://groups.google.com/forum/#!topic/web2py/3hY4niQu-jY.
The request is to use the postgres' jsonb support, via pydal.

[kirsn]

Thought it may be useful to just tabulate the differences between json and jsonb, and their implications (all the content is from the PostgreSQL documentation). Haven't included the jsonb advantages.

	jsonb	json	implications for developers
whitespace	discarded	preserved	semantically insignificant, and should not matter in consuming the underlying data
order of content	discarded	preserved	relying on json to retain order of content is a bad idea anyway
numbers print mode	Depending on underlying numeric type. 'E' notation may be discarded, in favour of an expanded notation	preserved	No loss of fidelity, but could be a problem if the 'E' notation representation is important.
duplicate keys	Removed, and replaced with the last one	retains duplicates	Need to be aware of the unique-key constraint
Unicode content	If DB is UTF-8, then all characters in Json should be correct Unicode characters. If DB is non-UTF, disallows Unicode escapes for non-ASCII characters (those above U+007F)	DB encoding does not matter; can store any Unicode character	Its probably a good practice to encode the DB with UTF-8, vs not.
Primitive datatypes	Json types are mapped to PostgreSQL types	not mapped to PostgreSQL types	The underlying PostgreSQL type restrictions are applicable to jsonb too.
Numeric types	numbers will be rejected if the range is larger than the postgresql numeric type range	retains such numbers	The underlying PostgreSQL type restrictions are applicable to jsonb too.

An interesting article around this: http://pgeoghegan.blogspot.in/2014/03/what-i-think-of-jsonb.html . tl;dr - "Still, like the earlier json type, jsonb in every sense “speaks JSON”. There are some limitations on what can be represented as a jsonb number, but those are exactly the same limitations that apply to the core numeric type (plus some limitations imposed by the JSON RFC, such as not accepting NaN values)."

[vanadium23]

I want to grub this issue, but see that in pr #332 is big refactoring. Should I start after it'll be merged?

[gi0baro]

@vanadium23 I'm sorry is taking quite long. Would be awesome if you start planning the code on that PR, the postgre part is ready. You can use the code of the branch as starting point, the PR should be ready to be merged by the end of the week.

[leonelcamara]

@gi0baro, @kirsn and @vanadium23 this is mostly done because I needed it for one of my projects, sorry I didn't notice this issue and said something earlier.

See:
#469
#561