Table Of Contents
Using Recursive Common table expressions to represent Tree structures Intermediate
From the Editors
PostGIS changing of the Guards
As David Page already noted, Leo and I are taking over responsibility of building PostGIS windows one-click installers/stack builder from Mark Cave-Ayland. The PostGIS 1.4 windows packaging was a little late in coming this time since it was our first and also some things changed in the PostGIS packaging for 1.4. Even so we made some mistakes such as statically compiling libproj in with the postgis-1.4.dll and forgetting some new images in the packaged html help, which we will fix in 1.4.1 release.
Mark will still be providing a supporting role and helping out when we screw up or helping us if we run into compile issues as we go along so he's not going away; he will be a great safety net. When Mark started his role a long time ago, he was as many would like to say "Very entrenched in the dark side," and over the years, he has seen the light. As a result, these moments of catching issues in the PostGIS release cycle that effect windows users such as troubleshooting the memory bug in the loader files that affected Windows Vista users and testing on various Windows OS, has fallen on us, because well we have access to all windows os.
It also became painful for Mark to walk in the shadow of darkness once he had seen the light. Luckily we are still windows addicts so this having to constantly test on Windows and building for Windows is something we would naturally do anyway and yes as shocking as it sounds we do run some production PostgreSQL apps on windows and it works pretty well, thank you very much. We don't expect this to change any time soon.
As part of this change, we hope to provide more interim windows builds of PostGIS so windows users can experiment with future releases before they come out. Yes compiling on windows is a tad bit more difficult than on Linux. These PostGIS windows experimental builds can be found http://postgis.net/windows_downloads
Main changes in PostGIS
- The PostGIS steering committee has agreed to be good and not be adding new functions between micro releases of PostGIS as we have done in the past and as we've been smacked around for. As part of that change, from PostGIS 1.4 moving forward each micro version will overwrite the previous micro version in the MS Windows registry. E.g. 1.4.1 will overwrite 1.4.0 so no need to uninstall the old and reinstall to get rid of registry junk. Just install on top of your existing 1.4.
- As of PostGIS 1.4 it is possible to run different versions of PostGIS in different databases on teh same PostgreSQL server install since the .so/.dll from minor to minor have unique names (naming is postgis-1.4.so (postgis-1.4.dll), postgis-1.5.so etc). This is mostly useful for testing and comparing different versions of PostGIS before you officially upgrade and if you have several different spatial apps using different databases, you don't risk breaking them all at once.
- PostGIS is now an official incubation project of OSGEO. Things are still being drafted. But I guess that means our PostGIS defacto steering committee composed of Kevin, Paul, Mark, and myself is now more or less official.
On the topic of Writing
We just submitted first revision of chapter 6 of our upcoming book PostGIS in Action and are working on our second revision of that chapter. The second revision will most likely be split into 2 chapters because its too long for a single chapter. It will cover loading/exporting various format types of spatial data and everything you never wanted to know about spatial reference systems.
We also submitted the second revision of the upcoming RefCardz DZone Essential PostgreSQL. Not sure when that will be released since we are already a bit behind schedule. I imagine we will have a couple more iterations of that before its done. It is really hard to cram into six pages the most important things that every PostgreSQL user should know and all the exciting stuff in PostgreSQL without feeling like you are leaving something critical out.
I'm always amazed how people manage to write books by themselves. If you think for each chapter you go thru 4-5 revisions and each subsequent revision is about 20-50% of the previous, that's a lot. Leo and I are writing together and its still a struggle though its been fun. I guess writing together has its own challenges because you are constantly cutting each others stuff out and arguing over what is important and what is not. We have managed to not have too many heated debates that would spoil dinner. This is good since Leo does the cooking because he thinks I'm too absent-minded to be capable of complex tasks like cooking.
From the Editors
Just read that Microsoft has formed a new foundation called CodePlex foundation, presumably to spinoff their Code plex site and allow it to stand separately from Microsoft. The mission appears to be to allow an easier avenue for developers working for proprietary software companies to contribute to open source projects.
Monty has some details about this on his blog The CodePlex Foundation: Why is Microsoft founding it?.
The line up of people they have on their advisory board (including Monty) and board of directors is interesting CodePlex About. I'm particularly happy that Miguel De Icaza is on the board since he is one of my favorite people and I believe shares my pragmatic ideals on the synergy between open source and non-open source software. I wonder what it takes to get on this board. It would be really nice if someone in the PostgreSQL community were on this board just to ensure the needs of the PostgreSQL community (especially our growing number of windows users) is well represented.
As to the argument of Monty's that software for sell is dying, not sure I quite agree though haven't given it much thought. Certainly I would like to think that service for sell is rising since that's the business we are in and enjoy most. One thing I believe is that software is getting more complicated and people expect more. With that said even as a company that sells software, you would be foolish not to try to leverage on the open source software out there that fits nicely into your codebase. You just won't be able to compete even with the sole proprietor next door who is with it.
Microsoft reinventing itself
First I would first like to give this caveat that yes I am a pro Microsofter. Always was, and really can't see myself changing. Over the years I have seen myself change along with the face of Microsoft. I no longer use just Microsoft products, but partake too of all the good things that the world has to offer, much of which thank goodness is free in the sense of not costing as well as not having restrictive uses, and fitting often more nicely with our clients codebase and general requirements than closed source software.
I have observed over the years, that Microsoft has been embracing PHP and basically trying to make the world forget about ASP. This all seemed puzzling to me at first and then realized it makes perfect logistical sense. ASP is a dead product and even as the owner of said dead product, you want to get your customer off of it as quickly as possible so you don't have to waste money supporting it. That is why the PostgreSQL and other communities push people to upgrade. All those die-hard ASP fans hated ASP.NET. ASP.NET was too complicated for their needs. Ironically the transition for many people from ASP to PHP was a much easier one than from ASP to ASP.NET. PHP also had the advantage of running pretty consistently on Linux as it did on Windows. Work still needs to be done to allow ASP.NET that luxury. I happen to like both for very different reasons and workflows.
Last week I noticed what appears to be an acceleration in whatever company Microsoft is turning into. I wanted to try out the
new SQL Server 2008 Reporting Services by downloading the free SQL server 2008 Express with Advanced Services. Last time I downloaded it the install of SQL Server 2008 express was a bit painful, but
this time I spent a lot of time puzzled. You see I had to download this thing called a Web Platform Installer. It recognized the dependencies I was missing and dowloaded it for me. What puzzled me however were these screens.
The strange thing is that a lot of the PHP side of products are MySQL centric and don't even work with SQL Server to my knowledge and much of it doesn't work with PostgreSQL (except for Moodle, Gallery, the variant of Drupal they packaged Acquia Drupal - claims to only support MySQL. What happened to plain Drupal that supports PostgreSQL). So on the one hand I was glad that Microsoft was embracing open source and on the other hand I was irritated by the choice of offerings. I'm not sure if I can blame Microsoft for the lack of applications I can actually use. I'm sure a poll was done and this is what people commonly use and being customer centric as they are that is what they put out.
PostgreSQL Q & A
Terminating Annoying Back Ends Beginner
One of the small little treats provided in PostgreSQL 8.4 is the new pg_terminate_backend function. In the past when we wanted to kill runaway postgresql queries issued by a database or user or hmm us, we would call the pg_cancel_backend function. The problem with that is it would simply cancel the query in the backend process, but often times the offending application would simply launch the same query again.
In PostgreSQL 8.4 a new function was introduced called pg_terminate_backend. This doesn't completely replace pg_cancel_backend, but basically does what you do when you go into say a Windows Task manager and kill the offending postgres process or on Linux, you call a kill command on a postgres process. Its nicer in the sense that you can do it all within PostgreSQL and you can use the pg_stat_activity query to help you out a bit. Also you don't run the risk as easily of killing the root postgres process and killing the postgres service all together.
Below are the ways we commonly use these functions. One of the features I really love about PostgreSQL which I miss when working with SQL Server, is the ability to call a function that does something from within a query. This feature makes SQL so much more powerful.
Slap on Wrist
-- Cancel all queries in an annoying database SELECT pg_cancel_backend(procpid) FROM pg_stat_activity WHERE datname = 'baddatabase'; -- Cancel all queries by an annoying user SELECT pg_cancel_backend(procpid) FROM pg_stat_activity WHERE usename = 'baduser';
Baseball bat to the head
Terminating backends is also useful for freeing up memory from idle postgres processes that for whatever reason were not released or if you need to rename a database and need to kill all live connections to a database to do so.
-- terminate process by annoying database SELECT pg_terminate_backend(procpid) FROM pg_stat_activity WHERE datname = 'baddatabase'; -- terminate process by an annoying user SELECT pg_terminate_backend(procpid) FROM pg_stat_activity WHERE usename = 'baduser';
One thing we have noticed with the baseball approach to database management is that it doesn't always play well with pooled connection like things. For example in one PHP app we had that uses pooled connections, the connections became stale. Or at least we think this is the culprit. So you sometimes have to restart the app, or it sometimes gives annoying messages to the user until the dead connections are released. With that said, we still try the slap on the wrist before reaching for the baseball bat. Its always nice to have an easy to swing baseball bat handy though.
Cross Compare of PostgreSQL 8.4, SQL Server 2008, MySQL 5.1
Comparison of PostgreSQL 8.4, Microsoft SQL Server 2008, MySQL 5.1
In our May 2008 issue of Postgres OnLine Journal, we cross compared Microsoft SQL Server 2005, MySQL 5, and PostgreSQL 8.3. Some people mentioned well since 8.4 has now come out, shouldn't we go back and update the reference. We deliberated and decided not to. To be fair all 3 products have released new versions, so it would seem unfair to compare a newer PostgreSQL against older versions of MS SQL Server and MySQL. We have therefore decided to repeat our exercise and include parts people felt we should have covered, as well as comparing the latest and greatest stable release of each product.
People ask us time and time again what's the difference why should you care which database you use. We will try to be very fair in our comparison. We will show equally how PostgreSQL sucks compared to the others. These are the items we most care about or think others most care about. There are numerous other differences if you get deep into the trenches of each.
People have been posting comments on Reddit as well
|Feature||Microsoft SQL Server 2008||MySQL 5.1||PostgreSQL 8.4|
|OS - Why is this important? Why would you even dream of not running on Windows? If you decide one day that Microsoft is not your best friend in the whole wide world, you can ditch them or at least on your DB Server (could that ever happen?). On a side note, Microsoft can't compete with Oracle on Linux/Unix anyway. If Microsoft has a non-Microsoft DB running on a customer's box, I wonder which database they would prefer - Oracle, IBM DB2, Sun MySQL or PostgreSQL?||Windows Desktop/Server||Windows Desktop/Server , Linux, Unix, Mac||Windows Desktop/Server, Linux, Unix, Mac|
|Licensing||Commercial - Closed Source, Various levels of features based on version, Free Crippleware (4 GB limit but free for embedding inclusion)||GPL Open Source, Commercial. Now owned by Oracle, and some of us are still wondering how Oracle will make hay out of its new found treasure. Will they use to upsell. Monty Widenius has an interesting blog entry explaining the MySQL dual licensing and how it has changed.||BSD Open Source. Its the freest of all and many argue about it. You can fork it all you want without giving back and make your own commercial derivative. Though ironically there are not all that many forks. Netezza forked it initially (though its unclear if any PostgreSQL code lives on in their product), GreenPlum forked it, EnterpriseDb forked to give Oracle features, but to their credit gives back a lot of fixes to the community.|
|Install/Maintenance Process||Most time-consuming to install and most dependencies, but lots of wizards to help you forget everything and mail you when somethng bad happens (non-free versions)||Still Easiest. There are a lot of prepackaged products with it and its install rarely fails.||Medium (I put PostgreSQL at medium because on occasion we have run into issues where we have manually init the db on Windows. Those are rare though. On Linux its still the deal that MySQL often comes pre-installed for you and for PostgreSQL you have to figure it out yourself or they give you some super antiquated version. With PostgreSQL Yum respository its much easier now if you manage your own box.|
|Drivers already installed on Windows||Yes - when you have a windows shop this is huge especially when you are not allowed to install stuff on client desktops and you need to integrate seamlessly with desktop apps. This is why using SQL Server Linked Server to get at yummy features of PostgreSQL comes in handy.||No||No|
|ODBC, JDBC, ADO.NET drivers available||Yes||Yes||Yes|
|Open Source products available for it||Few except CodePlex/.NET||Many||Few but ramping up and in PHP more than SQL Server|
|Commercial||Moderate -- still a lot of commercial stuff hasn't been certified on 2008 because 2008 is so new||Moderate||Moderate -- I think PostgreSQL has improved since last we checked, but probably still lower than MySQL penetration.|
|Updateable Views||Yes - even for 2 table views will automatically make them updateable if they have keys and update does not involve more than one table. You can write instead of triggers against more complex views to make them updateable||Yes - Single one table views are automatically updateable, some 2 table views are updateable if they don't have left joins and don't involve update of more than one table. If you have more complex views you want to make updateable - good riddance - no support for triggers or rules on views.||Yes, but not automatic. You have to write rules against views to make them updateable but can make very complicated views updateable as a result.|
|Materialized/Indexable Views||Yes but varies slightly depending on if you are running SQL Express, Workgroup, Standard, Enterprise and numerous restrictions on your views that makes it of limited use||No||No, but there are I think 2 contrib modules e.g. matviews that are simple and basically rebuild the materialized view|
|Can add columns and change names, data types of views without dropping||Yes||Yes||Yes - sort of - as of 8.4 you can now add columns to the end of view without dropping it.|
|Can drop tables, (drop, change size, data type of columns), and views used in views - this is a arguably a misfeature but sometimes it comes in handy when you are an EXPERT user :)||Yes - (but if you schema bind your tables and views, you can not drop dependent objects so this does appear to be the best of both worlds)||Yes - yikes!||No|
|Graphical Query/View Designer (e.g. you can see tables and select fields drag lines to do joins) included no additional charge. As people pointed out there are lots of commercial and free tools that will do this for you. We'll provide a listing and brief summary of features etc. of some of these in our product showcase later.||Yes via SQL Management Studio and Studio Express and pretty nice.||No||Yes. As of PgAdmin III 1.9, but its kind of hockey -- doesn't do JOINS right.|
|Computed Columns||Yes - but we still like using Views more except when we really need the computed column indexed and often we just do triggers. Computed columns are of very limited use since they can't hold roll-ups.||No - but looks like its slated for future release||No - but PostgreSQL has functional indexes so just use a view.|
|Functional Indexes - indexes based on a function||No - but you can create a computed column and create an index on it||No||Yes|
|Partial Indexes - e.g. you want to create a unique index but only consider non-null values||Yes - as of SQL server 2008 See Tom's notes and called Filtered Indexes.||No||Yes|
|ACID compliance - do I dear say this is sometimes over-rated - not all data is created equal and sometimes bulk-insert speed is more important than ACID||Yes||Some storage engines e.g. InnoDB, PBXT (see comments from Giuseppe Maxia) and (not MyISAM)||Yes|
|Foreign Key - Cascade Update/Delete||Yes||InnoDB and not MyISAM||Yes|
|Multi Row value insert||Yes||Yes||Yes|
|UPSERT logic - where you can simultaneously insert if missing and update if present||Yes via MERGE UPDATE||Yes - via INSERT IGNORE, REPLACE INSERT ON DUPLICATE UPDATE||No|
|Replication - haven't used much except for SQL Server so this is mostly hear-say||Yes - all sorts - log shipping, mirroring, snapshot, transactional and merge etc. and can even have non-SQL Server windows-based subscribers. Its still a bear to get working the way you want it and makes making structural changes difficult. Built-In||Yes - including master-master (built-in) See comments below and from numerours reports a big selling point of MySQL.||Yes but from reports seems to be the least polished of the bunch, although numerours third-party options to choose from that are both free and non-free. PostgreSQL 8.5 or higher is slated to have built-in replication. Sorry guys this did not make it in. Slony is still used for replication, and many like it but find it finicky and harder to use than MySQL.|
|Can program stored procs/functions in multiple languages||Yes - In theory any language that complies with CLR -e.g VB.Net, C#, IronPython - but you need to compile into a dll first and then load the dll into the database. The dll is stored as part of the database and the dependencies registered in the SQL Server GAC - a real PITA if you have lots of these dependencies than are non-standard.||No (except C and Pl/SQL)||Yes - PostgreSQL just does it the cool way (common ones PL/PgSQL, sql, PL/Python, PL/Perl, PL/R) - we like having our code right there where we can see what it is doing. Downside server must host the language environment. It now supports variadic functions similar to Oracle. Neither MySQL nor SQL Server support that.|
|Can define custom aggregate functions||Yes - any .NET language, but not TRANSACT SQL. Why is Transact-SQL thrown out to dust like this?||Yes but only in C as UDF||Yes - any PL language and built-in C, SQL, PLPgSQL.|
|Table Partitioning||Yes - only Enterprise version - functional, range||Yes||via Table Inheritance, Constraint Exclusion, RULES and Triggers - basically RANGE. Issues with using foreign-key constraints with inherited tables in 8.4 the constraint_exclusion has another option called "partition" which is the new default. Which basically means you can have constraint_exclusion for partitioning and not have your other queries suffer.|
|Can write Set/Table returning functions that can be used in FROM clause||Yes||No||Yes|
|Support creation of functions - e.g. CREATE FUNCTION||Yes||Yes||Yes|
|Support creation of stored procedures - e.g. CREATE PROCEDURE||Yes||Yes||Sort-Of - CREATE FUNCTION serves the same need|
|Dynamic and action SQL in functions||No - but you can in Stored procedures but you can't call stored procs from SELECT statements so much more limiting than PostgreSQL||No, but can in Stored procedures which aren't callable from SELECT statements so more limiting than PostgreSQL||Yes! - you can do really cool things with action functions in SELECT statements|
|Graphical Explain Tool - no additional charge||Yes - SQL Management Studio/Express||No (someone on Reddit mentioned maatkit visual explain for MySQL. This is still a text format though and not quite as pretty as SQL Server or PgAdmin III graphical explain plan. Are there others?||Yes - PgAdmin III|
|Job Scheduling Agent controllable from DB Manager client, for running batch sql and shell jobs - no additional charge (not CronTab)||Yes - SQL Agent (not for Express), administer via Management Studio. Can do sql, sql maintainence plans, batch scripts, and SSIS work flows. Its still the best. Has wizard for setting up maintenance plans.||Yes - though appears can only use it for MySQL sql calls.||Yes - PgAgent and can run postgresql sql as well as batch scripts. Administrated via PgAdmin III.|
|Access tables from other databases on same server||Yes - server.db.schema.table, can even access disparate data sources via linked server or open query||Yes - db.table, but not easily across servers. Across servers you need Federated storage engine See Rob Wultsch note||Sort of - via Dblink, but much less elegant than MSSQL and MySQL way and much less efficient. Can also access disparate data sources via DBI Link|
|Case-Insensitivity - e.g. LIKE 'abc%' and LIKE 'ABC%' mean the same thing||By default its not case sensitive, but can change this down to the column level.||It is not case-sensitive by default but depends on character set (see comments from Giuseppe Maxia)||By default is case-sensitive , but in 8.4 we have newer contrib citext integrated to define case insensitivity fields.|
|Date Time support||Finally they support plain date - Date, DateTime,DateTimeOffset. (Date and DateTimeOffset are new)||Date and DateTime but none with Timezone, but you can have timezone, see Rob Wultsch comments.||(Seems pretty much on par with SQL Server) - Not much changed - Date, TimeStamp and TimeStamp with Timezone (not to be confused with MySQL's timestamp which autoupdates or SQL Server's deprecated timestamp which is a binary). Has Interval which neither MySQL nor MS SQL Server have.|
|Authentication||Standard Db security and NT /Active Directory Authentication||Standard Db with table-driven IP like security||Extensive - standard, LDAP, SSPI (can tie in with Active Directory if running on NT server, but still not quite as nice as SQL Server seamless integration), PAM, trust by IP, etc.|
|Column Level Permissions||Yes||Yes||Yes (introduced in 8.4)|
|Windowing Functions OVER..PARTITION BY||Yes||No||Yes - and its way better than SQL Server 2008|
|Common Table Expressions and Recursive queries||Yes||No||Yes|
|OGC Spatial Support - for the My dad is better than your dad fight in the GIS world between SQL Server and PostgreSQL/PostGIS check out A look at PostgreSQL and ArcSDE, Also check out our companion critque of the 3 spatial offerings||Yes - now built-in, but we aren't allowed to provide benchmarks for obvious reasons. If you use it, you'll really want to install the SQL Server 2008 Spatial Extension tools. The upcoming SQL Server 2008 R2 (currently in CTP is supposed to have Report Builder with map integration features which should be interesting). It has geodetic which PostGIS does not. SQL Server 2008 and PostGIS have pretty identical commercial support for spatial, but PostGIS still has a much larger Open source tool belt following.||Yes - MBR mostly and spatial indexes only work under MyISAM. Limited spatial functions. Some commercial (MapDotNet, Manifold.net), Open source GIS tools gaining steam but still more behind PostGIS.||Yes - PostGIS is great and lots of spatial functions and fairly efficient indexing and lots of open source and commercial support - ESRI ArcGIS 9.3, MapInfo, Manifold, CadCorp, FME , no geodetic but expect the first version of geodetic in PostGIS 1.5, and fairly robust geodetic in PostGIS 2.0.|
|Schemas||Yes||No (technically MySQL is implemented as a single db with schemas -- according the the information_schema schema, though in practice its not quite as clear cut as Oracle)||Yes|
|CROSS APPLY||Yes||No||No but can for the most part simulate by putting set returning functions in SELECT clause. As of 8.4, all set returning functions can be used in the SELECT regardless of language it is written in.|
|LIMIT .. OFFSET||No - has TOP and ansi compliant ROW_NUMBER() OVER (ORDER BY somefield) As Row --- where ..Row >= ... AND Row <= ... which is much more cumbersome to use||Yes (no ansi compliant way)||Yes, and also supports the ansi compliant ROW_NUMBER() OVER (ORDER BY somefield)|
|Advanced Database Tuning Wizard||Yes - SQL Management Studio recommends indexes to put in etc. Very sweet. NOT available for Express or Workgroup.||No||No|
|Maintenance Plan Wizard||Yes via SQL Management Studio - Workgroup and above. Very sweet. Will walk you thru creating backup plan, reindexing plan, error checking and schedule these for you via SQL Agent||No||No|
|Pluggable Storage Engine||No||Yes||No|
|Query Planner for complex queries (like doing correlated joins, lots of joins, lots of aggregates etc)-- the thing that figures out how to navigate data based on SQL Statement and histograms and stuff. This is off the cuff rating and varies based on kinds of queries you write. For the joe blow blog or CMS or plain read SELECt ... FROM, this is probably not important and all 3 will perform adequately. We do a lot of statistical and financial apps where ability to run complex queries against millions of records in under 5 seconds is important.||Moderate (but supports parallel processing out of the box).||Sucks Okay this was a spatial analysis and we can argue why beat a dead horse. But this is just a bounding box query.||Best. PostgreSQL doesn't support parallel processing out of the box, but supports shared reads and with GridSQL (which we haven't tried), you do get parallelism|
|FullText Engine - all 3 have it, but we don't feel right comparing since we haven't used each enough to make an authoritative comparison. Its annoying there is no set standard for doing Full Text SQL queries||Yes||Yes||Yes|
|Sequences /Auto Number||Yes - via IDENTITY property of int field||Yes - via AUTO_INCREMENT of int field||Yes - via serial data type or defaulting to next Sequence of existing sequence object - this is better than MySQL and SQL Server simple auto_increment feature. The reason it is better is that you can use the same sequence object for multiple tables and you can have more than one per table. In the past PostgreSQL sequence was a pain but now you just create it with data type serial if you want it to behave like SQL Server and MySQL and it will automatically drop the sequence if you drop the table it is bound to.|
|Transactional DDL - ability to rollback CREATE, ALTER etc statements||Yes (I couldn't find any documentation on this, but I tested it and it correctly rolls back). There is a caveat that can't roll back DDL within a DDL trigger.||No||Yes - see this comparative analysis|
Using Recursive Common table expressions to represent Tree structures Intermediate
A very long time ago, we wrote an article on how to use PostgreSQL to show the fully qualified name of an item in an inventory tree. Basically we were modeling a paper products tree. The original article can be found here Using PostgreSQL User-Defined Functions to solve the Tree Problem and was based on PostgreSQL 7.4 technology.
We'll repeat the text here for completeness and demonstrate the PostgreSQL 8.4 that solves this and more efficiently.The Problem
Suppose you are tracking supplies and have a field called si_item and another called si_parentid. The parent keeps track of what subclass a supply item belongs to. E.g. you have paper parent that has subclasses such as recycled, non-recycled. When someone takes supplies, you want to return the fully qualified name e.g. Paper->Recycled->20 Lb
Below is what the structure of your table looks like.
si_id int, si_parentid int, si_item. In your table are the following entries
si_id si_parentid si_item 1 Paper 2 1 Recycled 3 2 20 lb 4 2 40 lb 5 1 Non-Recycled 6 5 20 lb 7 5 40 lb 8 5 Scraps
CREATE TABLE supplyitem(si_id integer PRIMARY KEY, si_parentid integer, si_item varchar(100)); --load up the table (multirow constructor introduced in 8.2) INSERT INTO supplyitem(si_id,si_parentid, si_item) VALUES (1, NULL, 'Paper'), (2,1, 'Recycled'), (3,2, '20 lb'), (4,2, '40 lb'), (5,1, 'Non-Recycled'), (6,5, '20 lb'), (7,5, '40 lb'), (8,5, 'Scraps'); --Recursive query (introduced in 8.4 returns fully qualified name) WITH RECURSIVE supplytree AS (SELECT si_id, si_item, si_parentid, CAST(si_item As varchar(1000)) As si_item_fullname FROM supplyitem WHERE si_parentid IS NULL UNION ALL SELECT si.si_id,si.si_item, si.si_parentid, CAST(sp.si_item_fullname || '->' || si.si_item As varchar(1000)) As si_item_fullname FROM supplyitem As si INNER JOIN supplytree AS sp ON (si.si_parentid = sp.si_id) ) SELECT si_id, si_item_fullname FROM supplytree ORDER BY si_item_fullname;
Result looks like
si_id | si_item_fullname ------+----------------------------- 1 | Paper 5 | Paper->Non-Recycled 6 | Paper->Non-Recycled->20 lb 7 | Paper->Non-Recycled->40 lb 8 | Paper->Non-Recycled->Scraps 2 | Paper->Recycled 3 | Paper->Recycled->20 lb 4 | Paper->Recycled->40 lb
Database Administration, Reporting, and Light application development Beginner
One of the most common questions people ask is Which tools work with PostgreSQL. In a sense the measure of a database's maturity/popularity are the number of vendors willing to produce management and development tools for it. Luckily there are a lot of vendors producing tools for PostgreSQL and the list is growing. One set of tools people are interested in are Database administration, ER diagramming, Query tools, and quickie application generators (RAD).
For this issue of our product showcase, we will not talk about one product, but several that fit in the aforementioned category. All the listed products work with PostgreSQL and can be used for database administration and/or architecting or provide some sort of light reporting/rapid application building suite. By light reporting/application building, we mean a tool with a simple wizard that a novice can use to build somewhat functional applications in minutes or days. This rules out all-purpose development things like raw PHP, .NET, Visual Studio, database drivers etc. Things we consider in this realm are things like OpenOffice Base and MS Access. Most of these tools are either free or have 30-day try before you buy options.
You can't really say one tool is absolutely better than another since each has its own strengths and caters to slightly different audiences and also you may like the way one tool does one important thing really well, though it may be mediocre in other respects. We also left out a lot of products we are not familiar with and may have gotten some things wrong.
If we left out your favorite product and you feel it meets these criteria, or you feel we made any errors, please let us know, and we'll add or correct it. We will be including Free open source as well as proprietary products in this mix. If we left out what you consider an important criteria, please let us know and we'll try to squeeze it in somewhere.
UPDATE: We have added more entries since we initially published this. In playing around with some of these, we discovered that more than we thought sport a drag and drop table draw line join kind of query builder, but some do it better than others. The same holds true with ER modeling relational foreign key gui dialogs within the relational diagram. Since our list is now bigger, we went back to test drive some of these to see how well they do it for PostgreSQL. So in these cases, you'll see more than a Yes/No answer and some of our personal prefences such as JOIN syntax implementation may show more than we would like.
The query builder part is probably something we are more critical of, not because we care that much for them, but that's the first thing new database users need badly. From an expert database user stance, this is only really useful if you can toggle back and forth between design and SQL view without losing your changes.
There were some also added features we noticed such as code completion and plpgsql debugger which we didn't test, but tried to put in the general admin/edit description section if we know it does it or someone commented on it.
We may have also incorrectly tagged some things No if it wasn't intuitively obvious how to do it in our 5-10 minutes of testing, so please correct us in those cases and how you do it as well.RAD stands for Rapid Application Development - we are only considering tools Yes if they include a wizard to build.
|Product||OS Support||Price (license)||Administration / Data Edit /Database Programming||DB Support||WYSIWIG Query Builder||Graphical Explain||RAD||Reporting||Data Import/Export||Relational Designer / ER Modeler|
|Aqua Datastudio 7.5||Windows, Linux, MacOSX, Solaris||Proprietary ($400) check feature matrix feature matrix||Yes - also has database compare/synchronize, backup and integration with subversion, general admin + edit and procedural debugger||PostgreSQL, MySQL, Oracle, SQL Server, Sybase, Informix, Apache Derby (others with JDBC driver)||Yes||Yes||No||Yes - both meta data and adhoc on screen charts (only viewable in tool?)||Yes - Export (XML, HTML, Excel), Import - Delimited||Yes|
|Database .NET||Windows no install required (aside from .NET) self-standing 5 MB exe||Freeware||Sort Of - ability to script table structures, browse table structure and data (no wysiwig for table design, or editing data)||PostgreSQL, Firebird, MySQL, SQLite, SQL Server, SQL Server CE, Oracle, MS Access, Dbase, OLEDb, ODBC||No||No||No||No||Yes - Export (CSV, TXT,XML)||No|
|Database Master||Windows||Proprietary ($49)||Yes - general user management, ability to edit data including blob, ability to add tables, but datatype options impoverished.||PostgreSQL, MySQL, SQLite, ODBC, OLEDb||No||No||No||Yes - simple query result with ability to export to PDF. In-built reports for server config, process etc.||Yes - Import/Export (XML, HTML, Excel, CSV)||Yes, but doesn't seem to be able to read foreign key relationships of pg (or couldn't get that to work) and can't edit from diagram.|
|DBTools Manager Enterprise 3.4||Windows||Proprietary ($100-$500) depending if you get add-on packs for non-OS dbs (also Freeware version and standard) - check feature matrix||Yes - include edit, also has database compare/synchronize, backup (in sql format), Task wizard for ETL (interface intuitive)||PostgreSQL, MySQL, Interbase, Firebird, SQLite packaged (extra purchase packs for SQL Server, Oracle, Sybase, ODBC, MS Access)||Yes - you have to launch the sql query designer icon (not intuitively obvious). Also automatically shows defined relationships, but doesn't seem to allow LEFT,RIGHT JOIN or toggle between SQL/Design view.||Yes||Yes but can currently only use within DBTools. A redistributable runtime is expected for later versions.||Yes - both meta data and data reports||Yes (also database migration wizard) - MS Access, Excel / Open Office spread sheet, CSV, DBF,HTML, XML - ADO/DAO/ODBC (Paradox, Foxpro, DBase) data sources||Yes|
|DbVisualizer 6.5||Windows, Linux, MacOSX||Proprietary ($150) (also a freeware version) check feature matrix feature matrix||Yes - general admin + edit and blob editing show in chart||PostgreSQL, Oracle, Sybase, SQL Server, DB2, Mimer, Neoview, MySQL, Informix, JavaDB/Derby||Yes - supports ANSI LEFT,RIGHT, FULL joins, and toggle between SQL and Design, changes in SQL view can be seen in design by clicking load to query design view.||Yes||No||No||Yes - CSV, HTML, XLS, XML||No? or at least couldn't find it|
|EMS SQL Management Studio for PostgreSQL (2007) include Manager and the whole bundle of export, backup etc. tools.||Windows||Proprietary ($250 for full-version) (also Freeware version of Manager with subset of features of full) - check feature matrix||Yes - eidt, table designer (supports full array of data types including array of types), also has database compare/synchronize, backup||PostgreSQL (similar products for MySQL, SQL Server, FireBird/Interbase, Oracle, DB2)||Yes can do views too. Nice toggle feature between query builder and sql view. Supports inner/left/outer/full joins, however adding in GROUP BY in sql view confuses the designer though can make group by changes in designer. Making simple join changes in SQL view or adding columns can toggle back and forth to design view.||Yes||No||Yes - both meta data and data reports||Yes - MS Access, Excel / Open Office spread sheet, CSV, DBF,HTML, XML - ADO data sources||Yes including ability to edit foreign keys and add columns from diagrammer.|
|MicroOlap Database Designer/SQL for PostgreSQL||Windows (can run in Linux under WINE)||Proprietary ($400)||Yes (Designer)||PostgreSQL (similar products for MySQL and SQL Server)||Yes||No||No||Yes -- just meta data/relational designer/reverse engineer reports.||Import structures from ODBC supported dbs||Yes (Designer) - fairly sophisticated and reverse engineering|
|Navicat 8 for PostgreSQL||Windows, Mac OSX, Linux||Proprietary (~$200 with report viewer) and Free Lite version (only for non-proprietary use)||Yes include job agent, backup, report scheduling, DDL, data synch of data and/or structure compare etc.||PostgreSQL (similar product for MySQL, Oracle)||Yes||No||No||Yes -- focused on end user reports with charts and ability to schedule and email reports.||dbf, access, excel, html, xml and 10 other flat file formats||No|
|MS Access 2000-2007||Windows||Proprietary ($200 or part of MS Office Professional+)||No DDL but can edit data||Any database with an ODBC/OLEDB driver||Yes (pretty good), can't create views||No||Yes, Desktop App and relies on MS Access full or free runtime download.||Yes - has subreports, charting fairly advanced||Yes - from and to any ODBC data source or excel with wizards and ability to save import specs||Yes but will not make any changes to PostgreSQL. It will just look pretty and can print. Also doesn't read relationships from PostgreSQL aside from primary key, but you can draw them in the relationship diagrammer.|
|Once:Radix||Mac OSX/ Windows / Linux||Free (GPL)||No||PostgreSQL||No||No||Yes - webapps based on Java Servlets (Apache/Tomcat)||Yes - uses Jasper Reports||No||No|
|Open Office Base 3.1||Windows /Mac / Linux / Unix||Free (LGPL) - (also Star Office proprietary)||Yes (can define tables/etc. but sometimes flaky for DDL, fine for editing data)||Any database with an JDBC/SDBC/ODBC driver||Yes (pretty good) - can also design database views graphically, but can't reedit :(||No||Yes - end app needs OpenOffice base to run||Yes can also do more sophisticated reporting if you download Sun Report builder||Yes - via the Paste Special/Paste commands - supports CSV, HTML Table, OOBase/Excel copy pase. The wizard is nice but tempermental when importing into PostgreSQL. We'll write up another article about tricks of getting around its idiosyncracies.||Yes. Will also read Primary/Foreign key constraints and allow you to edit them. Kind of flaky for creating relationships (sometimes creates duplicate foreign keys if you reedit a relationship) but great for looking at them and setting layout. Still seems to lack a print option in this version. We use PgAdmin to create them and OO to look at them.|
|Pentaho BI Suite Enterprise||Windows, Linux||Commercial Open Source (comes in priced and free community editions). You can also download pieces separately like Report Designer. Pricing for commercial is not stated. Licensing is a mix of (GPL/LGPL/Mozilla PL) see feature matrix pg 3||This is focused on BI, so BI administration, report design, OLAP, report scheduler, dashboards - web based reporting. built on java/tomcat/jetty/mondrian. Seems like a fairly hefty product (600MB download for full), not designed for beginners. Note we did not test it so can't speak for its merits.||While you can report on PostgreSQL data and any source with a JDBC driver, insists on you installing MySQL to store BI metadata so its evidentally very MySQL centric.||?||?||?||Yes - that's what its for. Web-based reporting in category of MS Reporting Services, Cognos,SAP BI Objects||Appears to have Advanced ETL features to pull data from any data source||No|
|PhpPgAdmin 4+||Windows, Linux, Mac OSX, Unix any webserver supporting PHP (any client with a web browser)||Free (GPL)||Yes DDL builder, user management, data editing in grid view, database object browser||PostgreSQL||Sort of||No||No||Advanced reporting and dashboards. Web-based||CSV,tab, xml||No|
|PgAdmin III 1.10||Windows, Linux, Mac OSX, Unix||Free Open Source (BSD)||Yes DDL builder, plpgsql debugger as plugin, user management, data editing in grid view, database object browser, job schedule interface to pgAgent||PostgreSQL, EnterpriseDb, GreenPlum||Yes (not great)||Yes (great)||No||Administration specific - Db objects||Limited to CSV. No direct import (but you can use psql/PostgreSQL native COPY||No|
|PostgreSQL Maestro 9.5||Windows||Proprietary ($300 for the full PostgreSQL bundle, $200 for just Maestro)||Yes DDL builder,pl/pgsql debugger, user management, data editing in grid view with blob edit, database object browser||PostgreSQL (similar products for MySQL, MS SQL Server,Oracle, SQLite, Firebird, Sybase, DB2)||Yes||No||Yes (packaged separately as a free tool - a PHP page generator for query or table)||Yes, includes quick charts||Yes||Yes|
|Power*Architect 0.9||Java 1.6 (Windows, Linux, Mac OSX,Unix)||Free open source (GPL)||Sort Of (can browse, but no edit/create except via import/export and model generation scripts), ability to compare data models||PostgreSQL, SQL Server, MySQL, Oracle, Derby, DB2, HSQL, SQLStream||No||No||No||Yes but just modeling reports. Ability to export data model to PDF and XML.||Yes - Export - CSV/HTML, Import -- appears to have ability to import and export between database connections, but had trouble getting it to work.||Yes. This is its main focus. Lots of ER features and ability to generate logical data models and generate scripts to make them physical for desired database platform.|
|SQL Workbench/J||Java 6 (JDK 1.6) based - Windows, Linux, Mac OSX, Unix||Free Open Source (License details) Apache 2.0 license||Geared toward querying, editing, and data import/export. Sports a nice SQL formatter, nice SQL field/table code completion, schema diff/data diff, ability to see DDL of all tables, triggers etc and script, and a really nice data pumper. It is also very light-weight with a very sleek clean interface.||PostgreSQL, EnterpriseDb, Oracle, H2, Firebird, Apache Derby, IBM DB2, Ingres, SQLite, MySQL, MS SQL Server though appears can support most JDBC driver based dbs. Have to download JDBC driver separately and specify its location via the driver template interfacee||No -just query editor with data browser/basic data edit (autogenerate of UPDATE/Insert sql)||No||No||No||Yes -XML/txt but also sports an impressive looking Data Pumper that allows you to map fields between two JDBC datasources and copy data across.||No|
|SQuirreL SQL 3.0||Java 1.6 (Windows, Linux, Mac OSX,Unix)||Free open source (LGPL)||Yes most of the additional features are available via plugins included as options in the install or for free download||PostgreSQL (plug-in based architecture), specific ones also for Firebird, MySQL, MS SQL Server, H2, Oracle, Informix, Sybase, DB2. Although you can check to install the PostgreSQL plugin, you still need to download the jdbc driver from http://jdbc.postgresql.org/download.html and dump in the lib folder of SQuirrel install for the PostgreSQL plugin to become active. Same for other databases.||No||No||No||No||Yes - Import/Export CSV/Excel||Yes via Graph plugin. Renders relationships but doesn't allow adding/editing relationships.|