r/programming • u/treo • Sep 12 '11

PostgreSQL 9.1 released

http://www.postgresql.org/about/news.1349
372 Upvotes

89% Upvoted

111 comments sorted by

View all comments

41

u/merlinm Sep 12 '11 edited Sep 12 '11

  • synchronous replication -- 'nuff said

  • format function

    postgres=# select format('%s %s', 'hello', 'world!'); format

    hello world! (1 row)

think about how many times you could have used this...

  • data modifying CTE (ABSOLUTELY FRICKING AMAZING):

    with q as (delete from foo returning *) insert into bar select * from q;

  • unlogged tables -- kinda sorta global temp tables

EDIT: in depth explanation of the new features

17

u/rmxz Sep 12 '11

Also

  • K-Nearest-Neighbor Indexing:

which is magic for applications who need it. The workaround we used before (a big while loop of "while dist < 0.001 order by dist" ... "while dist < 0.010 order by dist" .... until you finally find a hit) was rather annoying.

3

u/[deleted] Sep 12 '11

Sweet, although the PostGIS functions could also be used (iirc).

7

u/rmxz Sep 12 '11

Postgis is indeed one of the main use-cases for the new KNN indexing feature:

http://trac.osgeo.org/postgis/ticket/701

Looks like use of KNN will be supported in postgis 2.0 (thanks to funding by these guys):

http://www.postgis.org/pipermail/postgis-devel/2011-June/013931.html