Difference between revisions of "Useful SQL"

From Roaring Penguin
Jump to: navigation, search
Line 2: Line 2:
 
   psql -U spam spam -c "<actual command>"
 
   psql -U spam spam -c "<actual command>"
 
PostgreSQL check a variable from command line:
 
PostgreSQL check a variable from command line:
   SELECT * FROM settings WHERE variable = 'BayesDBFormat';
+
   SELECT * FROM settings WHERE variable = '<variable name>';
 
Delete domain whitelists:
 
Delete domain whitelists:
 
   DELETE FROM domain_action WHERE action = 'allow-always';
 
   DELETE FROM domain_action WHERE action = 'allow-always';
Line 8: Line 8:
 
   DELETE FROM sender_action WHERE action = 'allow-always';
 
   DELETE FROM sender_action WHERE action = 'allow-always';
 
Find held message size for specific incidents
 
Find held message size for specific incidents
   SELECT length(msg) FROM held_messages WHERE incident_id in (518210, 518197, 519116);
+
   SELECT length(msg) FROM held_messages WHERE incident_id in (<incident id>, <incident id>, ...);
 
Find count of messages in incidents table
 
Find count of messages in incidents table
 
   SELECT status, count(*) FROM incidents group BY status;
 
   SELECT status, count(*) FROM incidents group BY status;

Revision as of 15:28, 11 September 2015

You can run the following SQL commands from the commend line by prefixing them with

  psql -U spam spam -c "<actual command>"

PostgreSQL check a variable from command line:

  SELECT * FROM settings WHERE variable = '<variable name>';

Delete domain whitelists:

  DELETE FROM domain_action WHERE action = 'allow-always';

Delete sender whitelists:

  DELETE FROM sender_action WHERE action = 'allow-always';

Find held message size for specific incidents

  SELECT length(msg) FROM held_messages WHERE incident_id in (<incident id>, <incident id>, ...);

Find count of messages in incidents table

  SELECT status, count(*) FROM incidents group BY status;