# su -c "psql -d maillog -c \"copy (select date,sender,destination,subject from maillog where destination like '%domain.com%') to '/tmp/mails.csv' with csv;\" " postgres

Export a postgresql query into csv file

This command will "su" the execution of the command to the postgres user(implies that you are already logger as root), and export the result of the query to a file on the csv format. You'll need to adequate the fields and database information to one of your choice/need.
Sample Output
head -3 /tmp/mails.csv

2012-09-24 10:00:11.470321,sender@domainsender.com,"dest1@domain.com, dest2@domain.com",Alert:one host is down
2012-09-24 10:45:10.920158,sender@domainsender.com,"dest1@domain.com, dest2@domain.com",Alert:three hosts are down
2012-09-24 10:50:03.736794,sender@domainsender.com,"dest1@domain.com, dest2@domain.com",Alert:one host is down

0
By: Risthel
2013-02-13 13:03:17

These Might Interest You

  • Replace the credentials to psql if necessary, and the my-query part with your query. Show Sample Output


    0
    psql -U quassel quassel -c "SELECT message FROM backlog ORDER BY time DESC LIMIT 1000;" | grep my-query
    Tatsh · 2014-10-12 19:53:06 0
  • Benchmark a SQL query against MySQL Server. The example runs the query 10 times, and you get the average runtime in the output. To ensure that the query does not get cached, use `RESET QUERY CACHE;` on top in the query file. Show Sample Output


    0
    perf stat -r 10 sh -c "mysql > /dev/null < query.sql"
    particleflux · 2018-05-03 12:20:03 0
  • This command converts a MySQL query directly into a .csv (Comma Seperated Value)-file. Show Sample Output


    8
    echo "SELECT * FROM table; " | mysql -u root -p${MYSQLROOTPW} databasename | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > outfile.csv
    jacquesloonen · 2009-02-16 18:55:18 1

  • 0
    pg_dump -U postgres [nomeDB] > db.dump
    michelem · 2009-02-06 15:45:03 0
  • Add the followin to ~/.bashrc #colour export LESS_TERMCAP_mb=$'\E[01;31m' export LESS_TERMCAP_md=$'\E[01;37m' export LESS_TERMCAP_me=$'\E[0m' export LESS_TERMCAP_se=$'\E[0m' export LESS_TERMCAP_so=$'\E[01;44;33m' export LESS_TERMCAP_ue=$'\E[0m' export LESS_TERMCAP_us=$'\E[01;32m'


    15
    echo "export LESS_TERMCAP_mb=$'\E[01;31m'" >> ~/.bashrc
    totti · 2011-10-20 17:34:14 1
  • You might want to secure your AWS operations requiring to use a MFA token. But then to use API or tools, you need to pass credentials generated with a MFA token. This commands asks you for the MFA code and retrieves these credentials using AWS Cli. To print the exports, you can use: `awk '{ print "export AWS_ACCESS_KEY_ID=\"" $1 "\"\n" "export AWS_SECRET_ACCESS_KEY=\"" $2 "\"\n" "export AWS_SESSION_TOKEN=\"" $3 "\"" }'` You must adapt the command line to include: * $MFA_IDis ARN of the virtual MFA or serial number of the physical one * TTL for the credentials Show Sample Output


    1
    head -n1 | xargs -I {} aws sts get-session-token --serial-number $MFA_ID --duration-seconds 900 --token-code {} --output text --query [Credentials.AccessKeyId,Credentials.SecretAccessKey,Credentials.SessionToken]
    keymon · 2016-04-12 10:57:00 0

What do you think?

Any thoughts on this command? Does it work on your machine? Can you do the same thing with only 14 characters?

You must be signed in to comment.

What's this?

commandlinefu.com is the place to record those command-line gems that you return to again and again. That way others can gain from your CLI wisdom and you from theirs too. All commands can be commented on, discussed and voted up or down.

Share Your Commands



Stay in the loop…

Follow the Tweets.

Every new command is wrapped in a tweet and posted to Twitter. Following the stream is a great way of staying abreast of the latest commands. For the more discerning, there are Twitter accounts for commands that get a minimum of 3 and 10 votes - that way only the great commands get tweeted.

» http://twitter.com/commandlinefu
» http://twitter.com/commandlinefu3
» http://twitter.com/commandlinefu10

Subscribe to the feeds.

Use your favourite RSS aggregator to stay in touch with the latest commands. There are feeds mirroring the 3 Twitter streams as well as for virtually every other subset (users, tags, functions,…):

Subscribe to the feed for: