CSV 檔全名 Comma Seprated Values 是一種純 Text File.
【注意】許多程序產生奇怪的並且有時候不正確的 CVS 檔案,所以這個檔案格式更像一種慣用格式,而不是一種標準。因此你可能碰到一些不能使用這個機制輸入的檔案,而 COPY 也可能產生一些其它程序不能處理的檔案。
CSV Format
This format is used for importing and exporting the Comma Separated Value (CSV) file format used by many other programs, such as spreadsheets. Instead of the escaping used by PostgreSQL's standard text mode, it produces and recognizes the common CSV escaping mechanism.
The values in each record are separated by the DELIMITER character. If the value contains the delimiter character, the QUOTE character, the NULL string, a carriage return, or line feed character, then the whole value is prefixed and suffixed by the QUOTE character, and any occurrence within the value of a QUOTE character or the ESCAPE character is preceded by the escape character. You can also use FORCE QUOTE to force quotes when outputting non-NULL values in specific columns.
The CSV format has no standard way to distinguish a NULL value from an empty string. PostgreSQL's COPY handles this by quoting. A NULL is output as the NULL string and is not quoted, while a data value matching the NULL string is quoted. Therefore, using the default settings, a NULL is written as an unquoted empty string, while an empty string is written with double quotes (""). Reading values follows similar rules. You can use FORCE NOT NULL to prevent NULL input comparisons for specific columns.
Because backslash is not a special character in the CSV format, \., the end-of-data marker, could also appear as a data value. To avoid any misinterpretation, a \.
data value appearing as a lone entry on a line is automatically quoted
on output, and on input, if quoted, is not interpreted as the
end-of-data marker. If you are loading a file created by another
application that has a single unquoted column and might have a value of
\., you might need to quote that value in the input file.
Note: In CSV mode, all characters are significant. A quoted value surrounded by white space, or any characters other than DELIMITER, will include those characters. This can cause errors if you import data from a system that pads CSV lines with white space out to some fixed width. If such a situation arises you might need to preprocess the CSV file to remove the trailing white space, before importing the data into PostgreSQL.
Note:
CSV mode will both recognize and produce CSV files with quoted values
containing embedded carriage returns and line feeds. Thus the files are
not strictly one line per table row like text-mode files.
Note:
Many programs produce strange and occasionally perverse CSV files, so
the file format is more a convention than a standard. Thus you might
encounter some files that cannot be imported using this mechanism, and COPY might produce files that other programs cannot process.