14

Is there an analog to CREATE TABLE IF NOT EXISTS for creating databases?

Background: I am writing a script to automatically set up the schema in PostgreSQL on an unknown system. I am not sure if the database (or even part of the schema) was already deployed, so I want to structure my code to not fail (or ideally even show errors) if some of the structure already exists. I want to differentiate the errors that prevent me from creating a database (so abort future schema changes since they will not work) from this error.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Konstantin Tarashchanskiy
  • 2,762
  • 2
  • 31
  • 46

1 Answers1

6

No but you could query the pg_catalog.pg_database table to see if it exists.

Eelke
  • 20,897
  • 4
  • 50
  • 76
  • 1
    You need to do this in function then, and this wouldn't work in 9.3, because Postgres doesn't allow to create/drop databases from inside function or multi-command string. – ahanin Oct 12 '13 at 17:39
  • But you could write an external script in php, python, perl or whatever you favour. – Eelke Feb 03 '14 at 13:04
  • 1
    Wouldn't it be simpler to just use DROP TABLE IF EXISTS "foo"; CREATE TABLE "foo"; – Enwired Apr 14 '14 at 23:14
  • 2
    @Enwired - what if you don't want to drop the existing table if it exists? You might want to create it if it doesn't exist, but leave it alone if it does. – Steve Midgley Dec 04 '14 at 20:00
  • If that is what you want, then the commands I specified wouldn't be useful for you. The original question was actually about creating a database, not a table, so my comment was accidentally off-topic anyway! Sorry. – Enwired Dec 05 '14 at 22:10