How to Export a PostgreSQL Query to CSV

Exporting PostgreSQL query results to CSV is a common task in database management. CSV (Comma-Separated Values) is a file format that stores tabular data in plain text, where each line represents a row and each value within a row is separated by a comma. This format is widely supported and can be easily imported into various applications such as spreadsheets, databases, and data analysis tools.

There are multiple ways to export PostgreSQL query results to CSV, depending on the tools and programming languages you are using. In this article, we will explore various methods to accomplish this task. We will cover exporting PostgreSQL query results to CSV using the command line, pgAdmin, and different programming languages such as Python, Java, PHP, Ruby, Node.js, .NET, Perl, Bash, Go, Rust, C++, PowerShell, R, Scala, Julia, Swift, Kotlin, Groovy, Lua, TypeScript, PowerShell Core, Objective-C, Erlang, OCaml, Perl 6, Crystal, Scheme, CoffeeScript, and Visual Basic.

Let's dive into each method and see how to export PostgreSQL query results to CSV using different tools and programming languages.

Exporting PostgreSQL Query Result to CSV Using Command Line

The command line provides a simple and straightforward way to export PostgreSQL query results to CSV. PostgreSQL provides the psql command-line tool, which allows us to interact with the database and execute queries. We can use the -c option to specify the query we want to execute and the -o option to redirect the output to a file.

Here's an example of how to export a PostgreSQL query result to CSV using the command line:

psql -c "COPY (SELECT * FROM table_name) TO '/path/to/output.csv' CSV HEADER"

In this example, we use the COPY command with a subquery to select the data we want to export. The TO clause specifies the output file path, and the CSV option indicates that we want to export the data in CSV format. The HEADER option includes a header row with the column names in the output file.

You can modify the query inside the COPY command to select the specific columns or add any additional filtering or sorting criteria as needed.

Exporting PostgreSQL Query Result to CSV Using pgAdmin

pgAdmin is a popular open-source administration and development platform for PostgreSQL. It provides an intuitive graphical user interface that allows us to manage databases, execute queries, and perform various database-related tasks.

To export a PostgreSQL query result to CSV using pgAdmin, follow these steps:

1. Open pgAdmin and connect to your PostgreSQL database.

2. In the object browser, navigate to the schema and table you want to export data from.

3. Right-click on the table and select "Query Tool" to open the query editor.

4. Write your query in the editor.

5. Click the "Execute" button to execute the query and view the result.

6. Right-click on the query result grid and select "Save As".

7. Choose the CSV format and specify the output file path.

8. Click "Save" to export the query result to CSV.

pgAdmin provides a convenient way to execute queries and export the results to CSV directly from its graphical user interface. You can also save the query for future use or modify it as needed.

Exporting to CSV Using Python

Python is a popular programming language for data analysis and manipulation. It provides various libraries and modules that make it easy to work with databases, including PostgreSQL. We can use the psycopg2 library in Python to connect to a PostgreSQL database and execute queries.

Here's an example of how to export a PostgreSQL query result to CSV using Python:

import psycopg2
import csv

# Connect to the PostgreSQL database
conn = psycopg2.connect(
    host="your_host",
    database="your_database",
    user="your_user",
    password="your_password"
)

# Create a cursor object to execute queries
cursor = conn.cursor()

# Execute the query
cursor.execute("SELECT * FROM table_name")

# Fetch all rows from the query result
rows = cursor.fetchall()

# Specify the output file path
output_file = "/path/to/output.csv"

# Write the query result to CSV
with open(output_file, "w", newline="") as file:
    writer = csv.writer(file)
    writer.writerow([desc[0] for desc in cursor.description])  # Write column names
    writer.writerows(rows)  # Write rows

# Close the cursor and connection
cursor.close()
conn.close()

In this example, we first import the necessary modules: psycopg2 for connecting to the PostgreSQL database and executing queries, and csv for writing the query result to CSV.

Next, we establish a connection to the PostgreSQL database using the psycopg2.connect() function. Make sure to provide the correct host, database, user, and password parameters.

