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
c database
d name
e file
h command
i file
l
psql session
q
psql
r
s
!
\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.