H2: how to tell if table exists?

Question

I'm trying to write Java code that checks to see if an H2 table exists: if it doesn't exist, it first executes a CREATE TABLE query that creates the table before it proceeds any further.

I followed the advice in this Google Groups question but it simply does not work.

If I run the following query:

SELECT COUNT(*) AS count FROM information_schema.tables WHERE table_name = 'word_types'

I get back a single row with a COUNT field which has a value of 0; this indicates that the word_types tables doesn't exist. But when I run:

SELECT * FROM word_types

I get back 0 result sets, but the SQL frontend/GUI that I'm using shows me all the fields/columns that exist in the word_types table. Additionally, when I drill down into my database's list of available tables (using the same GUI), I see word_types exists.

So what is the correct query to use when trying to determine if an H2 table exists or not? Using v1.3.173. Thanks in advance!

Answer 1

First: check the case in which you type tables' names. It's very important. word_types and WORD_TYPES are two different tables.
Second: If you want to check if table exists and if it doesn't then create one, I recommend you to use the following example:

CREATE TABLE IF NOT EXISTS TEST(ID INT PRIMARY KEY, NAME VARCHAR(255));