<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<title> Practical PostgreSQL</title>
<meta name="keywords" content="postgresql, support, recovery, design, software, hosting, expterise, programming, postgres, pgsql, replication, mysql, firebird, database, sql, free, open source, software" />
<meta name="description" content="Command Prompt, Inc. is the largest and oldest US based commercial PostgreSQL support provider. We provide the only commercially viable integrated PostgreSQL replication solution, but also custom programming, and support. We authored the book Practical PostgreSQL, the procedural language plPHP, and adding trigger capability to plPerl. " />
<link rel="shortcut icon" href="../favicon.ico" type="image/x-icon" />
<link rel="stylesheet" type="text/css" href="../css/layout.css" />
<link rel="stylesheet" type="text/css" href="../css/cmd4.css" />
<script language="javascript" type="text/javascript"><!--
greymenuhomewideLit = new Image();
greymenuhomewideLit.src = "/images/greymenu_homewide_lit.gif";
greymenuaboutLit = new Image();
greymenuaboutLit.src = "/images/greymenu_about_lit.gif";
greymenuproductsLit = new Image();
greymenuproductsLit.src = "/images/greymenu_products_lit.gif";
greymenuservicesLit = new Image();
greymenuservicesLit.src = "/images/greymenu_services_lit.gif";
greymenusupportLit = new Image();
greymenusupportLit.src = "/images/greymenu_support_lit.gif";
greymenucommunityLit = new Image();
greymenucommunityLit.src = "/images/greymenu_community_lit.gif";
//--></script>
</head>
<body>
<div align="center">
<div id="widecontainer">
<div id="widetop">
<div class="throwleft" style="width: 300px; text-align: left;"><a href="http://www.commandprompt.com/"><img src="../images/cmd4logo_wide.gif" border="0" alt="Command Prompt, Inc." /></a></div>
<div class="throwright" style="width: 400px; text-align: right;">
<div id="topright">
<form action="index.html" name="cmdform" method="post">
<ul>
<li><input type="text" name="search" value="" /></li>
<li><input type="submit" name="action" value="Search Book" /></li>
</ul>
</form>
</div>
</div>
</div>
<ul id="greymenu_wide">
<li><a href="http://www.commandprompt.com/home/"><img onmouseover="this.src = greymenuhomewideLit.src;" onmouseout="this.src = '/images/greymenu_homewide.gif';" src="../images/greymenu_homewide.gif" border="0" height="22" title="" alt="" /></a></li><li><img src="../images/greymenu_divider.gif" border="0" width="2" height="22" alt="|" /></li><li><a href="http://www.commandprompt.com/about/"><img onmouseover="this.src = greymenuaboutLit.src;" onmouseout="this.src = '/images/greymenu_about.gif';" src="../images/greymenu_about.gif" border="0" height="22" title="" alt="" /></a></li><li><img src="../images/greymenu_divider.gif" border="0" width="2" height="22" alt="|" /></li><li><a href="http://www.commandprompt.com/products/"><img onmouseover="this.src = greymenuproductsLit.src;" onmouseout="this.src = '/images/greymenu_products.gif';" src="../images/greymenu_products.gif" border="0" height="22" title="" alt="" /></a></li><li><img src="../images/greymenu_divider.gif" border="0" width="2" height="22" alt="|" /></li><li><a href="http://www.commandprompt.com/services/"><img onmouseover="this.src = greymenuservicesLit.src;" onmouseout="this.src = '/images/greymenu_services.gif';" src="../images/greymenu_services.gif" border="0" height="22" title="" alt="" /></a></li><li><img src="../images/greymenu_divider.gif" border="0" width="2" height="22" alt="|" /></li><li><a href="http://www.commandprompt.com/support/"><img onmouseover="this.src = greymenusupportLit.src;" onmouseout="this.src = '/images/greymenu_support.gif';" src="../images/greymenu_support.gif" border="0" height="22" title="" alt="" /></a></li><li><img src="../images/greymenu_divider.gif" border="0" width="2" height="22" alt="|" /></li><li><a href="http://www.commandprompt.com/community/"><img onmouseover="this.src = greymenucommunityLit.src;" onmouseout="this.src = '/images/greymenu_community.gif';" src="../images/greymenu_community.gif" border="0" height="22" title="" alt="" /></a></li> </ul>
<div style="padding-left: 16px; width: 96%;">
<HTML
><HEAD
><TITLE
>Using SQL with PostgreSQL</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.72
"><LINK
REL="HOME"
TITLE="Practical PostgreSQL"
href="book1"><LINK
REL="UP"
TITLE="Using PostgreSQL"
href="p1162"><LINK
REL="PREVIOUS"
TITLE="Tables in PostgreSQL"
href="x4636"><LINK
REL="NEXT"
TITLE="Using Tables"
href="x5021"></HEAD
><BODY
CLASS="CHAPTER"
BGCOLOR="#FFFFFF"
TEXT="#000000"
LINK="#0000FF"
VLINK="#840084"
ALINK="#0000FF"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="3"
ALIGN="center"
>Practical PostgreSQL</TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="bottom"
><A
href="x4636"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="80%"
ALIGN="center"
VALIGN="bottom"
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="bottom"
><A
href="x5021"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="CHAPTER"
><H1
><A
NAME="APPLYINGSQL"
>Chapter 4. Using SQL with PostgreSQL</A
></H1
><DIV
CLASS="TOC"
><DL
><DT
><B
>Table of Contents</B
></DT
><DT
><A
href="c4890#AEN4903"
>Introduction to psql</A
></DT
><DT
><A
href="x5021"
>Using Tables</A
></DT
><DT
><A
href="x5504"
>Adding Data with INSERT and COPY</A
></DT
><DT
><A
href="x5802"
>Retrieving Rows with SELECT</A
></DT
><DT
><A
href="x7050"
>Modifying Rows with UPDATE</A
></DT
><DT
><A
href="x7234"
>Removing Rows with DELETE</A
></DT
><DT
><A
href="x7300"
>Using Sub-Queries</A
></DT
><DT
><A
href="x7379"
>Using Views</A
></DT
><DT
><A
href="x7543"
>Further SQL Application</A
></DT
></DL
></DIV
><P
>In this chapter we continue to discuss SQL, this time with a practical focus. We'll address creating tables, populating
tables with data, and managing that data via SQL statements. </P
><P
>Like most network-capable database systems, PostgreSQL fits into a client-server paradigm. The heart of PostgreSQL is the
server backend, or the <SPAN
><I
CLASS="EMPHASIS"
>postmaster</I
></SPAN
> process. It is called a "backend" because it is not meant to
directly interface with a user; rather, it can be connected to with a variety of clients.</P
><P
>When you start the PostgreSQL service, the <SPAN
><I
CLASS="EMPHASIS"
>postmaster</I
></SPAN
> process starts running in the
background, listening to a specific TCP/IP port for connections from clients. Unless explicitly configured,
<SPAN
><I
CLASS="EMPHASIS"
>postmaster</I
></SPAN
> will bind to, and listen on, port 5432.</P
><P
>There are several interfaces available through which clients may connect to the <SPAN
><I
CLASS="EMPHASIS"
>postmaster</I
></SPAN
> process.
The examples in this book use <SPAN
><I
CLASS="EMPHASIS"
>psql</I
></SPAN
>, the most portable and readily accessible
client distributed with PostgreSQL.</P
><P
>This chapter covers <SPAN
><I
CLASS="EMPHASIS"
>psql</I
></SPAN
> basics, how to create and use tables, and how to retrieve
and manage data within those tables. It also addresses SQL sub-queries and views.</P
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="AEN4903"
>Introduction to psql</A
></H1
><P
>The <SPAN
><I
CLASS="EMPHASIS"
>psql</I
></SPAN
> client is a command-line client distributed with PostgreSQL. It is often called the
<SPAN
><I
CLASS="EMPHASIS"
>interactive monitor</I
></SPAN
> or <SPAN
><I
CLASS="EMPHASIS"
>interactive terminal</I
></SPAN
>. With <SPAN
><I
CLASS="EMPHASIS"
>psql</I
></SPAN
>, you
get a simple yet powerful tool with which you can directly interface with the PostgreSQL server, and thereby begin
exploring SQL.</P
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="AEN4915"
>Starting psql</A
></H2
><P
>Before starting <SPAN
><I
CLASS="EMPHASIS"
>psql</I
></SPAN
>, be sure that you have either copied the <SPAN
><I
CLASS="EMPHASIS"
>psql</I
></SPAN
> binary
into a path in your system <TT
CLASS="LITERAL"
>PATH</TT
> variable (e.g.,
<SPAN
><I
CLASS="EMPHASIS"
>/usr/bin</I
></SPAN
>), or that you have placed the PostgreSQL binary path (e.g.,
<SPAN
><I
CLASS="EMPHASIS"
>/usr/local/pgsql/bin</I
></SPAN
>) within your list of paths in your
<TT
CLASS="LITERAL"
>PATH</TT
> environment variable (as shown in <A
href="c360"
>Chapter 2</A
>).</P
><P
> How you set the appropriate <TT
CLASS="LITERAL"
>PATH</TT
> variable will depend on your system shell. An
example in either bash or ksh might read: </P
><PRE
CLASS="SCREEN"
>$ <TT
CLASS="USERINPUT"
><B
>export PATH=$PATH:/usr/local/pgsql/bin</B
></TT
></PRE
><P
>An example in either csh or tcsh might read: </P
><PRE
CLASS="SCREEN"
>$ <TT
CLASS="USERINPUT"
><B
>set path=($path /usr/local/pgsql/bin)</B
></TT
></PRE
><DIV
CLASS="EXAMPLE"
><A
NAME="SETTINGSYSTEMPATHFORPSQL"
></A
><P
><B
>Example 4-1. Setting system path for psql</B
></P
><PRE
CLASS="SCREEN"
>[user@host user]$ <TT
CLASS="USERINPUT"
><B
>psql</B
></TT
>
bash: psql: command not found
[user@host user]$ <TT
CLASS="USERINPUT"
><B
>echo $PATH</B
></TT
>
/bin:/usr/bin:/usr/local/bin:/usr/bin/X11:/usr/X11R6/bin
[user@host user]$ <TT
CLASS="USERINPUT"
><B
>export PATH=$PATH:/usr/local/pgsql/bin</B
></TT
>
[user@host user]$ <TT
CLASS="USERINPUT"
><B
>psql testdb</B
></TT
>
Welcome to psql, 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
testdb=#</PRE
></DIV
><P
>Note that <A
href="c4890#SETTINGSYSTEMPATHFORPSQL"
>Example 4-1</A
> takes place within a bash shell.</P
><P
>Once you have appropriately set your <TT
CLASS="LITERAL"
>PATH</TT
> variable,
you should be able to type <SPAN
><I
CLASS="EMPHASIS"
>psql</I
></SPAN
>, along with a database name, to start
up the PostgreSQL interactive terminal.</P
><DIV
CLASS="WARNING"
><P
></P
><TABLE
CLASS="WARNING"
BORDER="1"
WIDTH="100%"
><TR
><TD
ALIGN="CENTER"
><B
>Warning</B
></TD
></TR
><TR
><TD
ALIGN="LEFT"
><P
> Shell environment variables are erased after you have logged
out. If you wish for your changes to the <TT
CLASS="LITERAL"
>PATH</TT
> variable
to be retained upon logging in, you need to enter the appropriate
<TT
CLASS="LITERAL"
>PATH</TT
> declaration into your shell-specific start-up
scripts (e.g., <SPAN
><I
CLASS="EMPHASIS"
>~/.bash_ profile</I
></SPAN
>).
</P
></TD
></TR
></TABLE
></DIV
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="AEN4949"
>Introduction to psql Syntax</A
></H2
><P
>Upon starting <SPAN
><I
CLASS="EMPHASIS"
>psql</I
></SPAN
>, you are greeted with a brief synopsis of four essential
<SPAN
><I
CLASS="EMPHASIS"
>psql</I
></SPAN
> <SPAN
><I
CLASS="EMPHASIS"
>slash commands</I
></SPAN
> : <TT
CLASS="LITERAL"
>\h</TT
> for SQL help,
<TT
CLASS="LITERAL"
>\?</TT
> for help on <SPAN
><I
CLASS="EMPHASIS"
>psql</I
></SPAN
>-specific commands, <TT
CLASS="LITERAL"
>\g</TT
> for executing
queries and <TT
CLASS="LITERAL"
>\q</TT
> for actually exiting <SPAN
><I
CLASS="EMPHASIS"
>psql</I
></SPAN
> once you are done.</P
><P
>Every <SPAN
><I
CLASS="EMPHASIS"
>psql</I
></SPAN
>-specific command is prefixed by a backslash; hence the term "slash command" used
earlier. For a complete list of slash commands and a brief description their functions, type <TT
CLASS="LITERAL"
>\?</TT
> into the
<SPAN
><I
CLASS="EMPHASIS"
>psql</I
></SPAN
> command line, and press enter.</P
><DIV
CLASS="EXAMPLE"
><A
NAME="AEN4965"
></A
><P
><B
>Example 4-2. Listing psql slash commands</B
></P
><PRE
CLASS="SCREEN"
>booktown=# <TT
CLASS="USERINPUT"
><B
>\?</B
></TT
>
\a toggle between unaligned and aligned mode
\c[onnect] [dbname|- [user]]
connect to new database (currently 'booktown')
\C <title> table title
\copy ... perform SQL COPY with data stream to the client machine
\copyright show PostgreSQL usage and distribution terms
\d <table> describe table (or view, index, sequence)
\d{t|i|s|v} list tables/indices/sequences/views
\d{p|S|l} list permissions/system tables/lobjects
\da list aggregates
\dd [object] list comment for table, type, function, or operator
\df list functions
\do list operators
\dT list data types
\e [file] edit the current query buffer or [file] with external editor
\echo <text> write text to stdout
\encoding <encoding> set client encoding
\f <sep> change field separator
\g [file] send query to backend (and results in [file] or |pipe)
\h [cmd] help on syntax of sql commands, * for all commands
\H toggle HTML mode (currently off)
\i <file> read and execute queries from <file>
\l list all databases
\lo_export, \lo_import, \lo_list, \lo_unlink
large object operations
\o [file] send all query results to [file], or |pipe
\p show the content of the current query buffer
\pset <opt> set table output <opt> = {format|border|expanded|fieldsep|
null|recordsep|tuples_only|title|tableattr|pager}
\q quit psql
\qecho <text> write text to query output stream (see \o)
\r reset (clear) the query buffer
\s [file] print history or save it in [file]
\set <var> <value> set internal variable
\t show only rows (currently off)
\T <tags> HTML table tags
\unset <var> unset (delete) internal variable
\w <file> write current query buffer to a <file>
\x toggle expanded output (currently off)
\z list table access permissions
\! [cmd] shell escape or command</PRE
></DIV
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="AEN4969"
>Executing Queries</A
></H2
><P
> Entering and executing queries within <SPAN
><I
CLASS="EMPHASIS"
>psql</I
></SPAN
> can be done two different ways.
When using the client in interactive mode, the normal method is to directly enter
queries into the prompt (i.e., standard input, or <TT
CLASS="LITERAL"
>stdin</TT
>). However,
through the use of <SPAN
><I
CLASS="EMPHASIS"
>psql</I
></SPAN
> 's <TT
CLASS="LITERAL"
>\i</TT
> slash command,
you can have <SPAN
><I
CLASS="EMPHASIS"
>psql</I
></SPAN
> read and interpret a file on your local
filesystem as the query data.
</P
><DIV
CLASS="SECT3"
><H3
CLASS="SECT3"
><A
NAME="AEN4977"
>Entering queries at the psql prompt</A
></H3
><P
> To enter queries directly into the prompt, open <SPAN
><I
CLASS="EMPHASIS"
>psql</I
></SPAN
> and make
sure you are connected to the correct database (and logged in as the correct user). You will
be presented with a prompt that, by default, is set to display the name of the database you
are currently connected to. The prompt will look like this:
<SPAN
><I
CLASS="EMPHASIS"
>psql</I
></SPAN
>:
</P
><PRE
CLASS="SCREEN"
>testdb=#</PRE
><P
>To pass SQL statements to PostgreSQL, simply type them into the prompt. Anything you
type (barring a slash command) will be queued until you terminate the query with a semicolon. This is the case
even if you start a new line of type, thus allowing you to spread query statements across multiple lines.
Examine <A
href="c4890#ENTERINGSTATEMENTSINTOPSQL"
>Example 4-3</A
> to see how this is done.</P
><DIV
CLASS="EXAMPLE"
><A
NAME="ENTERINGSTATEMENTSINTOPSQL"
></A
><P
><B
>Example 4-3. Entering statements into psql</B
></P
><PRE
CLASS="SCREEN"
>testdb=# <TT
CLASS="USERINPUT"
><B
>SELECT * FROM employees</B
></TT
>
testdb-# <TT
CLASS="USERINPUT"
><B
> WHERE firstname = 'Michael';</B
></TT
></PRE
></DIV
><P
>The query entered in <A
href="c4890#ENTERINGSTATEMENTSINTOPSQL"
>Example 4-3</A
> will return a table
that consists of all employees whose first name is Michael. The query could be broken up
over multiple lines to improve readability, and <SPAN
><I
CLASS="EMPHASIS"
>psql</I
></SPAN
> would not send it
to the backend until the terminating semicolon was sent. The prompt will show the end-character of a previous line if the character
requires a closing character, such as a parenthesis or a quote (this is not shown in the example).
If you were to issue a <TT
CLASS="LITERAL"
>CREATE TABLE</TT
> command to start a statement,
and then hit enter to begin a new line for readability purposes, you would see a prompt similar to
the one displayed in <A
href="c4890#LEAVINGENDCHARACTERSOPEN"
>Example 4-4</A
>.
</P
><DIV
CLASS="EXAMPLE"
><A
NAME="LEAVINGENDCHARACTERSOPEN"
></A
><P
><B
>Example 4-4. Leaving end-characters open</B
></P
><PRE
CLASS="SCREEN"
>testdb=# <TT
CLASS="USERINPUT"
><B
>CREATE TABLE employees (</B
></TT
>
testdb(#</PRE
></DIV
><P
> At this point you could continue the statement. The <SPAN
><I
CLASS="EMPHASIS"
>psql</I
></SPAN
> prompt is
informing you of the open parenthesis by inserting an open parenthesis symbol into the prompt.
</P
></DIV
><DIV
CLASS="SECT3"
><H3
CLASS="SECT3"
><A
NAME="AEN5001"
>Editing the query buffer</A
></H3
><P
> Use the <TT
CLASS="LITERAL"
>\e</TT
> command to edit the current query buffer with the
editor that your <TT
CLASS="LITERAL"
>EDITOR</TT
> environment variable is set to.
Doing so can be very useful when entering queries and statements in <SPAN
><I
CLASS="EMPHASIS"
>psql</I
></SPAN
>,
as you can easily view and modify all lines of your query or statement before it is committed.
<A
href="c4890#SETTINGTHEEDITORVARIABLE"
>Example 4-5</A
> shows how to set the <TT
CLASS="LITERAL"
>EDITOR</TT
>
variable. The <SPAN
><I
CLASS="EMPHASIS"
>vi</I
></SPAN
> editor will be used if <TT
CLASS="LITERAL"
>EDITOR</TT
>
is not set.
</P
><DIV
CLASS="EXAMPLE"
><A
NAME="SETTINGTHEEDITORVARIABLE"
></A
><P
><B
>Example 4-5. Setting the EDITOR variable</B
></P
><PRE
CLASS="SCREEN"
>$ <TT
CLASS="USERINPUT"
><B
>set EDITOR='joe'</B
></TT
>
$ <TT
CLASS="USERINPUT"
><B
>export EDITOR</B
></TT
></PRE
></DIV
><P
> You can also use this command to save your current buffer as a file. Issue the
<TT
CLASS="LITERAL"
>\e</TT
> command to enter editing mode.
This will open your editor and load the buffer as if it were a file. Complete whatever work you
wish to do with the buffer, then use your editor's save function to save the buffer and return to
<SPAN
><I
CLASS="EMPHASIS"
>psql</I
></SPAN
>. To save the query as a normal file, use your editor's
save-as function and save it as a file other than the <SPAN
><I
CLASS="EMPHASIS"
>.tmp</I
></SPAN
>
created by <TT
CLASS="LITERAL"
>\e</TT
>.
</P
></DIV
></DIV
></DIV
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
href="x4636"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
href="book1"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
href="x5021"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>Tables in PostgreSQL</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
href="p1162"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>Using Tables</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>
</div>
<br />
<div id="thefineprint">
<a href="http://www.commandprompt.com/products/mammothpostgresql/"><img src="../images/powered_by_mammoth.gif" alt="Powered by Mammoth PostgreSQL" title="Powered by Mammoth PostgreSQL" border="0" /></a>
<br />
Copyright © 2000-2007 Command Prompt, Inc. All Rights Reserved. All trademarks property of their respective owners.
</div>
</div>
</div>
</body>
</html>