Postgres Cheat Sheet

postgres_cheat_sheet

Postgres Cheat Sheet
Source: Postgresql Documentation

### shell commands
	
	creatuser <user>
	deletesuer <user>
	createdb -O <user> -E utf8 -T <template> <db_name>
	dropdb <db_name>

### psql commands

	psql <db_name>		# connects to database using psql
	\h					# sql commands
	\?					# psql commands
	\q					# quit
	
	
### sql queries
	SELECT version();
	SELECT current_date;
	SELECT 2+2;
	
	CREATE DATABASE <name> WITH OWNER = <owner>;
	CREATE USER <user> with PASSWORD 'password';
	CREATE ROLE admin WITH CREATEDB CREATEROLE;
	
	CREATE TABLE weather (
	    city            varchar(80),
	    temp_lo         int,	-- low temperature
	    temp_hi         int,	-- high temperature
	    prcp            real,	-- precipitation
	    date            date
	); 
	
	CREATE TABLE cities (
		name            varchar(80),
		location        point
	);
	# can enter with line breaks
	# comments with -- and ends at the line break
	# varchar(80) = arbitrary character strings up to 80 chars in length
	# real = single precision floating-point numbers
	# int, smallint, real, double precision, char(N), varchar(N), date, time
	# timestamp, and interval

	DROP TABLE <table_name>;
	
	INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');
	
	INSERT INTO cities (name, location)
		VALUES ('San Francisco', '(-194.0, 53.0)');
	INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)
		VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');
	INSERT INTO weather (date, city, temp_hi, temp_lo)
		VALUES ('1994-11-29', 'Hayward', 54, 37);
	# require single quotes except for numeric values
	# list columns explicitly
	
	COPY weather FROM ’/home/user/weather.txt’; 
	# can copy from a file (pg. 1317)
	
	SELECT * FROM weather;						
	SELECT city, temp_lo, temp_hi, prcp, date FROM weather; 
	SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;
		
	# * is all columns
	# can set particular columns
	# can do expressions as use "AS" to relabel the output column
	
	SELECT * FROM weather
		WHERE city = 'San Francisco' AND prcp > 0.0;
	# select which rows are wanted
	# use boolean operators (AND, OR, NOT)
	
	SELECT * FROM weather ORDER BY city;
	SELECT * FROM weather ORDER BY city, temp_lo;
	SELECT DISTINCT city FROM weather;
	SELECT DISTINCT city FROM weather ORDER BY city;
	# DISTINCT removes duplicates
	# ORDER BY sorts the results
	
	SELECT * FROM weather, cities WHERE city = name;
	SELECT city, temp_lo, temp_hi, prcp, date, location
		FROM weather, cities
		WHERE city = name;
	SELECT weather.city, weather.temp_lo, weather.temp_hi,
	       weather.prcp, weather.date, cities.location
		FROM weather, cities
		WHERE cities.name = weather.city;
	SELECT *
	    FROM weather INNER JOIN cities ON (weather.city = cities.name);
	# inner join queries
	# can qualify columns and which table they belong to
	
	SELECT * FROM weather LEFT OUTER JOIN cities ON (weather.city = cities.name);
	# left outer join
	# left table will have all rows and only those that match in right table

	SELECT * FROM weather RIGHT OUTER JOIN cities ON (weather.city = cities.name);
	# right outer join	
	# right table will have all rows and only those that match in left table
	
	SELECT * FROM weather FULL OUTER JOIN cities ON (weather.city = cities.name);
	# both tables will have all rows listed...whether matched or not
	
	SELECT W1.city, W1.temp_lo AS low, W1.temp_hi AS high, 
		W2.city, W2.temp_lo AS low, W2.temp_hi AS high 
		FROM weather W1, weather W2
		WHERE W1.temp_lo < W2.temp_lo
		AND W1.temp_hi > W2.temp_hi;
	# self join
	# relabel weather table as W1, W2
	
	SELECT max(temp_lo) FROM weather;
	# aggregate functions - count, sum, avg, max, min
	
	SELECT city FROM weather
	    WHERE temp_lo = (SELECT max(temp_lo) FROM weather);
	# subquery
	
	SELECT city, max(temp_lo)
	    FROM weather
	    GROUP BY city;
	
	SELECT city, max(temp_lo)
	    FROM weather
		GROUP BY city
		HAVING max(temp_lo) < 40;
	
	SELECT city, max(temp_lo)
	    FROM weather
		WHERE city LIKE 'S%' 
		GROUP BY city
		HAVING max(temp_lo) < 40;
	# where = contains no aggregate functions
	# where selects input rows before groups and aggregates are computed
	# having = contains aggregate functions
	
	UPDATE weather
		SET temp_hi = temp_hi - 2, temp_lo = temp_lo-2
		WHERE date > '1994-11-28';
	# updating existing rows
	
	DELETE FROM weather WHERE city = 'Hayward';
	# deleting rows