7.3 Building Simple SQL Statements

Now that you have seen how tables can be added to a database, it is time to perform some simple SQL statements.

One of the most important commands when working with SQL is the INSERT command. INSERT is one way to add data to a table. Since the first days of SQL, the INSERT command has been part of the ANSI SQL specification, and it is a core component of every relational and object relational database built in SQL. The syntax overview of the INSERT command is not too complicated:

 phpbook=# \h INSERT Command:     INSERT Description: create new rows in a table Syntax: INSERT INTO table [ ( column [, ...] ) ]     { DEFAULT VALUES | VALUES ( expression [, ...] ) | SELECT query } 

The first thing you have to define is into which table the data passed to the database by the command has to be inserted. In the next step it is possible to define a list of columns into which you want to insert data. If no list is provided, PostgreSQL assumes that data has to be added to all columns of a record.

Take a look at an example of a simple INSERT command:

 phpbook=# INSERT INTO t_person (name, zip_code, city) VALUES ('Hans', 1150, 'Vienna'); INSERT 21709 1 

If no error occurred, the record has successfully been added to the table. Three columns in the record have been defined in the SQL statement. In this example no value has been assigned to the column called id. The list defining the list of columns comes directly after the name of the data the data has been added to. After the keyword VALUES, the table is passed to the database. In the next step, only two columns should be inserted into the table. You can try to remove one column from the list:

 phpbook=# INSERT INTO t_person (name, zip_code) VALUES ('Epi', 1060, 'Vienna'); ERROR:  INSERT has more expressions than target columns 

In this case PostgreSQL will fail because only two columns should be added to the database, but three values are passed to the database by the SQL statement. To make sure that PostgreSQL can accept the command, one value has to be removed from the list:

 phpbook=# INSERT INTO t_person (name, zip_code) VALUES ('Epi', 1060); INSERT 21710 1 

In the next step you will see how the data you have just added to the database can easily be retrieved. For selecting data stored in one or more than just one table, you can use the SELECT command. The syntax overview of the SELECT command is impressive, and SELECT is one of the most powerful commands provided by PostgreSQL. To retrieve the syntax overview of SELECT, you can use \h:

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

In this section you will not learn about all the details of the SELECT command, but you will see how to write simple SELECT statements and how to retrieve data from a table efficiently. In Chapter 8 SELECT and many other commands will be covered in a more detailed way.

Now that you have seen the syntax overview of the SELECT command, take a look at a simple SQL statement you can use to retrieve all records from a table.

 phpbook=# SELECT * FROM t_person;  id | name | zip_code |  city ----+------+----------+--------   1 | Hans |     1150 | Vienna   2 | Epi  |     1060 | (2 rows) 

The asterisk tells PostgreSQL to retrieve all columns from t_person. The result of the query is a list of two records. These are the two records you have just inserted into the table, but why is there already a value in the first column? As you have seen, the id of a record has never been added to the table. However, the id is obviously in the table. If you take a look at the SQL commands for creating the tables, you will find out that the first column has been defined as serial. This means that PostgreSQL will create a sequence used for consecutively numbering every record inserted into the table automatically. This makes it easy to generate an index consisting of a list of unique values. The column containing the serial will automatically be the primary key of the table.

In the preceding example you saw that you can use an asterisk to retrieve all columns from a table. In some cases it is not necessary to retrieve all columns, so it is also possible to list only those columns needed in the result:

 phpbook=# SELECT id, name, zip_code, city FROM t_person;  id | name | zip_code |  city ----+------+----------+--------   1 | Hans |     1150 | Vienna   2 | Epi  |     1060 | (2 rows) 

The order of the columns is flexible as well. If city has to be listed before id, it has to be changed in the list of columns:

 phpbook=# SELECT city, id, name, zip_code FROM t_person;   city  | id | name | zip_code --------+----+------+----------  Vienna |  1 | Hans |     1150         |  2 | Epi  |     1060 (2 rows) 

Another way to retrieve data is to tell PostgreSQL explicitly where to take the columns from. In the next example you tell PostgreSQL that all columns in table t_person must be listed. This syntax is important when working with more than just one table:

 phpbook=# SELECT t_person.* FROM t_person;  id | name | zip_code |  city ----+------+----------+--------   1 | Hans |     1150 | Vienna   2 | Epi  |     1060 | (2 rows) 

