diff options
| author | Tomas Vondra <tomas.vondra@postgresql.org> | 2019-01-19 23:48:16 +0100 |
|---|---|---|
| committer | Tomas Vondra <tomas.vondra@postgresql.org> | 2019-01-20 00:22:14 +0100 |
| commit | 31f3817402da736b29014ace470cf70aeb126ac5 (patch) | |
| tree | ab02bcacacdb944f47996f10435a94c9f0258543 /doc/src | |
| parent | d6ef7fe75c1fb7ccc4ad8e3dd38d4e84d6fe9b9f (diff) | |
| download | postgresql-31f3817402da736b29014ace470cf70aeb126ac5.tar.gz postgresql-31f3817402da736b29014ace470cf70aeb126ac5.zip | |
Allow COPY FROM to filter data using WHERE conditions
Extends the COPY FROM command with a WHERE condition, which allows doing
various types of filtering while importing the data (random sampling,
condition on a data column, etc.). Until now such filtering required
either preprocessing of the input data, or importing all data and then
filtering in the database. COPY FROM ... WHERE is an easy-to-use and
low-overhead alternative for most simple cases.
Author: Surafel Temesgen
Reviewed-by: Tomas Vondra, Masahiko Sawada, Lim Myungkyu
Discussion: https://www.postgresql.org/message-id/flat/CALAY4q_DdpWDuB5-Zyi-oTtO2uSk8pmy+dupiRe3AvAc++1imA@mail.gmail.com
Diffstat (limited to 'doc/src')
| -rw-r--r-- | doc/src/sgml/ref/copy.sgml | 27 |
1 files changed, 27 insertions, 0 deletions
diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml index 411941ed31f..254d3ab8eb3 100644 --- a/doc/src/sgml/ref/copy.sgml +++ b/doc/src/sgml/ref/copy.sgml @@ -25,6 +25,7 @@ PostgreSQL documentation COPY <replaceable class="parameter">table_name</replaceable> [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ] FROM { '<replaceable class="parameter">filename</replaceable>' | PROGRAM '<replaceable class="parameter">command</replaceable>' | STDIN } [ [ WITH ] ( <replaceable class="parameter">option</replaceable> [, ...] ) ] + [ WHERE <replaceable class="parameter">condition</replaceable> ] COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ] | ( <replaceable class="parameter">query</replaceable> ) } TO { '<replaceable class="parameter">filename</replaceable>' | PROGRAM '<replaceable class="parameter">command</replaceable>' | STDOUT } @@ -353,6 +354,32 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable </listitem> </varlistentry> + <varlistentry> + <term><literal>WHERE</literal></term> + <listitem> + <para> + The optional <literal>WHERE</literal> clause has the general form +<synopsis> +WHERE <replaceable class="parameter">condition</replaceable> +</synopsis> + where <replaceable class="parameter">condition</replaceable> is + any expression that evaluates to a result of type + <type>boolean</type>. Any row that does not satisfy this + condition will not be inserted to the table. A row satisfies the + condition if it returns true when the actual row values are + substituted for any variable references. + </para> + + <para> + Currently, subqueries are not allowed in <literal>WHERE</literal> + expressions, and the evaluation does not see any changes made by the + <command>COPY</command> itself (this matters when the expression + contains calls to <literal>VOLATILE</literal> functions). + </para> + + </listitem> + </varlistentry> + </variablelist> </refsect1> |