Retrieving Data from the Sample Database
| At this point, you should have a sample database (movies) that contains three tables ( tapes , customers , and rentals ) and a few rows in each table. You know how to get data into a table; now let's see how to view that data. The SELECT statement is used to retrieve data from a database. SELECT is the most complex statement in the SQL language, and the most powerful. Using SELECT , you can retrieve entire tables, single rows, a group of rows that meet a set of constraints, combinations of multiple tables, expressions, and more. To help you understand the basics of the SELECT statement, I'll try to break it down into each of its forms and move from the simple to the more complex. SELECT ExpressionIn its most simple form, you can use the SELECT statement to retrieve one or more values from a set of predefined functions. You've already seen how to retrieve your PostgreSQL user id: movies=# select user; current_user --------------- korry (1 row) movies=# \q Other values that you might want to see are select 5; -- returns the number 5 (whoopee) select sqrt(2.0); -- returns the square root of 2 select timeofday();-- returns current date/time select now(); -- returns time of start of transaction select version(); -- returns the version of PostgreSQL you are using select now(), timeofday();
The previous example shows how to SELECT more than one piece of information ”just list all the values that you want, separated by commas. The PostgreSQL User's Guide contains a list of all the functions that are distributed with PostgreSQL. In Chapter 2, I'll show you how to combine columns , functions, operators, and literal values into more complex expressions. SELECT * FROM TableYou probably won't use the first form of the SELECT statement very often ”it just isn't very exciting. Moving to the next level of complexity, let's see how to retrieve data from one of the tables that you created earlier: movies=# SELECT * FROM customers; id customer_name phone birth_date balance ----+----------------------+----------+------------+--------- 3 Panky, Henry 555-1221 1968-01-21 0.00 1 Jones, Henry 555-1212 1970-10-10 0.00 4 Wonderland, Alice N. 555-1122 1969-03-05 3.00 2 Rubin, William 555-2211 1972-07-10 15.00 (4 rows) When you write a SELECT statement, you have to tell PostgreSQL what information you are trying to retrieve. Let's take a closer look at the components of this SELECT statement. Following the SELECT keyword, you specify a list of the columns that you want to retrieve. I used an asterisk ( * ) here to tell PostgreSQL that we want to see all the columns in the customers table. Next, you have to tell PostgreSQL which table you want to view; in this case, you want to see the customers table. Now let's look at the results of this query. A SELECT statement returns a result set . A result set is a table composed of all the rows and columns (or fields ) that you request. A result set may be empty. You asked PostgreSQL to return all the columns in the customers table ”notice that the columns are displayed (from left to right) in the order that you specified when you created the table. You may have noticed that the rows are returned in an (apparently) arbitrary order. That's an important thing to keep in mind: Unless you specifically request that PostgreSQL return rows in a particular order, you won't be able to predict which rows will come first [5] . This is a performance feature; if you don't care about row ordering, let PostgreSQL return the rows in the fastest possible way.
SELECT Single-Column FROM TableIf you don't want to view all of the columns from a table, you can replace the * (following the SELECT keyword) with the name of a column: movies=# SELECT title FROM tapes; title --------------- The Godfather The Godfather Casablanca Citizen Kane Rear Window (5 rows) Again, the rows are presented in an arbitrary order. But this time you see only a single column. You may have noticed that "The Godfather" appears twice in this list. That happens because our imaginary video store owns two copies of that movie. I'll show you how to get rid of duplicates in a moment. SELECT Column-List FROM TableSo far, you have seen how to select all the columns in a table and how to select a single column. Of course, there is a middle ground ”you can select a list of columns: movies=# SELECT customer_name, birth_date FROM customers; customer_name birth_date ----------------------+------------ Jones, Henry 1970-10-10 Rubin, William 1972-07-10 Panky, Henry 1968-01-21 Wonderland, Alice N. 1969-03-05 (4 rows) Instead of naming a single column after the SELECT keyword, you can provide a column-separated list of column names. Column names can appear in any order, and the results will appear in the order you specify. SELECT Expression-List FROM TableIn addition to selecting columns, you can also select expressions. Remember, an expression is a combination of columns, functions, operators, literal values, and other expressions that will evaluate to a single value. Here is an example: movies=# SELECT movies-# customer_name, movies-# birth_date, movies-# age( birth_date ) movies-# FROM customers; customer_name birth_date age ----------------------+------------+------------------------------ Jones, Henry 1970-10-10 31 years 4 mons 3 days 01:00 Rubin, William 1972-07-10 29 years 7 mons 3 days 01:00 Panky, Henry 1968-01-21 34 years 23 days Wonderland, Alice N. 1969-03-05 32 years 11 mons 8 days (4 rows) In this example, I've selected two columns and an expression. The expression age( birth_date ) is evaluated for each row in the table. The age() function subtracts the given date from the current date [6] .
Selecting Specific RowsThe preceding few sections have shown you how to specify which columns you want to see in a result set. Now let's see how to choose only the rows that you want. First, I'll show you to how to eliminate duplicate rows; then I'll introduce the WHERE clause. SELECT [ALL DISTINCT DISTINCT ON]In an earlier example, you selected the titles of all the videotapes owned by your video store: movies=# SELECT title FROM tapes; title --------------- The Godfather The Godfather Casablanca Citizen Kane Rear Window (5 rows) Notice that "The Godfather" is listed twice (you own two copies of that video). You can use the DISTINCT clause to filter out duplicate rows: movies=# SELECT DISTINCT title FROM tapes; title --------------- Casablanca Citizen Kane Rear Window The Godfather (4 rows) You now have a single row with the value "The Godfather." Let's see what happens when you add the tape_id back into the previous query: movies=# SELECT DISTINCT title, tape_id FROM tapes; title tape_id ---------------+---------- Casablanca MC-68873 Citizen Kane OW-41221 Rear Window AH-54706 The Godfather AB-12345 The Godfather AB-67472 (5 rows) We're back to seeing "The Godfather" twice. What happened ? The DISTINCT clause removes duplicate rows, not duplicate column values; and when the tape IDs are added to the result, the rows containing "The Godfather" are no longer identical. If you want to filter rows that have duplicate values in one (or more) columns, use the DISTINCT ON() form: movies=# SELECT DISTINCT ON (title) title, tape_id FROM tapes; title tape_id ---------------+---------- Casablanca MC-68873 Citizen Kane OW-41221 Rear Window AH-54706 The Godfather AB-12345 (4 rows) Notice that one of the "The Godfather" rows has been omitted from the result set. If you don't include an ORDER BY clause (I'll cover that in a moment), you can't predict which row in a set of duplicates will be included in the result set. You can list multiple columns (or expressions) in the DISTINCT ON() clause. The WHERE ClauseThe next form of the SELECT statement includes the WHERE clause. Here is the syntax diagram for this form: SELECT expression-list FROM table WHERE conditions Using the WHERE clause, you can filter out rows that you don't want included in the result set. Let's see a simple example. First, here is the complete customers table: movies=# SELECT * FROM customers; id customer_name phone birth_date balance ----+----------------------+----------+------------+--------- 1 Jones, Henry 555-1212 1970-10-10 0.00 2 Rubin, William 555-2211 1972-07-10 15.00 3 Panky, Henry 555-1221 1968-01-21 0.00 4 Wonderland, Alice N. 555-1122 1969-03-05 3.00 (4 rows) Now pick out only those customers who owe you some money: movies=# SELECT * FROM customers WHERE balance > 0; id customer_name phone birth_date balance ----+----------------------+----------+------------+--------- 2 Rubin, William 555-2211 1972-07-10 15.00 4 Wonderland, Alice N. 555-1122 1969-03-05 3.00 (2 rows) In this example, I've used a single condition to restrict the rows included in the result set: balance > 0 . When PostgreSQL executes a SELECT statement, it evaluates the WHERE clause as it processes each row. If all the conditions specified by the WHERE clause are met, the row will be included in the result set (if a row meets all the conditions in the WHERE clause, the row satisfies the WHERE clause). Here is an example that is slightly more complex: movies=# SELECT customer_name, phone FROM customers movies-# WHERE movies-# ( balance = 0 ) movies-# AND movies-# ( AGE( birth_date ) < '34 years' ) movies-# ; customer_name phone ---------------+---------- Jones, Henry 555-1212 (1 row) In this query, I've specified two conditions, separated by an AND operator. The conditions are: balance = 0 and AGE( birth_date ) < '34 years' [7] . As before, PostgreSQL reads each row in the customers table and evaluates the WHERE clause. If a given row is to be included in the result set, it must satisfy two constraints ” balance must be equal to zero and the customer must be younger than 34 years of age. If either of these conditions is false for a given row, that row will not be included in the result set.
AND is one of the logical operators supported by PostgreSQL. A logical operator is used to combine logical expressions . A logical expression is an expression that evaluates to TRUE , FALSE , or unknown ( NULL ). The other two logical operators are OR and NOT . Let's see how the OR operator works: movies=# SELECT id, customer_name, balance, AGE(birth_date) movies-# FROM customers movies-# WHERE movies-# ( balance = 0 ) movies-# OR movies-# ( AGE( birth_date ) < '30 years' ) movies-# ; id customer_name balance age ----+----------------+---------+------------------------------ 1 Jones, Henry 0.00 31 years 4 mons 5 days 01:00 2 Rubin, William 15.00 29 years 7 mons 5 days 01:00 3 Panky, Henry 0.00 34 years 25 days (3 rows) The OR operator evaluates to TRUE if either (or both) of the conditions is TRUE . The first row ( id = 1) is included in the result set because it satisfies the first condition ( balance = 0 ). It is included even if it does not satisfy the second condition. The second row ( id = 2 ) is included in the result set because it satisfies the second condition, but not the first. You can see the difference between AND and OR . A row satisfies the AND operator if both conditions are TRUE . A row satisfies the OR operator if either condition is TRUE (or if both are TRUE ). The NOT operator is simple: movies=# SELECT * FROM customers movies-# WHERE movies-# NOT ( balance = 0 ) movies-# ; id customer_name phone birth_date balance ----+----------------------+----------+------------+--------- 2 Rubin, William 555-2211 1972-07-10 15.00 4 Wonderland, Alice N. 555-1122 1969-03-05 3.00 (2 rows) NOT evaluates to TRUE if its operand is FALSE and evaluates to FALSE if its operand is TRUE . The NOT operator inverts (or reverses) a test. Without the NOT operator, the previous example would have returned all customers where the balance column was equal to zero. With the NOT operator, you get the other rows instead. One other point that I should mention about the WHERE clause. Just because you mention a column in the WHERE clause does not mean that you have to include the column in the result set. For example: movies=# SELECT id, customer_name FROM customers movies-# WHERE movies-# balance != 0 movies-# ; id customer_name ----+---------------------- 2 Rubin, William 4 Wonderland, Alice N. (2 rows) This example also shows a more common alternative to the NOT operator. The != operator means " is not equal to ." The != operator is not an exact replacement for NOT ”it can only be used to check for inequality, whereas NOT is used to reverse the sense of any logical expression. NULL ValuesSometimes when you add data to a table, you find that you don't know what value you should include for a column. For example, you may encounter a customer who does not want to provide you with his or her birthday. What value should be recorded in the birth_date column for that customer? You don't really want to make up an answer ”you want a date value that means "unknown." This is what the NULL value is for. NULL usually means that you don't know what value should be entered into a column, but it can also mean that a column does not apply. A NULL value in the birth_date column certainly means that we don't know a customer's birth_date , not that birth_date does not apply [8] . On the other hand, you might want to include a rating column in the tapes table. A NULL value in the rating column might imply that the movie was produced before ratings were introduced and therefore the rating column does not apply.
Some columns should not allow NULL values. In most cases, it would not make sense to add a customer to your customers table unless you know the customer's name. Therefore, the customer_name column should be mandatory (in other words, customer_name should not allow NULL values). Let's drop and re-create the customers table so that you can tell PostgreSQL which columns should allow NULL values: movies=# DROP TABLE customers; DROP movies=# CREATE TABLE customers ( movies-# customer_id INTEGER UNIQUE NOT NULL, movies-# name VARCHAR(50) NOT NULL, movies-# phone CHAR(8), movies-# birth_date DATE, movies-# balance DECIMAL(7,2) movies-#); CREATE The NOT NULL modifier tells PostgreSQL that the customer_id and name columns are mandatory. If you don't specify NOT NULL , PostgreSQL assumes that a column is optional. You can include the keyword NULL to make your choices more obvious: movies=# DROP TABLE customers; DROP movies=# CREATE TABLE customers ( movies-# customer_id INTEGER UNIQUE NOT NULL, movies-# name VARCHAR(50) NOT NULL, movies-# phone CHAR(8) NULL, movies-# birth_date DATE NULL, movies-# balance DECIMAL(7,2) NULL movies-#); CREATE Notice that a column of any data type can support NULL values. The NULL value has a unique property that is often the source of much confusion. NULL is not equal to any value , not even itself. NULL is not less than any value, and NULL is not greater than any value. Let's add a customer with a NULL balance: movies=# INSERT INTO customers movies-# VALUES movies-# ( movies(# 5, 'Funkmaster, Freddy', '555-FUNK', NULL, NULL movies(# ) movies-# ; Now we have five customers: movies=# SELECT * FROM customers; id customer_name phone birth_date balance ----+----------------------+----------+------------+--------- 1 Jones, Henry 555-1212 1970-10-10 0.00 2 Rubin, William 555-2211 1972-07-10 15.00 3 Panky, Henry 555-1221 1968-01-21 0.00 4 Wonderland, Alice N. 555-1122 1969-03-05 3.00 5 Funkmaster, Freddy 555-FUNK (5 rows) One of these customers has a NULL balance. Let's try a few queries: movies=# SELECT * FROM customers WHERE balance > NULL; id customer_name phone birth_date balance ----+---------------+-------+------------+--------- (0 rows) This query did not return any rows. You might think that it should have customer number 2 (Rubin, William); after all, 15.00 is surely greater than 0. But remember, NULL is not equal to, greater than, or less than any other value. NULL is not the same as zero. Rather than using relational operators ( ' = ', ' != ', ' < ', or ' > '), you should use either the IS or IS NOT operator. movies=# SELECT * FROM customers WHERE balance IS NULL; id customer_name phone birth_date balance ----+--------------------+----------+------------+--------- 6 Funkmaster, Freddy 555-FUNK (1 row) movies=# SELECT * FROM customers WHERE balance IS NOT NULL; id customer_name phone birth_date balance ----+----------------------+----------+------------+--------- 1 Jones, Henry 555-1212 1970-10-10 0.00 2 Rubin, William 555-2211 1972-07-10 15.00 3 Panky, Henry 555-1221 1968-01-21 0.00 4 Wonderland, Alice N. 555-1122 1969-03-05 3.00 (4 rows) The NULL value introduces another complication. If NULL is not greater than, equal to, or less than any other value, what would ' NULL + 4' mean? Is NULL + 4 greater than NULL ? It can't be because that would imply that NULL is less than NULL + 4 and, by definition, NULL can't be less than another value. What does all this mean? It means that you can't do math with a NULL value. movies=# SELECT id, customer_name, balance, balance+4 FROM customers; id customer_name balance ?column? ----+----------------------+---------+---------- 1 Jones, Henry 0.00 4.00 2 Rubin, William 15.00 19.00 3 Panky, Henry 0.00 4.00 4 Wonderland, Alice N. 3.00 7.00 5 Funkmaster, Freddy (5 rows) This query shows what happens when you try to perform a mathematical operation using NULL . When you try to add ' 4 ' to NULL , you end up with NULL . The NULL value complicates logic operators as well. Most programmers are familiar with two-valued logic operators (that is, logic operators that are defined for the values TRUE and FALSE ). When you add in NULL values, the logic operators become a bit more complex. Tables 1.4, 1.5, and 1.6 show the truth tables for each logical operator. Table 1.4. Truth Table for Three-Valued AND Operator
Table 1.5. Truth Table for Three-Valued OR Operator
Table 1.6. Truth Table for Three-Valued NOT Operator
I don't mean to scare you away from the NULL value ”it's very useful and often necessary ”but you do have to understand the complications that it introduces. The ORDER BY ClauseSo far, all the queries that you have seen return rows in an arbitrary order. You can add an ORDER BY clause to a SELECT command if you need to impose a predictable ordering. The general form of the ORDER BY clause is [9]
ORDER BY expression [ ASC DESC ] [, ...] The ASC and DESC terms mean ascending and descending, respectively. If you don't specify ASC or DESC , PostgreSQL assumes that you want to see results in ascending order. The expression following ORDER BY is called a sort key . Let's look at a simple example: movies=# SELECT * FROM customers ORDER BY balance; id customer_name phone birth_date balance ----+----------------------+----------+------------+--------- 1 Jones, Henry 555-1212 1970-10-10 0.00 3 Panky, Henry 555-1221 1968-01-21 0.00 4 Wonderland, Alice N. 555-1122 1969-03-05 3.00 2 Rubin, William 555-2211 1972-07-10 15.00 5 Funkmaster, Freddy 555-FUNK (5 rows) You can see that this SELECT command returns the result set in ascending order of the balance column. Here is the same query, but in descending order: movies=# SELECT * FROM customers ORDER BY balance DESC; id customer_name phone birth_date balance ----+----------------------+----------+------------+--------- 2 Rubin, William 555-2211 1972-07-10 15.00 4 Wonderland, Alice N. 555-1122 1969-03-05 3.00 1 Jones, Henry 555-1212 1970-10-10 0.00 3 Panky, Henry 555-1221 1968-01-21 0.00 5 Funkmaster, Freddy 555-FUNK (5 rows) This time, the largest balance is first, followed by successively smaller values. You may have noticed something odd about how the ORDER BY clause handles the customer named Freddy Funkmaster. Recall from the previous section that NULL cannot be compared to other values. By its very nature, the ORDER BY clause must compare values. PostgreSQL resolves this issue with a simple rule: NULL values always sort last. For ascending sorts, NULL is considered greater than all other values. For descending sorts, NULL is considered less than all other values. Note that starting with PostgreSQL version 7.2, NULL is always considered larger than all other values when evaluating an ORDER BY clause. You can include multiple sort keys in the ORDER BY clause. The following query sorts customers in ascending balance order, and then in descending birth_date order: movies=# SELECT * FROM customers ORDER BY balance, birth_date DESC; id customer_name phone birth_date balance ----+----------------------+----------+------------+--------- 1 Jones, Henry 555-1212 1970-10-10 0.00 3 Panky, Henry 555-1221 1968-01-21 0.00 4 Wonderland, Alice N. 555-1122 1969-03-05 3.00 2 Rubin, William 555-2211 1972-07-10 15.00 5 Funkmaster, Freddy 555-FUNK (5 rows) When an ORDER BY clause contains multiple sort keys, you are telling PostgreSQL how to break ties. You can see that customers 1 and 3 have the same value (0.00) in the balance column ”you have asked PostgreSQL to order rows using the balance column. What happens when PostgreSQL finds two rows with the same balance? When two sort key values are equal, PostgreSQL moves to the next sort key to break the tie. If two sort key values are not equal, sort keys with a lower precedence are ignored. So, when PostgreSQL finds that customers 1 and 3 have the same balance , it moves to the birth_date column to break the tie. If you don't have a sort key with a lower precedence, you won't be able to predict the ordering of rows with duplicate sort key values. You can include as many sort keys as you like. LIMIT and OFFSETOccasionally, you will find that you want to answer a question such as "Who are my top 10 salespeople?" In most relational databases, this is a difficult question to ask. PostgreSQL offers two extensions that make it easy to answer "Top n " or "Bottom n "-type questions. The first extension is the LIMIT clause. The following query shows the two customers who owe you the most money: movies=# SELECT * FROM customers ORDER BY balance DESC LIMIT 2; id customer_name phone birth_date balance ----+----------------------+----------+------------+--------- 2 Rubin, William 555-2211 1972-07-10 15.00 4 Wonderland, Alice N. 555-1122 1969-03-05 3.00 (2 rows) You can see here that I used an ORDER BY clause so that the rows are sorted such that the highest balances appear first ”in most cases, you won't use a LIMIT clause without also using an ORDER BY clause. Let's change this query a little ”this time we want the top five customers who have a balance over $10: movies=# SELECT * FROM customers movies-# WHERE movies-# balance >= 10 movies-# ORDER BY balance DESC movies-# LIMIT 5; id customer_name phone birth_date balance ----+----------------+----------+------------+--------- 2 Rubin, William 555-2211 1972-07-10 15.00 (1 row) This example shows that the LIMIT clause won't always return the number of rows that were specified. Instead, LIMIT returns no more than the number of rows that you request. In this sample database, you have only one customer who owes you more than $10. The second extension is the OFFSET n clause. The OFFSET n clause tells PostgreSQL to skip the first n rows of the result set. For example: movies=# SELECT * FROM customers ORDER BY balance DESC OFFSET 1; id customer_name phone birth_date balance ----+----------------------+----------+------------+--------- 4 Wonderland, Alice N. 555-1122 1969-03-05 3.00 1 Jones, Henry 555-1212 1970-10-10 0.00 3 Panky, Henry 555-1221 1968-01-21 0.00 5 Funkmaster, Freddy 555-FUNK (4 rows) In this case, we are viewing all the customers except the customer with the greatest balance . It's common to use LIMIT and OFFSET together: movies=# SELECT * FROM customers movies-# ORDER BY balance DESC LIMIT 2 OFFSET 1; id customer_name phone birth_date balance ----+----------------------+----------+------------+--------- 4 Wonderland, Alice N. 555-1122 1969-03-05 3.00 1 Jones, Henry 555-1212 1970-10-10 0.00 (2 rows) Formatting Column ResultsSo far, you have seen how to tell PostgreSQL which rows you want to view, which columns you want to view, and the order in which the rows should be returned. Let's take a short side-trip here and learn how to change the appearance of the values that you select. Take a look at the following query: movies=# SELECT id, customer_name, balance, balance+4 FROM customers; id customer_name balance ?column? ----+----------------------+---------+---------- 1 Jones, Henry 0.00 4.00 2 Rubin, William 15.00 19.00 3 Panky, Henry 0.00 4.00 4 Wonderland, Alice N. 3.00 7.00 5 Funkmaster, Freddy (5 rows) PostgreSQL inserts two lines of text between your query and the result set. These two lines are (obviously) column headings. You can see that the header for each of the first three columns contains the name of the column. What about the last column? When you SELECT an expression, PostgreSQL uses " ?column? " for the field header [10] .
You can change field headers using the AS clause: movies=# SELECT id, customer_name, movies-# balance AS "Old balance", movies-# balance + 4 AS "New balance" movies-# FROM customers; id customer_name Old balance New balance ----+----------------------+-------------+------------- 1 Jones, Henry 0.00 4.00 2 Rubin, William 15.00 19.00 3 Panky, Henry 0.00 4.00 4 Wonderland, Alice N. 3.00 7.00 5 Funkmaster, Freddy (5 rows) Notice that you can provide a field header for table columns as well as for expressions. If you rename a field and the query includes an ORDER BY clause that refers to the field, the ORDER BY should use the new name, not the original one: movies=# SELECT id, customer_name, movies-# balance AS "Old balance", movies-# balance + 4 AS "New balance" movies-# FROM customers movies-# ORDER BY "Old balance"; id customer_name Old balance New balance ----+----------------------+-------------+------------- 1 Jones, Henry 0.00 4.00 3 Panky, Henry 0.00 4.00 4 Wonderland, Alice N. 3.00 7.00 2 Rubin, William 15.00 19.00 5 Funkmaster, Freddy (5 rows) This section explained how to change the column headers for a SELECT command. You can also change the appearance of the data values. In the next section, I'll show you a few examples using date values for illustration. Working with Date ValuesPostgreSQL supports six basic date, time, and date/time data types, as shown in Table 1.7. I'll use the term temporal to cover date, time, and date/time data types. Table 1.7. PostgreSQL Temporal Data Types
I'll cover the details of the date/time data types in Chapter 2. You have already seen two of these temporal data types. The customers table contains a DATE column ( birth_date ): movies=# \d customers Table "customers" Attribute Type Modifier ---------------+-----------------------+---------- id integer not null customer_name character varying(50) not null phone character(8) birth_date date balance numeric(7,2) Index: customers_id_key movies=# SELECT customer_name, birth_date FROM customers; customer_name birth_date ----------------------+------------ Jones, Henry 1970-10-10 Rubin, William 1972-07-10 Panky, Henry 1968-01-21 Wonderland, Alice N. 1969-03-05 Funkmaster, Freddy (5 rows) You've also seen the INTERVAL data type ”the AGE() function returns an INTERVAL : movies=# SELECT customer_name, AGE( birth_date ) FROM customers; customer_name age ----------------------+------------------------------ Jones, Henry 31 years 4 mons 8 days 01:00 Rubin, William 29 years 7 mons 8 days 01:00 Panky, Henry 34 years 28 days Wonderland, Alice N. 32 years 11 mons 13 days Funkmaster, Freddy (5 rows) Date/time values are usually pretty easy to work with, but there is a complication that you need to be aware of. Let's say that I need to add a new customer: movies=# INSERT INTO customers movies-# VALUES movies-# ( movies-# 7, 'Gull, Jonathon LC', '555-1111', '02/05/1984', NULL movies-# ); This customer has a birth_date of '02/05/1984' ”does that mean "February 5 th 1984", or "May 2 nd 1984"? How does PostgreSQL know which date I meant ? The problem is that a date such as ' 02/05/1984 ' is ambiguous ”you can't know which date this string represents without knowing something about the context in which it was entered. '02/05/1984' is ambiguous. 'May 02 1984' is unambiguous. PostgreSQL enables you to enter and display dates in a number of formats ”some date formats are ambiguous and some are unambiguous. The DATESTYLE runtime variable tells PostgreSQL how to format dates when displaying data and how to interpret ambiguous dates that you enter. The DATESTYLE variable can be a little confusing. DATESTYLE is composed of two parts . The first part, called the convention, tells PostgreSQL how to interpret ambiguous dates. The second part, called the display format, determines how PostgreSQL displays date values. The convention controls date input and the display format controls date output. Let's talk about the display format first. PostgreSQL supports four different display formats. Three of the display formats are unambiguous and one is ambiguous. The default display format is named ISO . In ISO format, dates always appear in the form ' YYYY-MM-DD '. The next display format is GERMAN . In GERMAN format, dates always appear in the form ' DD.MM.YYYY '. The ISO and GERMAN formats are unambiguous because the format never changes. The POSTGRES format is also unambiguous, but the display format can vary. PostgreSQL needs a second piece of information (the convention) to decide whether the month should appear before the day ( US convention) or the day should appear before the month ( European convention). In POSTGRES format, date values display the day-of-the-week and month name in abbreviated text form; for example ' Wed May 02 1984 ' ( US ) or ' Wed 02 May 1984 ' ( European ). The final display format is SQL. SQL format is ambiguous. In SQL format, the date 'May 02 1984' is displayed as ' 05/02/1984 ' ( US ), or as ' 02/05/1984 '( European ). Table 1.8. DATESTYLE Display Formats
As I mentioned earlier, the ISO and GERMAN display formats are unambiguous. In ISO format, the month always precedes the day. In GERMAN format, the day always precedes the month. If you choose POSTGRES or SQL format, you must also specify the order in which you want the month and day components to appear. You can specify the desired display format and month/day ordering (that is, the convention) in the DATESTYLE runtime variable: movies=# SET DATESTYLE TO 'US,ISO'; -- 1984-05-02 movies=# SET DATESTYLE TO 'US,GERMAN'; -- 02.05.1984 movies=# SET DATESTYLE TO 'US,POSTGRES'; -- Wed May 02 1984 movies=# SET DATESTYLE TO 'US,SQL'; -- 05/02/1984 movies=# SET DATESTYLE TO 'EUROPEAN,ISO'; -- 1984-05-02 movies=# SET DATESTYLE TO 'EUROPEAN,GERMAN'; -- 02.05.1984 movies=# SET DATESTYLE TO 'EUROPEAN,POSTGRES'; -- Wed 02 May 1984 movies=# SET DATESTYLE TO 'EUROPEAN,SQL'; -- 02/05/1984 The convention part of the DATESTYLE variable determines how PostgreSQL will make sense of the date values that you enter. The convention also affects the ordering of the month and day components when displaying a POSTGRES or SQL date. Note that you are not restricted to entering date values in the format specified by DATESTYLE . For example, if you have chosen to display dates in 'US,SQL' format, you can still enter date values in any of the other formats. Recall that the ISO and GERMAN date formats are unambiguous ”the ordering of the month and day components is predefined. A date entered in POSTGRES format is unambiguous as well ”you enter the name of the month so it cannot be confused with the day. If you choose to enter a date in SQL format, PostgreSQL will look to the first component of DATESTYLE (that is, the convention) to determine whether you want the value interpreted as a US or a European date. Let's look at a few examples. movies=# SET DATESTYLE TO 'US,ISO'; movies=# SELECT CAST( '02/05/1984' AS DATE ); 1984-02-05 movies=# SET DATESTYLE TO 'EUROPEAN,ISO'; movies=# SELECT CAST( '02/05/1984' AS DATE ); 1984-05-02 In this example, I've asked PostgreSQL to display dates in ISO format, but I've entered a date in an ambiguous format. In the first case, you can see that PostgreSQL interpreted the ambiguous date using US conventions (the month precedes the day). In the second case, PostgreSQL uses European conventions to interpret the date. Now let's see what happens when I enter an unambiguous date: movies=# SET DATESTYLE TO 'US,ISO'; SET VARIABLE movies=# SELECT CAST( '1984-05-02' AS DATE ); 1984-05-02 movies=# SET DATESTYLE TO 'EUROPEAN,ISO'; SET VARIABLE movies=# SELECT CAST( '1984-05-02' AS DATE ); 1984-05-02 This time, there can be no confusion ”an ISO -formatted date is always entered in ' YYYY-MM-DD ' format. PostgreSQL ignores the convention. So, you can see that I can enter date values in many formats. If I choose to enter a date in an ambiguous format, PostgreSQL uses the convention part of the current DATESTYLE to interpret the date. I can also use DATESTYLE to control the display format. Matching PatternsIn the previous two sections, you took a short detour to learn a little about how to format results. Now let's get back to the task of producing the desired results. The WHERE clause is used to restrict the number of rows returned by a SELECT command [11] . Sometimes, you don't know the exact value that you are searching for. For example, you may have a customer ask you for a film, but he doesn't remember the exact name, although he knows that the film has the word "Citizen" in the title. PostgreSQL provides two features that make it possible to search for partial alphanumeric values.
LIKE and NOT LIKEThe LIKE operator provides simple pattern-matching capabilities. LIKE uses two special characters that indicate the unknown part of a pattern. The underscore ( _ ) character matches any single character. The percent sign ( % ) matches any sequence of zero or more characters. Table 1.9 shows a few examples. Table 1.9. Pattern Matching with the LIKE Operator
Now let's see how to use the LIKE operator in a SELECT command: movies=# SELECT * FROM tapes WHERE title LIKE '%Citizen%'; tape_id title duration ----------+----------------------+---------- OW-41221 Citizen Kane KJ-03335 American Citizen, An (2 rows) The LIKE operator is case-sensitive: movies=# SELECT * FROM tapes WHERE title LIKE '%citizen%'; tape_id title duration ---------+-------+---------- (0 rows) If you want to perform case-insensitive pattern matching, use the ILIKE operator: movies=# SELECT * FROM tapes WHERE title ILIKE '%citizen%'; tape_id title duration ----------+----------------------+---------- OW-41221 Citizen Kane KJ-03335 American Citizen, An (2 rows) You can, of course, combine LIKE and ILIKE with the NOT operator to return rows that do not match a pattern: movies=# SELECT * FROM tapes WHERE title NOT ILIKE '%citizen%'; tape_id title duration ----------+---------------+-------------- AB-12345 The Godfather AB-67472 The Godfather MC-68873 Casablanca AH-54706 Rear Window OW-42200 Sly 01:36 OW-42201 Stone 4 days 01:36 (6 rows) Pattern Matching with Regular ExpressionsThe LIKE and ILIKE operators are easy to use, but they aren't very powerful. Fortunately, PostgreSQL lets you search for data using regular expressions . A regular expression is a string that specifies a pattern. The language that you use to create regular expressions is far more powerful than the LIKE and ILIKE operators. You have probably used regular expressions before; programs such as grep , awk , and the Unix (and DOS) shells use regular expressions. The LIKE and ILIKE operators define two pattern-matching characters; the regular expression operator defines far more. First, the character " . " within a regular expression operates in the same way as the " _ " character in a LIKE pattern: it matches any single character. The characters " .* " in a regular expression operate in the same way as the " % " character in a LIKE pattern: they match zero or more occurrences of any single character. Notice that in a regular expression, you use two characters to match a sequence of characters, whereas you use a single character in a LIKE pattern. The regular expression " .* " is actually two regular expressions combined into one complex expression. As I mentioned earlier, the " . " character matches any single character. The " * " character matches zero or more occurrences of the pattern that precedes it. So, " .* " means to match any single character, zero or more times. There are three other repetition operators: The " + " character matches one or more occurrences of the preceding pattern, and the " ? " character matches zero or one occurrence of the preceding pattern. If you need to get really fancy (I never have), you can use the form " {x[,y]} " to match at least x and no more than y occurrences of the preceding pattern. You can also search for things other than " . ". For example, the character " ^ " matches the beginning of a string and " $ " matches the end. The regular expression syntax even includes support for character classes. The pattern " [:upper:]*[:digit:] " will match any string that includes zero or more uppercase characters followed by a single digit. The " " character gives you a way to search for a string that matches either of two patterns. For example, the regular expression " (^God).*Donuts.* " would match a string that either starts with the string " God " or includes the word " Donuts ". Regular expressions are extremely powerful, but they can get awfully complex. If you need more information, Chapter 4 of the PostgreSQL User's Manual provides an exhaustive reference to the complete regular expression syntax. Table 1.10 shows how to construct regular expressions that match the same strings matched by the LIKE patterns in shown in Table 1.9. Table 1.10. Pattern Matching with Regular Expressions
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
EAN: 2147483647
Pages: 220