The commands listed below are some that we use often, but many more exist. Check the man page for more details and information. To define a new user in your database, run the createuser utility program:
[root@deep] /# su postgres
[postgres@deep /]$ createuser
Enter name of user to add ---> admin
Enter user's postgres ID or RETURN to use unix user ID: 500 ->
Is user "admin" allowed to create databases (y/n) y
Is user "admin" a superuser? (y/n) y
createuser: admin was successfully added
To remove a user in your database, run the destroyuser utility program:
[root@deep] /# su postgres
[postgres@deep /]$ destroyuser
Enter name of user to delete ---> admin
destroyuser: delete of user admin was successful.
To create a new database, run the createdb utility program:
[root@deep] /# su postgres
[postgres@deep /]$ createdb dbname 
| dbname is the name of the database. |
or with the Postgres terminal monitor program (psql)
[root@deep] /# su admin
[admin@deep /]$ psql template1
Welcome to the POSTGRESQL interactive sql monitor:
Please read the file COPYRIGHT for copyright terms of POSTGRESQL
[PostgreSQL 6.5.3 on i686-pc-linux-gnu, compiled by egcs ]
type \? for help on slash commands
type \q to quit
type \g or terminate with semicolon to execute query
You are currently connected to the database: template1
template1-> create database foo;
CREATEDB
Client connections can be restricted by IP address and/or user name via the pg_hba.conf file in PG_DATA.
Other useful Postgres terminal monitor program commands (psql) are: To connect to the new database, use the command:
template1-> \c foo
connecting to new database: foo
foo->
To create a table, use the command:
foo-> create table bar (i int4, c char(16));
CREATE
foo->
To inspect the new table, use the command:
foo-> \d bar
foo->
Table = bar
+----------------------------------+----------------------------------+------------+
| Field | Type | Length |
+----------------------------------+----------------------------------+------------+
| I | int4 | 4 |
| c | char() | 16 |
+----------------------------------+----------------------------------+------------+
To drop a table, index, view, use the command:
foo-> drop table table_name;
foo-> drop index index_name;
foo-> drop view view_name;
To insert into: once a table is created, it can be filled using the command.
foo-> insert into table_name (name_of_attr1, name_of_attr2, name_of_attr3)
foo-> values (value1, value2, value3);