Create Postgres database using batch file with [template],[encoding],[owner] and a .sql file

Question

I want to create a Postgres database using a batch file. Now the normal way of doing this is the following:

"C:\Program Files\PostgreSQL9円.0\bin\createdb.exe" -U Myadmin MydatAbseName

This script above creates a database with the default database parameters. However, I want to create a database with the following parameters, as follows:

 WITH OWNER = Myadmin 
 TEMPLATE = template0 
 ENCODING = 'SQL_ASCII'
 TABLESPACE = pg_default
 LC_COLLATE = 'C'
 LC_CTYPE = 'C'
 CONNECTION LIMIT = -1;

Please tell me how to create a database with the above parameters using Batch files.

Also let me know how to use a .sql file to do the same, like this command-line:

"C:\Program Files\PostgreSQL9円.0\bin\createdb.exe" -U Myadmin -f C:\createDB.sql;

Answer 1

The client program createdb does not support all those options.
Create a file db_create.sql:

CREATE DATABASE MydatAbseName
 WITH OWNER myadmin 
 TEMPLATE template0
 ENCODING 'SQL_ASCII'
 TABLESPACE pg_default
 LC_COLLATE 'C'
 LC_CTYPE 'C'
 CONNECTION LIMIT -1;

Call it:

psql -U postgres postgres -f C:/path/to/db_create.sql

The trick here is to connect to the default database named "postgres" and create the new database from there. I do it with the default superuser named "postgres" in my example.
psql -f executes the SQL commands in the given file.

You could also just execute a single command with psql -c (no file involved):

psql -U postgres postgres -c "CREATE DATABASE MydatAbseName WITH OWNER Myadmin
EMPLATE template ENCODING 'SQL_ASCII' TABLESPACE pg_default LC_COLLATE 'C'
LC_CTYPE C' CONNECTION LIMIT -1"

Read the fine manual on creating a database here and here.
More on psql.

---

On Windows, it looks something like this:

"C:\Program Files\PostgreSQL\verson_number\bin\psql.exe" -U user -f C:/path/to/db_create.sql postgres

The last "postgres" is the name of the default db. If you want to use it in a batch file you have to answer a password prompt or connect with a user that is allowed access without providing a password. Basics in the manual chapters "The Password File" and "The pg_hba.conf File". More here:

• Run batch file with psql command without password

• PresleyDias: hello, i tried both the methods ,but i could not get them to work somehow, for the first method im getting message saying C:\>"C:\Program Files\PostgreSQL9円.0\bin\psql.exe" psql -u nansis_admin -f 'C:\ createDB.sql' psql: warning: extra command-line argument "nansis_admin" ignored psql: warning: extra command-line argument "-f" ignored psql: warning: extra command-line argument "'C:\createDB.sql'" ignored Password for user -u: after i enter the password, no database is created.
• PresleyDias: my .sql file has the following content ________________________________________ CREATE DATABASE MydatAbseName WITH OWNER nansis_Admin TEMPLATE template ENCODING 'SQL_ASCII' TABLESPACE pg_default LC_COLLATE 'C' LC_CTYPE 'C' CONNECTION LIMIT -1; __________________________________________-- for the second method, the no database is being created, please help thanks for the quick reply though :)
• Erwin Brandstetter: @PresleyDias: you have psql twice in your command. Try: "C:\Program Files\PostgreSQL9円.0\bin\psql.exe" postgres -U nansis_admin -f 'C:\createDB.sql'. Works only if user nansis_admin has the required privileges.
• PresleyDias: Hey my bad:) but i tried this "C:\Program Files\PostgreSQL9円.0\bin\psql.exe" postgres -U nansis_admin -f 'C:\createDB.sql' but still msg comes on execution of the bat file C:\>"C:\Program Files\PostgreSQL9円.0\bin\psql.exe" postgres -U nansis_admin -f ' C:\createDB.sql' psql: warning: extra command-line argument "nansis_admin" ignored psql: warning: extra command-line argument "-f" ignored psql: warning: extra command-line argument "'C:\createDB.sql'" ignored Password for user -U: nansis_admin is the superuser
• Erwin Brandstetter: @PresleyDias: I added some more to my answer.