login

Subscribe to PostgreSQL Guide websiteRSS Feed
 » PostgreSQL Guide » Creating Databases

Creating Database in PostgreSQL

To create a new database in PostgreSQL you must be a super user or have CREATEDB privilege. Here is the syntax of creating a new database in PostgreSQL.

CREATE DATABASE name
    [ [ WITH ] [ OWNER [=] database_owner ]
           [ TEMPLATE [=] template ]
           [ ENCODING [=] encoding ]
           [ TABLESPACE [=] tables_pace ]
           [ CONNECTION LIMIT [=] connection_limit ] ]

When creating a new database, by default, you are the owner of that database. You can also assign the owner of the database by using addition OWNER to another user by specifying the user name.

By default, the new created database clones the standard system database template. You can also create a new virgin database by assign template0 to TEMPLATE. in this case, PostgreSQL will create all standard predefined objects to the new database.

Encoding specify the encoding of the database you want to create.

Table space allows you define specific location in the server where the data resides.

Connection LIMIT allows you set the number of concurrent connection to connect the new database, -1 by default means unlimited connection.

For example to create a database called store you can use the following query:

CREATE DATABASE store
  WITH OWNER = postgres
  ENCODING = 'WIN1252';

Once created, you can delete the database by using DROP DATABASE statement. Here is the syntax:

DROP DATABASE database_name

To remove the database store in the database server, you can apply the DROP DATATBASE statement above as below:

 

DROP DATABASE store

For convenience, PostgreSQL provides an alternative way to manage database via shell programs which are called createdb and dropdb. Both of programs can be launched from command line, then they connect to the PostgreSQL database server and execute Create Database and Drop Database statements.