"Create table if not exists" - how to check the schema, too?
SELECT *FROM INFORMATION_SCHEMA.TABLESWHERE TABLE_NAME = 'TableName' AND TABLE_SCHEMA = 'public'
CREATE TABLE IF NOT EXISTS ... is not a standard SQL code.
The thing to do is to check if the table is already in the catalogue.For instance, in Java you may do something like:connection.getMetaData().getTables(connection.getCatalog(), null, null, null)
For more info see javadoc java.sql.Connection.
Twofold answer :
(a) The existence of a table is something that should be ensured by the installation procedure of an application, not by the application itself at run-time.
(b) If you really think you have a valid reason for deviating from (a), you could try and query the catalog, which is a database consisting of tables whose structure is, more or less, prescribed by the INFORMATION_SCHEMA of the SQL standard. Which tables exist, which columns they have, which data types those columns are, which keys are declared, etc. etc., it's all in there.