Postgresql:如何仅在表不存在的情况下创建表?

In Postgresql, how can I do a condition to create a table only if it does not already exist?

在Postgresql中，只有当表不存在时，我才能创建一个条件?

Code example appreciated.

代码示例。

10 个解决方案

#1

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 EXISTS can be used. IF NOT EXISTS will only create the table if it doesn't exist already.

我不确定它是什么时候添加的，但是为了完整性起见，我想指出，在9.1版(可能之前)中，如果不存在，可以使用。如果不存在，将只创建不存在的表。

Example:

例子:

CREATE TABLE IF NOT EXISTS users.vip
(
  id integer
)

This will create a table named vip in the schema users if the table doesn't exist.

如果这个表不存在，这将在模式用户中创建一个名为vip的表。

Source

源

#2

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();

#3

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语句时该表是否存在。

#4

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.

的问候。

#5

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表，如果存在则执行下拉，如果需要新创建的表，则执行CREATE表。

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.

如果匹配，您将得到一个true值，否则它将返回一个空数据集。您可以使用该值来确定是否需要执行CREATE TABLE。

#6

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，您可以使用具有“复制表”异常捕获的函数。

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;

#7

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)。我希望这能帮助某人。这里没有实现create table部分，只是检查表是否已经存在。通过连接到数据库和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;
}

#8

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.

这将创建一个不存在的表，如果存在则产生一个错误。错误被捕获。

#9

http://www.postgresql.org/docs/8.2/static/sql-droptable.html

DROP TABLE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

#10

-1

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;

#1