python Postgresql CREATE DATABASE IF NOT EXISTS is error

Question

I tried to learn about postgresql using python. I want to create condition CREATE DATABASE IF NOT EXISTS, but I always get an error. The error is :

File "learn_postgres.py", line 27, in InitDatabase
cursor.execute("CREATE DATABASE IF NOT EXISTS python_db") psycopg2.ProgrammingError: syntax error at or near "NOT"
LINE 1: CREATE DATABASE IF NOT EXISTS python_db

Answer 1

Postgres does not support the condition IF NOT EXISTS in the CREATE DATABASE clause, however, IF EXISTS is supported on DROP DATABASE

There are two options:

1. drop & recreate

   cursor.execute('DROP DATABASE IF EXISTS python_db')
   cursor.execute('CREATE DATABASE python_db')
   # rest of the script
   

2. check the catalog first & branch the logic in python

   cursor.execute("SELECT 1 FROM pg_catalog.pg_database WHERE datname = 'python_db'")
   exists = cursor.fetchone()
   if not exists:
       cursor.execute('CREATE DATABASE python_db')
   # rest of the script
   

Answer 2

You could query from pg_catalog.pg_database to check if the database is exist like this:

SELECT datname FROM pg_catalog.pg_database WHERE datname = 'python_db'

Then from here you can add the logic to create your db.