Difference between revisions of "Useful SQL"

From Roaring Penguin
Jump to: navigation, search
Line 1: Line 1:
 
PostgreSQL check a variable from command line:
 
PostgreSQL check a variable from command line:
   psql -U spam spam -c "select * from settings where variable = 'BayesDBFormat';"
+
   psql -U spam spam -c "SELECT * FROM settings WHERE variable = 'BayesDBFormat';"
 
Delete domain whitelists:
 
Delete domain whitelists:
 
   psql -U spam -c "DELETE FROM domain_action WHERE action = 'allow-always'"
 
   psql -U spam -c "DELETE FROM domain_action WHERE action = 'allow-always'"
 
Delete sender whitelists:
 
Delete sender whitelists:
 
   psql -U spam -c "DELETE FROM sender_action WHERE action = 'allow-always'"
 
   psql -U spam -c "DELETE FROM sender_action WHERE action = 'allow-always'"
 +
Find held message size for specific incidents
 +
  psql -U postgres spam SELECT length(msg) FROM held_messages WHERE incident_id in (518210, 518197, 519116);
 +
Find count of messages in incidents table
 +
  psql -U postgres spam SELECT status, count(*) FROM incidents group BY status;
 +
 
<div style="float:right; clear:both; margin-right:0.5em">[[Support Wiki | [Home]]]</div>
 
<div style="float:right; clear:both; margin-right:0.5em">[[Support Wiki | [Home]]]</div>
 
[[category:All]][[category:Troubleshooting]]
 
[[category:All]][[category:Troubleshooting]]

Revision as of 13:58, 14 May 2014

PostgreSQL check a variable from command line:

  psql -U spam spam -c "SELECT * FROM settings WHERE variable = 'BayesDBFormat';"

Delete domain whitelists:

  psql -U spam -c "DELETE FROM domain_action WHERE action = 'allow-always'"

Delete sender whitelists:

  psql -U spam -c "DELETE FROM sender_action WHERE action = 'allow-always'"

Find held message size for specific incidents

  psql -U postgres spam SELECT length(msg) FROM held_messages WHERE incident_id in (518210, 518197, 519116);

Find count of messages in incidents table

  psql -U postgres spam SELECT status, count(*) FROM incidents group BY status;