CSV
CSV
(Comma Separated Value
) is a simple ad-hoc data format used for data exchange.
PostgreSQL provides a number of options to generate and import data in CSV
format, using (as the name implies) commas to seperate record values (although alternative separators / delimiters can be used).
psql
psql's \copy
command can work with CSV
files.
From PostgreSQL 12, psql can generate output in CSV
format with \pset format csv
or the command-line --csv
option.
COPY
The SQL command COPY can import and export CSV
files, albeit only to/from files accessible by the PostgreSQL system user.
file_fdw
The file_fdw foreign data wrapper, which is built on top of the functionality underlying the COPY comand, enables CSV
files on the server's local filesystem to be treated as tables.
CSV log output format
PostgreSQL can optionally generate log files in CSV format, if the value csvlog
is included in the log_destination
parameter.
Examples
Using the following contrived example dataset:
postgres=# CREATE TABLE csvdemo (id INT, val1 TEXT, val2 TEXT); CREATE TABLE postgres=# INSERT INTO csvdemo VALUES (1,'foo','bar'), (2,'baz','boo,zoo'); INSERT 0 2 postgres=# SELECT * FROM csvdemo; id | val1 | val2
----+------+---------
1 | foo | bar
2 | baz | boo,zoo (2 rows)
Using psql
postgres=# \pset format csv Output format is csv. postgres=# SELECT * FROM csvdemo; id,val1,val2
1,foo,bar
2,baz,"boo,zoo"
Alternatively, executing direct from the system command line:
psql -d postgres -U postgres --csv -c "SELECT * FROM csvdemo" id,val1,val2
1,foo,bar
2,baz,"boo,zoo"
Using \copy
:
postgres=# \copy (SELECT * FROM csvdemo) to '/tmp/psql-copy.csv' (FORMAT 'csv', HEADER 'TRUE') COPY 2 postgres $ cat /tmp/psql-copy.csv
id,val1,val2 1,foo,bar 2,baz,"boo,zoo"
Using COPY
Example is from within psql, but applies to any application.
postgres=# COPY (SELECT * FROM csvdemo) to '/tmp/copy.csv' (FORMAT 'csv', HEADER 'TRUE'); COPY 2 postgres $ cat /tmp/copy.csv
id,val1,val2 1,foo,bar 2,baz,"boo,zoo" postgres $ echo '3,zoo,"fizz,buzz"' >> /tmp/copy.csv postgres=# COPY csvdemo FROM '/tmp/foo.csv' (FORMAT 'csv') WHERE id=3; COPY 1 postgres=# SELECT * FROM csvdemo; id | val1 | val2 ----+------+----------- 1 | foo | bar 2 | baz | boo,zoo 3 | zoo | fizz,buzz (3 rows)
(Note: COPY ... WHERE
available from PostgreSQL 12)
Using file_fdw
The extension file_fdw
must be available on the PostgreSQL server.
postgres=# CREATE EXTENSION file_fdw; CREATE EXTENSION postgres=# CREATE SERVER file_fdw_csv_test FOREIGN DATA WRAPPER file_fdw; CREATE SERVER postgres=# CREATE FOREIGN TABLE csv_test ( id INT, val1 TEXT, val2 TEXT ) SERVER file_fdw_csv_test OPTIONS ( filename '/tmp/copy.csv', format 'csv', header 'TRUE' ); postgres=# SELECT * FROM csv_test; id | val1 | val2 ----+------+----------- 1 | foo | bar 2 | baz | boo,zoo 3 | zoo | fizz,buzz (3 rows)
References
- PostgreSQL documentation: file_fdw
- PostgreSQL documentation: COPY command
- PostgreSQL documentation: psql
Useful links
- Relevant RFC: RFC 4180 - Common Format and MIME Type for Comma-Separated Values (CSV) Files
- Wikipedia: Comma-separated values
- How to import and export data using CSV files in PostgreSQL - December 2019 blog article by Amit Sharma / EnterpriseDB