| # Licensed to the Apache Software Foundation (ASF) under one |
| # or more contributor license agreements. See the NOTICE file |
| # distributed with this work for additional information |
| # regarding copyright ownership. The ASF licenses this file |
| # to you under the Apache License, Version 2.0 (the |
| # "License"); you may not use this file except in compliance |
| # with the License. You may obtain a copy of the License at |
| # |
| # http://www.apache.org/licenses/LICENSE-2.0 |
| # |
| # Unless required by applicable law or agreed to in writing, |
| # software distributed under the License is distributed on an |
| # "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY |
| # KIND, either express or implied. See the License for the |
| # specific language governing permissions and limitations |
| # under the License. |
| |
| CREATE TABLE source ( |
| int_col INT NULL, |
| date_col DATE NULL, |
| char_col CHAR(16), |
| varchar_col VARCHAR(16) |
| ); |
| |
| INSERT INTO source VALUES(1, '2000-01-01', 'aa', 'aaa'); |
| INSERT INTO source VALUES(2, '2000-02-02', 'bb', 'bbb'); |
| INSERT INTO source VALUES(3, '2000-03-03', 'cc', 'ccc'); |
| INSERT INTO source VALUES(4, '2000-04-04', 'aa', 'ddd'); |
| INSERT INTO source VALUES(5, '2000-05-05', 'bb', 'eee'); |
| INSERT INTO source VALUES(6, '2000-06-06', 'cc', 'fff'); |
| |
| COPY source TO stderr WITH (DELIMITER '|'); |
| -- |
| 1|2000-01-01|aa|aaa |
| 2|2000-02-02|bb|bbb |
| 3|2000-03-03|cc|ccc |
| 4|2000-04-04|aa|ddd |
| 5|2000-05-05|bb|eee |
| 6|2000-06-06|cc|fff |
| == |
| |
| COPY |
| SELECT char_col, SUM(int_col) |
| FROM source |
| GROUP BY char_col |
| TO stderr; |
| -- |
| aa 5 |
| bb 7 |
| cc 9 |
| == |
| |
| COPY |
| SELECT * FROM ( |
| SELECT -int_col * 1000, 'Negative' FROM source |
| UNION ALL |
| SELECT int_col * 1000, 'Positive' FROM source |
| ) AS t(VALUE, SIGN) |
| ORDER BY value |
| TO stderr WITH (FORMAT 'CSV', DELIMITER e'\t'); |
| -- |
| VALUE SIGN |
| -6000 Negative |
| -5000 Negative |
| -4000 Negative |
| -3000 Negative |
| -2000 Negative |
| -1000 Negative |
| 1000 Positive |
| 2000 Positive |
| 3000 Positive |
| 4000 Positive |
| 5000 Positive |
| 6000 Positive |
| == |
| |
| # WITH clause. |
| WITH r(x, y) AS ( |
| SELECT i, i + 1 |
| FROM generate_series(0, 9) AS g(i) |
| ) |
| COPY |
| SELECT x * y AS value FROM r ORDER BY value |
| TO stderr; |
| -- |
| 0 |
| 2 |
| 6 |
| 12 |
| 20 |
| 30 |
| 42 |
| 56 |
| 72 |
| 90 |
| == |
| |
| |
| # Test handling of NULL values and special characters. |
| DELETE FROM source; |
| INSERT INTO source VALUES(1, '2000-01-01', 'abc', 'def'); |
| INSERT INTO source VALUES(2, '2000-02-02', e'a\ta', '|,|'); |
| INSERT INTO source VALUES(NULL, NULL, e'b\nb', '"""'); |
| |
| COPY source TO stderr; |
| -- |
| 1 2000-01-01 abc def |
| 2 2000-02-02 a\ta |,| |
| \N \N b\nb """ |
| == |
| |
| COPY source TO stderr WITH (FORMAT 'CSV', HEADER FALSE); |
| -- |
| 1,2000-01-01,abc,def |
| 2,2000-02-02,a a,"|,|" |
| ,,"b |
| b","""""""" |
| == |
| |
| COPY source TO stderr WITH (FORMAT 'CSV', DELIMITER '|', NULL_STRING '.na', HEADER TRUE); |
| -- |
| int_col|date_col|char_col|varchar_col |
| 1|2000-01-01|abc|def |
| 2|2000-02-02|a a|"|,|" |
| .na|.na|"b |
| b"|"""""""" |
| == |