New in PostgreSQL 9.3: Data Types

In series of writing further on upcoming features in 9.3, I thought about including new improvements coming in data types in PostgreSQL.
1. Increase the maximum length of large objects from 2GB to 4TB

PostgreSQL has support of Large Objects from starting. However the limit of large objects in PostgreSQL was limited to 2GB.

From 9.3 onwards, PostgreSQL can store large objects up to 4TB. Thats happened due to lifting the limitation of API for large object. APIs like lo_seek(),lo_tell cannot return over 2GB offset and main reason was offset parameters defined for these function is of 4 bytes and results length provided by these functions is 4 bytes. If user do the calculation, he can see 2^31-1=2GB, it resulted in 2GB.

To overcome from this limitation, PostgreSQL is coming with new APIs: lo_seek64 and lo_tell64 functions. Libpq interface will check if those lo_tell64/seek64 exits then use it or use the 32 bit of lo_seek/lo_tell functions, this way compatibility has been maintained in PostgreSQL for older release and new upcoming release.

This is good add-on for PostgreSQL. Thanks to Tatsuo Ishii (pgpool developer and developer for this add-on).

2. Text timezone designations using ISO "T" function for timestamptz
This is new for timestamtz. In 9.3, text timezone designation is allowed using ISO "T" format. This was not working in pre-9.3. Lets look at one example:
In pre-9.3

worktest=# select '2011-08-29T09:11:14.123 America/Chicago'::timestamptz;
ERROR:  invalid input syntax for type timestamp with time zone: "2011-08-29T09:11:14.123 America/Chicago"
LINE 1: select '2011-08-29T09:11:14.123 America/Chicago'::timestampt...

As user can see, pre-9.3 has complained about it. However in In 9.3 this has been fixed.

pgsqltest=# select '2011-08-29T09:11:14.123 America/Chicago'::timestamptz;
        timestamptz         
----------------------------
 2011-08-29 10:11:14.123-04
(1 row)

Good to see this fix.

3. New operators and Functions for JSON data strings
PostgreSQL 9.3, is coming withe some new functions and operators for JSON data types, which is add-ons for users who uses JSON data type in their application. Now, they can explore new functions and operators for their use case. Pre-9.3, had following functions:

array_to_json
row_to_json

In 9.3, we have following new operators:

Operator	Right Operand Type	Description	Example
->	int	Get JSON array element	'[1,2,3]'::json->2
->	text	Get JSON object field	'{"a":1,"b":2}'::json->'b'
->>	int	Get JSON array element as text	'[1,2,3]'::json->>2
->>	text	Get JSON object field as text	'{"a":1,"b":2}'::json->>'b'
#>	array of text	Get JSON object at specified path	'{"a":[1,2,3],"b":[4,5,6]}'::json#>'{a,2}'
#>>	array of text	Get JSON object at specified path as text	'{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}'

And following new functions:

Function	Return Type	Example
to_json(anyelement)	json	to_json('Fred said "Hi."'::text)
json_array_length(json)	int	json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]')
json_each(json)	SETOF key text, value json	select * from json_each('{"a":"foo", "b":"bar"}')
json_each_text(from_json json)	SETOF key text, value text	select * from json_each_text('{"a":"foo", "b":"bar"}')
json_extract_path(from_json json, VARIADIC path_elems text[])	json	json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4')
json_extract_path_text(from_json json, VARIADIC path_elems text[])	text	json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4', 'f6')
json_object_keys(json)	SETOF text	json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}')
json_populate_record(base anyelement, from_json json, [, use_json_as_text bool=false]	anyelement	select * from json_populate_record(null::x, '{"a":1,"b":2}')
json_populate_recordset(base anyelement, from_json json, [, use_json_as_text bool=false]	SETOF anyelement	select * from json_populate_recordset(null::x, '[{"a":1,"b":2},{"a":3,"b":4}]')
json_array_elements(json)	SETOF json	json_array_elements('[1,true, [2,false]]')

4. New functions to support hstore to JSON

PostgreSQL 9.3, is also coming with new functions for converting values of hstore to JSON. Following are new functions with example which is coming in for hstore to JSON data types.

Function	Return Type	Description	Example	Result
hstore_to_json(hstore)	json	get hstore as a json value	hstore_to_json('"a key"=>1, b=>t, c=>null, d=>12345, e=>012345, f=>1.234, g=>2.345e+4')	{"a key": "1", "b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", "g": "2.345e+4"}
hstore_to_json_loose(hstore)	json	get hstore as a json value, but attempting to distinguish numerical and Boolean values so they are unquoted in the JSON	hstore_to_json_loose('"a key"=>1, b=>t, c=>null, d=>12345, e=>012345, f=>1.234, g=>2.345e+4')	{"a key": 1, "b": true, "c": null, "d": 12345, "e": "012345", "f": 1.234, "g": 2.345e+4}