SQL Postgresql：仅当表不存在时如何创建表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/435424/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgresql: how to create table only if it does not already exist?
提问by Fredriku73
In Postgresql, how can I do a condition to create a table only if it does not already exist?
在 Postgresql 中，只有当表不存在时，我才能做一个条件来创建表？
Code example appreciated.
代码示例表示赞赏。
回答by Skalli
I'm not sure when it was added, but for the sake of completeness I'd like to point out that in version 9.1 (maybe before) IF NOT EXISTScan be used. IF NOT EXISTSwill only create the table if it doesn't exist already.
我不确定它是什么时候添加的，但为了完整起见，我想指出在 9.1 版（可能之前）IF NOT EXISTS中可以使用。IF NOT EXISTS仅在表不存在时才创建表。
Example:
例子：
CREATE TABLE IF NOT EXISTS users.vip
(
id integer
)
This will create a table named vipin the schema usersif the table doesn't exist.
如果该表不存在，这将创建一个vip在架构中命名users的表。
回答by Michael Buen
create or replace function update_the_db() returns void as
$$
begin
if not exists(select * from information_schema.tables
where
table_catalog = CURRENT_CATALOG and table_schema = CURRENT_SCHEMA
and table_name = 'your_table_name_here') then
create table your_table_name_here
(
the_id int not null,
name text
);
end if;
end;
$$
language 'plpgsql';
select update_the_db();
drop function update_the_db();
回答by Jared
Just create the table and don't worry about whether it exists. If it doesn't exist it will be created; if it does exist the table won't be modified. You can always check the return value of your SQL query to see whether the table existed or not when you executed the create statement.
只需创建表，不要担心它是否存在。如果它不存在，它将被创建；如果它确实存在，该表将不会被修改。您可以随时检查 SQL 查询的返回值，以查看执行 create 语句时该表是否存在。
回答by Carlos Eduardo Olivieri
I think to check the pg_class table perhaps help you, something like that:
我想检查 pg_class 表可能对你有帮助，就像这样：
SELECT COUNT (relname) as a FROM pg_class WHERE relname = 'mytable'
if a = 0 then (CREATE IT)
Regards.
问候。
回答by vol7ron
This is an old question. I'm only bringing back to suggest another answer. Note: other better answers already exist, this is just for educational purposes.
这是一个老问题。我只是带回来建议另一个答案。注意：其他更好的答案已经存在，这仅用于教育目的。
The easiest way is to do what others have said; perform the CREATE TABLE if you want to keep the existing data, or perform a DROP IF EXISTS and then a CREATE TABLE, if you want a freshly created table.
最简单的方法就是照别人说的去做；如果要保留现有数据，请执行 CREATE TABLE，如果要新创建的表，请先执行 DROP IF EXISTS，然后再执行 CREATE TABLE。
Another alternative is to query the system table for its existence and proceed from there.
另一种选择是查询系统表是否存在并从那里继续。
SELECT true FROM pg_tables WHERE tablename = <table> [AND schemaname = <schema>];
In use:
正在使用：
-- schema independent:
SELECT true FROM pg_tables WHERE tablename = 'foo';
-- schema dependent:
SELECT true FROM pg_tables WHERE tablename = 'foo' AND schemaname = 'bar';
If it matches you'll have a true value, otherwise it should return an empty dataset. You can use that value to determine if you need to perform a CREATE TABLE.
如果匹配，您将获得一个真值，否则它应该返回一个空数据集。您可以使用该值来确定是否需要执行 CREATE TABLE。
回答by Stéphane
The best answer has been given by Skalli if you're running Postgresql 9.1+.
如果您运行的是 Postgresql 9.1+，Skalli 已经给出了最佳答案。
If like me you need to do that with Postgresql 8.4, you can use a function with a 'duplicate_table' exception catch.
如果像我一样需要在 Postgresql 8.4 中执行此操作，则可以使用带有“duplicate_table”异常捕获的函数。
This will ignore the generated error when the table exists and keep generating other errors.
当表存在时，这将忽略生成的错误并继续生成其他错误。
Here is an example working on Postgresql 8.4.10 :
这是一个在 Postgresql 8.4.10 上工作的例子：
CREATE FUNCTION create_table() RETURNS VOID AS
$$
BEGIN
CREATE TABLE my_table_name(my_column INT);
EXCEPTION WHEN duplicate_table THEN
-- Do nothing
END;
$$
LANGUAGE plpgsql;
回答by Nobody
What I used to check whether or not a table exists (Java & PostgreSQL) prior to creating it. I hope this helps someone. The create table portion is not implemented here, just the check to see if a table already exists. Pass in a connection to the database and the tableName and it should return whether or not the table exists.
在创建表之前我用来检查表是否存在（Java 和 PostgreSQL）。我希望这可以帮助别人。这里没有实现创建表部分，只是检查一个表是否已经存在。传递到数据库和 tableName 的连接，它应该返回该表是否存在。
public boolean SQLTableExists(Connection connection, String tableName) {
boolean exists = false;
try {
Statement stmt = connection.createStatement();
String sqlText = "SELECT tables.table_name FROM information_schema.tables WHERE table_name = '" + tableName + "'";
ResultSet rs = stmt.executeQuery(sqlText);
if (rs != null) {
while (rs.next()) {
if (rs.getString(1).equalsIgnoreCase(tableName)) {
System.out.println("Table: " + tableName + " already exists!");
exists = true;
} else {
System.out.println("Table: " + tableName + " does not appear to exist.");
exists = false;
}
}
}
} catch (SQLException sqlex) {
sqlex.printStackTrace();
}
return exists;
}
回答by Devon Yoo
The easiest answer is :
最简单的答案是：
catch{
#create table here
}
This creates a table if not exists and produces an error if exists. And the error is caught.
如果不存在，则创建一个表，如果存在则产生错误。并且错误被捕获。
回答by PaulG
http://www.postgresql.org/docs/8.2/static/sql-droptable.html
http://www.postgresql.org/docs/8.2/static/sql-droptable.html
DROP TABLE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]
回答by gauravmunjal
Try running a query on the table. If it throws an exception then catch the exception and create a new table.
尝试在表上运行查询。如果它抛出异常，则捕获异常并创建一个新表。
try {
int a = db.queryForInt("SELECT COUNT(*) FROM USERS;");
}
catch (Exception e) {
System.out.print(e.toString());
db.update("CREATE TABLE USERS (" +
"id SERIAL," +
"PRIMARY KEY(id)," +
"name varchar(30) NOT NULL," +
"email varchar(30) NOT NULL," +
"username varchar(30) NOT NULL," +
"password varchar(30) NOT NULL" +
");");
}
return db;
