standard types

createdb mydb dropdb mydb

createdb * will default to creating a db named the same name as your user account.

Creating Tables

CREATE TABLE weather ( city varchar(80), temp_lo int, temp_hi int, prcp real, date date);

DROP TABLE weather;

Populating a table with rows.

INSERT INTO weather (city, temp_lo, temp_hi, prcp, date) VALUES ( 'San Francisco', 46, 50, 0.25, '1994-11-27');

Querying a table

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;
SELECT * FROM weather WHERE city = 'San Francisco' AND prcp > 0.0;
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;

JOINS

SELECT * FROM weather INNER JOIN cities ON (weather.city = cities.name);

Aggregate Functions

SELECT max(temp_lo) FROM weather;

cheat sheet

login to psql

psql -U postgres

list databases

db=# \l

list roles

db=# \du

connect to database

db=# \c <databasename>

list tables in connected database

db=# \dt

resources

comments powered by Disqus