Sunday, June 08. 2008
Printer Friendly
The PostgreSQL 8.4 planned release is March 1, 2009 and is outlined in the PostgreSQL 8.4 Development plan.
It has just passed its May 2008 commit fest milestone and is currently in its July 2008 Commit Fest. Lots of PostgreSQL Planet bloggers have started showcasing some of the new features in store.
We will briefly list our favorite planned and already committed patches.
Continue reading "PostgreSQL 8.4 goodies in store"
Thursday, June 05. 2008
Printer Friendly
Below is a Thumbnail view of a PostgreSQL 8.3 TSearch Cheat Sheet
that covers PostgreSQL 8.3 Full Text search engine constructs.
This one we broke into two pages so its a bit more readable
than our PostgreSQL 8.3 cheat sheet.
PDF landscape version 8.5 x 11" of this cheatsheet is available at PostgreSQL 8.3 TSearch Full-Text Search in PDF 8/12 by 11 and also available in
PDF A4 format and HTML.
Tuesday, May 20. 2008
Printer Friendly
What is PHP Gallery 2?
PHP Gallery 2 is a web-based management system
for storing pictures and other documents such as movies and flash files. While it is not designed for storing documents such as Microsoft Word or PDF, it serves as a simple storage container for those as well and will even automatically create thumbnails for PDFs if you have ImageMagick installed. It is similar to Gallery 1 except unlike Gallery 1, the meta data of documents
is stored in a database as opposed to the file system. Documents are still stored in the file system.
Gallery is Open Source software licensed under GPL. Details here.
We've been using Gallery 2 for various projects over the past year or so because it has been fairly easy to integrate
into our PHP applications.
Below is the list of features we like most about it:
- Supports one of our favorite databases and those other 2 - PostgreSQL, MySQL, Oracle.
Minor gripe - you can tell from the docs that there is a MySQL bias.
- Cross-Platform - will work anywhere PHP works.
- It uses PHP ADODB as the database abstraction layer.
- It uses Smarty Templating engine.
- Lots of Plugins to choose from - we'll go over our favorites later
- When you upload a high-res image it automatically creates 2 other sizes (thumbnail and regular web view)
Continue reading "PHP Gallery 2 for Picture Storage and Simple Document Management"
Monday, May 19. 2008
Printer Friendly
In prior articles of this series, we covered the following:
- Showcasing REST in PostgreSQL - The PreQuel we went over what REST is and isn't
- REST in PostgreSQL Part 1 - The DB components we loaded the Pagila database and created a db plpgsql search function to support our rest server service
- REST in PostgreSQL Part 2 A - The REST Server service with ASP.NET we demonstrated a REST web service using Mono.NET, MS.NET both in C#, VB.Net/Monobasic
- REST in PostgreSQL Part 2 B - The REST Server service with PHP 5 we demonstrated a REST web service using PHP 5
- REST in PostgreSQL Part 3 A - Simple REST Client in Adobe Flex 3 we demonstrated a basic REST client in Adobe Flex
In this article we shall continue where we left off by adding paging functionality to our Adobe Flex REST grid client.
Continue reading "REST in PostgreSQL Part 3 B - The REST Client in Adobe Flex 3 with Paging"
Sunday, May 18. 2008
Printer Friendly
Comparison of Microsoft SQL Server 2005, MySQL 5, and PostgreSQL 8.3
The below is by no means an exhaustive comparison of these 3 databases and functionality may not
be necessarily ordered in order of importance. These are just our experiences with using these 3 databases.
These are the databases we use most often. If we left your favorite database out - please don't take offense. Firebird for one has some neat features such as its small footprint and extensive SQL support, but we have not explored that Db.
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.
For those looking to compare MySQL and PostgreSQL you may want to also check out http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL
If you really want to get into the guts of a relational database and the various parts that make it up and how the various databases differentiate in their implementations,
we suggest reading Architecture of a Database System by
Joseph M. Hellerstein, Michael Stonebraker, and James Hamilton. Architecture of a Database System focuses mostly on Oracle, DB2, and SQL Server but does provide some insight into MySQL and PostgreSQL.
Continue reading "Cross Compare of SQL Server, MySQL, and PostgreSQL"
Saturday, May 10. 2008
Printer Friendly
One of the great selling points of PostgreSQL is its pluggable PL language architecture. MySQL is known for its pluggable storage and
PostgreSQL is known for its pluggable PL language architecture. From Monty's notes on slide 12 looks like MySQL
may be working on a pluggable PL language architecture of their own.
The most common of these languages are the all-purpose languages SQL and C (these are built-in and not really PLs like the others, but we'll throw them in there),
PLPgSQL which is also built-in but not always enabled, PL/Perl, PL/Python, and the domain specific languages PL/R, PL/SH and gaining popularity Skype released PL/Proxy.
There are others in the family such as PL/Tcl, PL/PHP, PL/Ruby, PL/Scheme (a dialect of Lisp), PL/Java, PL/Lua and PL/LOLCode (for kicks and as a reference implementation. Think of LOLCode as PostgreSQL Pluggable PL equivalent of MySQL's BLACK HOLE storage engine.) .
The other interesting thing about the PostgreSQL PL language architecture is that it is a fairly thin wrapper around these languages.
This means the kind of code you write in those languages is pretty much what you would write if you were doing general programming
in those languages minus some spi calls. Since the handler is a just a thin wrapper around the environment, the language environment must be installed on the database server before you can use the PL language handler. This means you can have these functions utilized in your SQL statements and you can write
in a language you feel comfortable with if you can get the darn PL compiled for your environment or someone has already kindly compiled it for your environment or that it is even compilable for your environment. The pluggable PL architecture means you can
write a PL Handler for your favorite language or invent your own language that you can run in the database. In the end
the barrier between code,data, and semantic constructs is more of a constraint imposed by compilers. If you have any doubts about the above statement, you need only look at some javascript injection attacks
to bring the statement home. One of my fantasies is
developing a language that morphs itself, that utilizes the database as its morphing engine and its OS and that breaks the illusion of data being data, code being code, and lacks rigid semantics.
Of the languages we have worked with, SmallTalk comes closest to a language that satisfies these ideals and Lisp to a much lesser extent. Lisp lacked the semantic elegance of SmallTalk among other things.
Most people are used to having their procedural language push their data around. PL code living in PostgreSQL allows your data to push your procedural code around in a set-based way. This is a simple but pretty powerful feature since data is in general more fluid than code. For interpretated/just-in time compiled languages it can live in the database, for compiled it has to call compiled functions.
Continue reading "Choosing the right Database Procedural Language PL"
Wednesday, May 07. 2008
Printer Friendly
In prior articles of this series, we covered the following:
- Showcasing REST in PostgreSQL - The PreQuel we went over what REST is and isn't
- REST in PostgreSQL Part 1 - The DB components we loaded the Pagila database and created a db plpgsql search function to support our rest server service
- REST in PostgreSQL Part 2 A - The REST Server service with ASP.NET we demonstrated a REST web service using Mono.NET, MS.NET both in C#, VB.Net/Monobasic
- REST in PostgreSQL Part 2 B - The REST Server service with PHP 5 we demonstrated a REST web service using PHP 5
Continue reading "REST in PostgreSQL Part 3 A - Simple REST Client in Adobe Flex 3"
Friday, May 02. 2008
Printer Friendly
There has been a lot of talk lately about PostgreSQL and what MySQL can learn from the PostgreSQL clan. We would like to look at the reverse of that.
This article is a bit of a complement
to Joshua Drake's What MySQL (and really, Sun) can learn from PostgreSQL.
First of all a lot of staunch advocates of PostgreSQL wonder what exactly
is it that MySQLers see in that beast of a database
or as Martin Mickos likes to call it The Ferrari of databases?
Continue reading "What can PostgreSQL learn from MySQL"
Monday, April 28. 2008
Printer Friendly
People have asked us how to calculate running totals a number of times; not a lot but enough that we feel we should document the general technique.
This approach is fairly ANSI-SQL standard and involves using SELF JOINS. In a later article we shall describe how to calculate moving averages which
follows a similar technique but with some extra twists.
Note that the below examples can also be done with a correlated sub-select in the SELECT clause and in some cases that sometimes works better. Perhaps
we shall show that approach in a later issue.
We tend to prefer the look of the SELF JOIN though and in practice it is generally more efficient since its easier for planners to optimize and doesn't always result in a nested loop strategy.
Just feels a little cleaner and if you are totaling a lot of columns (e.g number of items, products) etc,
much more efficient.
Continue reading "How to calculate Running Totals and Sums in SQL"
Friday, April 25. 2008
Printer Friendly
We would like to thank Jeff Crumbley of IILogistics for providing many of these steps
and informing us that Microsoft has finally released a
64-bit OLEDB for ODBC driver.
For those who have not experienced the torture of this situation - let me start with a little background.
First if you are running SQL Server 2005 32-bit and wished to create a linked server to a PostgreSQL server, everything is hunky dory. If
however you had a SQL Server 2005 64-bit server, you ran into 2 very annoying obstacles.
- Obstacle 1: There for a long-time was no 64-bit ODBC driver nor native driver for PostgreSQL. This obstacle was somewhat alleviated
when Fuurin Kazanbai made experimental compiled 64-bit PostgreSQL ODBC drivers available which work for AMD and Intel based processors.
- Obstacle 2: All looked good in the world until you tried this in SQL Server 2005 64-bit and low and behold - you needed a 64-bit OLEDB provider
for ODBC to use it in SQL Server 2005 64-bit. Yes we waited patiently for years for this piece to be available. We still love you Microsoft.
Then as Jeff Crumbley pointed out - Microsoft released an OLEDB 64-bit provider for ODBC in early April 2008.
Continue reading "Setting up PostgreSQL as a Linked Server in Microsoft SQL Server 64-bit"
Sunday, April 20. 2008
Printer Friendly
First we'd like to thank Devrim of Command Prompt for working hard on making this new YUM repository available. In this article we will go over using the new PostgreSQL YUM repository for Redhat Fedora, Enterprise Linux and CentOS distros that is available at http://yum.pgsqlrpms.org/.
UPDATE - since Devrim's move from Command prompt - he has started a new yum repository. You may want to use
this one instead since it seems more up to date than the other. http://yum.pgrpms.org/.
We are gearing the content of this article to the described user profile
- Person has SSH root access to their PostgreSQL box
- Person is new to using Red Hat Linux
- Person may be new to using Linux in general
NOTE: We have a newer article on Installing PostgreSQL 8.4 with Yum and an even newer one for PostgreSQL 9.0
please refer to Installing PostgreSQL 9.0 via Yum. This old article is for PostgreSQL 8.3.
Continue reading "An Almost Idiot's Guide to PostgreSQL YUM"
Friday, April 18. 2008
Printer Friendly
People coming from SQL Server and MySQL often complain about how you can't query other databases
from within a PostgreSQL database. In Microsoft SQL Server, there is a concept of querying across databases
on the same server
with dbname.dbo.sometable and querying across servers (even of different types e.g. Oracle) by setting up a linked server and doing
something such as servername.dbname.dbo.sometable or using the OPENROWSET(..) or OPENQUERY(...) syntax.
MySQL has a similar feature by using syntax dbname.sometable, but MySQL lacks schemas so there is no way to segregate a
database into nice buckets as you can with SQL Server and PostgreSQL.
In this article we shall provide some examples of using the contrib module - dblink to query local PostgreSQL databases and remote PostgreSQL databases.
DbLink is probably most comparable in structure to SQL Server's OpenRowset functionality.
It lacks the power of SQL Server's Linked Server
approach or OPENQUERY that allows for synchronized joins between linked servers/databases and local tables and updates/inserts on linked servers. This makes it not terribly useful
in cases where you need to join lots of data with local data. It does however come in handy for bulk copy operations from one database/server to another.
Continue reading "Using DbLink to access other PostgreSQL Databases and Servers"
Monday, April 14. 2008
Printer Friendly
PostGIS 1.3.3 is out
PostGIS 1.3.3 has been released and is already in the PostgreSQL RPM and soon will be in Yum repository.
PostgreSQL 8.3 users who are using PostGIS are encouraged to upgrade because this release contains a fix for a major bug that
affected spatial aggregates in 8.3. The windows Application Stack Builder version with the update should be out within this or next week.
Shp2PgSQL Loader now can load plain DBFs
We've been working on the 2007 Topologically Integrated Geographic Encoding and Referencing (Tiger) data
recently. For those who are familiar with the US Census current Tiger format, this is the first version to be released in
Environmental Systems Research Institute (ESRI) Shape file format. We ran into one small problem. The Tiger data includes related data with no geometry. These come as plain old
DBase (DBF) files. Prior versions of Shp2PgSQL could not deal with DBF files with no corresponding Shape (SHP) geometry files, but the version
packaged with 1.3.3 can.
I would like to thank Paul Ramsey for checking over my DBF-only patch and squeezing it into this release.
Now that I have gotten my hands dirty again with C code, I almost feel like a real programmer. As a side note, even if you don't use PostGIS, this should
come in handy for loading any DBF file into PostgreSQL.
New PostgreSQL Yum Repository
We recently had the pleasure of trying out the new PostgreSQL YUM repository for Fedora/RedHat Enterprise/Cent OS
distros that is maintained by Devrim GÜNDÜZ. It made the process of installation on Redhat Enterprise Linux a lot simpler.
In this issue we shall provide step by step instructions on using it geared toward the non-Red Hat Linux/CentOS indoctrinated folk (AKA the misguided Microsoft Windows people).
The reason we feel this is necessary is that a lot of people develop on Windows and then deploy on Linux. The Linux experience can
be somewhat intimidating, so we hope to make this an easier process by assuming you know little if anything about Linux. So stay tuned for that article.
Wednesday, April 09. 2008
Printer Friendly
This is a continuation of our REST series. The following topics have already been covered
- Showcasing REST in PostgreSQL - The PreQuel we went over what REST is and isn't
- REST in PostgreSQL Part 1 - The DB components we loaded the Pagila database and created a db plpgsql search function that spits out XML to support our rest server service
- REST in PostgreSQL Part 2 A - The REST Server service with ASP.NET we demonstrated a REST web service using Mono.NET, MS.NET both in C#, VB.Net/Monobasic
Now in this Part 2B series, we shall demonstrate the same REST server service using PHP
Setting up the PHP application
-
PHP already has the PostgreSQL drivers available as a .so (on Linux) or .dll on Windows. For windows users if you
are running PHP under IIS and in ISAPI mode, you will not be able to dynamically load libraries, so you need to enable php_pgsql in your PHP.ini file.
- We tend to keep it enabled regardless of which platform we are on since a lot of our PHP development involves PostgreSQL. The extension is php_pgsql in the php.ini file
- PHP has numerous database abstraction libraries to choose from. We are using the adodb abstraction library for PHP which can be downloaded from http://adodb.sourceforge.net/.
Continue reading "REST in PostgreSQL Part 2 B - The REST Server service with PHP 5"
Saturday, April 05. 2008
Printer Friendly
People have asked us on several occasions if there is such a construct
SELECT * EXCEPT(...list) FROM sometable . Sadly we do not think such a
thing exists in the ANSI SQL Specs nor in PostgreSQL.
The above feature would come in handy when you have certain fields in your tables that are common
across tables, but you need to leave them out in your query. A common case of this is when you have PostGIS tables loaded using shp2pgsql
with a fields called gid and the_geom which are not terribly useful for simple data queries.
There are 2 common ways we use to achieve this result.
- Using PgAdmin's CREATE SELECT script feature. This exists in other GUI tools as well.
- Using an Information Schema script hack to construct the SELECT statement
Continue reading "How to SELECT ALL EXCEPT some columns in a table"
|