One thing you have to take care of when retrieving data is that you must not list columns that are not in the table because PostgreSQL will not accept such queries.

 phpbook=# SELECT t_person.Hans FROM t_person; ERROR:  No such attribute or function 'hans' 

7.3.1 Using WHERE Clauses

In most cases, you don't want to retrieve all values from a table but only data satisfying certain conditions. This is the typical task for a database. Just like any other database, PostgreSQL has been optimized for this kind of operation. To restrict the data returned, a WHERE clause has to be added to the query. In the next example you can see how you can find all records where the id is exactly 1:

 phpbook=# SELECT id, name, zip_code FROM t_person WHERE id=1;  id | name | zip_code ----+------+----------   1 | Hans |     1150 (1 row) 

One record is returned because there is no other record having the same id. The desired value has to be passed to the server without single quotes. With integer values this will work, but how can you pass strings to the database?

 phpbook=# SELECT * FROM t_person WHERE name='Hans';  id | name | zip_code |  city ----+------+----------+--------   1 | Hans |     1150 | Vienna (1 row) 

In this example you want to find all records where the name is Hans. As you can see, the values have to be defined using single quotes.

7.3.1.1 Escaping Special Characters

Let's insert an additional record into the table:

 phpbook=# INSERT INTO t_person (name) VALUES ('o'Connor'); phpbook'# 

You want to insert the name o'Connor into the table but somehow it does not seem to work. PostgreSQL is still prompting for input because the SQL statement is not complete. The problem is that the database cannot distinguish single quotes in the string you want to insert, and single quotes are needed for telling SQL where a string starts and ends. To get around the problem, single quotes must be escaped. You can do this in two ways:

 phpbook=# INSERT INTO t_person (name) VALUES ('o''Connor'); INSERT 21712 1 

The first thing you can do is to use two single quotes instead of one. This way PostgreSQL will know that a single quote has to be sent to the database. The second possibility is to use a backslash instead:

 phpbook=# INSERT INTO t_person (name) VALUES ('o\'Connor'); INSERT 21713 1 

Let's see if both values have successfully been inserted into the table:

 phpbook=# SELECT * FROM t_person;  id |   name   | zip_code |  city ----+----------+----------+--------   1 | Hans     |     1150 | Vienna   2 | Epi      |     1060 |   3 | o'Connor |          |   4 | o'Connor |          | (4 rows) 

Both records are in the table and are the same. In the next step you can try to retrieve all values where the name is o'Connor:

 phpbook=# SELECT * FROM t_person WHERE name='o''Connor';  id |   name   | zip_code | city ----+----------+----------+------   3 | o'Connor |          |   4 | o'Connor |          | (2 rows) 

The single quotes have to be escaped as you saw when writing the INSERT statements.

7.3.1.2 Handling NULL Values

All columns that have not been defined in the INSERT operation are set to NULL. NULL does not mean zero and NULL does not mean blank. NULL is a special value and means that the column does not contain a value.

To retrieve all records where no value has been inserted into city, you can write a query that checks for NULL values:

 phpbook=# SELECT * FROM t_person WHERE city IS NULL;  id |   name   | zip_code | city ----+----------+----------+------   2 | Epi      |     1060 |   3 | o'Connor |          |   4 | o'Connor |          | (3 rows) 

As you can see, IS NULL can be used to retrieve NULL values from the column. Let's try something else:

 phpbook=# SELECT * FROM t_person WHERE city='';  id | name | zip_code | city ----+------+----------+------ (0 rows) 

In this scenario you are looking for an empty string, but because NULL is not an empty string no records will be returned. This is an important point.

In PostgreSQL 7.2, there is another thing you should take care of:

 phpbook=# SELECT * FROM t_person WHERE city=NULL;  id | name | zip_code | city ----+------+----------+------ (0 rows) 

Don't check for NULL the way it is shown in the listing it won't work.

7.3.1.3 Multiple Conditions

In most cases it is not enough to have just one condition. The more complex your application is, the more likely it will be that you have to write more complex WHERE clauses.

