Sunday, January 27. 2013
Printer Friendly
This exercise is a continuation of our How to bulk export tables from MS Access. Now while this approach will work for other databases besides PostgreSQL, you'll probably need to fiddle with the subroutine to make it work for some other databases. PostgreSQL is fairly ANSI-SQL standard so not too much fiddling should be required to port to SQL Server, MySQL, Oracle etc.
Unlike the prior Visual basic subroutine we showed that exports the tables, this just creates an SQL script that you run on the already created PostgreSQL database that contains the exported data. We didn't test the quote option though we coded it in the subroutine, since like we said we hate having to quote fields. If perchance you are one of those folks that likes to put spaces in your field names to make it more englishy, then you'll need to quote or revise the other routine to convert your spaces to _ or some other thing.
Continue reading "How to recreate MS Access primary keys and indexes in PG"
Printer Friendly
Despite all the nasty things people say about MS Access, it does have fairly database agnostic, easy to use import and export tools and a not too shabby query wizard and query tool. But of course, it's not a server side database so at a certain point won't scale for database storage. You can still use it as a front-end to a server-side database such as PostgreSQL or SQL Server. So once you outgrow it for data storage, you'll probably want a quick way to export your data out. Unfortunately, well at least in the 2003 version, while you can bulk link tables from an ODBC connection, you can only export one table at a time using the "select table" right-click export. In this article we'll show a quick and dirty export MS Access Visual basic sub routine we use to export all tables. This we've only tested on MS Access 2003, so if you are using higher, you might need to customize it a bit. This script should work fine for exporting to any database connection with modification of the connection string.
Continue reading "How to bulk export tables from MS Access"
Tuesday, January 22. 2013
Printer Friendly
Just a heads up, the Second Edition of PostGIS In Action has officially
reached MEAP stage meaning you can buy now and get draft chapters as we
write them and get final copy when released. Have first drafts of 5 chapters so far. And many more coming shortly.
It is currently the Manning Deal of the Day
January 22, 2013
PostGIS in Action
Second Edition
Get half off the MEAP eBook
or MEAP pBook
Enter pgislaunchau in the Promotional Code box when you check out.
http://www.manning.com/obe2
With purchase you also get the E-Book copy of the first edition.
Saturday, January 19. 2013
Printer Friendly
We discussed a while back the Text array foreign data wrapper that allows you to register and query a delimited file as if it were a regular table with one array column.
It's probably the FDW we use most often and reminded of that recently when I had to query a 500,000 record resident list tab delimited file to prep for geocoding.
When we upgraded to 9.2 and we could no longer compile, I wrote to Andrew Dunstan about this and he kindly created a 9.2 version.
Unfortunately there are still quite a few FDWs broken as a result of the 9.2 changes and I was hoping to try to apply similar patches to them that I saw Andrew do, but
haven't had the patience or time yet. Anyway we've compiled these for 9.2 under our mingw64-w64 and mingw64-w32 chains using Andrew's 9.2 GitHub stable branch.
https://github.com/adunstan/file_text_array_fdw/tree/REL9_2_STABLE
and we've tested them using the PostgreSQL EDB windows VC++ compiled versions. We hope you find them as useful as we have.
I'm hoping to add more FDWs to these 9.2 bags once
we have those working again. If you want to compile yourself or compile others, we have instructions in the packaged README.txt.
Sunday, January 06. 2013
Printer Friendly
In the last article we said you can't have a LEFT JOIN with LATERAL. Turns out we were mistaken and YES indeed you can and when you do it is equivalent or more powerful than SQL Server's OUTER APPLY.
Let us say that in our query we wanted all my zips returned but for ones that had keys where the value is in a certain range, we want those keys returned. we'd do this. The fact we need all even if they have no such keys necessitates us putting the condition in the ON rather than the WHERE.
SELECT zip, (h).key, (h).value As val
FROM zcta5 AS foo
LEFT JOIN LATERAL each(hstore(foo) - 'zip'::text) As h
ON ((h).value BETWEEN '12345' and '14567')
ORDER BY zip
limit 5;
Output would be
zip | key | val
------+--------+----------
00601 | |
00602 | |
00603 | |
00606 | awater | 12487.00
00610 | hu10 | 12618
Printer Friendly
One of the improvements coming in PostgreSQL 9.3 is the new LATERAL SQL clause. LATERAL allows you to write more
succinct code than you would be able to otherwise and will be a welcome companion to extensions like hstore and PostGIS which both
have a plethora of set returning functions. In this article, I'll just demonstrate it's use with hstore and subsequent I'll talk
about it's potential use in PostGIS raster,geometry, and topology for exploding subelements.
One thing I was very interested in aside from the succinctness is whether it will be more performant than the older approach.
A perfect test case for hstore would be the example we just demonstrated in Unpivoting data in PostgreSQL. For this example, we'll rewrite the hstore view using LATERAL instead of employing a subselect.
Continue reading "PostgreSQL 9.3 Lateral Part 1: Use with HStore"
Friday, January 04. 2013
Printer Friendly
A while ago we demonstrated how to create cross tabulation tables using tablefunc extension aka (Pivot Tables) (basically collapsing
rows into columns).
Recently someone asked me how to do the reverse (convert columns to rows). He found a solution to the problem here: http://stackoverflow.com/questions/1128737/unpivot-and-postgresql
using a combination of array and unnest. That approach is very similar to SQL Server's built-in Pivot SQL predicate.
The solution seemed
nice enough except similar to the SQL Server Unpivot, it required knowing the column names beforehand so very hard to genericize.
So would it be possible to accomplish this feat without knowing the columns names (except for the key) and be able to do it with one SQL statement. I realized that the
PostgreSQL hstore extension fit the bill nicely. In this article I'll demonstrate both approaches by creating a view
using both.
Continue reading "Unpivoting data in PostgreSQL"
Monday, December 31. 2012
Printer Friendly
We recently started upgrading some of our databases on windows 64-bit to 9.2, in doing so we ran into a nasty issue we discovered when some of our backups were failing. The first time it happened, I chucked it up to a dirty PostgreSQL 8.4 database being restored to PostgreSQL 9.2.1. The second time it happened restoring a 9.1 database to 9.2.2, I thought, better look into this to see if there is a known issue. Low and behold I found this:
http://archives.postgresql.org/pgsql-bugs/2012-12/msg00091.php (Bug #7741). Apparently something to do with granting rights on Types.
As a workaround for this problem so our backups would work again was to delete the offending permissions from system tables. It's probably not the best way but only way we could think of, we delete the bad record in pg_default_acl and after that backup works without complaint.
SELECT * into zz_bad_pg_default_acl FROM pg_default_acl WHERE defaclobjtype = 'T';
DELETE from pg_default_acl WHERE defaclobjtype = 'T';
If anyone else has further input on this, I'd be interested.
Thursday, December 13. 2012
Printer Friendly
We just finished the first draft of the first 5 chapters of the second edition of PostGIS in Action and is slated to be added to Manning's Early Action Program (MEAP) in the next 2-3 weeks. Some people have asked us about this when they can start purchasing the new edition. The new edition is purchaseable as soon as it hits MEAP phase. With a MEAP purchase you get the E-Book drafts as soon as they are available and if you buy the MEAP with hard-copy option, you also get the final hard-copy when released. MEAP is the same price as the regular book except it can only be bought direct thru Manning and it gives you access to early content so you can see all our mistakes and cross outs as things change.
We shuffled some of the chapters a bit from our earlier table of contents, but in these first 5 chapters you'll be exposed to new features in PostGIS 2.0, the more modern way of creating spatial tables, utilizing the new raster and topology types, and also find out about the new great stuff coming in PostGIS 2.1 that is already available in PostGIS 2.1 pre-release. More on that in the coming weeks.
What is coming in PostGIS 2.1 that you don't want to miss? Lots. Check out our Waiting for PostGIS 2.1 series and also a list of Duncan Golicher's highlights, which Pierre Racine has kindly outlined in Duncan Golicher's series of PostGIS articles
Printer Friendly
As Hitoshi Hirada mentioned already in PL/v8 is now the richest procedural language , PL/V8 1.3 has been recently released and some of the great new features are automatic conversion of json objects, and ability to write window functions. Not only does PL/V8 allow you to make the most of the native JSON support in PostgreSQL 9.2, but in many cases particularly numeric processing, the speed is much better than what you get with plpgsql. Someone asked us a while back about this and we are hoping to do a demonstration of pl/v8 for numeric processing where you can see the marked difference in speed and perhaps compare with something like PL/R that is also often used for array numeric processing.
For those who are on windows we've compiled PLv8 1.3 for windows 32-bit and windows 64-bit that work with PostgreSQL EDB installs. We haven't had a chance to thoroughly test them yet, so let us know if you find issues. We've saved the last versions we compiled under a different name since some things are not backward compatible between the PLv8 1.1 and 1.3 releases.
We are also hoping to making our windows binary download links more visible. For stop gap fix, we are tagging all the articles concerning things like FDW extensions, PostGIS, plv8 we've compiled for windows under winextensions and accessible from http://www.postgresonline.com/winextensions.php
Continue reading "PLV8 1.3 windows binaries for PostgreSQL 9.2"
Wednesday, November 28. 2012
Printer Friendly
This was an article we were meaning to write and got reminded by Szymon Guz latest PostgreSQL search_path behavior.
The PostgreSQL search_path variable allows you to control what order schemas are searched and which schemas do not require schema qualification to use tables/views/functions in the schema. The pg_catalog schema is one that never requires schema qualification even if you don't add it to your schema search_path. Schemas are searched in the order they are listed
and when creating new objects (e.g. tables, views, function, types) that are not schema qualified, they are always created in the first schema of the search path. Any objects not in a schema listed in the search_path must be schema qualified.
Continue reading "Schema and search_path surprises"
Monday, November 12. 2012
Printer Friendly
Issue: Ever had the situation where you decided to delete a table or some other function or type but you weren't absolutely sure if other functions in the database depended on these.
For things like tables and views that depend on a function, type or other table/view, PostgreSQL won't allow you to delete the dependency object without doing a drop cascade.
For stored procedures though, while it will prevent you from deleting a dependency type if the function returns or takes as input the object to be deleted, it doesn't save you if the body of the function references these objects. This dependency information is not always known and in fact may be dynamic with dynamically generated sql or schema path settings.
So how do you inspect functions for usage of other items?
Solution:
PostgreSQL has a table called pg_catalog.pg_proc which stores the source code of functions (non-C) in the prosrc column.
So lets say you had a table called employees you want to get rid of or simply rename, to find out all functions that reference the term "employees", you would do something like this:
SELECT proname, proargnames, prosrc
FROM pg_proc
WHERE prosrc ILIKE '%employees%';
Saturday, November 10. 2012
Printer Friendly
We've been fighting to get clients we have thinking of upgrading or creating new apps to also choose PostgreSQL in the process.
Here I'll just itemize some of the obstacles we've run into in making the sale. All of these fall under the It is not Database X line item.
By database X I mean SQL Server, MySQL, and Oracle and for us in exactly that order. Our obstacle focus is probably a bit different from others since
we are consultants to mostly Windows shops or consultants to ISVs who have to sell their applications to U.S. government agencies or units of agencies.
Continue reading "Biggest Obstacle to PostgreSQL Adoption: It is not Database X"
Monday, October 15. 2012
Printer Friendly
We were recently looking for an open source light-weight database web administration tool that would be preferably database cross-platform and would work on either ASP.NET or PHP. Adminer fit the bill.
Adminer is written in PHP.
It supports, out of the box PostgreSQL, MySQL, SQLite2 and SQLite3, and even SQL Server and Oracle. This covers most of the databases we commonly use.
Here we'll go over some other features we liked about it that are rare in other web database administration tools.
Continue reading "Adminer web-based database administration"
Thursday, October 04. 2012
Printer Friendly
In the spirit of Depesz, Waiting for PostgreSQL 9.3 series, we've started our own little PostGIS series on our Boston GIS blog called Waiting for PostGIS 2.1 that showcases all the new cool features coming in PostGIS 2.1 as they get committed to the code base. Check it out.
If you are on windows, the PostGIS 2.1 window binary builds get built whenever there is a change in PostGIS 2.1 code base by Winnie our windows PostGIS build bot, for PostgreSQL 9.0-9.2 (64-bit) and 9.2 (32-bit), so you can't use the I can't compile excuse if you are on windows, not to test out the new changes :). You can download these from Windows Experimental Builds. We are hoping to do the same for Debian Squeeze (6) soon. The main reason we built our bot Debbie on Debian (which makes the source tarballs), is because Debian is a very popular deployment platform for PostGIS, that is currently underserved so we have many Debian users frustrated at not having a readily available PostGIS 2.0 release for example.
|