1
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
8
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
10
HREF="mailto:pgsql-docs@postgresql.org"><LINK
12
TITLE="PostgreSQL 9.1beta1 Documentation"
13
HREF="index.html"><LINK
16
HREF="queries.html"><LINK
18
TITLE="Table Expressions"
19
HREF="queries-table-expressions.html"><LINK
21
TITLE="Combining Queries"
22
HREF="queries-union.html"><LINK
25
HREF="stylesheet.css"><META
26
HTTP-EQUIV="Content-Type"
27
CONTENT="text/html; charset=ISO-8859-1"><META
29
CONTENT="2011-04-27T21:20:33"></HEAD
35
SUMMARY="Header navigation table"
47
>PostgreSQL 9.1beta1 Documentation</A
56
TITLE="Table Expressions"
57
HREF="queries-table-expressions.html"
74
>Chapter 7. Queries</TD
89
TITLE="Combining Queries"
90
HREF="queries-union.html"
104
NAME="QUERIES-SELECT-LISTS"
105
>7.3. Select Lists</A
108
> As shown in the previous section,
109
the table expression in the <TT
113
constructs an intermediate virtual table by possibly combining
114
tables, views, eliminating rows, grouping, etc. This table is
115
finally passed on to processing by the <I
119
list determines which <SPAN
126
intermediate table are actually output.
133
NAME="QUERIES-SELECT-LIST-ITEMS"
134
>7.3.1. Select-List Items</A
137
> The simplest kind of select list is <TT
141
emits all columns that the table expression produces. Otherwise,
142
a select list is a comma-separated list of value expressions (as
144
HREF="sql-expressions.html"
147
could be a list of column names:
149
CLASS="PROGRAMLISTING"
150
>SELECT a, b, c FROM ...</PRE
152
The columns names <TT
162
are either the actual names of the columns of tables referenced
166
> clause, or the aliases given to them as
168
HREF="queries-table-expressions.html#QUERIES-TABLE-ALIASES"
171
space available in the select list is the same as in the
175
> clause, unless grouping is used, in which case
176
it is the same as in the <TT
182
> If more than one table has a column of the same name, the table
183
name must also be given, as in:
185
CLASS="PROGRAMLISTING"
186
>SELECT tbl1.a, tbl2.a, tbl1.b FROM ...</PRE
188
When working with multiple tables, it can also be useful to ask for
189
all the columns of a particular table:
191
CLASS="PROGRAMLISTING"
192
>SELECT tbl1.*, tbl2.a FROM ...</PRE
195
HREF="queries-table-expressions.html#QUERIES-WHERE"
200
> If an arbitrary value expression is used in the select list, it
201
conceptually adds a new virtual column to the returned table. The
202
value expression is evaluated once for each result row, with
203
the row's values substituted for any column references. But the
204
expressions in the select list do not have to reference any
205
columns in the table expression of the <TT
209
they can be constant arithmetic expressions, for instance.
217
NAME="QUERIES-COLUMN-LABELS"
218
>7.3.2. Column Labels</A
221
> The entries in the select list can be assigned names for subsequent
222
processing, such as for use in an <TT
226
or for display by the client application. For example:
228
CLASS="PROGRAMLISTING"
229
>SELECT a AS value, b + c AS sum FROM ...</PRE
233
> If no output column name is specified using <TT
237
the system assigns a default column name. For simple column references,
238
this is the name of the referenced column. For function
239
calls, this is the name of the function. For complex expressions,
240
the system will generate a generic name.
246
> keyword is optional, but only if the new column
247
name does not match any
252
HREF="sql-keywords-appendix.html"
254
>). To avoid an accidental match to
255
a keyword, you can double-quote the column name. For example,
259
> is a keyword, so this does not work:
261
CLASS="PROGRAMLISTING"
262
>SELECT a value, b + c AS sum FROM ...</PRE
266
CLASS="PROGRAMLISTING"
267
>SELECT a "value", b + c AS sum FROM ...</PRE
269
For protection against possible
270
future keyword additions, it is recommended that you always either
274
> or double-quote the output column name.
283
> The naming of output columns here is different from that done in
288
HREF="queries-table-expressions.html#QUERIES-TABLE-ALIASES"
291
to rename the same column twice, but the name assigned in
292
the select list is the one that will be passed on.
302
NAME="QUERIES-DISTINCT"
309
> After the select list has been processed, the result table can
310
optionally be subject to the elimination of duplicate rows. The
314
> key word is written directly after
335
can be used to specify the default behavior of retaining all rows.)
338
> Obviously, two rows are considered distinct if they differ in at
339
least one column value. Null values are considered equal in this
343
> Alternatively, an arbitrary expression can determine what rows are
344
to be considered distinct:
347
>SELECT DISTINCT ON (<TT
372
> is an arbitrary value
373
expression that is evaluated for all rows. A set of rows for
374
which all the expressions are equal are considered duplicates, and
375
only the first row of the set is kept in the output. Note that
379
> of a set is unpredictable unless the
380
query is sorted on enough columns to guarantee a unique ordering
381
of the rows arriving at the <TT
388
> processing occurs after <TT
398
> clause is not part of the SQL standard
399
and is sometimes considered bad style because of the potentially
400
indeterminate nature of its results. With judicious use of
404
> and subqueries in <TT
408
construct can be avoided, but it is often the most convenient
418
SUMMARY="Footer navigation table"
429
HREF="queries-table-expressions.html"
447
HREF="queries-union.html"
457
>Table Expressions</TD
471
>Combining Queries</TD
b'\\ No newline at end of file'