Database

Administration & Configuration Connections Show Max Connections (value and source) SELECT setting, source, sourcefile, sourceline FROM pg_settings WHERE name = 'max_connections'; Set Max Connections ALTER system SET max_connections = 250; Kill Connections for a Database SELECT pg_terminate_backend(pid) FROM pg_catalog.pg_stat_activity -- we don't want to kill our own connection WHERE pid != pg_backend_pid() -- we don't want to kill connections to other databases AND datname = 'MYDATABASE' Show Statement Timeout Settings for All Users SELECT r.rolname, (SELECT unnest(s.setconfig) FROM pg_db_role_setting s WHERE s.setrole = r.oid AND s.setconfig::text LIKE '%statement_timeout%' LIMIT 1) AS statement_timeout FROM pg_roles r WHERE r.rolcanlogin ORDER BY r.rolname; ...

Count unique / all values SELECT COUNT(DISTINCT <FIELDNAME>), COUNT(ALL <FIELDNAME>) FROM <TABLE>; Partially anonymize e-mail addresses UPDATE <TABLE_NAME> SET <EMAIL_FIELD>= INSERT( <EMAIL_FIELD>, POSITION('@' IN <EMAIL_FIELD>), 100, CONCAT(FLOOR(1 + (RAND() * 100)),'@hascode.com')) WHERE POSITION('@' IN <EMAIL_FIELD>)>0; Find duplicate entries SELECT COUNT(*), <FIELDNAME> FROM <TABLENAME> GROUP BY <FIELDNAME> HAVING COUNT(*)>1; MySQL Fix Zero Dates for Data Imports Error: ERROR 1067 (42000) at line 1234: Invalid default value for ‘datefield’ In newer MySQL Versions zero in date or zero dates are forbidden .. check this with: ...