Monday, August 12, 2013

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}

No comments:

Post a Comment