Friday, July 01. 2016
Continue reading "regexp_split_to_table and string_to_array unnest performance"
Whenever you need to split a text into multiple records breaking by some delimeter, there are two common options that PostgreSQL provides. The first is
regpexp_split_to_table and then next popular is using the unnest function in combination with string_to_array.
Here is an example using regexp_split_to_table:
FROM regexp_split_to_table('john,smith,jones', ',') AS a;
You can achieve the same result by using the construct:
FROM unnest(string_to_array('john,smith,jones', ',')) AS a;
With short text you won't notice much perfomance difference. But what happens if we pass in a humungous text?
Saturday, April 02. 2016
Continue reading "PostGIS 2.2 Windows users hold off on installing latest PostgreSQL patch release"
Someone reported recently on PostGIS mailing list, that they were unable to install PostGIS 2.2.1 bundle or PostGIS 2.2.2 binaries on a clean PostgreSQL 9.5.2 install.
Someone also complained about PostgreSQL 9.3 (though not clear the version) if that is a separate issue or the same. I have tested on PostgreSQL 9.5.2 Windows 64-bit and confirmed the issue. The issue does not affect PostgreSQL 9.5.1 and older. I haven't confirmed its an issue with the 32-bit installs, but I suspect so too. This
issue will affect OGR_FDW users and people who used our compiled WWW_FDW.
Monday, June 29. 2015
Continue reading "PostgreSQL OGR FDW update and PostGIS 2.2 news"
PostGIS 2.2 is planned to reach feature freeze June 30th 2015 so we can make the September PostgreSQL 9.5 curtain call with confidence. Great KNN enhancements for PostgreSQL 9.5 only users. I've been busy getting all my ducks lined up. A lot on tiger geocoder and address standardizer extension to be shipped with windows builds, story for later. One other feature we plan to ship with the windows PostGIS 2.2 builds is the ogr_fdw ogr_fdw Foreign data wrapper extension. I've been nagging Paul Ramsey a lot about issues with it, this in particular https://github.com/pramsey/pgsql-ogr-fdw/issues/25, and after some prodding, he finally put his nose in and fixed them and pinged Even Rouault for some help on a GDAL specific item.
Needless to say, I've been super happy with the progress and support I've gotten with ogr_fdw development and really enjoying my ogr_fdw use. The XLSX reading a file saved after the connection was open required a fix in GDAL 2.0 branch (which missed GDAL 2.0.0 release, so because of this, this new package contains a GDAL 2.0.1ish library. Hopeful GDAL 2.0.1 will be out before PostGIS 2.2.0 comes out so I can release without guilt with this fix.
Saturday, January 24. 2015
Continue reading "Using SSL https connections with www_fdw on windows"
One of the foreign data wrappers I included in the PostgreSQL 9.3 Windows FDW bag and PostgreSQL 9.4 Windows FDW bag is the www_fdw extension used for querying web services. Someone asked that since I didn't build curl with SSL support,
they are unable to use it with https connections. The main reason I didn't is that the EDB installs come with ssleay32.dll and libeay32.dll (even the 64-bit) which
are dependencies of curl when built with SSL support. I wanted to minimize the issue of distributing dlls that are packaged with Windows PostgreSQL installers already.
Though this article is specific to using www_fdw on Windows systems, many of the issues are equally applicable to other platforms, so may be worth a read if you are running into similar issues with using specialty SSL certificates on Linux/Unix/Mac.
Thursday, January 22. 2015
Continue reading "Installing PostGIS packaged address_standardizer on Ubuntu"
One of the changes coming to you in PostGIS 2.2 are additional extensions. Two ones close to my heart are the address_standardizer (which was a separate project before, but folded into PostGIS in upcoming 2.2) and the SFCGAL extension for doing very advanced 3D stuff (was just an sql script in older versions, but made an extension in 2.2 and new functions added). We had a need to have address standardizer running on our Ubuntu box,
but since PostGIS 2.2 isn't released yet, you can't get it without some compiling. Luckily the steps are fairly trivial if you are already running PostGIS 2.1.
In this article, I'll walk thru just building and installing the address_standardizer extension from the PostGIS 2.2 code base. Though I'm doing this on Ubuntu,
the instructions are pretty much the same on any Linux, just replacing with your Linux package manager.
Saturday, January 03. 2015
Continue reading "Updated Foreign Data Wrappers for PostgreSQL 9.3 Windows"
As stated in last article, I've packaged FDW binaries for PostgreSQL 9.3 windows 32-bit and 64-bit and added in the ogr_fdw one. These we've tested with the standard EDB Vc++ built PostgreSQL windows installs and work fine with those.
This package is an updated list from ones we've distributed before that includes ogr_fdw and recompiled with latests source from www_fdw and file_textarray
Saturday, December 06. 2014
Oracle FDW is a foreign data wrapper PostgreSQL extension that allows you to read and write to Oracle database tables from a PostgreSQL database. You can get it via the PGXN network or the main website http://laurenz.github.io/oracle_fdw/.
What is new about the latest 1.1.0 release is that there is now support for the Oracle SDO_GEOMETRY type that allows you to map the most common geometry types POINT, LINE, POLYGON, MULTIPOINT, MULTILINE and MULTIPOLYGON to PostGIS geometry type. Much of the spatial plumbing work was done by Vincent Mora of Oslandia. If we have any Windows Oracle users out there, yes there are binaries available for windows for PostgreSQL 9.1- 9.4 for both 32-bit and 64-bit. The FDW does have a dependency on the OCI.dll which I think comes shipped with Oracle products. Unfortunately, we are not Oracle users so can't kick the tires.
Friday, May 30. 2014
Continue reading "psql watch for batch processing"
A while back, we discussed using pgAdmin pgScript as a quicky way for
running a repetitive update script where you want each loop to commit right away. Since stored functions have to commit as a whole, you can't use stored functions alone for this kind of processing.
Question: Can you do similar easily with psql?
yes with the \watch command described nicely by Michael Paquier
a while back.
If you are using the psql client packaged with PostgreSQL 9.3 or above,
then you can take advantage of the
\watch command that was introduced in that version of psql. We'll demonstrate that
by doing a batch geocoding exercise with PostGIS tiger geocoder and also revise our example from the prior article to use the more efficient and terser LATERAL construct introduced in PostgreSQL 9.3.
Wednesday, March 26. 2014
Continue reading "An almost idiot's guide to install PostgreSQL 9.3, PostGIS 2.1 and pgRouting with Yum"
In this exercise, we'll go thru installing PostgreSQL 9.3 on a CentOS 6 64-bit box. We'll cover upgrading in a later article. For the rest of this article, we'll go over configuring yum to use the PostgreSQL PGDG Yum repository found at http://yum.postgresql.org
, which has the latest and greatest of 9.3. It's been a while since we wrote step by step instructions for installing with Yum.
Note: PostGIS 2.1.2 release is eminent, so you might want to wait till we release and Yum is updated before you install/upgrade.
Saturday, December 21. 2013
Continue reading "Using wget directly from PostgreSQL using COPY FROM PROGRAM"
One of the features new to PostgreSQL 9.3 is the
COPY FROM PROGRAM sql construct and in psql the equivalent
\copy from program. Michael Paquier covered the COPY TO/FROM PROGRAM in hist Postgres 9.3 feature highlight: COPY TO/FROM PROGRAM. Depesz covered the companion psql construction in
Support for piping copy to from an external program.
Michael demonstrated an example using curl. I wanted to try something similar using wget since I have wget readily available on all my Linux and Unix boxes. For this example I'll demonstrate doing it on windows, but doing it on Linux is much the same and simpler since the wget and curl are generally already in Linux default path.
Saturday, November 09. 2013
Continue reading "Migrating from SQL Server to PostgreSQL"
Alexander Kuznetsov on SQLblog.com
has an interesting series going entitled with Learning PostgreSQL. In the series he focuses on what it takes to move
a SQL Server database and App to PostgreSQL and highlights some of the key differences between the two platforms that you should watch out for.
I recommend it to any SQL Server developer planning to make the switch to PostgreSQL or any PostgreSQL consultant tasked with the job
and not familiar with the intricacies of SQL Server.
His PostgreSQL feed can be found here here
So far on his list of articles in the series:
Saturday, November 02. 2013
Continue reading "Generating Create Foreign Table Statements for postgres_fdw"
In PostgreSQL 9.3 Postgres_FDW: A Test Drive
we talked about taking Postgres Foreign Data Wrapper for a test drive. One downside of the Postgres FDW and actually most PostgreSQL FDWs is that
the foreign table structure can't be inspected from the source and the fields have to be explicitly stated in the foreign table definition. If you have a lot of tables to script, this can
quickly become tedious. For our planned used cases, we plan to script Foreign tables from a source database for tables that are bulky and rarely change
and then build materialized views against those for faster performance where needed. To help in this end, we wrote a quick
SQL function that you install on the source database and when run will generate foreign table creation statements to install on a target database.
One thing that was important to us was that the function properly handle views and typmod types since PostGIS now uses typmod heavily and many of our databases have spatial data
and complex views we'd like to link in as foreign tables.
Saturday, September 28. 2013
Continue reading "PostGIS 2.1 Bundle for PostgreSQL 9.3 Windows Stackbuilder"
PostGIS 2.1 bundle for PostgreSQL 9.3 Windows is now available on StackBuilder for windows users. You will find both the 32-bit and 64-bit versions. The bundle includes the following features:
- PostGIS 2.1 with extensions support for postgis (which has geometry/geography/raster), postgis_tiger_geocoder, and postgis_topology.
- pgRouting 2.0.0 with extension support -- just do
CREATE EXTENSION pgRouting
- address_standardizer, which is needed for better tiger geocoding normalizing. Installed with
CREATE EXTENSION address_standardizer
One thing that is different with this packaging is that we are no longer providing a template_postgis database. However if you want your database created for you,
you can check the Create spatial database option which will create a regular old database with all the packaged extensions under the sun created for you.
Monday, September 09. 2013
Continue reading "PostGIS 2.1 windows bundle"
PostgreSQL 9.3 came out today and we are excited to start using the new features. PostGIS 2.1.0 came out about 2 weeks ago and pgRouting just cut the RC 3 release. For windows PostGIS users who are impatient to try the new suite, binaries
can be found on the Unreleased versions of PostGIS.net windows page.
We are planning an official release sometime probably next week on StackBuilder. We are waiting for release of pgRouting 2.0 before we do which should be out next week. This new 2.1 release will be dubbed the PostGIS 2.1 Bundle since it will have more than just PostGIS. It will include postgis extensions (postgis which includes geometry,raster, geography) , postgis_topology, postgis_tiger_geocoder), address_standardizer extension (a companion to tiger geocoder), and pgRouting 2.0.
For those people running PostGIS 2.0 and PostgreSQL 9.0+, especially (raster and geography) users, I highly recommend you jump to PostGIS 2.1. PostGIS 2.1 is a soft upgrade from 2.0. For raster there are enormous speed improvements and new functions. The ones we are most excited about in raster are the much much much faster ST_Clip and ST_Union functions (which now does multi-band in addition to being faster). These two functions are highly important since they are generally the first step in many raster workflows. Geography has speed improvements for point in poly and a ST_Segmentize function done on the spheroid (important for long range). Geometry has a couple of new functions. The Enhanced 3D functionality provided by SFCGAL is brand new and probably won't be distributed by many package maintainers until PostGIS 2.2 where it will garner a few more features and stability improvements.
Friday, August 23. 2013
Continue reading "CREATE SCHEMA IF NOT EXISTS in 9.3 and tiger geocoder"
One of the new features in PostgreSQL 9.3 is
CREATE SCHEMA IF NOT EXISTS someschema;. We were so excited about this new feature that we started using it in the tiger geocoder loader routine. For some reason we thought it was available since 9.1 which gained
CREATE TABLE IF NOT EXISTS sometable;
which we noted in Sweat the small stuff, it really matters