next up previous index
Next: Talking to a PostgreSQL Up: Databases: From Small to Previous: PostgreSQL under Cygwin

Talking to a PostgreSQL Data Base with psql

psql  is the SQL interpreter that talks to PostgreSQL. You can use it to interrogate a PostgreSQL data base interactively or to execute SQL programs, in which case you will need to invoke psql with the -f option followed  by the name of the file that contains the program.

In order to connect to PostgreSQL data base with psql, the data base must exist already. The command initdb creates two template data bases, which are always there in the PostgreSQL data space. You can see them if you issue the psql -l command: 

gustav@WOODLANDS:../gustav 12:23:06 !508 $ psql -l
       List of databases
   Name    | Owner  | Encoding  
-----------+--------+-----------
 template0 | gustav | SQL_ASCII
 template1 | gustav | SQL_ASCII
(2 rows)

gustav@WOODLANDS:../gustav 12:23:10 !509 $
The command createdb, I have used in the previous section, is just a shell script, which connects  to template1 and then  issues the SQL create database command. But it's a very convenient wrapper, and so I'm going to use it again to recreate the data base neighbors I have dropped in the previous section:
gustav@WOODLANDS:../gustav 12:27:15 !510 $ createdb neighbors
CREATE DATABASE
gustav@WOODLANDS:../gustav 12:27:31 !511 $
Now I can connect to it:
gustav@WOODLANDS:../gustav 12:29:26 !513 $ psql neighbors
Welcome to psql 7.3.4, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

neighbors=#
If the psql prompt ends with the hash, #, it means that you are the owner of the data base. Right now the data base doesn't have any tables in it yet. But how to define the tables is the SQL thing and I won't dwell on it too much, because you probably know all about it already. I want to focus more on the specific features of psql. Still we'll have to create some data if only to illustrate various features of psql. But before we get there, let's have a look at what we can do in psql without any tables defined.

First you can ask about psql specific  commands by typing  \?:

neighbors=# \?
 \a             toggle between unaligned and aligned output mode
 \c[onnect] [DBNAME|- [USER]]
                connect to new database (currently "neighbors")
 \C [STRING]    set table title, or unset if none
 \cd [DIR]      change the current working directory
 [ ... lots of various commands listed ... ]
 \x             toggle expanded output (currently off)
 \z [PATTERN]   list table access privileges (same as \dp)
 \! [COMMAND]   execute command in shell or start interactive shell
psql specific commands all begin with a backslash. Some important ones are
$\backslash$c database
  connects to the database
$\backslash$d name
  describes the named object, it may be a table, a view, an index or a sequence
$\backslash$e file
  edit the query buffer or a file with an external editor
$\backslash$h command
  help on syntax of SQL commands
$\backslash$i file
  executes commands from the file
$\backslash$l
  lists databases accessible to this psql session
$\backslash$q
  quits psql
$\backslash$r
  reset the query buffer
$\backslash$s
  display history
$\backslash$!
  execute a command in shell or start interactive shell

\h is used to help you with SQL language commands. If you just type \h without any arguments, psql will list all available SQL commands in three columns:

neighbors=# \h
Available help:
  ABORT                     CREATE TABLE              EXECUTE                   
  ALTER DATABASE            CREATE TABLE AS           EXPLAIN                   
  ALTER GROUP               CREATE TRIGGER            FETCH                     
  ALTER TABLE               CREATE TYPE               GRANT                     
  ALTER TRIGGER             CREATE USER               INSERT                    
  ALTER USER                CREATE VIEW               LISTEN                    
  ANALYZE                   DEALLOCATE                LOAD                      
  BEGIN                     DECLARE                   LOCK                      
  CHECKPOINT                DELETE                    MOVE                      
  CLOSE                     DROP AGGREGATE            NOTIFY                    
  CLUSTER                   DROP CAST                 PREPARE                   
  COMMENT                   DROP CONVERSION           REINDEX                   
  COMMIT                    DROP DATABASE             RESET                     
  COPY                      DROP DOMAIN               REVOKE                    
  CREATE AGGREGATE          DROP FUNCTION             ROLLBACK                  
  CREATE CAST               DROP GROUP                SELECT                    
  CREATE CONSTRAINT TRIGGER DROP INDEX                SELECT INTO               
  CREATE CONVERSION         DROP LANGUAGE             SET                       
  CREATE DATABASE           DROP OPERATOR CLASS       SET CONSTRAINTS           
  CREATE DOMAIN             DROP OPERATOR             SET SESSION AUTHORIZATION 
  CREATE FUNCTION           DROP RULE                 SET TRANSACTION           
  CREATE GROUP              DROP SCHEMA               SHOW                      
  CREATE INDEX              DROP SEQUENCE             START TRANSACTION         
  CREATE LANGUAGE           DROP TABLE                TRUNCATE                  
  CREATE OPERATOR CLASS     DROP TRIGGER              UNLISTEN                  
  CREATE OPERATOR           DROP TYPE                 UPDATE                    
  CREATE RULE               DROP USER                 VACUUM                    
  CREATE SCHEMA             DROP VIEW                 
  CREATE SEQUENCE           END                       
