27.6. Commands often used

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  (1)

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;

Note: 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


To create a table, use the command:

                 foo-> create table bar (i int4, c char(16));

To inspect the new table, use the command:

                 foo-> \d bar


                 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);