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 /src/test/regress/expected/copy2.out | |
| parent | d6ef7fe75c1fb7ccc4ad8e3dd38d4e84d6fe9b9f (diff) | |
| download | postgresql-31f3817402da736b29014ace470cf70aeb126ac5.tar.gz | |
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 'src/test/regress/expected/copy2.out')
| -rw-r--r-- | src/test/regress/expected/copy2.out | 40 |
1 files changed, 39 insertions, 1 deletions
diff --git a/src/test/regress/expected/copy2.out b/src/test/regress/expected/copy2.out index 1d5bed5cc5..75d4119eaa 100644 --- a/src/test/regress/expected/copy2.out +++ b/src/test/regress/expected/copy2.out @@ -49,6 +49,32 @@ CONTEXT: COPY x, line 1: "2002 232 40 50 60 70 80" COPY x (b, c, d, e) from stdin delimiter ',' null 'x'; COPY x from stdin WITH DELIMITER AS ';' NULL AS ''; COPY x from stdin WITH DELIMITER AS ':' NULL AS E'\\X' ENCODING 'sql_ascii'; +COPY x TO stdout WHERE a = 1; +ERROR: WHERE clause not allowed with COPY TO +LINE 1: COPY x TO stdout WHERE a = 1; + ^ +COPY x from stdin WHERE a = 50004; +COPY x from stdin WHERE a > 60003; +COPY x from stdin WHERE f > 60003; +ERROR: column "f" does not exist +LINE 1: COPY x from stdin WHERE f > 60003; + ^ +COPY x from stdin WHERE a = max(x.b); +ERROR: aggregate functions are not allowed in COPY FROM WHERE conditions +LINE 1: COPY x from stdin WHERE a = max(x.b); + ^ +COPY x from stdin WHERE a IN (SELECT 1 FROM x); +ERROR: cannot use subquery in COPY FROM WHERE condition +LINE 1: COPY x from stdin WHERE a IN (SELECT 1 FROM x); + ^ +COPY x from stdin WHERE a IN (generate_series(1,5)); +ERROR: set-returning functions are not allowed in COPY FROM WHERE conditions +LINE 1: COPY x from stdin WHERE a IN (generate_series(1,5)); + ^ +COPY x from stdin WHERE a = row_number() over(b); +ERROR: window functions are not allowed in COPY FROM WHERE conditions +LINE 1: COPY x from stdin WHERE a = row_number() over(b); + ^ -- check results of copy in SELECT * FROM x; a | b | c | d | e @@ -73,12 +99,15 @@ SELECT * FROM x; 4006 | 6 | BackslashN | \N | before trigger fired 4007 | 7 | XX | XX | before trigger fired 4008 | 8 | Delimiter | : | before trigger fired + 50004 | 25 | 35 | 45 | before trigger fired + 60004 | 25 | 35 | 45 | before trigger fired + 60005 | 26 | 36 | 46 | before trigger fired 1 | 1 | stuff | test_1 | after trigger fired 2 | 2 | stuff | test_2 | after trigger fired 3 | 3 | stuff | test_3 | after trigger fired 4 | 4 | stuff | test_4 | after trigger fired 5 | 5 | stuff | test_5 | after trigger fired -(25 rows) +(28 rows) -- check copy out COPY x TO stdout; @@ -102,6 +131,9 @@ COPY x TO stdout; 4006 6 BackslashN \\N before trigger fired 4007 7 XX XX before trigger fired 4008 8 Delimiter : before trigger fired +50004 25 35 45 before trigger fired +60004 25 35 45 before trigger fired +60005 26 36 46 before trigger fired 1 1 stuff test_1 after trigger fired 2 2 stuff test_2 after trigger fired 3 3 stuff test_3 after trigger fired @@ -128,6 +160,9 @@ N before trigger fired BackslashN before trigger fired XX before trigger fired Delimiter before trigger fired +35 before trigger fired +35 before trigger fired +36 before trigger fired stuff after trigger fired stuff after trigger fired stuff after trigger fired @@ -154,6 +189,9 @@ I'm null before trigger fired 6 before trigger fired 7 before trigger fired 8 before trigger fired +25 before trigger fired +25 before trigger fired +26 before trigger fired 1 after trigger fired 2 after trigger fired 3 after trigger fired |