RetroSearch Browse

Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://www.w3resource.com/PostgreSQL/snippets/how-to-save-postgresql-output-to-a-csv-file.php below:

Website Navigation

How to save PostgreSQL PL/pgSQL Output to a CSV File

How to save PostgreSQL PL/pgSQL Output to a CSV FileLast update on December 23 2024 07:38:59 (UTC/GMT +8 hours)

Exporting PL/pgSQL Output to a CSV File in PostgreSQL

In PostgreSQL, you can save the output of a SELECT query or any result from a PL/pgSQL function to a CSV file. This can be done using the COPY command, which is efficient for exporting query results directly to a file. This guide covers how to save PL/pgSQL output to a CSV file step-by-step.

1. Using the COPY Command for Direct Export

The COPY command can export data directly to a CSV file. You can use it for any query that produces output, including within PL/pgSQL functions.

Syntax:

COPY (SELECT column1, column2 FROM table_name WHERE condition) 
TO '/path/to/your/file.csv' 
WITH (FORMAT CSV, HEADER);

TO '/path/to/your/file.csv': Specifies the path and filename where you want to save the output.
WITH (FORMAT CSV, HEADER): Exports in CSV format and includes a header row.

Example Code:

-- Export the result of a query to a CSV file
COPY (SELECT id, name, email FROM users WHERE active = true)  -- Select data to export
TO '/tmp/active_users.csv'        -- Define file path
WITH (FORMAT CSV, HEADER);    -- Specify CSV format with header

Explanation:

COPY (SELECT id, name, email FROM users WHERE active = true): Executes a query on the users table to select only active users.
TO '/tmp/active_users.csv': Saves the output in a file named active_users.csv located in the /tmp directory.
WITH (FORMAT CSV, HEADER): Exports the result in CSV format and includes a header with column names.

2. Using psql to Export Query Results to CSV

You can run COPY commands directly within the psql command-line tool to export data to a CSV file without using a PL/pgSQL function.

Example Code:

# Run psql and export data to CSV
psql -U postgres -d your_database -c "\COPY (SELECT * FROM your_table) TO '/path/to/file.csv' CSV HEADER"

Explanation:

psql -U postgres -d your_database: Connects to PostgreSQL as the specified user and database.
\COPY (SELECT * FROM your_table) TO '/path/to/file.csv' CSV HEADER: Exports the results to a CSV file with headers.

3. Using PL/pgSQL Function to Generate CSV Data

You can also use a PL/pgSQL function to generate specific data, and then export the output with COPY.

Example Code:

CREATE OR REPLACE FUNCTION export_active_users_to_csv() RETURNS void AS $$
DECLARE
BEGIN
    -- Export query result to a CSV file
    PERFORM COPY (SELECT id, name FROM users WHERE active = true)  -- Fetch active users
    TO '/tmp/active_users.csv'                                     -- Define file path
    WITH (FORMAT CSV, HEADER);                                     -- Specify CSV format with header
END;
$$ LANGUAGE plpgsql;

Explanation:

CREATE OR REPLACE FUNCTION export_active_users_to_csv(): Creates a function that exports active users to a CSV file.
COPY (SELECT id, name FROM users WHERE active = true) TO '/tmp/active_users.csv' WITH (FORMAT CSV, HEADER);: Executes a COPY command within the function, exporting only active users to a CSV file.

Important Notes

File Path Permissions: Ensure PostgreSQL has permission to write to the specified file path. Absolute paths are recommended.
Using psql with \COPY: If running the command as a regular user with psql, use \COPY instead of COPY to save files to the local system.
Automated Exports: Schedule PL/pgSQL functions with cron or similar tools to automate regular exports.

All PostgreSQL Questions, Answers, and Code Snippets Collection.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4