Postgres OnLine Journal (Entries tagged as postgresql 9.1)http://www.postgresonline.com/journal/
an In depth look at the PostgreSQL open source databaseenSerendipity 1.7.8 - http://www.s9y.org/Sun, 15 Jul 2012 06:40:20 GMThttp://www.postgresonline.com/journal/templates/default/img/s9y_banner_small.pngRSS: Postgres OnLine Journal - an In depth look at the PostgreSQL open source databasehttp://www.postgresonline.com/journal/
10021Foreign Data Wrap (FDW) Text Array, hstore, and Jagged Arrayshttp://www.postgresonline.com/journal/archives/259-Foreign-Data-Wrap-FDW-Text-Array,-hstore,-and-Jagged-Arrays.html
9.19.2contrib spotlightctefdwshstorepostgresql versionshttp://www.postgresonline.com/journal/archives/259-Foreign-Data-Wrap-FDW-Text-Array,-hstore,-and-Jagged-Arrays.html#commentshttp://www.postgresonline.com/journal/wfwcomment.php?cid=2590http://www.postgresonline.com/journal/rss.php?version=2.0&type=comments&cid=259nospam@example.com (Leo Hsu and Regina Obe)
<p>As we discussed in <a href="http://www.postgresonline.com/journal/archives/251-File-FDW-Family-Part-2-file_textarray_fdw-Foreign-Data-Wrapper.html" target="_blank">file_textarray_fdw Foreign Data Wrapper</a>, <a href="http://people.planetpostgresql.org/andrew/index.php?/archives/271-Deploying-file_text_array-Foreign-Data-wrapper.html" target="_blank">Andrew Dunstan's text array foreign data wrapper</a> works great for bringing in a delimited file and not having to worry about the column names until they are in.
We had demonstrated one way to tag the field names to avoid having to keep track of index locations, by using hstore and the header column in conjunction.
The problem with that is it doesn't work for jagged arrays. Jagged arrays are when not all rows have the same number of columns. I've jury rigged a small example
to demonstrate the issue. Luckily with the power of PostgreSQL arrays you can usually get around this issue and still have nice names for your columns. We'll demonstrate that too.</p> <br /><a href="http://www.postgresonline.com/journal/archives/259-Foreign-Data-Wrap-FDW-Text-Array,-hstore,-and-Jagged-Arrays.html#extended">Continue reading "Foreign Data Wrap (FDW) Text Array, hstore, and Jagged Arrays"</a>
Sun, 15 Jul 2012 02:35:00 -0400http://www.postgresonline.com/journal/archives/259-guid.htmlarraycommon table expressionsctefile_textarray_fdwforeign data wrapperpostgresql 9.1PostgreSQL: Up and Running book officially outhttp://www.postgresonline.com/journal/archives/258-PostgreSQL-Up-and-Running-book-officially-out.html
9.19.2editor notepostgispostgresql versionshttp://www.postgresonline.com/journal/archives/258-PostgreSQL-Up-and-Running-book-officially-out.html#commentshttp://www.postgresonline.com/journal/wfwcomment.php?cid=25810http://www.postgresonline.com/journal/rss.php?version=2.0&type=comments&cid=258nospam@example.com (Leo Hsu and Regina Obe)
<p>Our new book <a href="/store.php?asin=1449326331" target="_blank">PostgreSQL: Up and Running</a> is officially out. It's available in hard-copy and e-Book version directly from O'Reilly,
Safari Books Online and available from Amazon in Kindle store. It should be available in hard-copy within the next week or so from other distributors.</p>
<p>Sadly we won't be attending <a href="http://www.oscon.com/oscon2012/" target="_blank">OSCON</a> this year, but there are several PostgreSQL talks going on. If you are speaking at a talk or other PostgreSQL related get together, and would like
to give out some free coupons of our book or get a free e-book copy for yourself to see if it's worth effort mentioning, please send us an e-mail: lr at pcorp.us .</p>
<p>Our main focus in writing the book is demonstrating features that make PostgreSQL uniquely poised for newer kinds of workflows with particular focus on PostgreSQL 9.1 and 9.2.
Part of the reason for this focus is our roots and that we wanted to write a short book to get a feel for the audience. We started to use PostgreSQL in 2001 because of
PostGIS, but were still predominantly SQL Server programmers. At the time SQL Server did not have a spatial component that integrated seamlessly with SQL.
As die-hard SQLers, PostGIS really turned us on. As years went by, we began to use PostgreSQL
not just for our spatial apps, but predominantly non-spatial ones as well that had heavy reporting needs and that we had a choice of platform.
So we came for PostGIS but stayed because of all the other neat features PostgreSQL had that we found lacking in SQL Server. Three off the bat
are arrays, regular expressions, and choice of procedural languages. Most other books on the market just treat PostgreSQL like it's any other relational database.
In a sense that's good because it demonstrates
that using PostgreSQL does not require a steep learning curve if you've used another relational database. We didn't spend as much time on these common features as we'd like to
in the book because it's a short book and we figure most users familiar with relational databases
are quite knowledgeable of common features from other experience. It's true that a lot of people coming to PostgreSQL are looking for cost savings,
ACID compliance, cross-platform support and decent speed
, but as PostgreSQL increases in speed, ease of features, and unique features, we think we'll be seeing more people migrating
just because its simply better than any other databases
for the new kinds of workflows we are seeing today -- e.g. BigData analysis, integration with other datasources, leveraging of domain specific languages in a more seamless way with data.</p>
<p>So what's that creature on the cover? <br /> <a href="/images/postgresqlup_and_running_big.jpg" target="_blank"><img src="/images/postgresql_up_running.gif" alt="PostgreSQL: Up and Running - elephant shrew" border="0" /></a> <br />It's an <a href="http://www.trinity.edu/departments/public_relations/news_releases/080306ribblesengi.htm" target="_blank">elephant shrew (sengi)</a> and is neither an elephant nor a shrew, but closest in ancestry to the elephant, sea cow, and aardvark.
It is only found
in Africa (mostly East Africa around Kenya) and in zoos. It gets its name from its unusually long nose which it uses for sniffing out insect prey and keeping tabs on its mate. It has some other unusual habits:
it's a trail blazer building trails it uses to scout insect prey and also builds escape routes on the trail it memorizes to escape from predators. It's monogamous, but prefers to keep separate quarters from its mate. Males
will chase off other males and females will chase off other females. <a href="http://animal.discovery.com/animals/life/rufous-sengi.html" target="_blank">It's fast</a> and can usually out-run its predators.</p>
Tue, 10 Jul 2012 14:53:00 -0400http://www.postgresonline.com/journal/archives/258-guid.htmlbook writingpostgresql 9.1postgresql 9.2File FDW Family: Part 2 file_textarray_fdw Foreign Data Wrapperhttp://www.postgresonline.com/journal/archives/251-File-FDW-Family-Part-2-file_textarray_fdw-Foreign-Data-Wrapper.html
9.1contrib spotlightfdwspostgresql versionswinextensionshttp://www.postgresonline.com/journal/archives/251-File-FDW-Family-Part-2-file_textarray_fdw-Foreign-Data-Wrapper.html#commentshttp://www.postgresonline.com/journal/wfwcomment.php?cid=2512http://www.postgresonline.com/journal/rss.php?version=2.0&type=comments&cid=251nospam@example.com (Leo Hsu and Regina Obe)
<p>Last time we demonstrated <a href="http://www.postgresonline.com/journal/archives/250-File-FDW-Family-Part-1-file_fdw.html" target="_blank">how to query delimited text files</a> using the <a href="http://www.postgresql.org/docs/current/interactive/file-fdw.html"><em>fdw_file</em></a> that comes packaged with PostgreSQL 9.1+, this time we'll continue our journey into Flat file querying Foreign Data Wrapper using an experimental foreign data wrapper designed for also querying delimited data, but outputting it as a single column text array table.
This one is called <em>file_textarray_fdw</em> and developed by Andrew Dunstan. It's useful if you are dealing with for example jagged files, where not all columns are not properly filled in for each record or there are just a ton of columns you don't want to bother itemizing before you bring in. The benefit is you can still query and decide how you want to break it apart. You can grab the source code from <a href="https://github.com/adunstan/file_text_array_fdw" target="_blank">file_text_array_fdw source code</a>. If you are on windows, we have compiled binaries in our Bag o' FDWs for both <a href="/downloads/fdw_win32_91_bin.zip">PostgreSQL 9.1 32-bit FDW for Windows bag</a> and <a href="/downloads/fdw_win64_91_bin.zip">PostgreSQL 9.1 64-bit FDW for Windows bag</a> that should work fine with the EDB installed windows binaries.
For other systems, the compile is fairly easy if you have the postgresql development libraries installed.</p> <br /><a href="http://www.postgresonline.com/journal/archives/251-File-FDW-Family-Part-2-file_textarray_fdw-Foreign-Data-Wrapper.html#extended">Continue reading "File FDW Family: Part 2 file_textarray_fdw Foreign Data Wrapper"</a>
Wed, 09 May 2012 23:46:00 -0400http://www.postgresonline.com/journal/archives/251-guid.htmlfdwfile_textarray_fdwforeign data wrapperhstorepostgresql 9.1File FDW Family: Part 1 file_fdwhttp://www.postgresonline.com/journal/archives/250-File-FDW-Family-Part-1-file_fdw.html
9.1contrib spotlightfdwspostgresql versionshttp://www.postgresonline.com/journal/archives/250-File-FDW-Family-Part-1-file_fdw.html#commentshttp://www.postgresonline.com/journal/wfwcomment.php?cid=2502http://www.postgresonline.com/journal/rss.php?version=2.0&type=comments&cid=250nospam@example.com (Leo Hsu and Regina Obe)
<p>Last time we demonstrated how to use the <a href="http://www.postgresonline.com/journal/archives/246-ODBC-Foreign-Data-wrapper---odbc_fdw-on-windows.html" target="_blank">ODBC Foreign Data wrapper</a>, this time we'll continue our journey into Foreign Data Wrapper land by demonstrating what I'll call the <em>File FDW family of Foreign Data Wrappers</em>. There is one that usually comes packaged with PostgreSQL 9.1 which is called <a href="http://www.postgresql.org/docs/9.1/interactive/file-fdw.html"><em>fdw_file</em></a> but there are two other experimental ones I find very useful which are
developed by Andrew Dunstan both of which Andrew demoed in <a href="http://people.planetpostgresql.org/andrew/uploads/fdw2.pdf">PostgreSQL Foreign Data Wrappers</a> and talked
about a little bit <a href="http://people.planetpostgresql.org/andrew/index.php?/archives/260-Text-files-from-a-remote-source.html" target="_blank">Text files from a remote source</a>. As people who have to deal with text data files day in and out, especially ones from mainframes, these satisfy a certain itch.</p>
<ul><li>file_fdw - for querying delimited text files.</li>
<li><a href="https://github.com/adunstan/file_fixed_length_record_fdw" target="_blank">file_fixed_length_fdw</a> - this one deals with fixed length data. We discussed methods of importing fixed length data in <a href="http://www.postgresonline.com/journal/archives/157-Import-fixed-width-data-into-PostgreSQL-with-just-PSQL.html">Import Fixed width data</a>. This is yet another approach but has the benefit that you can also use it to import just a subset of a file. </li>
<li><a href="https://github.com/adunstan/file_text_array_fdw" target="_blank">file_text_array_fdw</a> - this one queries a delimited file as if each delimiete row was a text array. It is ideal for those less than perfect moments when someone gives you a file with a 1000 columns and you don't have patience to look at what the hell those columns mean just yet.</li>
</ul></p>
<p>In this article, we'll just cover the <code>file_fdw</code> one, but will follow up in subsequent articles, demonstrating the array and fixed length record ones.</p> <br /><a href="http://www.postgresonline.com/journal/archives/250-File-FDW-Family-Part-1-file_fdw.html#extended">Continue reading "File FDW Family: Part 1 file_fdw"</a>
Thu, 03 May 2012 15:43:00 -0400http://www.postgresonline.com/journal/archives/250-guid.htmlfdwforeign data wrapperpostgresql 9.1ODBC Foreign Data wrapper to query SQL Server on Window - Part 2http://www.postgresonline.com/journal/archives/249-ODBC-Foreign-Data-wrapper-to-query-SQL-Server-on-Window-Part-2.html
9.1contrib spotlightfdwssql serverhttp://www.postgresonline.com/journal/archives/249-ODBC-Foreign-Data-wrapper-to-query-SQL-Server-on-Window-Part-2.html#commentshttp://www.postgresonline.com/journal/wfwcomment.php?cid=2497http://www.postgresonline.com/journal/rss.php?version=2.0&type=comments&cid=249nospam@example.com (Leo Hsu and Regina Obe)
<p>As promised in <a href="http://www.postgresonline.com/journal/archives/246-ODBC-Foreign-Data-wrapper---odbc_fdw-on-windows.html" target="_blank">our prior article: ODBC Foreign Data wrapper on windows</a>, we'll demonstrate how to query SQL Server using the Foreign Data Wrapper. This we are testing on windows.
As far as querying SQL Server / PostgreSQL goes, the Foreign Data Wrapper still lacks many features that the <a href="http://www.postgresonline.com/journal/archives/103-Using-Microsoft-SQL-Server-to-Update-PostgreSQL-Data.html" target="_blank">SQL Server Linked Server</a> approach provides.
The key ones we find currently lacking: ability to do updates and reference a table directly from server without knowing underlying structure. That said
the Foreign data Wrapper approach has possiblity to support a lot more data sources with ease. We'll demonstrate in subsequent articles using the <b>www_fdw</b> to query
web services which we've been playing a lot with and the often packaged in <b>file_fdw</b>. Enough of that let's start with a concrete example.</p>
<p>Warning, this is not production ready, but seems like a very promising start and with more testing can become very robust. Although we are demonstrating odbc_fdw on windows,
it is supported on Unix via the UnixODBC, but the data sources you can query will probably be different.
I'm really looking forward to how the FDW technology in PostgreSQL will push the envelop. I've been playing around with the <b>www_fdw</b> as well and been impressed how easily it is to
query webservices with SQL. A very ah-hah moment.</p> <br /><a href="http://www.postgresonline.com/journal/archives/249-ODBC-Foreign-Data-wrapper-to-query-SQL-Server-on-Window-Part-2.html#extended">Continue reading "ODBC Foreign Data wrapper to query SQL Server on Window - Part 2"</a>
Sun, 15 Apr 2012 20:09:00 -0400http://www.postgresonline.com/journal/archives/249-guid.htmlfdwforeign data wrapperpostgresql 9.1sql serverODBC Foreign Data wrapper - odbc_fdw on windowshttp://www.postgresonline.com/journal/archives/246-ODBC-Foreign-Data-wrapper-odbc_fdw-on-windows.html
9.1fdwsnew in postgresqlwinextensionshttp://www.postgresonline.com/journal/archives/246-ODBC-Foreign-Data-wrapper-odbc_fdw-on-windows.html#commentshttp://www.postgresonline.com/journal/wfwcomment.php?cid=2460http://www.postgresonline.com/journal/rss.php?version=2.0&type=comments&cid=246nospam@example.com (Leo Hsu and Regina Obe)
<div style="background-color:green;color:white">If you are looking for odbc fdw drivers for PostgreSQL 9.5 and 9.6 refer to <a href="http://www.postgresonline.com/journal/archives/372-ODBC-FDW-now-supports-9.5-and-9.6.html" style="color:white" target="_blank">this newer article</a></div>
<p>One of the new features in PostgreSQL 9.1 that we've been meaning to try is the new foreign data wrapper support.
Now that we are in compile mode gearing up for releasing PostGIS 2.0.0 for windows (both 32 and 64-bit), we thought we'd give the <a href="http://pgxn.org/dist/odbc_fdw/" target="_blank">odbc_fdw</a> a try trying to compile on windows. Last we tried we weren't successful because
we couldn't get past the -lodbc required step.</p>
<p>It turns out there is an easy fix to the ODBC dependency issue and I'm not sure I changed the line right. In the makefile we changed <b>-lodbc</b> to <b>-lodbc32</b>. This was needed for both compiling 32-bit as well as the 64-bit. We compiled the 64-bit version under our Mingw-64 chain
and 32-bit under our old Mingw gcc 3.4.5. Sadly we still don't have our mingw64 (compile for windows 32-bit compile up yet). Our ming64 for windows 32 can compile the 9.2 development branch but not the 9.1.3. Go figure. Anyrate to make a long story short -- we have 32-bit binaries for PostgreSQL (you can use in VC++ builds) and 64-bit binaries as well that you can use for the VC++ EDB builds for those who are interested in experimenting.
<a href="/downloads/fdw_win32_91_bin.zip">PostgreSQL 9.1 Windows 32-bit ODBC FDW</a> &#160;&#160;<a href="/downloads/odbc_fdw_win64bin.zip">PostgreSQL 9.1 Windows 64-bit ODBC FDW</a></p>
<p>So far we've tried the PostgreSQL 64-bit data wrapper against a SQL Server 2005 DSN and it seems to work fine. Have yet to try it on other ODBC sources.
We'll write up a more detailed article describing how to make the connections.</p>
<p>There is one trick to getting Mingw64 compiled PostgreSQL extensions to work with the Windows 64-bit EDB builds, and that is that when you compile your PostgreSQL under mingw64,
you have to configure with option <code>----disable-float8-byval</code> as we noted in our <a href="http://trac.osgeo.org/postgis/wiki/DevWikiWinMingW64" target="_blank">PostGIS Window 64 build instructions</a>.</p>
Tue, 27 Mar 2012 16:30:00 -0400http://www.postgresonline.com/journal/archives/246-guid.htmlfdwforeign data wrapperodbcodbc_fdwpostgresql 9.1KNN Gist for PostGIS in Placehttp://www.postgresonline.com/journal/archives/222-KNN-Gist-for-PostGIS-in-Place.html
9.1new in postgresqlpostgispostgresql versionshttp://www.postgresonline.com/journal/archives/222-KNN-Gist-for-PostGIS-in-Place.html#commentshttp://www.postgresonline.com/journal/wfwcomment.php?cid=2220http://www.postgresonline.com/journal/rss.php?version=2.0&type=comments&cid=222nospam@example.com (Leo Hsu and Regina Obe)
<p>Lots of people have been asking the never ending question of when PostGIS is going to get on the band wagon and support KNN GIST like other GIST based types trigrams, full text search etc. Well it's happened in PostGIS 2.0 and now committed. More of the gory details at <a href="http://blog.opengeo.org/2011/09/28/indexed-nearest-neighbour-search-in-postgis/" target="_blank">Indexed Nearest Neighbour Search in PostGIS</a>.
In short this will make point / point distance searches and rankings way way faster and help also with other distance searches by providing approximations to start with.</p>
<p>We are still preparing the PostgreSQL 9.1 2.0 32-bit windows builds that will have this functionality and should have that ready in the next day or so.</p>
<p>To summarize what you can expect. We spent a lot of time discussing and were torn between a <a href="http://www.postgis.org/documentation/manual-svn/geometry_distance_box.html" target="_blank">box distance operator &lt;#&gt;</a> and a <a href="http://www.postgis.org/documentation/manual-svn/geometry_distance_centroid.html" target="_blank">centroid box distance operator &lt;-&gt;</a>, so we ended up having both. The reason being is that for some kinds of geometries e.g. streets that aren't diagonal a box distance operator seems to be a much better approximation of distance than a centroid box distance operator. For points of course the two concepts are the same and not an approximation so point / point distance you'd be better off using the new KNN sorting than ST_Distance + ST_DWithin as we have suggested in past. I'll be doing some benchmarks in the coming weeks comparing the old way and speed differences you can expect and perhaps throwing together box and centroid cocktails that combine the two weapons into thought provoking WTFs (or as Dave Fetter would say &quot;That's very <a href="http://en.wikipedia.org/wiki/Rube_Goldberg" target="_blank">Rube Goldberg</a> of you&quot;).</p>
<p>I suspect I'll probably be sticking with &lt;#&gt; because <a href="http://www.postgis.org/pipermail/postgis-devel/2011-September/015291.html" target="_blank">I like the symbol better and I was one of the ones fighting for it</a> :).</p>
Thu, 29 Sep 2011 00:29:00 -0400http://www.postgresonline.com/journal/archives/222-guid.htmlpostgispostgresql 9.1PostgreSQL 9.1 Exploring Extensionshttp://www.postgresonline.com/journal/archives/216-PostgreSQL-9.1-Exploring-Extensions.html
9.1contrib spotlightfirst_last_aggintermediatekmeanpostgistemporalwindow functionshttp://www.postgresonline.com/journal/archives/216-PostgreSQL-9.1-Exploring-Extensions.html#commentshttp://www.postgresonline.com/journal/wfwcomment.php?cid=2166http://www.postgresonline.com/journal/rss.php?version=2.0&type=comments&cid=216nospam@example.com (Leo Hsu and Regina Obe)
<p>One of the new features I'm excited about in upcoming PostgreSQL 9.1 are extensions. It is also my hope
that for PostGIS 2.0, we'll be able to package <a href="http://trac.osgeo.org/postgis/ticket/964" target="_blank">PostGIS 2.0 as an extension</a>.
Reinspired in my mission by David Wheeler's recent post and video on <a href="http://blog.pgxn.org/post/8742312488/video-building-and-distributing-extensions-without-c" target="_blank">Building and Distributing Extensions without C</a>, I decided to take some time to investigate how all the extension pieces fit together.</p>
<p>The three things I like most about extensions
are: <ul><li>It has a companion sql CREATE EXTENSION and catalog of what's installed and available right from the db, which makes installing/uninstalling relatively painless</li>
<li>Installed functions don't get backed up with data, which is really a bit of a nightmare for PostGIS folks and relief be much welcome as you can tell in Paul's <a href="http://blog.cleverelephant.ca/2010/09/postgis-back-up-restore.html" target="_blank">PostGIS backup and restore</a>
which gets a bit more of an adventure in PostGIS 2.0 now that we have raster and topology and many more fun fun GEOS functions.</li>
<li>The ease with which you can uninstall,migrate your extension to another schema, and upgrade (in theory). There will be caveats here of course
with changes that require on disk format and involve data.</li></ul></p>
<p>Of course the ease is all in the thoughtfulness of the packaging. To get some ideas of how we would go about packaging
PostGIS 2.0 as an extension (it could very well be 3 extensions if we decide to package the core postgis, raster, and topology (and even tiger geocoder) as separate extensions), I thought I would take a look at how others have packaged theirs, and how one goes about registering one of these packages
to make it available in CREATE EXTENSION.</p>
<h3>Figuring out the extensions you have available ready to install</h3>
<p>First I decided to start by doing a little snooping, by applying some lessons from our previous article <a href="http://www.postgresonline.com/journal/archives/215-Querying-table,-view,-column-and-function-descriptions.html" target="_blank">Querying table, view, column and function descriptions</a>
I wrote this query to figure out what useful functions are available to learn about extensions.</p>
<pre><code><span class="syntax0"><span class="syntax-KEYWORD1">SELECT</span> p.proname <span class="syntax-KEYWORD1">AS</span> funcname, d.description
<span class="syntax-KEYWORD1">FROM</span> pg_proc p
<span class="syntax-KEYWORD1">INNER</span> <span class="syntax-KEYWORD1">JOIN</span> pg_namespace n <span class="syntax-KEYWORD1">ON</span> n.oid <span class="syntax-OPERATOR">=</span> p.pronamespace
<span class="syntax-KEYWORD2">LEFT</span> <span class="syntax-KEYWORD1">JOIN</span> pg_description <span class="syntax-KEYWORD1">As</span> d <span class="syntax-KEYWORD1">ON</span> <span class="syntax-OPERATOR">(</span>d.objoid <span class="syntax-OPERATOR">=</span> p.oid <span class="syntax-OPERATOR">)</span>
<span class="syntax-KEYWORD1">WHERE</span> n.nspname <span class="syntax-OPERATOR">=</span> <span class="syntax-LITERAL1">'</span><span class="syntax-LITERAL1">pg_catalog</span><span class="syntax-LITERAL1">'</span>
<span class="syntax-KEYWORD1">AND</span> <span class="syntax-OPERATOR">(</span>d.description ILIKE <span class="syntax-LITERAL1">'</span><span class="syntax-LITERAL1">%extension%</span><span class="syntax-LITERAL1">'</span> <span class="syntax-KEYWORD1">or</span> p.proname ILIKE <span class="syntax-LITERAL1">'</span><span class="syntax-LITERAL1">%extension%</span><span class="syntax-LITERAL1">'</span><span class="syntax-OPERATOR">)</span>
<span class="syntax-KEYWORD1">ORDER</span> <span class="syntax-KEYWORD1">BY</span> n.nspname, p.proname ;
</span></code>
-- which output this --
funcname | description
---------------------------------+-----------------------------------------------------
pg_available_extension_versions | list available extension versions
pg_available_extensions | list available extensions
pg_extension_config_dump | flag an extension's table contents to be emitted by pg_dump
pg_extension_update_paths | list an extension's version update paths</pre> <br /><a href="http://www.postgresonline.com/journal/archives/216-PostgreSQL-9.1-Exploring-Extensions.html#extended">Continue reading "PostgreSQL 9.1 Exploring Extensions"</a>
Sun, 14 Aug 2011 22:02:00 -0400http://www.postgresonline.com/journal/archives/216-guid.htmlpostgis extensions kmean period temporalpostgresql 9.1State of PostGIS and PostGIS Windows binaries for 9.1 9.0 and 8.4http://www.postgresonline.com/journal/archives/213-State-of-PostGIS-and-PostGIS-Windows-binaries-for-9.1-9.0-and-8.4.html
8.49.09.1new in postgresqlpostgispostgresql versionshttp://www.postgresonline.com/journal/archives/213-State-of-PostGIS-and-PostGIS-Windows-binaries-for-9.1-9.0-and-8.4.html#commentshttp://www.postgresonline.com/journal/wfwcomment.php?cid=2132http://www.postgresonline.com/journal/rss.php?version=2.0&type=comments&cid=213nospam@example.com (Leo Hsu and Regina Obe)
<p>PostGIS 2.0.0 has inched a lot closer to completion. This past week, <a href="http://blog.cleverelephant.ca/" target="_blank">Paul</a> enabled his gserialization work which changed the on disk-format of PostGIS and in return I think we'll have a much better platform to grow on. With this change we now have the 3D index and bounding box bindings in place. Say hello to the <b>&&&amp;</b> operator which is like <b>&&amp;</b>, but is 3D aware and comes with its own companion 3D spatial indexes. This will allow you to do true 3D bounding box searches with any of the new 2.5/3D geometries we have in place for PostGIS 2.0.0. We are still noodling out the semantics of boxes. Read Paul's call for action on the <a href="http://www.postgis.org/pipermail/postgis-devel/2011-June/013654.html" target="_blank">The Box Plan?</a>, if you are interested. PostgreSQL 8.4 is the lowest supported version for PostGIS 2.0.0. It took a bit of squabbling between PSC members to make that decision, but I put my foot down and I think in the end was for the best to allow us to use new features, less platforms to test, and get rid of some unnecessary code. </p>
<h4>PostGIS Windows 32-bit Experimental builds fresh off the presses</h4>
<p>With all these changes, if you are running an earlier alpha release of PostGIS 2.0.0, you'll need to do a dump restore since the on disk format is now changed.</p>
<p>If you are on windows and want to give some of this all a test drive, you can download one of our <a href="http://postgis.net/windows_downloads#PostGIS_2_0_0" target="_blank">PostGIS 2.0.0 Windows experimental builds</a>. We still only have 32-bit builds. We have builds
for PostgreSQL 8.4, PostgreSQL 9.0, and PostgreSQL 9.1 beta 2. The problems we faced in PostgreSQL 9.1 beta 1 were resolved in beta 2 so that most regress tests past except some minor ones involving stupid things like difference in line number marking of errors. Complement your PostgreSQL 9.1 beta 2 meal with a yummy large helping of PostGIS 2.0.0 goodness.</p>
<br /><a href="http://www.postgresonline.com/journal/archives/213-State-of-PostGIS-and-PostGIS-Windows-binaries-for-9.1-9.0-and-8.4.html#extended">Continue reading "State of PostGIS and PostGIS Windows binaries for 9.1 9.0 and 8.4"</a>
Thu, 16 Jun 2011 12:34:00 -0400http://www.postgresonline.com/journal/archives/213-guid.htmlpostgispostgresql 9.1PostgreSQL 9.1 Trigrams teaching LIKE and ILIKE new trickshttp://www.postgresonline.com/journal/archives/212-PostgreSQL-9.1-Trigrams-teaching-LIKE-and-ILIKE-new-tricks.html
9.1basicscontrib spotlightintermediatepgtrgmpostgresql versionshttp://www.postgresonline.com/journal/archives/212-PostgreSQL-9.1-Trigrams-teaching-LIKE-and-ILIKE-new-tricks.html#commentshttp://www.postgresonline.com/journal/wfwcomment.php?cid=2121http://www.postgresonline.com/journal/rss.php?version=2.0&type=comments&cid=212nospam@example.com (Leo Hsu and Regina Obe)
<p>There once existed programmers who were asked to explain this snippet of code: <code>1 + 2</code> </p>
<ul><li>The C programmer explained &quot;It's a common mathematical expression.&quot;</li>
<li>The C++, Java, C# and other impure object-oriented programmers said &quot;We concur. It's a common mathematical expression.&quot;</li>
<li>The <a href="http://en.wikipedia.org/wiki/Smalltalk" target="_blank">Smalltalk</a> programmer explained &quot;1 adds 2.&quot;</li>
<li>The <a href="http://en.wikipedia.org/wiki/Lisp_(programming_language)" target="_blank">Lisp</a> programmer stood up, a bit in disgust, and said, &quot;No no! You are doing it all wrong!&quot;<br /> The Lisp Programmer then pulled out
a <a href="http://en.wikipedia.org/wiki/Polish_notation" target="_blank">Polish calculator</a>, punched in <code>+ 1 2</code>
,and with a very serious face, explained <br /> &quot;+ should be pushing those other two around.&quot;</li>
</ul>
<p>I find this episode interesting because while the Lisp programmer I feel is more right, the Smalltalk programmer has managed to follow the rest of the crowd and still stick
to her core principle. This brings us to what does this have to do with <a href="http://www.postgresonline.com/journal/archives/169-Fuzzy-string-matching-with-Trigram-and-Trigraphs.html" target="_blank">trigrams</a>
in PostgreSQL 9.1. Well just like <code>1 + 2</code> being a common mathematical expression, <code>abc LIKE '%b%'</code> is a common logical relational database expression that we have long taken for granted as not an indexable operation in most
databases (not any other database to I can think of) until PostgreSQL 9.1, which can utilize trigram indices (the Lisp programmer behind the curtain) to make it fast.</p>
<p>There are 2 main enhancements happening with <a href="http://www.postgresonline.com/journal/archives/169-Fuzzy-string-matching-with-Trigram-and-Trigraphs.html" target="_blank">trigrams</a> in PostgreSQL 9.1
both of which depesz has already touched on in <a href="http://www.depesz.com/index.php/2011/02/19/waiting-for-9-1-faster-likeilike/" target="_blank">FASTER LIKE/ILIKE</a>
and <a href="http://www.depesz.com/index.php/2010/12/11/waiting-for-9-1-knngist/" target="_blank">KNNGIST</a>. This means you can have an even faster trigram search than you ever
have had before and you can do it in such a fashion that doesn't require any PostgreSQL trigram specific syntactical expressions. So while PostgreSQL 9.1 might be understanding LIKE much like all the other databases
you work with, if you have a trigram index in place, it will just be doing it a little faster and sometimes a lot faster using the more clever PostgreSQL 9.1 planner.
This is one example of how you can use applications designed for many databases and still be able to utilize advanced features in
your database of choice. In this article we'll demonstrate.</p>
<p>For this example we'll use a table of 490,000 someodd records consisting of Massachusetts street segments and their names excerpted from <a href="http://www.census.gov/geo/www/tiger/tgrshp2010/tgrshp2010.html" target="_blank">TIGER 2010</a> data. You can
download the trimmed data set from <a href="/downloads/featnames_short.zip" target="_blank">here</a> if you want to play along.</p> <br /><a href="http://www.postgresonline.com/journal/archives/212-PostgreSQL-9.1-Trigrams-teaching-LIKE-and-ILIKE-new-tricks.html#extended">Continue reading "PostgreSQL 9.1 Trigrams teaching LIKE and ILIKE new tricks"</a>
Mon, 06 Jun 2011 01:23:00 -0400http://www.postgresonline.com/journal/archives/212-guid.htmlmysqlpostgresql 9.1sql servertrigramtrigraphNavigating PostgreSQL 9.1 beta 1 with PgAdmin III 1.14.0 Beta 1http://www.postgresonline.com/journal/archives/206-Navigating-PostgreSQL-9.1-beta-1-with-PgAdmin-III-1.14.0-Beta-1.html
9.1basicsbeginnerpgadminpostgishttp://www.postgresonline.com/journal/archives/206-Navigating-PostgreSQL-9.1-beta-1-with-PgAdmin-III-1.14.0-Beta-1.html#commentshttp://www.postgresonline.com/journal/wfwcomment.php?cid=2060http://www.postgresonline.com/journal/rss.php?version=2.0&type=comments&cid=206nospam@example.com (Leo Hsu and Regina Obe)
<p>We've started to play with PostgreSQL 9.1beta and the PgAdmin III 1.14.0 Beta 1. We'll briefly go over the cool gems
found in PgAdmin III beta 1. Most of the new features are for navigating the upcoming PostgreSQL 9.1. Well first obstacle we ran into
was we can't get our favorite extension, PostGIS, to compile against PostgreSQL 9.1beta though it did with the alphas, so you won't be seeing any windows experimental builds until we resolve this issue.
Details of ticket here? <a href="http://trac.osgeo.org/postgis/ticket/940" target="_blank">PostGIS 2.0 won't compile for PostgreSQL 9.1 beta1</a></p>
<p>Despite that minor set back, we decided to push on and navigate the new features by using PgAdmin III 1.14.0 as our Tour Guide. Below is a list of new features you can experience
via PgAdmin III 1.14.0 Beta 1. I'm sure there are more we missed, but these are the ones that were most flashing.</p> <br /><a href="http://www.postgresonline.com/journal/archives/206-Navigating-PostgreSQL-9.1-beta-1-with-PgAdmin-III-1.14.0-Beta-1.html#extended">Continue reading "Navigating PostgreSQL 9.1 beta 1 with PgAdmin III 1.14.0 Beta 1"</a>
Fri, 06 May 2011 23:45:00 -0400http://www.postgresonline.com/journal/archives/206-guid.htmlpgadminpostgresql 9.1sql med