How to Create Users and Databases easily in PostgreSQL

After the good response to the last post on How to Install PostgreSQL easily on Windows Vista or XP?, lets today focus on how to Create new Users and new Databases easily in PostgreSQL. As mentioned previously we will use the techy psql which is the command line interface for PostgreSQL.

You will need to first connect to the user postgres from your localhost to create the database owner. The command prompt postgres=# indicates that you are connected to the postgres database. The # in the command prompt actually means that the user connected to is a database superuser[Sort Off like in Linux].

Creating a New User:

To create a New User(mynewuser) in PostgreSQL we will use the CREATE ROLE command as follows:

postgres=#CREATE ROLE mynewuser
postgres-#login
postgres-#password 'mypassword';


This will create a new user called 'mynewuser' with the password 'mypassword'.

Creating TableSpace :

Now we will move ahead to create the default tablespace for the new database. This is where the database’s files will be stored. Create a new folder structure as C:/myDB/mynewuser/system

The steps below will help you to create a new tablespace as mynewuser_system

postgres=# CREATE TABLESPACE mynewuser_system
postgres-# OWNER jason
postgres-# LOCATION 'C:/myDB/mynewuser/system';


Hers's how you can check if the tablespace was created. Just key in the following command.

postgres=#db+ mynewuser_system

Create a Database and the related Database objects:

Finally we will now create a new database and the associated database objects. It's very simple... so here are the SQL Statements.
postgres=# CREATE DATABASE myFirstPSQLdb
postgres-# OWNER jason
postgres-# TEMPLATE template0
postgres-# TABLESPACE mynewuser_system;

You can verify if the Database is installed properly by the following command.
postgres=#l+

Now we will finally create the related Database Objects.

First login into the Database:
postgres=# c myFirstPSQLdb

Then run your SQL script as follows:
myFirstPSQLdb=# i C:/myDB/myObjects.sql


Do let me know how this article helped too.

Related Articles : How to Install PostgreSQL easily on Windows Vista or XP?