We create a cursor object using the conn.cursor() method, which allows us to execute queries.

We execute the query using the cursor.execute() method. In this example, we select all rows from the "table_name" table.

We fetch all rows from the query result using the cursor.fetchall() method.

We specify the output file path where we want to save the CSV file.

Finally, we use the csv.writer object to write the query result to CSV. We first write the column names by extracting them from cursor.description, and then write the rows using the writer.writerows() method.

Make sure to close the cursor and connection using the cursor.close() and conn.close() methods after you are done.

You can modify the query to select specific columns or add any additional filtering or sorting criteria as needed.

Exporting to CSV Using JDBC

JDBC (Java Database Connectivity) is a Java API that allows Java programs to interact with databases. It provides a standard set of interfaces and classes for connecting to databases, executing queries, and retrieving results.

To export a PostgreSQL query result to CSV using JDBC, follow these steps:

1. Set up a JDBC connection to the PostgreSQL database. Make sure you have the PostgreSQL JDBC driver (e.g., postgresql.jar) in your classpath.

2. Create a Connection object using the DriverManager.getConnection() method. Provide the correct connection URL, username, and password.

3. Create a Statement object using the connection.createStatement() method.

4. Execute the query using the Statement.executeQuery() method.

5. Iterate over the query result using the ResultSet object.

6. Write the query result to CSV using a CSV writer library or by manually formatting the data.

7. Close the ResultSet, Statement, and Connection objects.

Here's an example of how to export a PostgreSQL query result to CSV using JDBC in Java:

import java.io.FileWriter;
import java.io.IOException;
import java.sql.*;

public class ExportToCsvExample {
    public static void main(String[] args) {
        String jdbcUrl = "jdbc:postgresql://your_host:your_port/your_database";
        String username = "your_username";
        String password = "your_password";
        String query = "SELECT * FROM table_name";
        String outputCsvFile = "/path/to/output.csv";

        try (Connection connection = DriverManager.getConnection(jdbcUrl, username, password);
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(query);
             FileWriter fileWriter = new FileWriter(outputCsvFile)) {

            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();

            // Write column names to CSV
            for (int i = 1; i <= columnCount; i++) {
                String columnName = metaData.getColumnName(i);
                fileWriter.append(columnName);
                if (i < columnCount) {
                    fileWriter.append(",");
                }
            }
            fileWriter.append("\n");

            // Write rows to CSV
            while (resultSet.next()) {
                for (int i = 1; i <= columnCount; i++) {
                    Object value = resultSet.getObject(i);
                    fileWriter.append(value != null ? value.toString() : "");
                    if (i < columnCount) {
                        fileWriter.append(",");
                    }
                }
                fileWriter.append("\n");
            }

            System.out.println("Exported query result to CSV successfully.");

        } catch (SQLException | IOException e) {
            e.printStackTrace();
        }
    }
}

In this example, we first define the JDBC URL, username, password, query, and output CSV file path. Make sure to replace the placeholders with the actual values.

We establish a connection to the PostgreSQL database using the DriverManager.getConnection() method.

We create a Statement object using the connection.createStatement() method.

We execute the query and obtain a ResultSet using the statement.executeQuery() method.

We get the metadata of the result set using the resultSet.getMetaData() method. This allows us to retrieve information about the columns in the result set.

We use a FileWriter to write the query result to the output CSV file. We first write the column names by iterating over the metadata and extracting the column names using the metaData.getColumnName() method. We then write the rows by iterating over the result set and retrieving the values using the resultSet.getObject() method.

Finally, we close the result set, statement, and connection using the try-with-resources statement.

You can modify the query to select specific columns or add any additional filtering or sorting criteria as needed.

Related Article: How to Truncate Tables in PostgreSQL

Exporting to CSV Using PHP

PHP is a popular server-side scripting language for web development. It provides various libraries and extensions for interacting with databases, including PostgreSQL. We can use the pg_query() function in PHP to execute queries and retrieve results from PostgreSQL.

To export a PostgreSQL query result to CSV using PHP, follow these steps:

1. Set up a connection to the PostgreSQL database using the pg_connect() function. Provide the correct host, database, username, and password parameters.

2. Execute the query using the pg_query() function.

3. Fetch all rows from the query result using the pg_fetch_all() function.

4. Specify the output file path where you want to save the CSV file.

5. Open the output file in write mode using the fopen() function.

6. Write the column names to the CSV file using the fputcsv() function.

7. Iterate over the rows and write each row to the CSV file using the fputcsv() function.

8. Close the output file using the fclose() function.

Here's an example of how to export a PostgreSQL query result to CSV using PHP:

<?php
// Set up a connection to the PostgreSQL database
$conn = pg_connect("host=your_host dbname=your_database user=your_user password=your_password");

// Execute the query
$result = pg_query($conn, "SELECT * FROM table_name");

// Fetch all rows from the query result
$rows = pg_fetch_all($result);

// Specify the output file path
$outputFile = "/path/to/output.csv";

// Open the output file in write mode
$file = fopen($outputFile, "w");

// Write the column names to the CSV file
$columnNames = array_keys($rows[0]);
fputcsv($file, $columnNames);

// Write each row to the CSV file
foreach ($rows as $row) {
    fputcsv($file, $row);
}

// Close the output file
fclose($file);

// Close the database connection
pg_close($conn);

echo "Exported query result to CSV successfully.";
?>

In this example, we first set up a connection to the PostgreSQL database using the pg_connect() function. Make sure to provide the correct host, database, username, and password parameters.

We execute the query using the pg_query() function and obtain the result resource.

We fetch all rows from the query result using the pg_fetch_all() function. This returns an array of associative arrays, where each associative array represents a row.

We specify the output file path where we want to save the CSV file.

We open the output file in write mode using the fopen() function.

We write the column names to the CSV file by extracting them from the first row and using the fputcsv() function.

We iterate over the rows and write each row to the CSV file using the fputcsv() function.

Finally, we close the output file using the fclose() function and close the database connection using the pg_close() function.

You can modify the query to select specific columns or add any additional filtering or sorting criteria as needed.

Exporting to CSV Using Ruby

Ruby is a dynamic, object-oriented scripting language known for its simplicity and readability. It provides libraries and gems for interacting with databases, including PostgreSQL. We can use the pg gem in Ruby to connect to a PostgreSQL database and execute queries.

To export a PostgreSQL query result to CSV using Ruby, follow these steps:

1. Install the pg gem if you haven't already done so by running gem install pg in your terminal.

2. Require the pg gem in your Ruby script.

3. Set up a connection to the PostgreSQL database using the PG::Connection.new method. Provide the correct host, port, database, username, and password parameters.

4. Execute the query using the connection.exec method.

5. Fetch all rows from the query result using the result.values method.

6. Specify the output file path where you want to save the CSV file.

7. Open the output file in write mode using the File.open method.

8. Write the column names to the CSV file.

9. Iterate over the rows and write each row to the CSV file.

10. Close the output file.

Here's an example of how to export a PostgreSQL query result to CSV using Ruby:

require 'pg'
require 'csv'

# Set up a connection to the PostgreSQL database
connection = PG::Connection.new(
  host: 'your_host',
  port: 'your_port',
  dbname: 'your_database',
  user: 'your_user',
  password: 'your_password'
)

# Execute the query
result = connection.exec('SELECT * FROM table_name')

# Fetch all rows from the query result
rows = result.values

# Specify the output file path
output_file = '/path/to/output.csv'

# Write the query result to CSV
CSV.open(output_file, 'w') do |csv|
  csv << result.fields  # Write column names
  rows.each { |row| csv << row }  # Write rows
end

puts 'Exported query result to CSV successfully.'

# Close the connection
connection.close

In this example, we first require the pg gem and the csv module in Ruby.

