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}

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s