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