Let's start with a simple query:

 phpbook=# SELECT * FROM t_person WHERE zip_code=1150 AND city='Vienna';  id | name | zip_code |  city ----+------+----------+--------   1 | Hans |     1150 | Vienna (1 row) 

The query can be used to retrieve all records from the table where the postal code is 1150 and the city is Vienna. Both conditions have to be fulfilled if a record should be displayed in the result.

Sometimes it is necessary to see if a certain value in a column is higher than a predefined value. In this case, use the > operator:

 phpbook=# SELECT * FROM t_person WHERE zip_code=1150 AND id>1;  id | name | zip_code | city ----+------+----------+------ (0 rows) 

No record operators> operators> operators> operators> operators> operators> is retrieved because the only record satisfying the first condition has an id that is exactly 1. If you want to find all records where the id is higher or equal than 1, use the >= operator:

 phpbook=# SELECT * FROM t_person WHERE zip_code=1150 AND id>=1;  id | name | zip_code |  city ----+------+----------+--------   1 | Hans |     1150 | Vienna (1 row) 

The counterpart= operators>= operators>= operators>= operators>= operators>= operators> of the = operator is the <> operator:

 phpbook=# SELECT * FROM t_person WHERE zip_code=1150 AND id <> 1;  id | name | zip_code | city ----+------+----------+------ (0 rows) 

As you operators> operators> operators> operators> operators> operators> can see, no record matches the conditions in the WHERE clause. Now that you've used the AND operator, you will learn to use the OR operator:

 phpbook=# SELECT * FROM t_person WHERE name='Hans' OR name='Epi';  id | name | zip_code |  city ----+------+----------+--------   1 | Hans |     1150 | Vienna   2 | Epi  |     1060 | (2 rows) 

In this scenario, name can be Hans or Epi. If one of the two conditions matches, the record will be returned.

In the next example you see a slightly more complex query:

 phpbook=# SELECT * FROM t_person WHERE (name='Hans' OR name='Epi') AND city= 'Vienna';  id | name | zip_code |  city ----+------+----------+--------   1 | Hans |     1150 | Vienna (1 row) 

This query has selected every record where the name is Hans or Epi. city has to be Vienna but two names are considered to be valid.

The next query will lead to the same result:

 phpbook=# SELECT * FROM t_person WHERE name='Hans' OR (name='Epi' AND city= 'Vienna');  id | name | zip_code |  city ----+------+----------+--------   1 | Hans |     1150 | Vienna (1 row) 

PostgreSQL does not take the parentheses into consideration when executing the query.

7.3.2 Formatting the Output

In some cases it is necessary to format the output of a query. Today this is not as important as it used to be, but it is still something worth mentioning. Back in the early days of SQL, people used to build entire reports with the help of command-line tools, so operations like concatenating the content of columns were essential. Nowadays most of the formatting is done on the application level.

Before you learn how to concatenate columns, you will see how Hello World can be implemented using SQL:

 phpbook=# SELECT 'Hello World';   ?column? -------------  Hello World (1 row) 

Simply select the string Hello World and it will be displayed by the database. In this case you don't need a table to select the data from because it is not stored in the database. In some databases, such as Oracle, having a FROM clause is necessary, but in PostgreSQL this is redundant.

Now that you have seen how to select a string, you will see how to connect various columns with each other:

 phpbook=# SELECT name || ' lives in ' || city FROM t_person WHERE id=1;        ?column? ----------------------  Hans lives in Vienna (1 row) 

In this example the column labeled name is selected from the database. With the help of the || operator, the column is combined with the string lives in and the column called city. The column generated by concatenating the various values is called ?column?. This name might not be what you need. You can change the caption of the column:

 phpbook=# SELECT name || ' lives in ' || city AS "the caption" FROM t_person WHERE id=1;      the caption ----------------------  Hans lives in Vienna (1 row) 

As you can see, it is an easy task to combine various columns to one string, but let's see how you can use WHERE clauses:

 phpbook=# SELECT name || ' lives in ' || city AS caption FROM t_person WHERE name || ' lives in ' || city = 'Hans lives in Vienna';        caption ----------------------  Hans lives in Vienna (1 row) 

