PostgreSQL Database
Using CREATE DATABASE
Syntax
CREATE DATABASE dbname;
Using createdb Command
Syntax
createdb [option...] [dbname [description]]
Parameters
| S.No. | Parameter & Description |
|---|---|
| 1 | dbname The name of a database to create. |
| 2 | description Specifies a comment to be associated with the newly created database. |
| 3 | options command-line arguments, which createdb accepts. |
Options
| S.No. | Parameter & Description |
|---|---|
| 1 | -D tablespace Specifies the default tablespace for the database. |
| 2 | -e Echo the commands that createdb generates and sends to the server. |
| 3 | -E encoding Specifies the character encoding scheme to be used in this database. |
| 4 | -l locale Specifies the locale to be used in this database. |
| 5 | -T template Specifies the template database from which to build this database. |
| 6 | --help Show help about createdb command line arguments, and exit. |
| 7 | -h host Specifies the host name of the machine on which the server is running. |
| 8 | -p port Specifies the TCP port or the local Unix domain socket file extension on which the server is listening for connections. |
| 9 | -U username User name to connect as. |
| 10 | -w Never issue a password prompt. |
| 11 | -W Force createdb to prompt for a password before connecting to a database. |
Example
Open the command prompt and go to the directory where PostgreSQL is installed. Go to the bin directory and execute the following command to create a database.
createdb -h localhost -p 5432 -U postgres testdb
password ******
Once a database is created using either of the above-mentioned methods, you can check it in the list of databases using \l, i.e., backslash el command as follows −
postgres-# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+-------+-----------------------
postgres | postgres | UTF8 | C | C |
template0 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
testdb | postgres | UTF8 | C | C |
(4 rows)
postgres-#
Database SQL Prompt
Type the following command to connect/select a desired database; here, we will connect to the testdb database.
postgres=# \c testdb;
psql (9.2.4)
Type "help" for help.
You are now connected to database "testdb" as user "postgres".
testdb=#
OS Command Prompt
You can select your database from the command prompt itself at the time when you login to your database. Following is a simple example −
psql -h localhost -p 5432 -U postgress testdb
Password for user postgress: ****
psql (9.2.4)
Type "help" for help.
You are now connected to database "testdb" as user "postgres".
testdb=#
You are now logged into PostgreSQL testdb and ready to execute your commands inside testdb. To exit from the database, you can use the command \q.
Using DROP DATABASE
Syntax
DROP DATABASE [ IF EXISTS ] name
Parameters
| S.No. | Parameter & Description |
|---|---|
| 1 | IF EXISTS Do not throw an error if the database does not exist. A notice is issued in this case. |
| 2 | name The name of the database to remove. |
Using dropdb Command
Syntax
dropdb [option...] dbname
Parameter
| S.No. | Parameter & Description |
|---|---|
| 1 | dbname The name of a database to be deleted. |
| 2 | option command-line arguments, which dropdb accepts. |
Options
The following table lists the command-line arguments dropdb accepts −
| S.No. | Parameter & Description |
|---|---|
| 1 | -e Shows the commands being sent to the server. |
| 2 | -i Issues a verification prompt before doing anything destructive. |
| 3 | -V Print the dropdb version and exit. |
| 4 | --if-exists Do not throw an error if the database does not exist. A notice is issued in this case. |
| 5 | --help Show help about dropdb command-line arguments, and exit. |
| 6 | -h host Specifies the host name of the machine on which the server is running. |
| 7 | -p port Specifies the TCP port or the local UNIX domain socket file extension on which the server is listening for connections. |
| 8 | -U username User name to connect as. |
| 9 | -w Never issue a password prompt. |
| 10 | -W Force dropdb to prompt for a password before connecting to a database. |
| 11 | --maintenance-db=dbname Specifies the name of the database to connect to in order to drop the target database. |
Example
dropdb -h localhost -p 5432 -U postgress testdb
Password for user postgress: ****
