Postgresql: how to create table only if it does not already exist?

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

Code example appreciated.

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.

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

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.

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.

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.


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;
}

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.

The best answer has been given by Skalli if you're running Postgresql 9.1+.

If like me you need to do that with Postgresql 8.4, you can use a function with a 'duplicate_table' exception catch.

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 :

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;

[To see links please register here]

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

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;

I think to check the pg_class table perhaps help you, something like that:

SELECT COUNT (relname) as a FROM pg_class WHERE relname = 'mytable'

if a = 0 then (CREATE IT)

Regards.