The entire command for concatenating the various fields has to be added to the WHERE clause. As the next listing shows you cannot use the name specified by using AS:

 phpbook=# SELECT name || ' lives in ' || city AS caption FROM t_person WHERE caption = 'Hans lives in Vienna'; ERROR:  Attribute 'caption' not found ERROR:  Attribute 'caption' not found 

Caption is not an attribute of a table in the FROM clause, so an error occurs.

7.3.3 LIMIT and OFFSET

LIMIT and OFFSET are two commands for restricting the rows returned by the database. With the help of LIMIT it is possible to tell PostgreSQL that only some records at the beginning of the result should be displayed. The number of records you want to be displayed has to be added to the LIMIT command. In the next listing you can see how two values can be displayed:

 phpbook=# SELECT * FROM t_person LIMIT 2;  id | name | zip_code |  city ----+------+----------+--------   1 | Hans |     1150 | Vienna   2 | Epi  |     1060 | (2 rows) 

The first two records of the result have been displayed on the screen. If you want to omit a fixed number of records at the beginning of the result, you can use the query in the next listing:

 phpbook=# SELECT * FROM t_person LIMIT 2,1;  id |   name   | zip_code | city ----+----------+----------+------   2 | Epi      |     1060 |   3 | o'Connor |          | (2 rows) 

This syntax was supported by PostgreSQL databases prior to 7.3. If you are currently building new applications, don't use this syntax because it will be removed in version 7.3. To get around the problem, use OFFSET:

 phpbook=# SELECT * FROM t_person LIMIT 2 OFFSET 1;  id |   name   | zip_code | city ----+----------+----------+------   2 | Epi      |     1060 |   3 | o'Connor |          | (2 rows) 

The previous two queries are equal and will lead to the same result.

7.3.4 Temporary Tables

Temporary tables are a very comfortable feature of PostgreSQL and they can be used to store information in tables that are only needed in the current session. Temporary tables can only be seen by the person who has created them and as soon as the connection terminates, temporary tables are deleted automatically.

Let's connect to the database called phpbook:

 [postgres@athlon postgres]$ psql phpbook 

If no error occurred, psql has been started successfully. Now you can create a temporary table:

 phpbook=# CREATE TEMPORARY TABLE tmp_data(id int4, data text); CREATE 

Let's see if the definition of the table can be retrieved by using \d:

 phpbook=# \d tmp_data Did not find any relation named "tmp_data". 

The table cannot be found in the list of tables because it is only a temporary table.

The next piece of code shows how to add data to the table:

 phpbook=# INSERT INTO tmp_data VALUES (1, 'one'); INSERT 24755 1 

As you can see, you can add data to a temporary table just as you would if it were not a temporary table. Selecting data also works the same way:

 phpbook=# SELECT * FROM tmp_data;  id | data ----+------   1 | one (1 row) 

If you leave psql and connect to the database again, you will find out that the table is not in the database any more:

 phpbook=# SELECT * FROM tmp_data; ERROR:  Relation 'tmp_data' does not exist ERROR:  Relation 'tmp_data' does not exist 

Another important thing is that a user can only see his personal set of temporary tables. Therefore it is possible that many users can create temporary tables having the same name. Table 7.1 gives some examples.

Table 7.1. Temporary Tables and Scope

User 1	User 2	Comment
psql phpbook		User 1 connects to the database
	psql phpbook	User 2 connects to the database
CREATE TEMPORARY TABLE tmp_data (id int4, data text);		User 1 creates a table
	CREATE TEMPORARY TABLE tmp_data (id int4);	User 2 creates a table
INSERT INTO tmp_data VALUES(1, 'a');		User 1 inserts a record
	INSERT INTO tmp_data VALUES(2);	User 2 inserts a record
SELECT * FROM tmp_data;		User 1 retrieves one record from the database
\q		User 1 disconnects
	SELECT * FROM tmp_data;	User 2 retrieves one record from the database
	\q	User 2 disconnects

As you can see, user 1 and user 2 have their own temporary tables with the same name but PostgreSQL is not confused by this.

7.3.4.1 Temporary Tables and Serials

