While SELECT * is a good example of a basic query, and is sometimes very useful,
you will probably be interested in retrieving only a few columns worth of information at a time. To stay
efficient, and to keep your queries clear, it is a good idea to explicitly specify the intended target columns rather than to
use the asterisk. This is especially true when using the JOIN clause, as will be
discussed in the Section called Joining Data Sets with JOIN
."
To specify the target columns for a query, list the names of the columns following the
SELECT
keyword. The query
will return data for only those columns that you list. The order of these columns need not match their literal order in the table,
and columns may be listed more than once, or not at all, as shown in Example 4-24.
Example 4-24. Re-Ordering columns
booktown=#
SELECT id, author_id, title, id
booktown-#
FROM books;
id | author_id | title | id
-------+-----------+-----------------------------+-------
7808 | 4156 | The Shining | 7808
4513 | 1866 | Dune | 4513
4267 | 2001 | 2001: A Space Odyssey | 4267
1608 | 1809 | The Cat in the Hat | 1608
1590 | 1809 | Bartholomew and the Oobleck | 1590
25908 | 15990 | Franklin in the Dark | 25908
1501 | 2031 | Goodnight Moon | 1501
190 | 16 | Little Women | 190
1234 | 25041 | The Velveteen Rabbit | 1234
2038 | 1644 | Dynamic Anatomy | 2038
156 | 115 | The Tell-Tale Heart | 156
41472 | 1212 | Practical PostgreSQL | 41472
41473 | 7805 | Programming Python | 41473
41477 | 7805 | Learning Python | 41477
41478 | 7806 | Perl Cookbook | 41478
(15 rows)
As you can see, the data sets returned in both Example 4-24 and Example 4-23 are nearly identical. The second set is returned in a different column arrangement,
(omitting the subject_id column, and repeating the
id column twice) as a result of the target list.
