diff options
| author | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-04-13 13:44:03 +0000 |
|---|---|---|
| committer | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-04-13 13:44:03 +0000 |
| commit | 293913568e6a7a86fd1479e1cff8e2ecb58d6568 (patch) | |
| tree | fc3b469a3ec5ab71b36ea97cc7aaddb838423a0c /doc/src/sgml/html/queries-select-lists.html | |
| parent | Initial commit. (diff) | |
| download | postgresql-16-293913568e6a7a86fd1479e1cff8e2ecb58d6568.tar.xz postgresql-16-293913568e6a7a86fd1479e1cff8e2ecb58d6568.zip | |
Adding upstream version 16.2.upstream/16.2
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'doc/src/sgml/html/queries-select-lists.html')
| -rw-r--r-- | doc/src/sgml/html/queries-select-lists.html | 122 |
1 files changed, 122 insertions, 0 deletions
diff --git a/doc/src/sgml/html/queries-select-lists.html b/doc/src/sgml/html/queries-select-lists.html new file mode 100644 index 0000000..3414941 --- /dev/null +++ b/doc/src/sgml/html/queries-select-lists.html @@ -0,0 +1,122 @@ +<?xml version="1.0" encoding="UTF-8" standalone="no"?> +<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>7.3. Select Lists</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="queries-table-expressions.html" title="7.2. Table Expressions" /><link rel="next" href="queries-union.html" title="7.4. Combining Queries (UNION, INTERSECT, EXCEPT)" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">7.3. Select Lists</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="queries-table-expressions.html" title="7.2. Table Expressions">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="queries.html" title="Chapter 7. Queries">Up</a></td><th width="60%" align="center">Chapter 7. Queries</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.2 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="queries-union.html" title="7.4. Combining Queries (UNION, INTERSECT, EXCEPT)">Next</a></td></tr></table><hr /></div><div class="sect1" id="QUERIES-SELECT-LISTS"><div class="titlepage"><div><div><h2 class="title" style="clear: both">7.3. Select Lists <a href="#QUERIES-SELECT-LISTS" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="queries-select-lists.html#QUERIES-SELECT-LIST-ITEMS">7.3.1. Select-List Items</a></span></dt><dt><span class="sect2"><a href="queries-select-lists.html#QUERIES-COLUMN-LABELS">7.3.2. Column Labels</a></span></dt><dt><span class="sect2"><a href="queries-select-lists.html#QUERIES-DISTINCT">7.3.3. <code class="literal">DISTINCT</code></a></span></dt></dl></div><a id="id-1.5.6.7.2" class="indexterm"></a><p> + As shown in the previous section, + the table expression in the <code class="command">SELECT</code> command + constructs an intermediate virtual table by possibly combining + tables, views, eliminating rows, grouping, etc. This table is + finally passed on to processing by the <em class="firstterm">select list</em>. The select + list determines which <span class="emphasis"><em>columns</em></span> of the + intermediate table are actually output. + </p><div class="sect2" id="QUERIES-SELECT-LIST-ITEMS"><div class="titlepage"><div><div><h3 class="title">7.3.1. Select-List Items <a href="#QUERIES-SELECT-LIST-ITEMS" class="id_link">#</a></h3></div></div></div><a id="id-1.5.6.7.4.2" class="indexterm"></a><p> + The simplest kind of select list is <code class="literal">*</code> which + emits all columns that the table expression produces. Otherwise, + a select list is a comma-separated list of value expressions (as + defined in <a class="xref" href="sql-expressions.html" title="4.2. Value Expressions">Section 4.2</a>). For instance, it + could be a list of column names: +</p><pre class="programlisting"> +SELECT a, b, c FROM ... +</pre><p> + The columns names <code class="literal">a</code>, <code class="literal">b</code>, and <code class="literal">c</code> + are either the actual names of the columns of tables referenced + in the <code class="literal">FROM</code> clause, or the aliases given to them as + explained in <a class="xref" href="queries-table-expressions.html#QUERIES-TABLE-ALIASES" title="7.2.1.2. Table and Column Aliases">Section 7.2.1.2</a>. The name + space available in the select list is the same as in the + <code class="literal">WHERE</code> clause, unless grouping is used, in which case + it is the same as in the <code class="literal">HAVING</code> clause. + </p><p> + If more than one table has a column of the same name, the table + name must also be given, as in: +</p><pre class="programlisting"> +SELECT tbl1.a, tbl2.a, tbl1.b FROM ... +</pre><p> + When working with multiple tables, it can also be useful to ask for + all the columns of a particular table: +</p><pre class="programlisting"> +SELECT tbl1.*, tbl2.a FROM ... +</pre><p> + See <a class="xref" href="rowtypes.html#ROWTYPES-USAGE" title="8.16.5. Using Composite Types in Queries">Section 8.16.5</a> for more about + the <em class="replaceable"><code>table_name</code></em><code class="literal">.*</code> notation. + </p><p> + If an arbitrary value expression is used in the select list, it + conceptually adds a new virtual column to the returned table. The + value expression is evaluated once for each result row, with + the row's values substituted for any column references. But the + expressions in the select list do not have to reference any + columns in the table expression of the <code class="literal">FROM</code> clause; + they can be constant arithmetic expressions, for instance. + </p></div><div class="sect2" id="QUERIES-COLUMN-LABELS"><div class="titlepage"><div><div><h3 class="title">7.3.2. Column Labels <a href="#QUERIES-COLUMN-LABELS" class="id_link">#</a></h3></div></div></div><a id="id-1.5.6.7.5.2" class="indexterm"></a><p> + The entries in the select list can be assigned names for subsequent + processing, such as for use in an <code class="literal">ORDER BY</code> clause + or for display by the client application. For example: +</p><pre class="programlisting"> +SELECT a AS value, b + c AS sum FROM ... +</pre><p> + </p><p> + If no output column name is specified using <code class="literal">AS</code>, + the system assigns a default column name. For simple column references, + this is the name of the referenced column. For function + calls, this is the name of the function. For complex expressions, + the system will generate a generic name. + </p><p> + The <code class="literal">AS</code> key word is usually optional, but in some + cases where the desired column name matches a + <span class="productname">PostgreSQL</span> key word, you must write + <code class="literal">AS</code> or double-quote the column name in order to + avoid ambiguity. + (<a class="xref" href="sql-keywords-appendix.html" title="Appendix C. SQL Key Words">Appendix C</a> shows which key words + require <code class="literal">AS</code> to be used as a column label.) + For example, <code class="literal">FROM</code> is one such key word, so this + does not work: +</p><pre class="programlisting"> +SELECT a from, b + c AS sum FROM ... +</pre><p> + but either of these do: +</p><pre class="programlisting"> +SELECT a AS from, b + c AS sum FROM ... +SELECT a "from", b + c AS sum FROM ... +</pre><p> + For greatest safety against possible + future key word additions, it is recommended that you always either + write <code class="literal">AS</code> or double-quote the output column name. + </p><div class="note"><h3 class="title">Note</h3><p> + The naming of output columns here is different from that done in + the <code class="literal">FROM</code> clause (see <a class="xref" href="queries-table-expressions.html#QUERIES-TABLE-ALIASES" title="7.2.1.2. Table and Column Aliases">Section 7.2.1.2</a>). It is possible + to rename the same column twice, but the name assigned in + the select list is the one that will be passed on. + </p></div></div><div class="sect2" id="QUERIES-DISTINCT"><div class="titlepage"><div><div><h3 class="title">7.3.3. <code class="literal">DISTINCT</code> <a href="#QUERIES-DISTINCT" class="id_link">#</a></h3></div></div></div><a id="id-1.5.6.7.6.2" class="indexterm"></a><a id="id-1.5.6.7.6.3" class="indexterm"></a><a id="id-1.5.6.7.6.4" class="indexterm"></a><p> + After the select list has been processed, the result table can + optionally be subject to the elimination of duplicate rows. The + <code class="literal">DISTINCT</code> key word is written directly after + <code class="literal">SELECT</code> to specify this: +</p><pre class="synopsis"> +SELECT DISTINCT <em class="replaceable"><code>select_list</code></em> ... +</pre><p> + (Instead of <code class="literal">DISTINCT</code> the key word <code class="literal">ALL</code> + can be used to specify the default behavior of retaining all rows.) + </p><a id="id-1.5.6.7.6.6" class="indexterm"></a><p> + Obviously, two rows are considered distinct if they differ in at + least one column value. Null values are considered equal in this + comparison. + </p><p> + Alternatively, an arbitrary expression can determine what rows are + to be considered distinct: +</p><pre class="synopsis"> +SELECT DISTINCT ON (<em class="replaceable"><code>expression</code></em> [<span class="optional">, <em class="replaceable"><code>expression</code></em> ...</span>]) <em class="replaceable"><code>select_list</code></em> ... +</pre><p> + Here <em class="replaceable"><code>expression</code></em> is an arbitrary value + expression that is evaluated for all rows. A set of rows for + which all the expressions are equal are considered duplicates, and + only the first row of the set is kept in the output. Note that + the <span class="quote">“<span class="quote">first row</span>”</span> of a set is unpredictable unless the + query is sorted on enough columns to guarantee a unique ordering + of the rows arriving at the <code class="literal">DISTINCT</code> filter. + (<code class="literal">DISTINCT ON</code> processing occurs after <code class="literal">ORDER + BY</code> sorting.) + </p><p> + The <code class="literal">DISTINCT ON</code> clause is not part of the SQL standard + and is sometimes considered bad style because of the potentially + indeterminate nature of its results. With judicious use of + <code class="literal">GROUP BY</code> and subqueries in <code class="literal">FROM</code>, this + construct can be avoided, but it is often the most convenient + alternative. + </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="queries-table-expressions.html" title="7.2. Table Expressions">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="queries.html" title="Chapter 7. Queries">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="queries-union.html" title="7.4. Combining Queries (UNION, INTERSECT, EXCEPT)">Next</a></td></tr><tr><td width="40%" align="left" valign="top">7.2. Table Expressions </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.2 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 7.4. Combining Queries (<code class="literal">UNION</code>, <code class="literal">INTERSECT</code>, <code class="literal">EXCEPT</code>)</td></tr></table></div></body></html>
\ No newline at end of file |