When working with temporary tables, there is one topic worth looking at. When creating a table containing a serial, PostgreSQL will implicitly create a sequence. Because the serial is not part of the table itself, it is important to find out if the sequence and the table are removed or if the sequence remains. Let's create a table:

 phpbook=# CREATE TEMPORARY TABLE tmp_data(id serial, data text); NOTICE:  CREATE TABLE will create implicit sequence 'tmp_data_id_seq' for SERIAL column 'tmp_data.id' NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'tmp_data_id_key' for table 'tmp_data' CREATE 

A sequence has been created. After you disconnect from the database and connect again, the sequence cannot be found in the database:

 phpbook=# \d tmp_data_id_seq Did not find any relation named "tmp_data_id_seq". 

To make sure that it has really been removed, you can try to select the next value in the sequence.

 phpbook=# SELECT nextval("tmp_data_id_seq"); ERROR:  Attribute 'tmp_data_id_seq' not found 

It seems as if the sequence has already been removed, which is true.

To see how this can easily be handled, it is worth looking at the syntax overview of the CREATE SEQUENCE command:

 phpbook=# \h REATE SEQUENCE Command:     CREATE SEQUENCE Description: define a new sequence Syntax: CREATE [ TEMPORARY | TEMP ] SEQUENCE seqname [ INCREMENT increment ]     [ MINVALUE minvalue ] [ MAXVALUE maxvalue ]     [ START start ] [ CACHE cache ] [ CYCLE ] 

As you can see, PostgreSQL is capable of handling temporary sequences. Therefore PostgreSQL automatically generates a temporary sequence when a table is created, so no problems occur when working with serials.

7.3.5 SELECT INTO

From time to time it is necessary to store the result of a query in a table. This can be done by using the SELECT INTO command. In contrast to using views, SELECT INTO generates a physical copy of the query and stores it in a table.

Take a look at the syntax overview of the SELECT INTO command:

 phpbook=# \h SELECT INTO Command:     SELECT INTO Description: create a new table from the results of a query Syntax: SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]     * | expression [ AS output_name ] [, ...]     INTO [ TEMPORARY | TEMP ] [ TABLE ] new_table     [ FROM from_item [, ...] ]     [ WHERE condition ]     [ GROUP BY expression [, ...] ]     [ HAVING condition [, ...] ]     [ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]     [ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]     [ FOR UPDATE [ OF tablename [, ...] ] ]     [ LIMIT [ start , ] { count | ALL } ]     [ OFFSET start ] where from_item can be: [ ONLY ] table_name [ * ]     [ [ AS ] alias [ ( column_alias_list ) ] ] | ( select )     [ AS ] alias [ ( column_alias_list ) ] | from_item [ NATURAL ] join_type from_item     [ ON join_condition | USING ( join_column_list ) ] 

As you can see, SELECT INTO is nothing more than a special sort of SELECT statement that generates a table based on the result of a query. Recall the content of t_person:

 phpbook=# SELECT * FROM t_person;  id |   name   | zip_code |  city ----+----------+----------+--------   1 | Hans     |     1150 | Vienna   2 | Epi      |     1060 |   3 | o'Connor |          |   4 | o'Connor |          | (4 rows) 

The target of the next example is to use SELECT INTO to generate a copy of t_person:

 phpbook=# SELECT * INTO TEMPORARY TABLE tmp_person FROM t_person; SELECT 

tmp_person is a temporary table containing the same data as the original table:

 phpbook=# SELECT * FROM tmp_person;  id |   name   | zip_code |  city ----+----------+----------+--------   1 | Hans     |     1150 | Vienna   2 | Epi      |     1060 |   3 | o'Connor |          |   4 | o'Connor |          | (4 rows) 

Because tmp_person is a temporary table, it will be deleted automatically as soon as the user terminates the connection to the database.

7.3.5.1 Views versus SELECT INTO

Using views and SELECT INTO makes a significant difference. SELECT INTO stores the result of a query in a table, but if the data in the original tables changes, the new tables will not be updated. In contrast, a view always contains the most recent result because it is generated every time the view is accessed.

The problem with views is that if several computations that take a lot of time have to be performed using the same snapshot of data, it might take far too long to use views. In this case temporary tables generated by SELECT INTO might be a good choice because this way it is easy to reduce the amount of data involved in the query. The new table will only contain a brief excerpt of the data, and this is much faster than accessing the original tables every time.