PostgreSQL SQL conveniences

For years with Sybase I chafed at having to create "temp tables" with intermediate results in preparation for the final query. PostgreSQL eliminates the need for temp tables because it allows a subquery to follow the FROM of a SELECT. Evidently Oracle allows something similar too.

Another PostgreSQL convenience is generate_series. Sometimes in a SQL query one needs a list of all possible integers in a range. In Sybase, again, this has to be done with a temp table. But in PostgreSQL, one can SELECT FROM generate_series.

PostgreSQL SQL rich syntax obviates the need for stored procedures in these situations.

dotConnect for PostgreSQL

Don't even think about using PostgreSQL with Visual Studio without purchasing dotConnect.

http://www.devart.com/dotconnect/postgresql/

The alternatives are npgsql, which doesn't integrate with the Visual Studio data designer, and ODBC, which is slow. And although ODBC allows some use of the Visual Studio data designer, it has trouble with parametrized queries.

Also, don't bother with the free version of dotConnect -- it doesn't include the Visual Studio data designer integration. And, if you can, don't bother with the 30-day trial versions either as it's difficult to convert projects over from the trial version to the registered version.

PostgreSQL silent installation

I'm bundling PostgreSQL as part of a desktop application installation, only to find out what was the recommended silent installation procedure under PostgreSQL 8.3 -- using the MSI file -- has been removed from PostgreSQL 8.4. Evidently, you're supposed to use command line utilities to effect silent installation now -- and it's not entirely documented. After some trial and error, I determined that the following sequence will silently install PostgreSQL 8.4:

1. mkdir C:\Program Files\PostgreSQL\8.4
2. net user postgres password /ADD
3. initdb --username=postgres C:\MyDB
4. pg_ctl register -D C:\MyDB
5. net start PostgreSQL
6. psql -U postgres -f MyDDL.sql