Administration

To do some administration: su - postgres

  • createuser - USER -- creates a user (what about password?)
  • dropuser
  • createdb DB -E UTF8 -- creates a database (use UTF8 instead of ASCII_SQL). Also see CREATE DATABASE below.
  • dropdb

psql

Then, to operate on the database, launch psql -U user

(actually, using pgAdminIII or Tora helps a lot)

  • \h \?: help
  • \g: go
  • \i FILE: readfromfile
  • \q: quit
  • \l show databases
  • \d: show tables
  • \d table: describe table
  • \di table: describe indexes for table

SQL

  • HAVING: just like WHERE except uses aggregate functions. Never use aggregates with WHERE, always use with HAVING aggregates: min, max, avg, count, sum
  • Use subselects to query data: SELECT city FROM weather WHERE temp_lo = (SELECT min(temp_lo) FROM weather);
  • WHERE city LIKE 'P%' -- selects cities beginning with P
  • UNION, INTERSECT, EXCEPT;
  • CREATE DATABASE db OWNER user; -- how do I specify encoding?
  • CREATE TABLE table (NAME TYPE, ...);
  • INSERT INTO table (fields) VALUES (values);
  • COPY table FROM '/path/to/file/on/db/host' USING DELIMITERS '|';
  • UPDATE table SET field1=value1, field2=value2, ... WHERE condition;
  • DELETE FROM table WHERE condition;
  • SELECT [DISTINCT] col FROM table ORDER BY col;
  • SELECT * INTO TABLE temp FROM weather; -- redirect select
  • SELECT field AS name -- as is optional in SQL92 but not in Postgres
  • EXPLAIN ANALYZE query -- tells you what pg is doing, helps with optimization.

Thoughts

Ignore postgres inheritance. Just use STI instead.

Postgres does have arrays: int[4], text[][], etc. To insert into an array, use braces. But it uses one-based indexing!! Bleah. What were they thinking?