Jeetah

CREATE FUNCTION array_distinct(
      anyarray, -- input array 
      boolean DEFAULT false -- flag to ignore nulls
) RETURNS anyarray AS $f$
      SELECT array_agg(DISTINCT x) 
      FROM unnest($1) t(x) 
      WHERE CASE WHEN $2 THEN x IS NOT NULL ELSE true END;
$f$ LANGUAGE SQL IMMUTABLE;

Jeetah

  CREATE FUNCTION count_estimate(query text) RETURNS integer AS $$
DECLARE
  rec   record;
  rows  integer;
BEGIN
  FOR rec IN EXECUTE 'EXPLAIN ' || query LOOP
    rows := substring(rec."QUERY PLAN" FROM ' rows=([[:digit:]]+)');
    EXIT WHEN rows IS NOT NULL;
  END LOOP;

Jeetah

SELECT substring(query, 1, 100) AS short_query,                                                                                                                                                                                                                                                                                                            
  round(total_time::numeric, 2) AS total_time,                                                                                                                                                                                                                                                                                                             
  calls,                                                                                                                                                                                                                                                                                                                                

Jeetah

SELECT
      relname, 100 * idx_scan / (seq_scan + idx_scan) percent_of_times_index_used, 
      n_live_tup rows_in_table
FROM
     pg_stat_user_tables
WHERE
     seq_scan + idx_scan \> 0
ORDER BY
     n_live_tup DESC;
     

Jeetah

git log --oneline --pretty=format:"%s (%cn - %ci)" $(git describe --tags --abbrev=0 --match "prod/*" $(git rev-list --tags="prod/*" --max-count=1 --skip=1))..$(git describe --tags --abbrev=0 --match "prod/*")

Jeetah

SELECT table_name,
       pg_size_pretty(total_size) AS size
FROM (
    SELECT table_name,
           pg_total_relation_size(table_schema || '.' || table_name) AS total_size
    FROM information_schema.tables
    WHERE table_schema = 'public' -- Replace with your schema name if needed
) AS table_sizes
ORDER BY total_size DESC;

Jeetah

How to create an HTTPS certificate for localhost domains

This focuses on generating the certificates for loading local virtual hosts hosted on your computer, for development only.

Do not use self-signed certificates in production ! For online certificates, use Let's Encrypt instead (tutorial).

Jeetah

/* Source and reasoning: https://www.cybertec-postgresql.com/en/get-rid-of-your-unused-indexes/ */
SELECT s.schemaname,
       s.relname AS tablename,
       s.indexrelname AS indexname,
       pg_relation_size(s.indexrelid) AS index_size
FROM pg_catalog.pg_stat_user_indexes s
   JOIN pg_catalog.pg_index i ON s.indexrelid = i.indexrelid
WHERE s.idx_scan = 0      -- has never been scanned
  AND 0 <>ALL (i.indkey)  -- no index column is an expression
  AND NOT i.indisunique   -- is not a UNIQUE index

Jeetah

/* Background and source: https://www.cybertec-postgresql.com/en/index-your-foreign-key/ */
SELECT c.conrelid::regclass AS "table",
       /* list of key column names in order */
       string_agg(a.attname, ',' ORDER BY x.n) AS columns,
       pg_catalog.pg_size_pretty(
          pg_catalog.pg_relation_size(c.conrelid)
       ) AS size,
       c.conname AS constraint,
       c.confrelid::regclass AS referenced_table
FROM pg_catalog.pg_constraint c

Jeetah

set inputVolume to input volume of (get volume settings)
if inputVolume = 0 then
	set inputVolume to 80
	tell application "BetterTouchTool"
		set_string_variable "micState" to "🔊Unmute"
	end tell
else
	set inputVolume to 0
	tell application "BetterTouchTool"
		set_string_variable "micState" to "🔇MUTED"