====== Postgres Notes ======
  * [[http://www.postgresql.org/docs/|Postgres Documentation]]
  * [[http://www.pgadmin.org|pgAdmin]] - GUI administration tool for Postgres
  * Postgres has a binary windows installer package, which includes pgAdmin
  * There is a system path warning for conflicts with Cygwin; if Cygwin is installed, some dll's won't mix well
  * Installed as a service on Win32, creating a user'postgres' with a randomly generated password
  * port 5432, checked box to accept connections from all hosts
  * pgAdmin has a tips file at 'pgadmin III/docs/en_US/tips.txt'
  * database encoding warning - default database encoding is SQL_ASCII, but many files are UTF8 or other ecoding; make sure to specify an encoding with the createdb commands below
  * restoring a database from a sql script file (such as example databases and database dumps):
    * createdb dbname (enter) (then enter your password)
    * psql -f db_sql_file.sql [-d] dbname (enter) (enter your password)
    * you may also want to set the encoding: createdb --encoding UTF8 dbname
  * [[http://www.sqlmanager.net/en/products/postgresql/manager|EMS SQL Manager Lite]] is free - available for MySQL, Postgres, SQL Server, Oracle
  * Postgres on the Mac: http://developer.apple.com/internet/opensource/postgres.html
  * list available databases:
    * psql -l
    * psql -l -h myserver.com
    * psql -l -h myserver.com -U postgres
  * backticks (`) don't work in Postgres (commonly used in MySQL)
  * common primary key type in MySQL: "int(11) NOT NULL auto_increment" in Postgres is "serial", and a sequence is automatically created for the increment
  * type blob is bytea in Postgres
  * in expressions, _ (underscore) matches any single character, and % (percent) matches any string of zero or more characters
  * escapes and string constants are covered in section 4.1.2.1 of the Postgres documentation; to escape a single quote, precede it with another: ''
  * by default, for security reasons, Postgres does not listen on all available IP addresses; this needs enabled in data/postgresql.conf:
    * listen_addresses = '*'
  * to access the db from another host, you must edit data/pg_hba.conf
    * to add a local subnet, use something like: host all all 192.168.0.0/24 md5
    * be careful with any lines ending with 'trust', because those hosts will be allowed access unconditionally, without asking for a password
  * to reload PostgreSQL, such as for configuration file changes, you can use:<code>
pg_ctl reload  [-D DATADIR] [-s]
</code>

===== Queries =====
  * ILIKE "%rob" - case insensitive search
  * ORDER BY lower(colname) - case insensitive order by for colname
  * || (double pipe) - like CONCAT from MySQL: firstname || ', ' || lastname AS name
  * sequence functions:
    * CURVAL
    * NEXTVAL
    * SETVAL
  * SERIAL - auto creates sequence file and proper ordering; note that primary key needs specified as of recent Postgres versions
  * INTEGER