There is another new feature in 9.2 that doesn't get much press, and probably because it's hard to explain. It is a pretty useful feature if you are working with the new json type or the existing hstore type. In prior versions if you used a subquery and converted the rows to hstore or json the column names were not preserved. Andrew mentioned a back-port path for this issue in Upgradeable JSON. We described a workaround for this issue in Mail merging using hstore. The workaround for including PostGIS geometry in json record output as described in Native JSON type support wouldn't work as nicely without this enhancement. Here is an example to demonstrate.
CREATE EXTENSION hstore;
CREATE TABLE clients(client_id varchar(5) PRIMARY KEY, contact_name varchar(100), business varchar(100));
INSERT INTO clients(client_id, contact_name, business)
VALUES ('DCH', 'John Dewey', 'Dewey, Cheetham, and Howe'),
('IYS', 'Spencer Tracey', 'Investigators at Your Service'),
('ABC', 'Abby Back', 'It is all about ABC');
SELECT client_id, hstore(c) As data
FROM (SELECT client_id, contact_name
FROM clients) As c;
If you were running the above query in 9.1 or lower, you'd get this which as you can see is not ideal:
--9.1 behavior
client_id | data
-----------+-------------------------------------
DCH | "f1"=>"DCH", "f2"=>"John Dewey"
IYS | "f1"=>"IYS", "f2"=>"Spencer Tracey"
ABC | "f1"=>"ABC", "f2"=>"Abby Back"
If you run the same exact query in 9.2, you get this much nicer output
--9.2 behavior
client_id | data
----------+------------------------------------------------------
DCH | "client_id"=>"DCH", "contact_name"=>"John Dewey"
IYS | "client_id"=>"IYS", "contact_name"=>"Spencer Tracey"
ABC | "client_id"=>"ABC", "contact_name"=>"Abby Back"
Update - We just finished our final draft of
PostgreSQL: Up and Running which is already available for pre-order from Amazon and direct from
O'Reilly. It is expected to be out sometime in July 2012. It is mostly focused on PostgreSQL 9.0-9.2 with highlights and some examples of what's new in 9.2. The main audience of the book are converts from other databases, but we think people currently using PostgreSQL will find it useful as well. It exposes many of the new features, both performance and usability features, that have been added on in the past 3-4 versions and it has lots of SQL examples flaunting PostgreSQL unique and enterprise features - building aggregates, window aggs, type querying, plpython function writing, plpgsql, triggers, regular expressions, using foreign data wrappers and more.
PostGIS in Action
About the Authors
Consulting
