Skip to content

Instantly share code, notes, and snippets.

@johnnyjung
Created May 23, 2012 21:26
Show Gist options
  • Save johnnyjung/2777934 to your computer and use it in GitHub Desktop.
Save johnnyjung/2777934 to your computer and use it in GitHub Desktop.
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
@johnnyjung
Copy link
Author

pg_dump --table fieldgoal_old -U basketball -h remy.isi.edu -f fieldgoal.sql -O
\i. fieldgoal.sql

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment