Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
PostgreSQL supports the CSV file format as a native. You can import data from a CSV file and add it to a table, or you can save the result of any query as a CSV file.
You can also redirect the output to stdout and zip it before the file is written to the disk.
We do all this with the COPY command. Let’s create a test table, insert data into it, and then export from this table to a file with CSV extension.
Create a Table and Insert Data Into It:
1 2 | CREATE TABLE csvtest AS SELECT * FROM (VALUES (1,'a'), (2,'b'), (3,'c')) AS foo(id, value); |
For CSV output; You must specify a file path after the TO.
1 | COPY csvtest TO '/tmp/csvtest.csv' (FORMAT csv); |
Our file is ready; We can see the contents with the bash command “Cat”.
We can change the shape of our output by giving different parameters. Below, I’m adding a new record first. I then create the file using the COPY command. Lastly, I’m leaving psql to view the contents of the file.
The details of the query I shared above can be seen below;
1 2 3 4 5 6 7 | (SELECT * FROM csvtest ORDER BY 1 DESC) -> We listed the records in the table from big to small according to the id field. DELIMITER ';' -> to make it separated by a semicolon (;) QUOTE '"', FORCE_QUOTE (value) -> to ensure that non-null values in the value field in the output are covered with double quotes. NULL '<NULL>' -> Make sure that null values are written to <NULL>. |