neighbors=#

In order to refresh your knowledge on a specific SQL command in more detail simply follow \h with the name of the command, e.g.,

neighbors=# \h select
Command:     SELECT
Description: retrieve rows from a table or view
Syntax:
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    * | expression [ AS output_name ] [, ...]
    [ FROM from_item [, ...] ]
    [ WHERE condition ]
    [ GROUP BY expression [, ...] ]
    [ HAVING condition [, ...] ]
    [ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]
    [ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
    [ LIMIT { count | ALL } ]
    [ OFFSET start ]
    [ FOR UPDATE [ OF tablename [, ...] ] ]

where from_item can be:

[ ONLY ] table_name [ * ]
    [ [ AS ] alias [ ( column_alias_list ) ] ]
|
( select )
    [ AS ] alias [ ( column_alias_list ) ]
|
table_function_name ( [ argument [, ...] ] )
    [ AS ] alias [ ( column_alias_list | column_definition_list ) ]
|
table_function_name ( [ argument [, ...] ] )
    AS ( column_definition_list )
|
from_item [ NATURAL ] join_type from_item
    [ ON join_condition | USING ( join_column_list ) ]

neighbors=#
select is the SQL  command which extract data from the tables and prints them on standard output. It can be also used to print the output of various predefined functions, which, in turn, can be listed with \df. For example, there is a function called version. If you just type \df you will find this function towards the end of a very long listing. To list just the information about it type:
neighbors=# \df version
                       List of functions
 Result data type |   Schema   |  Name   | Argument data types 
------------------+------------+---------+---------------------
 text             | pg_catalog | version | 
(1 row)

neighbors=#
or, if you want more:
neighbors=# \df+ version
                                                       List of functions
 Result data type |   Schema   |  Name   | Argument data types | Owner  | Language |  Source code  |        Description        
------------------+------------+---------+---------------------+--------+----------+---------------+---------------------------
 text             | pg_catalog | version |                     | gustav | internal | pgsql_version | PostgreSQL version string
(1 row)

neighbors=#
To see what the function actually does type:
neighbors=# select version();
                                         version                                         
-----------------------------------------------------------------------------------------
 PostgreSQL 7.3.4 on i686-pc-cygwin, compiled by GCC gcc (GCC) 3.2 20020927 (prerelease)
(1 row)

neighbors=#
You can also use select to view the content of variables, e.g., current_date or to perform simple arithmetic:
neighbors=# select current_date;
    date    
------------
 2003-11-17
(1 row)

neighbors=# select 2 + 5;
 ?column? 
----------
        7
(1 row)

neighbors=#

So now let us fill the data base neighbors with some data. At this stage we get to use the SQL language. The SQL command create table  creates an empty table with some predefined columns, which you have to specify at this stage, for example:

neighbors=# create table dogs (
neighbors(#    name      varchar(80),
neighbors(#    owner     varchar(80),
neighbors(#    race      varchar(80),
neighbors(#    age       int,
neighbors(#    unit      int
neighbors(# );
CREATE TABLE
neighbors=#
Having created the table, we can commence inserting data into it with the SQL command insert :
neighbors=# insert into dogs (name, owner, race, age, unit)
neighbors-#    values ('Barky', 'Abraham Lincoln', 'Poodle', 3, 2387);
INSERT 16980 1
neighbors=# insert into dogs (name, owner, race, age, unit)
neighbors-#    values ('Fiote', 'Margaret Thatcher', 'Border Collie', 4, 2385);
INSERT 16981 1
neighbors=# insert into dogs (name, owner, race, age, unit)
neighbors-#    values ('Cedric', 'Woodrow Wilson', 'Mongrel', 8, 2383);
INSERT 16982 1
neighbors=# select * from dogs;
  name  |       owner       |     race      | age | unit 
--------+-------------------+---------------+-----+------
 Barky  | Abraham Lincoln   | Poodle        |   3 | 2387
 Fiote  | Margaret Thatcher | Border Collie |   4 | 2385
 Cedric | Woodrow Wilson    | Mongrel       |   8 | 2383
(3 rows)

neighbors=#

Let us create another table and populate it with some data at the same time:

neighbors=# create table cats (
neighbors(#    name     varchar(80),
neighbors(#    owner    varchar(80),
neighbors(#    race     varchar(80),
neighbors(#    age      int,
neighbors(#    unit     int
neighbors(# );
CREATE TABLE
neighbors=# insert into cats (name, owner, race, age, unit)
neighbors-#    values ('Smokey', 'Abraham Lincoln', 'Tabby', 5, 2387);
INSERT 16985 1
neighbors=# insert into cats (name, owner, race, age, unit)
neighbors-#    values ('Minnow', 'Benjamin Franklin', 'Bicolor', 3, 2381);
INSERT 16986 1
neighbors=# insert into cats (name, owner, race, age, unit)
neighbors-#    values ('Fluffy', 'Frederic Chopin', 'Siamese', 2, 2385);
INSERT 16987 1
neighbors=#

Once we have the data in, we can query the data base against a single table or against two (or more) tables at the same time. The latter usually is where interesting discoveries can be made.

Let us begin with querying a single table:

neighbors=# select * from dogs
neighbors-#    where race = 'Poodle';
 name  |      owner      |  race  | age | unit 
-------+-----------------+--------+-----+------
 Barky | Abraham Lincoln | Poodle |   3 | 2387
(1 row)

neighbors=#
Well, this tells us that my neighbor, Abraham Lincoln, has a poodle named Barky. This query:
neighbors=# select * from cats
neighbors-#    where age > 2;
  name  |       owner       |  race   | age | unit 
--------+-------------------+---------+-----+------
 Smokey | Abraham Lincoln   | Tabby   |   5 | 2387
 Minnow | Benjamin Franklin | Bicolor |   3 | 2381
(2 rows)

neighbors=#
tells us that there are two cats nearby that are more than 2 years old.

This query against two tables at the same time is more interesting:

neighbors=# select dogs.owner, dogs.unit
neighbors-#    from dogs, cats
neighbors-#    where dogs.owner = cats.owner
neighbors-#    and dogs.unit = cats.unit;
      owner      | unit 
-----------------+------
 Abraham Lincoln | 2387
(1 row)

neighbors=#
This tells us that Abraham Lincoln has both a dog and a cat. Such a query made against two tables at the same time is called a join query in the data base parlance.

But now we are going to make a much fancier query:

neighbors=# select dogs.unit, dogs.owner, cats.owner
neighbors-#    from dogs, cats
neighbors-#    where dogs.unit = cats.unit
neighbors-#    and dogs.owner not like cats.owner;
 unit |       owner       |      owner      
------+-------------------+-----------------
 2385 | Margaret Thatcher | Frederic Chopin
(1 row)

neighbors=#
It turns out that Margaret Thatcher cohabits with Frederic Chopin! In my neighborhood! The world must have gone to dogs!

This is an example of data mining and startling discoveries that it may yield. Verily I say unto you, great is the power of this technology.

But observe the following. Let us corrupt the cats data base and mistype the name of Abraham Lincoln, something that is more than likely  to happen in real life.

neighbors=# update cats
neighbors-#    set owner = 'Abrahan Lincoln'
neighbors-#    where owner = 'Abraham Lincoln';
UPDATE 1
neighbors=# select * from cats;
  name  |       owner       |  race   | age | unit 
--------+-------------------+---------+-----+------
 Minnow | Benjamin Franklin | Bicolor |   3 | 2381
 Fluffy | Frederic Chopin   | Siamese |   2 | 2385
 Smokey | Abrahan Lincoln   | Tabby   |   5 | 2387
(3 rows)

neighbors=#
Now let us repeat our earlier query:
neighbors=# select dogs.owner, dogs.unit
neighbors-#    from dogs, cats
neighbors-#    where dogs.owner = cats.owner;
 owner | unit 
-------+------
(0 rows)

neighbors=#
As you see, because of the stupid typo, we have just missed the fact that Abraham Lincoln has a dog and a cat - although in this case we could do the query differently, namely:
neighbors=# select dogs.owner, dogs.unit
neighbors-#    from dogs, cats
neighbors-#    where dogs.unit = cats.unit;
       owner       | unit 
-------------------+------
 Margaret Thatcher | 2385
 Abraham Lincoln   | 2387
(2 rows)

neighbors=#
and this correctly tells us that there are a dog and a cat living in units 2385 and 2387.

So, this, in short is how you can talk to PostgreSQL using a SQL client, psql.

Although in this example I have run psql and postmaster on the same machine, you would normally have them running on separate machines, reserving a more powerful system, e.g., a research group server, for the postmaster and the data directory itself, and running psql on, e.g., computational nodes of the AVIDD cluster or on a desktop PC in your office.


next up previous index
Next: Talking to a PostgreSQL Up: Databases: From Small to Previous: PostgreSQL under Cygwin
Zdzislaw Meglicki
2004-04-29