Contents

CSV

Comma Separated Value

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 (comma-separated values) 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)

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)

Categories

Data import/export