We set up a connection to the PostgreSQL database using the PG::Connection.new method. Make sure to provide the correct host, port, database, username, and password parameters.

We execute the query using the connection.exec method and obtain the PG::Result object.

We fetch all rows from the query result using the result.values method. This returns an array of arrays, where each inner array represents a row.

We specify the output file path where we want to save the CSV file.

We open the output file in write mode using the File.open method.

We write the column names to the CSV file by accessing the result.fields property and using the << operator.

We iterate over the rows and write each row to the CSV file using the << operator.

Finally, we close the output file using the close method and close the database connection using the connection.close method.

You can modify the query to select specific columns or add any additional filtering or sorting criteria as needed.

Exporting to CSV Using Node.js

Node.js is a JavaScript runtime built on Chrome's V8 JavaScript engine. It allows you to run JavaScript on the server-side and provides an event-driven, non-blocking I/O model that makes it lightweight and efficient. There are various libraries and modules available for working with databases in Node.js, including PostgreSQL. We can use the pg module in Node.js to connect to a PostgreSQL database and execute queries.

To export a PostgreSQL query result to CSV using Node.js, follow these steps:

1. Install the pg module if you haven't already done so by running npm install pg in your terminal.

2. Require the pg module in your Node.js script.

3. Set up a connection to the PostgreSQL database using the pg.Pool class. Provide the correct connection parameters such as host, port, database, username, and password.

4. Execute the query using the pool.query method.

5. Fetch all rows from the query result using the result.rows property.

6. Specify the output file path where you want to save the CSV file.

7. Create a writable stream using the fs.createWriteStream method.

8. Write the column names to the CSV file.

9. Iterate over the rows and write each row to the CSV file.

10. Close the writable stream.

Here's an example of how to export a PostgreSQL query result to CSV using Node.js:

const { Pool } = require('pg');
const fs = require('fs');
const csvWriter = require('csv-write-stream');

// Set up a connection pool to the PostgreSQL database
const pool = new Pool({
  user: 'your_user',
  host: 'your_host',
  database: 'your_database',
  password: 'your_password',
  port: 5432,
});

// Execute the query
pool.query('SELECT * FROM table_name', (error, result) => {
  if (error) {
    console.error('Error executing query:', error);
    return;
  }

  // Fetch all rows from the query result
  const rows = result.rows;

  // Specify the output file path
  const outputFile = '/path/to/output.csv';

  // Create a writable stream
  const writer = csvWriter();

  // Pipe the writable stream to the output file
  writer.pipe(fs.createWriteStream(outputFile));

  // Write the column names to the CSV file
  writer.write(Object.keys(rows[0]));

  // Write each row to the CSV file
  rows.forEach((row) => {
    writer.write(Object.values(row));
  });

  // Close the writable stream
  writer.end();

  console.log('Exported query result to CSV successfully.');

  // Release the connection from the pool
  pool.end();
});

In this example, we first require the pg, fs, and csv-write-stream modules in Node.js.

We set up a connection pool to the PostgreSQL database using the pg.Pool class. Make sure to provide the correct connection parameters such as host, port, database, username, and password.

We execute the query using the pool.query method and obtain the query result in the callback function.

We fetch all rows from the query result using the result.rows property.

We specify the output file path where we want to save the CSV file.

We create a writable stream using the csv-write-stream module and the fs.createWriteStream method.

We write the column names to the CSV file by accessing the keys of the first row and using the writer.write method.

We iterate over the rows and write each row to the CSV file by accessing the values of each row and using the writer.write method.

Finally, we close the writable stream using the writer.end method and release the connection from the pool using the pool.end method.

You can modify the query to select specific columns or add any additional filtering or sorting criteria as needed.

Exporting to CSV Using Java

Java is a popular programming language for building enterprise-scale applications. It provides a rich ecosystem of libraries and frameworks for working with databases, including PostgreSQL. We can use the JDBC (Java Database Connectivity) API in Java to connect to a PostgreSQL database and execute queries.

To export a PostgreSQL query result to CSV using Java, follow these steps: