15

I am new to postgreSQL and I am trying to create a schema file which will contain all the scripts required to create the database and required tables. The way I used to do this for SQl server was to check if the database exists and then run the necessary scripts.

The following script in postgreSQL throws an error saying, "CREATE DATABASE cannot be executed from a function or multi-command string"

do $$
begin
If not exists (select 1 from pg_database where datname = 'TestDB')
Then
 CREATE DATABASE "TestDB";
end if;
end
$$

I created a postgres database dump file by exporting a backup of the database but that contains,

Drop Database "TestDB"

Create Database "TestDB"

which means everytime I run the schema file, the database would be dropped and recreated and it would be a problem if there is data present in the database.

How do I check if the database exists in postgreSQL without having to drop the database and recreate it everytime I run the file please?

Thanks in Advance

Sugan88
  • 313
  • 1
  • 4
  • 21
  • 3
    Does this answer your question? [Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?](https://stackoverflow.com/questions/18389124/simulate-create-database-if-not-exists-for-postgresql) – benvc Oct 29 '19 at 17:42

1 Answers1

13

For shell script which creates the database if it does not exist and otherwise just keeps it as it is:

psql -U postgres -tc "SELECT 1 FROM pg_database WHERE datname = 'my_db'" | grep -q 1 || psql -U postgres -c "CREATE DATABASE my_db"

I found this to be helpful in devops provisioning scripts, which you might want to run multiple times over the same instance.

For those of you who would like an explanation:

-c = run command in database session, command is given in string
-t = skip header and footer
-q = silent mode for grep 
|| = logical OR, if grep fails to find match run the subsequent command

Answer credit andreasl from this question.

kooskoos
  • 4,622
  • 1
  • 12
  • 29