postgres

Posted on June 17, 2012 postgres
  • table is a named collection of rows.
  • each row of a given table has a the same set of named columns.
  • each column is a specific data type
  • columns have a fixed order in a row.
  • rows are not guaranteed to be in a certain order.
  • tables are grouped into databases
  • a collection of databases managed by a sinle server instance constitutes a database cluster.

standard types

  • int
  • smallint
  • real
  • double precision
  • char(N)
  • varchar(N)
  • date
  • time
  • timestamp
  • interval
  • additional postgres specific 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');
  • you can also do a bulk load from a flat file using the COPY command. COPY weather FROM ‘/home/mo/weather.txt’;

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