Скачать в pdf «PostgreSQL»

Suppose we wish to add all of our friends from the friend table to the customer table. As shown in Figure 8.17, instead of a values clause, insert can use the output of select to insert data into the table. Each column of the select matches a receiving column in the insert. Column names and character string constants can be used in the select output. In the line INSERT 0 6, six rows are inserted into the customer table. A zero object identifier is returned because more than one row is inserted.

Inserting into the customer name column presents an interesting challenge. The friend table stores first and last names in separate columns. In contrast, the customer table has only a single name column. The solution is to combine the firstname and lastname columns, with a space separating them. For example, a firstname of ‘Dean’ and a lastname of ‘Yeager’ must be inserted into as ‘Dean Yeager’. This combination becomes possible with trim() and the || operator. The trim() function removes trailing spaces. The two pipe symbols, ||, allow character strings to be joined together to form a single string, in a process called concatenation. In this example, trim(firstname), space ( ’), and lastname are joined using ||.

8.7    Creating Tables Using Select

In addition to inserting into existing tables, select can use an into clause to create a table and place all of its output into the new table. For example, suppose we want to create a new table called newfriend that is just like our friend table but lacks an age column. This task is easily done with the query shown in Figure
8.18. The select…into query performs three operations:

Скачать в pdf «PostgreSQL»