How to test sql scripts are standard sql in junit tests?

The H2 database supports different modes, which may help you with postgres testing, I've found that our sql often contains functions which are not supported but H2, but you can create your own "stored procedures" which actually invoke a static Java method to work around this. If you want to support different database vendors you should go down the vendor specific script route, unless you are doing really basic queries.

If you have the available resources I would recommend setting up a fully fledged UAT environment which you can use to test against a live postgres database, as even seemingly minor db configuration differences can impact query plans in unexpected ways.

I've usually made a very simple java-wrapper that tests this codeby using a localhost-connection with some standard user/pass settings.

Remember to use a temporary database or a known test-database so your testsdoesn't destroy anything important.

Reason for above is that I have had the need for specific databases (non standard features etc).

If you only want to test standard sql-stuff for junit tests (like syntax, selects etc),I would consider using a embedded sql database in java (ususally memory only).That way it is easy to test lots of stuff without the need to install a dband also without the risk of destoring other installations.

It sounds like you're looking for an SQL syntax parser and validator. The only Java SQL parser with which I'm familiar is Zql, but I've never actually used it.

A similar question was asked early last year, and the best answer there turned out to be writing your own parser with ANTLR.