This section explains how to write compatible SQL code for both MySQL and PostgreSQL and more generally any SQL99 compatible database. It focuses on Drupal 6. Please add the required information about D7.
Note that in Drupal 7 there is a Database API that provides a standard, vendor-agnostic abstraction layer for accessing database servers.
MySQLism and PostgreSQLism denote features that exist only in one database, cannot be easily ported and therefore should be avoided.
As a trick, when writing code in MySQL, search "MySQLism" in Drupal web site and you will access all tips quickly. When writing code in PostgreSQL, search "PostgreSQLism" in Drupal web site to do the same.
Sql Compliance Check
Here's a big tip: copy & paste your SQL into http://developer.mimer.se/validator/parser99/index.tml and test that it will run on all major DB systems.