Creating a Database in PostgreSQL - Page 2

November 25, 2003

Creating a Database for a Different User

As you have seen, when we create a user when connected to the instance as postgres, the database created is also created with postgres as the owner. There are many times when you want the owner of a database to be someone other than the connected user. To do this PostreSQL gives you an alteration to the CREATE DATABASE command that allows you to create a database under a different user. The user must first exist to the instance. You can easily create a user and create a database for that user as in Listing 5.

Listing 5

template1=# CREATE USER  jkoopmann;
CREATE USER
template1=
   # CREATE DATABASE db3 OWNER jkoopmann;
CREATE DATABASE

You can now see the new database and it's user by issuing the same SQL commands you did in Listings 2, 3, & 4. Listing 6 shows the sequence of SQL and the output. Just take note of the new database and user.

Listing 6 Verification of new database under a new user

db1=# select datid, datname from pg_stat_database;
 datid |  datname  | 
-------+-----------+
 16976 | db1       | 
 16977 | db3       |
     1 | template1 | 
 16975 | template0 | 
 (4 rows)

db1=# select datname, datdba from pg_database;
  datname  | datdba | 
-----------+--------+
 db1       |      1 | 
 db3       |    100 |
 template1 |      1 |
 template0 |      1 |
 (4 rows)

db1=# select usename, usesysid from pg_shadow;
  usename  | usesysid |
-----------+----------+
 postgres  |        1 |
 jkoopmann |      100 |

Getting Rid of a Database

Just as you can easily create a database, it is just as easy to get rid of a database. The reasons are many as to why someone might want to get rid of a database, just be careful since this command will drop all existence of the database and you will loose everything of importance in the database. In order to drop a database you must be the owner of the database or the superuser. Listing 7 shows a failed attempt to drop the db1 database because someone was connected to the database. Listing 8 shows the successful DROP DATABASE command and the acknowledgement of the drop command. Listing 9 shows us that we did in fact drop the database as it is not returned from the SQL statement against the pg_database table.

Listing 7
Failed attempt to drop database since a user was connected to it

template1=# drop database db1;
ERROR:  DROP DATABASE: database "db1" is being accessed by other users

Listing 8
Successful drop of database db3

template1=# drop database db3;
DROP DATABASE

Listing 9
Verification of successful drop database command

db1=# select datname, datdba from pg_database;
  datname  | datdba | 
-----------+--------+
 db1       |      1 |
 template1 |      1 |
 template0 |      1 |

Creating a Database from Another Database

Another alteration to the CREATE DATABASE command is the ability to clone a database. Just as in the DROP DATABASE command, no users can be connected to the database you are cloning. Listing 10 shows the failed attempt to clone a database while someone was connected to the template database and also shows the successful command to create a database (db4) from database db1.

Listing 10
Creation of a database from another database

template1=# create database db4 template db1;
ERROR:  CREATE DATABASE: source database "db1" is being accessed by other users

template1=# create database db4 template db1;
CREATE DATABASE 

The ability to create databases at will and have them available for use in PostgreSQL is only a few commands away. I personally was amazed at the simplicity behind the creation process which makes me wonder if it is too good to be true. I will accept it for what it is and begin to populate my database to determine the real implications of the simple installation of software and database. I hope it holds up as simplicity is hard to find now a days.

» See All Articles by Columnist James Koopmann