Using postgres

All postgres commands are completed with either a ';', or a backslash-escaped character.

• \g is the same as ';' (end of line - execute this command)
• \q means quit
• \p means print out the current buffer
• \h means help - which will print out the complete list of special characters.

Basic Commands:

$ psql test

SELECT CURRENT_USER;	#special username query
SELECT 1 + 3;		#can do arithmetic
SELECT 1 +		#can do multi-line
3 + 5 +
6\p			#can print out current buffer contents
\q			#quit

create databases:

$ createdb willn
CREATE DATABASE

list databases:

test=# \list  (or \l)
		List of databases
 Database  |  Owner   | Encoding  
-----------+----------+-----------
 template0 | postgres | SQL_ASCII
 template1 | postgres | SQL_ASCII
 test      | postgres | SQL_ASCII
(3 rows)

By looking through the source code, one can find that the actual SQL statement for this command is:

SELECT datname FROM pg_database WHERE datname !~ '^template';

describe tables:

test=# \d
   List of relations
  Name  | Type  | Owner 
--------+-------+-------
 friend | table | willn
(1 row)

Again, this is equivalent to the following SQL command:

SELECT relname FROM pg_class WHERE relname !~ '^pg_';

User Management:

It's fairly easy to manage access to databases:

$ createuser nobody
Shall the new user be allowed to create databases? (y/n) n
Shall the new user be allowed to create more new users? (y/n) n
CREATE USER

See also:

$ createuser nobody
test=# CREATE USER nobody
test=# ALTER USER nobody
test=# CREATE GROUP testusers WITH USER nobody, someone; 
test=# GRANT SELECT ON friend TO nobody;

References:

[ pgsql table basics ]

PostgreSQL: Introduction and Concepts
Basic SQL Commands
OpenACS "Simple PostgreSQL Installation Guide"
PHP PostgreSQL functions

$Id: pgsql-basics.html,v 1.5 2002/08/15 04:51:44 willn Exp $