6.3 Creating and Removing Databases


After creating the database cluster and starting PostgreSQL, it is time to create a database. Before you see how to do this, you will learn to find out which databases are already installed on the system:

 bash-2.04$ psql -l         List of databases    Name    |  Owner   | Encoding -----------+----------+-----------  template0 | postgres | SQL_ASCII  template1 | postgres | SQL_ASCII (2 rows) 

psql -l displays a list of all databases available on the system. As you can see, two databases are installed. These two databases are essential for PostgreSQL. template1 contains all data types and system tables a database usually has. Every database created will inherit all these data types and system tables from template1. This is a very comfortable feature because if you want to change the layout of all databases, you can simply modify template1 to your needs and all databases created after template1 will automatically contain these changes.

Creating databases can be done using a command called createdb:

 bash-2.04$ createdb --help createdb creates a PostgreSQL database. Usage:   createdb [options] dbname [description] Options:   -D, --location=PATH             Alternative place to store the database   -T, --template=TEMPLATE         Template database to copy   -E, --encoding=ENCODING         Multibyte encoding for the database   -h, --host=HOSTNAME             Database server host   -p, --port=PORT                 Database server port   -U, --username=USERNAME         Username to connect as   -W, --password                  Prompt for password   -e, --echo                      Show the query being sent to the backend   -q, --quiet                     Don't write any messages By default, a database with the same name as the current user is created. Report bugs to <pgsql-bugs@postgresql.org>. 

createdb is a very powerful and easy-to-use tool. It allows you to create databases on local and remote hosts. You can also define which character set should be used by the database. Although createdb provides many options, it is just a very small shell script. This shows how efficiently PostgreSQL works and how well the server has been implemented by the PostgreSQL development team.

Let's create a database:

 bash-2.04$ createdb phpbook -e CREATE DATABASE "phpbook" CREATE DATABASE 

You have created a database called phpbook. No character set has been defined, so PostgreSQL will take the default character set, which in this case is SQL_ASCII. In most cases SQL_ASCII will be enough and the character set does not have to be changed. When working with languages like Chinese, however, the character set has to be defined and set to the correct language. Because Chinese is beyond the scope of this book, we have decided to stick with English.

-e tells createdb to display the SQL command used to generate the database on the screen. As you can see, the SQL code is not complicated.

After creating the database, you can see it in the list generated by psql -l.

 bash-2.04$ psql -l         List of databases    Name    |  Owner   | Encoding -----------+----------+-----------  phpbook   | postgres | SQL_ASCII  template0 | postgres | SQL_ASCII  template1 | postgres | SQL_ASCII (3 rows) 

If not defined differently, the database is owned by postgres, which is the superuser on the system.

To remove a database from the system, dropdb can be used. The syntax of dropdb can easily be listed by using dropdb -help:

 bash-2.04$ dropdb --help dropdb removes a PostgreSQL database. Usage:   dropdb [options] dbname Options:   -h, --host=HOSTNAME             Database server host   -p, --port=PORT                 Database server port   -U, --username=USERNAME         Username to connect as   -W, --password                  Prompt for password   -i, --interactive               Prompt before deleting anything   -e, --echo                      Show the query being sent to the backend   -q, --quiet                     Don't write any messages Report bugs to <pgsql-bugs@postgresql.org>. 

To drop the database you have just created, you can use the following:

 bash-2.04$ dropdb phpbook DROP DATABASE 

Now the database has successfully been removed from the system again.

As you can see, creating and removing databases is an easy task. Simply use createdb and dropdb to create and remove databases. These two commands can also be used to work on a remote host. However, you need the rights to access PostgreSQL on the remote machine.



PHP and PostgreSQL. Advanced Web Programming2002
PHP and PostgreSQL. Advanced Web Programming2002
ISBN: N/A
EAN: N/A
Year: 2004
Pages: 201

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net