January 3, 2020 06:16 · September 12, 2019 07:46 · May 14, 2019 06:06 · December 17, 2018 07:35 · August 29, 2018 05:58 · August 29, 2018 05:19
 --- PSQL queries which also duplicated from https://github.com/anvk/AwesomePSQLList/blob/master/README.md
 --- some of them taken from https://www.slideshare.net/alexeylesovsky/deep-dive-into-postgresql-statistics-54594192

 -- I'm not an expert in PSQL. Just a developer who is trying to accumulate useful stat queries which could potentially explain problems in your Postgres DB.

 ------------
 -- Basics --
 ------------

 -- Get indexes of tables
 SELECT
    f.attnum AS number,
    c.relname AS table_name,
    f.attname AS column_name,
 FROM pg_attribute f
    JOIN pg_class c ON c.oid = f.attrelid
    JOIN pg_type t ON t.oid = f.atttypid
    LEFT JOIN pg_attrdef d ON d.adrelid = c.oid AND d.adnum = f.attnum
    LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
    LEFT JOIN pg_constraint p ON p.conrelid = c.oid AND f.attnum = ANY (p.conkey)
 $ brew update
 $ brew install hive
 -- One-to-many relationship

 CREATE TABLE customers (
 	customer_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 	first_name VARCHAR(255) NOT NULL,
 	last_name VARCHAR(255) NOT NULL,
 	email VARCHAR(255) NOT NULL,
 );


 CREATE TABLE `advisors` (
  `advisor_id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(255) NOT NULL,
  `last_name` varchar(255) NOT NULL,
  PRIMARY KEY (`advisor_id`)
 )

 CREATE TABLE `students` (
  `student_id` int(11) NOT NULL AUTO_INCREMENT,
 BEGIN;
 LOCK table_name;
 ALTER TABLE table_name ADD COLUMN column_new column_type;
 UPDATE table_name SET column_new = column_name;
 ALTER TABLE table_name DROP column_name;
 ALTER TABLE table_name RENAME column_new TO column_name;
 END;

 -- varchar -> integer
 --   UPDATE cpvbeacon_dev SET column_new = CAST (nullif(column_name, '') AS INTEGER);
 package http;

 import java.io.IOException;
 import java.io.UnsupportedEncodingException;
 import java.util.ArrayList;
 import java.util.List;

 import org.apache.http.HttpEntity;
 import org.apache.http.HttpResponse;
 import org.apache.http.NameValuePair;
	--- PSQL queries which also duplicated from https://github.com/anvk/AwesomePSQLList/blob/master/README.md
	--- some of them taken from https://www.slideshare.net/alexeylesovsky/deep-dive-into-postgresql-statistics-54594192

	-- I'm not an expert in PSQL. Just a developer who is trying to accumulate useful stat queries which could potentially explain problems in your Postgres DB.

	------------
	-- Basics --
	------------

	-- Get indexes of tables
	SELECT
	f.attnum AS number,
	c.relname AS table_name,
	f.attname AS column_name,
	FROM pg_attribute f
	JOIN pg_class c ON c.oid = f.attrelid
	JOIN pg_type t ON t.oid = f.atttypid
	LEFT JOIN pg_attrdef d ON d.adrelid = c.oid AND d.adnum = f.attnum
	LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
	LEFT JOIN pg_constraint p ON p.conrelid = c.oid AND f.attnum = ANY (p.conkey)
	-- One-to-many relationship

	CREATE TABLE customers (
	customer_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	first_name VARCHAR(255) NOT NULL,
	last_name VARCHAR(255) NOT NULL,
	email VARCHAR(255) NOT NULL,
	);

	CREATE TABLE `advisors` (
	`advisor_id` int(11) NOT NULL AUTO_INCREMENT,
	`first_name` varchar(255) NOT NULL,
	`last_name` varchar(255) NOT NULL,
	PRIMARY KEY (`advisor_id`)
	)

	CREATE TABLE `students` (
	`student_id` int(11) NOT NULL AUTO_INCREMENT,
	BEGIN;
	LOCK table_name;
	ALTER TABLE table_name ADD COLUMN column_new column_type;
	UPDATE table_name SET column_new = column_name;
	ALTER TABLE table_name DROP column_name;
	ALTER TABLE table_name RENAME column_new TO column_name;
	END;

	-- varchar -> integer
	-- UPDATE cpvbeacon_dev SET column_new = CAST (nullif(column_name, '') AS INTEGER);
	package http;

	import java.io.IOException;
	import java.io.UnsupportedEncodingException;
	import java.util.ArrayList;
	import java.util.List;

	import org.apache.http.HttpEntity;
	import org.apache.http.HttpResponse;
	import org.apache.http.NameValuePair;