SQL

Postgres psql

Command	Description
\timing	Turn on query timing
\x	Turn on transposed results table (for large schemas)
\q	Exit psql
\d+	List tables
\d+ table_name	Describe table
\l+	List databases
\c db_name	Connect to database
\?	List psql commands

Useful diagnostics

See more here: https://gist.github.com/anvk/475c22cbca1edc5ce94546c871460fdd

List nonzero table/index sizes:

select relname, pg_size_pretty(pg_total_relation_size(relname::regclass)) as full_size, pg_size_pretty(pg_relation_size(relname::regclass)) as table_size, pg_size_pretty(pg_total_relation_size(relname::regclass) - pg_relation_size(relname::regclass)) as index_size from pg_stat_user_tables order by pg_total_relation_size(relname::regclass) desc limit 100;

List database sizes:

select datname, pg_size_pretty(pg_database_size(datname)) from pg_database order by pg_database_size(datname);

List open connections:

SELECT * FROM pg_stat_activity WHERE datname = 'dbname';

Examples

Most X of the Day

SELECT X, count(X)
FROM some_table
WHERE some_condition AND date_column BETWEEN '2016-04-01 00:00:00' AND '2016-04-02 00:00:00'
GROUP BY X
ORDER BY COUNT(X) DESC

Top X of the day (Historical)

SELECT date, X, MAX(count)
FROM (SELECT DATE(date_column) as date, X, COUNT(X) as count
      FROM some_table
      WHERE some_condition
      GROUP BY DATE(date_column), symbol
      ORDER BY DATE(date_column) DESC, COUNT(X) DESC
) AS whatever
GROUP BY date
ORDER BY date DESC, count DESC

Rows per date

SELECT DISTINCT(DATE(date_column)), count(date_column)
FROM some_table
GROUP BY DATE(date_column)

Distinct values per group per day + multiple filters per column + since date

SELECT DATE(date_column), grouped_column, COUNT(distinct distinct_column), count(*)
FROM table
WHERE some_column = "value"
AND (other_column LIKE "%asdf%" OR other_column LIKE "%qwer%")
AND date_column > "1984-01-01 00:00:00"
GROUP BY DATE(date_column), grouped_column
ORDER BY DATE(date_column) DESC

Find broken foreign keys (no corresponding foreign record)

SELECT * FROM
(
  SELECT thing.id AS thing_id, thing.foreign_key AS thing_foreign_key, foreign_thing.key AS foreign_thing_key
  FROM thing
  LEFT JOIN foreign_thing ON thing.foreign_key = foreign_thing.key
) AS TEMP_TABLE
WHERE TEMP_TABLE.foreign_thing_key is NULL

Ex:

SELECT * FROM
(
  SELECT book.id AS book_id, book.author_id AS book_author_id, author.id AS author_id
  FROM book
  LEFT JOIN author ON book.author_id = author.id
) AS TEMP_TABLE
WHERE TEMP_TABLE.author_id is NULL