Difference between revisions of "Useful SQL"

Latest revision as of 15:30, 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;

To see the last run and the run interval:

  select abstime(last_run), period_in_minutes, task from ticker;

@@ Line 1: / Line 1: @@
+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:
-    psql -U spam spam -c "SELECT * FROM settings WHERE variable = 'BayesDBFormat';"
+    SELECT * FROM settings WHERE variable = '<variable name>';
 Delete domain whitelists:
-    psql -U spam -c "DELETE FROM domain_action WHERE action = 'allow-always'"
+    DELETE FROM domain_action WHERE action = 'allow-always';
 Delete sender whitelists:
-    psql -U spam -c "DELETE FROM sender_action WHERE action = 'allow-always'"
+    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);
+    SELECT length(msg) FROM held_messages WHERE incident_id in (<incident id>, <incident id>, ...);
 Find count of messages in incidents table
-    psql -U postgres spam SELECT status, count(*) FROM incidents group BY status;
+    SELECT status, count(*) FROM incidents group BY status;
+To see the last run and the run interval:
+   select abstime(last_run), period_in_minutes, task from ticker;
 <div style="float:right; clear:both; margin-right:0.5em">[[Support Wiki | [Home]]]</div>
 [[category:All]][[category:Troubleshooting]]