Postgres

Três diferenças entre Postgres e MS SQL Server

• NO TOP, so SELECT TOP 10 * FROM table, becomes SELECT * FROM table LIMIT 10 you 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)#%' (Or you can use the ILIKE operator)
• The plus operator cannot be used for concatination so SELECT firstname + ' ' + lastname AS fullname becomes SELECT firstname || ' ' || lastname AS fullname this way works on both servers.

Selecionar Ultimo Registro Inserido em uma Tabela Postgres

 select * from tabela order by id desc limit 1

Returning

• O SELECT tem uma opção RETURNNING em que podes devolver o ID e fazer depois uma segunda pesquisa o que é bastante melhor.
• EX:

INSERT INTO tabela (did, dname) VALUES (DEFAULT, 'XYZ nome') RETURNING did;

Limpando a tabela quando a FK está referenciada em outra tabela: