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.
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
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.
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.
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.