Database: dump query as CSV

Someone from business development asking you to dump a query as a csv from the database? You can run the query and output it as a CSV using the command line. If you’re using postgres you can first connect to psql and then run:

    COPY
    (SELECT * from users WHERE active = 'true')
    TO '/files/active_users.csv'
    WITH (FORMAT CSV, HEADER);

That will put the file with headers on your system at the absolute path that you specify. It is important to note that you can use relative paths so ./my-folder/file.csv would not work and psql would complain.

If you’re running MySQL, you connect to the mysql CLI and then run:

    SELECT *
    FROM users
    INTO OUTFILE '/var/lib/mysql-files/users.csv'
    FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
    LINES TERMINATED BY '\n';

Note that this will not output the file with headers. If you want to do that, it’s a bit more complicated…You’ll have to generate a custom query to specify the column names and add those (via a UNION) to the query:

SELECT 'id','name','active','created_at','updated_at'

UNION ALL

SELECT *
FROM users
INTO OUTFILE '/var/lib/mysql-files/users.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Read more about MySQL Read more about PostgreSQL

Instagram Post