Retrieving Data

Let's look at a sample database called shop. The database is a prototype of a bookshop, and we will use this database in most parts of this chapter. It might look a bit confusing at first, but we'll work though every detail of the SQL code and the database structure. The following is the output of pg_dump, an interface for dumping databases into ASCII files:

  CREATE SEQUENCE "products_id_seq" start 5 increment   1 maxvalue 2147483647 minvalue 1  cache 1 ;   SELECT nextval ('"products_id_seq"');   CREATE SEQUENCE "prodtext_id_seq" start 8 increment   1 maxvalue 2147483647 minvalue 1  cache 1 ;   SELECT nextval ('"prodtext_id_seq"');   CREATE SEQUENCE "prodcat_id_seq" start 9 increment   1 maxvalue 2147483647 minvalue 1  cache 1 ;   SELECT nextval ('"prodcat_id_seq"');   CREATE SEQUENCE "customer_id_seq" start 3 increment   1 maxvalue 2147483647 minvalue 1  cache 1 ;   SELECT nextval ('"customer_id_seq"');   CREATE SEQUENCE "sales_id_seq" start 4 increment   1 maxvalue 2147483647 minvalue 1  cache 1 ;   SELECT nextval ('"sales_id_seq"');   CREATE TABLE "products" (   "id" int4 DEFAULT   nextval('products_id_seq'::text) NOT NULL,   "prodid" int8 NOT NULL,   "price" numeric(9,2) NOT NULL,   "stored" numeric(9,2) DEFAULT 0,   PRIMARY KEY ("prodid")   );   CREATE TABLE "prodtext" (   "id" int4 DEFAULT   nextval('prodtext_id_seq'::text) NOT NULL,   "prodid" int8,   "lang" text,   "proddesc" text   );   CREATE TABLE "prodcat" (   "id" int4 DEFAULT   nextval('prodcat_id_seq'::text) NOT NULL,   "prodid" int8 NOT NULL,   "prodcat" character varying(50) NOT NULL,   PRIMARY KEY ("id")   );   CREATE TABLE "customer" (   "id" int4 DEFAULT   nextval('customer_id_seq'::text) NOT NULL,   "name" character varying(50) NOT NULL,   "email" character varying(50),   "state" character varying(50) NOT NULL,   PRIMARY KEY ("name")   );   CREATE TABLE "sales" (   "id" int4 DEFAULT   nextval('sales_id_seq'::text) NOT NULL,   "salestime" timestamp,   "prodid" int8,   "prodprice" numeric(9,2),   "amount" int4,   "name" character varying(50),   "email" character varying(50),   "state" character varying(50),   PRIMARY KEY ("id")   );   COPY "products" FROM stdin;   1       385343  44.99  20.00   2       385342  49.99  64.00   3       394568  39.99  18.00   4       106666  39.99  120.00   5       765354  29.99  98.00   \.   COPY "prodtext" FROM stdin;   1       385343  english Python Developer's Handbook   2       385343  german  Python Entwickler Handbuch   3       385342  english Linux Socket Programming   4       394568  english Linux Hardware Handbook   5       394568  german  Das Linux Hardware Buch   6       106666  english Linux: Networking for your Office   7       106666  german  Linuxnetzwerke frs Bro   8       765354  english Palm Programming   \.   COPY "prodcat" FROM stdin;   1       385343  Python   2       385343  Developer   3       385343  Handbook   4       385342  Linux   5       394568  Linux   6       394568  Handbook   7       106666  Networking   8       106666  Linux   9       765354  Palm   \.   COPY "customer" FROM stdin;   1       John   [e-mail]    Florida   2       Robert [e-mail]   Georgia   3       Peter  [e-mail]    Alaska   \.   COPY "sales" FROM stdin;   1       2001-04-03 15:01:27+02 106666  39.99   1      John   [e-mail]    Florida   2       2001-05-12 16:22:09+02 394568  39.99   1      John    [e-mail]    Florida   3       2001-05-12 17:19:04+02 385343  49.99   2      Robert  [e-mail]  Florida   4       2001-06-12 17:19:04+02 385343  44.99   1      Robert  [e-mail]  Florida   \.   CREATE INDEX "products_id_key" on "products"   using btree ("id" "int4_ops");   CREATE INDEX "prodtext_id_key" on "prodtext"   using btree ("id" "int4_ops");   CREATE INDEX "prodcat_id_key" on "prodcat"   using btree ("id" "int4_ops");   CREATE INDEX "customer_id_key" on "customer"   using btree("id" "int4_ops");   CREATE INDEX "customer_email_key" on "customer"   using btree ("email" "varchar_ops");   CREATE INDEX "sales_email_key" on "sales"   using btree ("email" "varchar_ops");   CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER DELETE   ON "products" NOT DEFERRABLE INITIALLY IMMEDIATE   FOR EACH ROW EXECUTE PROCEDURE   "RI_FKey_noaction_del" ('<unnamed>', 'prodtext',   'products', 'UNSPECIFIED', 'prodid', 'prodid');   CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER UPDATE   ON "products" NOT DEFERRABLE INITIALLY   IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE   "RI_FKey_noaction_upd" ('<unnamed>', 'prodtext',   'products', 'UNSPECIFIED', 'prodid', 'prodid');   CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER   INSERT OR UPDATE ON "prodtext"   NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW   EXECUTE PROCEDURE "RI_FKey_check_ins" ('<unnamed>',   'prodtext', 'products', 'UNSPECIFIED',   'prodid', 'prodid');  

Let's take a closer look at this code. First, sequences are created. Every table has a column called id , and the sequences assign a unique id to every row in the database. Then we create a table called products to store all information about the products in the shop. The column called prodid will be a unique product id. prodid is also the primary key of the table. price will contain the current price of the product, and stored will contain the number of available units of the product.

prodcat contains the product categories. One book might have multiple entries. The table is referred to by the product id. prodtext stores the title of a book in multiple languages. The customer table stores information about all registered users, and the sales table stores all information about sales, such as the number of books a user purchased or the price of a product.

The COPY commands are used to read data directly from the input file. Then, indices are created on the id column of all tables. At this point, it is useful to add additional indices for certain columns to speed up queries. It depends on the type of queries the user wants to perform on which columns to define indices.

Note

Creating indices is not always good, because sometimes disk space is wasted . Reduced storage is not the only drawback. Sometimes indices can slow the database down, especially when performing INSERT statements. This happens because the database must update the index and the data, which is actually slower than just doing the updates for the data.

Finally, triggers are added to guarantee the integrity of the database. It is not useful to have a product in table prodtext that doesn't exist in table products anymore ”the triggers help the user to do some data cleaning.

If you want to create a database with the data shown previously, create an empty database (use createdb shop ) and write a simple text file containing the SQL code. Then type

  psql shop < yourtextfile.sql  

As soon as the import is ready, you can connect to your database with psql.

Simple Queries

Retrieving data is probably the most important activity when dealing with databases. Storing data is useful only if you can find the right piece of information any time it is needed. In case of relational databases, you use the SELECT command to retrieve data from a database. SELECT statements can be very simple but also very tricky. This section should give you a short overview of how to use SELECT for simple queries. Let's start with an example. We want to retrieve where the product id is 385343 :

  SELECT * FROM products WHERE prodid='385343';  

The database executes the query and if everything is configured correctly, displays this result:

 id  prodid  price  stored ----+--------+-------+--------   1  385343  44.99   20.00 (1 row) 

The database has returned all requested records and all columns of the table. If you want to have only the last three columns of the table and only products with a prodid that is higher than 385343 , you use the following query:

  SELECT prodid,price,stored FROM products WHERE prodid > '385343';  

The result is

 prodid  price  stored --------+-------+--------  394568  39.99   18.00  765354  29.99   98.00 (2 rows) 

Sometimes it is necessary to build more complex WHERE clauses. Imagine a query where we want to retrieve all records where the product id is higher than 385343 and the price is lower than 35$ . The query looks like this:

  SELECT * FROM products WHERE prodid > '385343' AND price < 35;  

The result contains only one value, because the price of product number 394568 is higher than 35$ . The AND token tells the database to return only values where both conditions are fulfilled.

Very often AND and OR tokens have to be combined to find the right result. The following query retrieves all records in table products where the product id is 385343 if the price is lower than 35% . Additionally, all records are displayed where the price is exactly 44.99$ . With the help of the OR token, the database returns all records where the left expression (prodid > '385343' AND price < 35) or the right expression (price='44.99') returns true:

  SELECT * FROM products WHERE (prodid > '385343'   AND price < 35) OR price='44.99';  

Here is the result for this query:

 id  prodid  price  stored ----+--------+-------+--------   1  385343  44.99   20.00   5  765354  29.99   98.00 

The product with id of 385343 is returned because the price matches the right expression exactly. The product with number 765354 is returned because the product is higher than 385343 and the price is lower than 29.99$ .

You can see in these first few examples that SQL offers an easy syntax to perform simple queries quickly and without writing huge amounts of code.

Let's look at another example, finding all categories in table prodcat . Because some categories might be listed twice, you can use the DISTINCT operator to make sure that each word appears only once in the result:

  SELECT DISTINCT prodcat FROM prodcat;  

The result is

 prodcat ------------  Developer  Handbook  Linux  Networking  Palm  Python (6 rows) 

Only 6 rows are returned, although 9 rows are in the database, because the words Handbook and Linux appear more than once in the table. Use the following if you want the database to return only the first 3 rows in alphabetical order:

  SELECT DISTINCT prodcat FROM prodcat   ORDER BY prodcat ASC LIMIT 3;  

The result is

 prodcat -----------  Developer  Handbook  Linux (3 rows) 

You use the ORDER BY clause to sort the result. ASC means that the result has to be sorted in ascending order. If you want the database to return the result in descending order, you use the DESC token. The LIMIT command saves time when performing SELECT * operations on extremely huge tables. You also can display all but the first three records. With the help of OFFSET , you can skip the first n records returned.

You can use OFFSET and LIMIT together. If you want to get only the fourth record returned by the query, you use the following:

  SELECT DISTINCT prodcat FROM prodcat   ORDER BY prodcat ASC LIMIT 1 OFFSET 3;  

This query returns Networking . The explanation for the result is simple: OFFSET 3 makes the database skip the first 3 rows. LIMIT 1 takes the first line and omits the rest.

Performing Simple Calculations

In many cases, it is useful to perform simple calculations directly within an SQL statement. Assume a query where you want to calculate the total amount of money a user has to pay:

  SELECT prodid, prodprice, amount, prodprice*amount, name   FROM sales;  

You take the price of one unit of the product and multiply it with the amount of units the user bought. The result is

 prodid  prodprice  amount  ?column?  name --------+------------+-------+----------+--------  106666      39.99       1     39.99  John  394568      39.99       1     39.99  John  385343      49.99       2     99.98  Robert  385343      44.99       1     44.99  Robert 

You also can write more complex queries. In all cases, SQL uses the " infix " syntax.

Joining Tables

Joining tables is an extremely important issue when talking about relational and object relational databases. Joins are used to select and combine information from multiple columns. The tables you want to join must have at least one column in common.

Suppose you want to create a list with all product descriptions, their price, and the amount of books stored. The amount of books stored and the price can be found in the products table. The description of the products can be found in the prodtext table. Both tables have a column for the product id, so you can join the tables with the help of the product id:

  SELECT prodtext.lang, prodtext.proddesc,   products.price, products.stored   FROM products, prodtext   WHERE products.prodid=prodtext.prodid;  

The result is the following list of eight records containing the German and English titles of the books:

 lang               proddesc                price  stored ---------+-----------------------------------+-------+--------  english  Linux: Networking for your Office  39.99  120.00  german   Linuxnetzwerke frs Bro           39.99  120.00  english  Linux Socket Programming           49.99   64.00  english  Python Developer's Handbook        44.99   20.00  german   Python Entwickler Handbuch         44.99   20.00  english  Linux Hardware Handbook            39.99   18.00  german   Das Linux Hardware Buch            39.99   18.00  english  Palm Programming                   29.99   98.00 

If you now want to retrieve all books (English title only) where more than 80 books are stored, you use the following query:

  SELECT prodtext.lang, prodtext.proddesc,   products.price, products.stored   FROM products, prodtext   WHERE products.prodid=prodtext.prodid   AND prodtext.lang='english'   AND products.stored>80;  

The result is

 lang               proddesc                price  stored ---------+-----------------------------------+-------+--------  english  Linux: Networking for your Office  39.99  120.00  english  Palm Programming                   29.99   98.00 

Only two books are left. In the example, you can see that combining data using AND tokens is extremely easy. Remember the most important point when joining tables: The tables must have at least one column in common. This example contains the column called prodid . Don't forget to perform the actual join ( products.prodid=prodtext.prodid ) in your query. If you do forget, you will get the Cartesian product of the two tables as the result. The Cartesian product is a join without a WHERE clause. Try the following:

  SELECT * FROM products, prodtext;  

to find out about the outcome. The result will be 40 lines long, and most of the lines won't have anything in common.

In the next example, we want to generate a complete list of all sales including the product description:

  SELECT sales.salestime, prodtext.proddesc, sales.amount   FROM sales, prodtext   WHERE prodtext.prodid=sales.prodid   AND prodtext.lang='english';  

This joins the table prodtext and the table sales via the product id:

 salestime                    proddesc               amount ------------------------+----------------------------------+--------  2001-05-12 17:19:04+02  Python Developer's Handbook            2  2001-06-12 17:19:04+02  Python Developer's Handbook            1  2001-05-12 16:22:09+02  Linux Hardware Handbook                1  2001-04-03 15:01:27+02  Linux: Networking for your Office      1 

Self-Joins and Aliases

This section gives you insight into aliasing and self-joins. Aliases can be used to assign an additional name to a column. On the one hand, it is possible to write shorter and easier-to-read queries (aliasing can speed up writing a query significantly, especially when the query string is more complex). On the other hand, aliases can be used to perform self-joins. With a self-join , a table performs a join with itself. You will see an example of this later. First let's look at a simple example using aliases:

  SELECT * FROM products AS a where a.stored=20;  

This query selects all records from table products where the amount of books stored is exactly 20 . In this case, a is an alias for products . The query can also be written the following way:

  SELECT * FROM products a where a.stored=20;  

It is not necessary to use the AS token, but AS can sometimes make the query easier to read.

Let's look at a shop where each product can be assigned to multiple product categories. Because the number of categories of a product should not be limited, we won't define a separate column for every category. It would be nice to have a table with just one table for the product category. The following table is called prodcat in the sample database (use "SELECT

* FROM prodcat;" ):

 id  prodid   prodcat ----+--------+------------   1  385343  Python   2  385343  Developer   3  385343  Handbook   4  385342  Linux   5  394568  Linux   6  394568  Handbook   7  106666  Networking   8  106666  Linux   9  765354  Palm 

First you write a query to find all products that are assigned to the category Handbook :

  SELECT * FROM prodcat WHERE prodcat='Handbook';  

Two records are returned:

 id  prodid  prodcat ----+--------+----------   3  385343  Handbook   6  394568  Handbook 

Then we want to find all products that are listed in categories Handbook and Developer . We use a self-join:

  SELECT *   FROM prodcat AS a, prodcat AS b   WHERE a.prodcat='Handbook'   AND b.prodcat='Developer'   AND a.prodid=b.prodid;  

The result is

 id  prodid  prodcat   id  prodid   prodcat ----+--------+----------+----+--------+-----------   3  385343  Handbook   2  385343  Developer 

The database performs the join as if we had two identical tables. The result is only one record because only this record matches both categories. It is also possible to look for all records that match three categories.

Note

When working with multiple tables, you should use the * operator with care, because * displays all columns from all tables listed in the FROM clause.

In the next example, we look for all records that are assigned to Handbook , Developer , and Python :

  SELECT *   FROM prodcat AS a, prodcat AS b, prodcat AS c   WHERE a.prodcat='Handbook'   AND b.prodcat='Developer'   AND c.prodcat='Python'   AND a.prodid=b.prodid   AND a.prodid=c.prodid   AND b.prodid=c.prodid;  

The result is again one record:

 id   prodid  prodcat   id  prodid   prodcat   id  prodid  prodcat ----+--------+----------+----+--------+-----------+----+--------+---------   3  385343  Handbook   2  385343  Developer   1  385343  Python 

You can see in these examples that self-joins can be done rather easily. The only crucial point is that you must not forget the actual joins to get the right result. If you try your queries on small testing tables, make sure that you have every possible difficulty in them. If the result of a query seems to be right, try to go through the code again and check whether you have all necessary join operations in it. If you don't test thoroughly, you might have bad luck when performing the query on large tables.

The output of the previous query is not a beautiful one. Try the following query to make the result easier to understand:

  SELECT a.id AS rowid, a.prodid AS "product id"   FROM prodcat AS a, prodcat AS b, prodcat AS c   WHERE a.prodcat='Handbook'   AND b.prodcat='Developer'   AND c.prodcat='Python'   AND a.prodid=b.prodid   AND a.prodid=c.prodid   AND b.prodid=c.prodid;  

The result is much nicer than the previous one:

 rowid  product id -------+------------      3     385343 

In this example, you see how you can change the description of a table with the help of aliases. If the new heading contains whitespace characters , such as blanks, the new heading has to be quoted in the SQL query ”if not, it can be written without quotes. The table can now be read easier.

Aggregation Functions

Aggregates are a powerful feature of relational databases. They can be used to perform operations with multiple lines involved. Let's look at an example where we want to count the lines in a table with the help of the count() function:

  SELECT COUNT(*) FROM products;  

The result is

 count -------      5 (1 row) 

The count() function can also be combined with a WHERE clause. The following example shows you how to count all records where the amount of stored books is equal to or higher than 64 :

  SELECT COUNT(*) FROM products WHERE stored >= 64;  

The result of the query is 3 . As you might have noticed in the previous example, we queried and displayed only one column; now we also want the product id to be displayed:

  SELECT prodid, COUNT(*) FROM products;  

This query leads to an error:

 ERROR:  Attribute products.prodid must be GROUPed or used in an aggregate function 

The count() function doesn't know what to count any more and the database won't perform the query. The problem can be solved by adding an aggregate function to the query:

  SELECT prodid, COUNT(*) FROM products GROUP BY prodid;  

The result is a list with all product ids and the frequency they appear in the table:

 prodid  count --------+-------  106666      1  385342      1  385343      1  394568      1  765354      1 (5 rows) 

In the example, you can see that every product id appears only once in the table.

Now we want to write a more complex query. We want to get a list with the sum of all sales of a certain book, including the product description. We have to perform a join of table sales and table prodtext . We want only the English titles to be displayed. Here is the query:

  SELECT a.proddesc, SUM(b.amount)   FROM prodtext AS a, sales AS b   WHERE a.prodid=b.prodid   AND a.lang='english'   GROUP BY b.prodid, a.proddesc;  

The result is

 proddesc               sum -----------------------------------+-----  Linux: Networking for your Office    1  Python Developer's Handbook          3  Linux Hardware Handbook              1 (3 rows) 

We use the sum() function to get the sum of all sales. In the example, we use the GROUP BY token two times. This is necessary because one product id can have many titles. We have also used aliasing to make the actual code of the query shorter. Don't forget the actual join operation in the query; in this example, the join is performed by the a.prodid=b.prodid expression.

In some cases, it can be useful to get only a small piece of the result of a query using aggregate functions. Imagine an example where we want to get all products that have been sold more than once:

  SELECT prodid, SUM(amount) AS "total sum"   FROM sales   GROUP BY prodid   HAVING SUM(amount) > 1;  

The result is very short:

 prodid  total sum --------+-----------  385343          3 (1 row) 

Only product number 385343 is available in the list, where more than one unit has been sold. The query contains a HAVING clause. HAVING is used to perform condition tests on aggregate functions. In our case, we check whether the result of the GROUP BY is higher than 1 . A query can also have AND or OR tokens in the HAVING clause. The following example adds an AND token to the query we used earlier:

  SELECT prodid, SUM(amount) AS "total sum"   FROM sales   GROUP BY prodid   HAVING SUM(amount) > 1   AND SUM(amount) < 10;  

The result is the same as the result of the query used before, because no product was sold more often than nine times.

Now we want to calculate the average price of the products in table products :

  SELECT AVG(price) FROM products;  

The database performs the calculation and returns the following:

 avg ---------------  40.9900000000 (1 row) 

The value returned is precise, but in many cases not suitable. We want the result to be displayed like a currency, which means only two numbers after the comma. For that purpose, we use the round() function, which needs two arguments. The first argument is the actual value that has to be processed , and the second argument is used to define the precision. In our case, the query looks like this:

  SELECT ROUND(AVG(price),2) FROM products;  

The result is

 round -------  40.99 (1 row) 

This result is what we expected. Now we want the result to be displayed in full dollars without a comma:

  SELECT ROUND(AVG(price),0) FROM products;  

The result of the query is now 41 , because 41 is the closest integer value. The second parameter of the function can also be negative:

  SELECT ROUND(AVG(price),-2) FROM products;  

As you might have thought, the result is . You can see in these examples that PostgreSQL supports nested functions.

The next example shows how to calculate the minimum or the maximum of a column:

  SELECT MIN(price) FROM products;  

The result is

 min -------  29.99 (1 row) 

Note

Check out the docs of your PostgreSQL database to determine whether your version can also perform MAX() and MIN() with the help of indices. Otherwise, your query can be very slow on large tables.

Subqueries

Subqueries are used to calculate values that are needed within the query that is executed. To make it clearer, here is an example: Imagine you want to find all persons in a table whose income is higher than the average income of the persons stored in the table. You need the average income to find the persons with a higher income. One possibility to solve the problem is to perform two separate statements, but what do you do when half of the persons stored in the tables are removed by another user while processing the two SELECT statements? The only way to solve the problem properly is to use subqueries. A subquery is actually a query within a query.

Here is an example where we select all products with a higher price than the average price:

  SELECT *   FROM products   WHERE price >   (SELECT AVG(price) FROM products);  

First, the result of the subquery is calculated. After that, the result is used to perform the main query. Let's try it:

  SELECT *   FROM products   WHERE price >   (SELECT * FROM products);  

The database displays the following error:

 ERROR:  Subselect must have only one field 

It is obvious that the database can't perform the query because a price is only one value. When performing subqueries, you have to make sure that the subquery returns exactly one field.

One query can contain many subselects. The following is a query with two subselects for selecting all products that are more expensive than the average price, but less expensive than the average price plus 5$ :

  SELECT *   FROM products   WHERE price >   (SELECT AVG(price) FROM products)   AND  price <   (SELECT AVG(price)+5 FROM products);  

There will be only one record left in the result:

 id  prodid  price  stored ----+--------+-------+--------   1  385343  44.99   20.00 (1 row) 

Not only a query can contain many subselects, subselects can also contain subselects. This feature is powerful and useful ”especially when working with complex queries and data. Just insert a subquery in your SQL code where you need a special value that has to be calculated at runtime.

Using SELECT and INSERT

INSERT statements can easily be combined with SELECT statements. These are usually done when a large amount of data has to be copied from one table into another or when the results of complex queries are stored in temporary tables to achieve better performance. In the following example, we create a temporary table and insert some values into it. Here is the CREATE TABLE command:

  CREATE TEMPORARY TABLE temptable   (id integer, prodid int8, price numeric(9,2));  

Note

The temporary table won't be listed by a \d command, but the table can be treated like a nontemporary table.

Now we insert some data from table products into table temptable :

  INSERT INTO temptable(prodid, price) (SELECT prodid, price FROM products);  

Let's see whether the operations have been performed correctly:

  SELECT * FROM temptable;  

The test result is

 id  prodid  price ----+--------+-------      385343  44.99      385342  49.99      394568  39.99      106666  39.99      765354  29.99 (5 rows) 

You can see that the database has done all INSERT operations properly.

Pattern Matching and Regular Expressions in SQL

A regular expression is a formula for matching strings that follow some pattern. Many people are afraid to use regular expressions, but when you get used to them, you can't live without them. Perl hackers especially seem to be addicted to regular expressions, because they are a fast and precise way of performing substring searching.

Regular expressions are implemented in all major databases and can therefore also be used in PostgreSQL. Although the syntax of regular expressions differs slightly from the syntax programming languages such as Perl, it is easy to learn.

Regular expressions consist of characters and metacharacters. Metacharacters are used to tell the database that a pattern has to occur more than once or something like that. In the simplest case, a regular expression can be a search string. Let's start with an example: We want to retrieve all people in table customer whose name is John :

  SELECT * FROM customer WHERE name LIKE 'John';  

The result is

 id  name     email      state ----+------+-------------+---------   1  John  [e-mail]  Florida (1 row) 

Note

The database performs a substring search for John .

The LIKE token can be used to perform the pattern matching, but it is also possible to use the ~ operator instead.

Let's write a query where all names starting with R are displayed:

  SELECT * FROM customer WHERE name ~ '^R';  

The ^ operator is used to match the beginning of a string. The R must be the first character after the beginning.

 id   name      email       state ----+--------+--------------+---------   2  Robert  [e-mail]  Georgia (1 row) 

What happens if the name starts with r instead of R ? The ~ operator performs case-sensitive searching. We can use the ~* operator to perform case-insensitive queries:

  SELECT * FROM customer WHERE name ~* '^r';  

The result is the same as the previous one. Now let's query all names that do not start with r :

  SELECT * FROM customer WHERE name !~ '^R';  

The result contains the remaining records:

 id  name      email       state ----+-------+--------------+---------   1  John   [e-mail]   Florida   3  Peter  [e-mail]  Alaska (2 rows) 

The case-insensitive version of the query is

  SELECT * FROM customer WHERE name !~* '^r';  

In some cases, it is necessary to search for a sequence of characters in a string. Here is an example where we search for all names that have er somewhere in them:

  SELECT * FROM customer WHERE name ~* 'er';  

The query returns the records of Robert and Peter . It is also possible to see whether certain characters appear in a string:

  SELECT * FROM customer WHERE name ~ '[A-P]';  

This query returns all names that contain one of the letters from A to P . If you don't want to have the records from A to P , but do want all others, write [^A-P] instead. The same kind of query can be done with numbers.

Suppose we want to look for all records that do not have the numbers 2 or 3 in the id and contain the letters from A to P :

  SELECT * FROM customer WHERE id ~ '[^23]' AND name ~ '[A-P]';  

The result is

 id  name     email      state ----+------+-------------+---------   1  John  [e-mail]  Florida (1 row) 

The $ operator symbolizes the end of a string. If we want to query all records that end with an n , we can use the following SQL command:

  SELECT * FROM customer WHERE name ~ 'n$';  

The only name ending with an n is John , so that is the only record returned. What happens if we want to query records containing a $ or any other metacharacter? To show the solution for such a problem, let's create a temporary table and insert one record:

  CREATE TEMPORARY TABLE demo(demo text);   INSERT INTO demo VALUES ('demo$');  

Now we want to write a query that retrieves the records:

  SELECT * FROM demo WHERE demo ~ '\ $';  

The character has to be masqueraded using a backslash ( \ ). The result of the query is

 demo -------  demo$ (1 row) 

In this example, you can see how powerful regular expressions are, but the real power of regular expressions can be seen when you have to perform complex queries. Assume a situation where someone tells you that he has bought a book on "the third." You don't know the month but you know that it must have been in the year 2001. You can write a query that selects all possible records from table sales :

  SELECT id, salestime   FROM sales   WHERE salestime ~ '2001-.{ 2} -03';  

The query selects all records whose salestime contains 2001 , a - and exactly 2 characters before -03 . The two undefined characters are the month when the sale took place. A period ( . ) matches any character exactly once. Parentheses can be used to tell the database how many undefined characters must be found before the rest. If you don't know whether the month is stored with one or two numbers, you can use the following:

  SELECT id, salestime   FROM sales   WHERE salestime ~ '2001-.{ 1,2} -03';  

The result of the query contains the requested record:

 id        salestime ----+------------------------   1  2001-04-03 15:01:27+02 (1 row) 

Assume that you knew that the sale took place in March or April; you can use this query:

  SELECT id, salestime   FROM sales   WHERE salestime ~ '2001-.3?4?-03';  

A question mark ( ? ) symbolizes that a certain character may occur zero times or once. In this example, we look for March ( 3 ) or April ( 4 ). If one of the characters is found, the right result will be displayed.

Two additional operators are the + and the * . + means that the previous character must match at least once. * means that the previous character must match at least zero times. Imagine a query where you are looking for a certain product id. The only thing you know is that it started with 106 and continued with 6 , but you don't know how many times the 6 is in the product id. Here is the solution for your problem:

  SELECT * FROM products WHERE prodid ~ '106*';  

In this example, 6 has to occur after 10 at least zero times; the result will contain the requested product id:

 id  prodid  price  stored ----+--------+-------+--------   4  106666  39.99  120.00 (1 row) 

Tables 3.1 and 3.2 present an overview of the most important operators and metacharacters, respectively, of regular expressions.

Table 3.1. Operators of Regular Expressions

Operator	Description
~	Case-sensitive matching
~*	Case-insensitive matching
!~	Does not match, case-sensitive
!~*	Does not match, case-insensitive

Table 3.2. Metacharacters of Regular Expressions

Special Metacharacter	Description
^	Matches the beginning of a string.
$	Matches the end of a string.
.	Matches any character exactly once.
+	Matches previous character once or more often.
*	Matches previous character zero times or more often.
?	Matches previuous character zero times or more often.
[abc]	Matches one of the characters in the list.
[^abc]	Doesn't match one of the characters in the list.
[a-c]	Matches all characters from a to c.
[^a-c]	Doesn't match the characters from a to c.
{ x}	The previous character must occur exactly x times.
{ x,y}	The previous character must occur x to y times.
()	Parentheses are used to group expressions.

Groups occur as one character for the expression after the parentheses (for example, the * operator after the parentheses means that the whole expression in parentheses has to match zero times or more often).

The examples of regular expressions might seem a bit strange . You might wonder who is looking for persons or for products with a stupid sequence of characters in it. Don't be surprised, but most of the examples are taken from real-world situations. Sometimes a customer wants to know something, but he can't say precisely what. He might say, "It must be something like that." In these cases, you can use regular expressions. Regular expressions can save you a lot of time and lots of code. You can solve complex problems within a few seconds and with only a few characters or lines of code.

Those people who are already addicted to regular expressions ”like me ”know what I am talking about.

Using SELECT INTO Statements

You use SELECT INTO to redirect the result of a query into a table. This is sometimes useful when debugging code; or you can use it to write two simple, rather than one complex, SQL statement:

  SELECT prodid, price   INTO TABLE sel   FROM products;  

The SELECT INTO statement creates a new table called sel . Let's look at whether the table has been created and filled correctly:

 shop=#  SELECT * FROM sel;  prodid  price --------+-------  385343  44.99  385342  49.99  394568  39.99  106666  39.99  765354  29.99 (5 rows) 

sel has two columns called prodid and price , and contains the complete result of the SELECT statement. sel is not a temporary table, so it cannot be seen only by the user who created it. If you want sel to be a temporary table, try the following command:

  DROP TABLE sel;   SELECT prodid, price   INTO TEMPORARY TABLE sel   FROM products;  

The data you want to insert into a table created by SELECT INTO can be the result of a very complex SQL query.

Note

If you want to add data to a table without creating a new table, use INSERT in combination with SELECT statements.

Formatting the Output and Casting

When performing SELECT statements, psql creates a header for the tables. This header might not be, in many cases, the result you want to see. Let's see how you can make the output look right. We start with a simple SELECT statement:

  SELECT AVG(price) FROM products;  

The result of the query does not look right:

 avg ---------------  40.9900000000 (1 row) 

We want to change the header to average price , which can be done with the following query:

 shop=#  SELECT AVG(price) AS "average price" FROM products;  average price ---------------  40.9900000000 (1 row) 

Now we want to display the price and cast it to integer:

  SELECT CAST(AVG(price) AS int4) AS "average price"   FROM products;  

The result of the query looks like this:

 average price ---------------             41 (1 row) 

In this example, we perform an explicit cast. First the average price is calculated, and then the cast to integer is done. We can use the CAST() function to perform the operation. Casts can also be done without the help of the CAST() function. Simply add a colon and the datatype you want to receive to the column:

  SELECT AVG(price)::int4 AS "average price"   FROM products;  

Finally we want the price to be displayed with a $ at the end of the number, so we use the following code:

  SELECT '$'  CAST(AVG(price) AS int4)  AS "average price"   FROM products;  

The operator concatenates values in SQL. In our case, we add the $ symbol to the result:

 average price ---------------   (1 row) 

Note

Not all types of casts can be performed by the database. PostgreSQL 7.0.3, for instance, does not support casts from numeric to text; we had to use integer in the example , but to_char() is also possible.

Now we want to write a query where multiple columns are treated as one:

  SELECT name  ' bought '  amount  ' units' AS "result"   FROM sales;  

We select and concatenate multiple columns and assign the name result to the column. The table is now displayed as one column:

 result -----------------------  John bought 1 unit  John bought 1 unit  Robert bought 2 units  Robert bought 1 unit (4 rows) 

In the next example, we add a WHERE clause to the query that selects only those records that contain John :

  SELECT name  ' bought '  amount  ' units' AS result   FROM sales   WHERE name  ' bought '  amount  ' units'   ~ 'John';  

The result contains two records:

 result ---------------------  John bought 1 unit  John bought 1 unit (2 rows) 

We can't use the alias of the column in the WHERE clause because this would lead to an error in PostgreSQL 7.0.3. We have to use the full name of the column as it is shown in the previous example:

 shop=#  SELECT name  ' bought '  amount  ' units'   AS result   FROM sales   WHERE result ~ 'John';  ERROR:  Attribute 'result' not found 

Generating HTML

HTML is the most important language when dealing with Web applications. psql offers a flag that tells the database to generate HTML code instead of ordinary ASCII text. Unfortunately, this feature is supported only by psql and not by other interfaces of PostgreSQL. Because most Web interfaces won't use psql in combination with pipes, the flag is not used very often.

Use psql with the -H flag to enable HTML output. From now on, all results will be returned as HTML code. The most common way of performing a query that returns HTML code is to use your favorite UNIX shell.

Here is an example where we select all products that are more expensive than 40 dollars:

  echo "SELECT * FROM products WHERE price > 40;"  psql -H shop  

The result will be a table including all HTML tags:

 <table border=1>   <tr>     <th align=center>id</th>     <th align=center>prodid</th>     <th align=center>price</th>     <th align=center>stored</th>   </tr>   <tr valign=top>     <td align=right>1</td>     <td align=right>385343</td>     <td align=right>44.99</td>     <td align=right>20.00</td>   </tr>   <tr valign=top>     <td align=right>2</td>     <td align=right>385342</td>     <td align=right>49.99</td>     <td align=right>64.00</td>   </tr> </table> (2 rows)<br>