From the objective of OSS-DB Exam Silver
- Operation and Management - How to use the backup method (COPY statement (SQL), copy command (psql))
Sample Question
1.14
Choose two appropriate SQL commands to output the contents of table foo to standard output with comma separated values. It should be noted that processing of special characters and the like need not be considered.
- copy from foo to stdout (format csv);
- copy foo to stdout (delimiter ',');
- copy stdout from foo format = 'csv';
- copy foo to stdout (format csv);
- copy from foo to stdout (delimiter = 'csv');
※This sample exam is different from those that appear in the actual OSS-DB Exam.
2019/03/04
Answer and Explanation
By using the SQL COPY statement, you can copy the table contents to a file. If the output destination is STDOUT, it can be output to standard output instead of copying it to a file.
The syntax of the COPY statement is
COPY table name TO output destination [[WITH] (option)]
The default is tab-delimited in text format, but if you specify csv with the FORMAT option, it will be a comma separated CSV format. Alternatively, comma delimiting is also possible by setting the delimiter as a comma by the DELIMITER option.
Therefore, the correct answers are B and D.
Note that there are differences in the handling of special characters in the CSV format and the text format, so even if you set the delimiter as a comma with the DELIMITER option, it does not become a so-called CSV format.