Skip to content

Instantly share code, notes, and snippets.

@Jeetah
Jeetah / https-localhost.md
Created Oct 6, 2021 — forked from cecilemuller/2019-https-localhost.md
How to create an HTTPS certificate for localhost domains
View https-localhost.md

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).

View check_unused_indices.sql
/* 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
View check_missing_FK_indices.sql
/* 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
Jeetah / betterTouchTool_mic_control.scpt
Created Mar 26, 2021
BetterTouchTool Apple Script for muting/unmuting microphone
View betterTouchTool_mic_control.scpt
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"
View random_string.sql
CREATE OR REPLACE VIEW public.vw_random_string AS
SELECT substr(string_agg(substr('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'::text, ceil(random() * 62::double precision)::integer, 1), ''::text), 0, 10) AS rand_str
FROM generate_series(1, 45) generate_series(generate_series);
@Jeetah
Jeetah / median_function.sql
Last active Jul 16, 2020
SQL Function: Median
View median_function.sql
CREATE OR REPLACE FUNCTION _final_median(NUMERIC[])
RETURNS NUMERIC AS
$$
SELECT AVG(val)
FROM (
SELECT val
FROM unnest($1) val
ORDER BY 1
LIMIT 2 - MOD(array_upper($1, 1), 2)
OFFSET CEIL(array_upper($1, 1) / 2.0) - 1
@Jeetah
Jeetah / pg_duplicate_indices.sql
Last active Apr 20, 2018
Postgres: List duplicate indices
View pg_duplicate_indices.sql
WITH indexes AS (
SELECT
trel.relname AS table_name,
irel.relname AS index_name,
string_agg(a.attname, ', ' ORDER BY c.ordinality) AS columns
FROM pg_index AS i
JOIN pg_class AS trel ON trel.oid = i.indrelid
JOIN pg_class AS irel ON irel.oid = i.indexrelid
JOIN pg_attribute AS a ON trel.oid = a.attrelid
JOIN LATERAL unnest(i.indkey)
@Jeetah
Jeetah / pg_deadlocks.sql
Last active Jul 19, 2021
Postgres: Deadlocks (list details of blocking and blocked)
View pg_deadlocks.sql
with ld as (
select pid, array_agg(lock_detail) as lock_details
from (
select l.pid, concat(l.mode, ' on ', l.locktype, ' (Table: ', t.relname,')') as lock_detail
from pg_catalog.pg_locks l
left outer join pg_stat_all_tables t on t.relid = l.relation
where t.relname not like 'pg_%'
) as details
group by pid
)
@Jeetah
Jeetah / ConversationStarters.txt
Last active Apr 20, 2018
Conversation Starters (für Parties, Hochzeiten, Kennen Lernen, Meetings, AfterWork, ...). Auf deutsch.
View ConversationStarters.txt
Welches Spielzeug sollte es heute noch geben?
Was waren deine ersten 3 CDs/Platten?
Wenn du dich auf 3 Nahrungsmittel beschränken müsstest - welche?
Wenn du ein eigenes Flugzeug für einen Urlaub hättest - wohin würdest du fliegen?
An welchen historischen Moment erinnerst du dich am meisten?
@Jeetah
Jeetah / killProcessWithName.sh
Last active Apr 20, 2018
Kill process by name shell script
View killProcessWithName.sh
#!/bin/bash
set -euo pipefail
PROCESS_NAME=$1
PID_PRC=`ps aux | grep '${PROCESS_NAME}' | grep -v grep | awk '{print $2}'`
if [ -n "$PID_PRC" ]
then
echo "Found running process -> killing PID: '$PID_PRC'"
kill $PID_PRC
else
echo "Process not running"