"Create table if not exists" - how to check the schema, too?
Solution 1
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'TableName'
AND TABLE_SCHEMA = 'public'
Solution 2
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.
Solution 3
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.
Solution 4
I am not aware of any database that has this feature natively.
Have not used it (rolled my own code to do this) but maybe Apache DdlUtils can help.
It is a tricky thing to do, especially if you want it to work with different database vendors. Also, there are probably different opinions about how similar the schema needs to be in order to pass. Column names, column order, column types, primary key definition: certainly. But how about constraints, the names of the constraints, table space definitions, and so on?
Solution 5
Simply:
IF NOT EXISTS (SELECT 0
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'name_of_schema'
AND TABLE_NAME = 'name_of_table')
BEGIN
CREATE TABLE [name_of_schema].[name_of_table]
(
...
)
END
For more examples, check here.
Related videos on Youtube
Joonas Pulakka
I'm the glue that holds the gears of progress together.
Updated on April 16, 2022Comments
-
Joonas Pulakka about 2 years
Is there a (more or less) standard way to check not only whether a table named
mytable
exists, but also whether its schema is similar to what it should be? I'm experimenting with H2 database, andCREATE TABLE IF NOT EXISTS mytable (....)
statements apparently only check for the table´s name. I would expect to get an exception if there's a table with the given name, but different schema.
-
hansvb about 14 years"a table with a similar name"? "a schema similar to what it should be"? That is too vague for a general query tool to understand.
-
-
Joonas Pulakka about 14 yearsI see, it might not be as simple thing as I thought. Still, given that this is a check that virtually any piece of software using any database should do, it's amazing that there's no standard solution.
-
hansvb about 14 yearsMany applications do not want to trouble their user with a separate installer/updater script and manage their database scheme internally. Even if they do not create tables, it does make sense to check the table schema at startup to assert the integrity of the application (rather than failing with random database errors sometime later). I am not saying this approach is always appropriate, but it is a valid thing to want in some cases.
-
Joonas Pulakka about 14 yearsYes, I also think that while a) may be the usual way to go, it similar to, say, "the existence of a C: drive is something that should be ensured by the installation procedure of an application, not by the application itself at run-time.". But a serious application should make no assumptions about the environment. Instead, it should check it. Environments can change - and sooner or later they will.
-
Joonas Pulakka over 11 yearsCould you please elaborate? What does this do?
-
Thomas Mueller over 11 yearsI does what is say it does... It checks whether such a table exists in the database. The
INFORMATION_SCHEMA
is (somewhat) standardized, so this statement works in most databases. -
isapir over 11 yearsit works in MSSQL as well, but there the "default" schema is "dbo"
-
binki over 5 yearsWhat this answer doesn’t do is demonstrate how to compare the schema. It gets closer, though. One could use a query against
INFORMATION_SCHEMA.COLUMNS
and check if the existing columns match what is expected and react to that. However, I think that using something like named/numbered migrations and a migrations table like how EntityFramework supports would be easier and less error prone—especially when you need to preserve existing data but change how it is stored due to schema changes.