Thursday, February 25, 2010

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.