I recently switched a database server from MS SQL Server over to postgresql. Here's the top three differences in SQL:
- NO TOP, so
SELECT TOP 10 * FROM table, becomes
SELECT * FROM table LIMIT 10you can also use the maxrows attribute of CFQUERY to do this, if you want cross db code (which is good). MySQL also uses the LIMIT sytax, but Oracle uses yet another syntax
LIKE statements are case sensitive in postgresql, they can be made case insensitive like this:
SELECT * FROM table WHERE LOWER(column) LIKE '%#LCase(var)#%'
The plus operator cannot be used for concatination so
SELECT firstname + ' ' + lastname AS fullnamebecomes
SELECT firstname || ' ' || lastname AS fullnamethis way works on both servers.