docs:postgres:postgres_notes

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Next revision		 Previous revision
docs:postgres:postgres_notes [2007/09/03 16:39] – created billhdocs:postgres:postgres_notes [2008/08/03 00:25] (current) – external edit 127.0.0.1
Line 1: Line 1:
 ====== Postgres Notes ====== ====== Postgres Notes ======
-FIXME random notes - some need clarification or deletion+  * [[http://www.postgresql.org/docs/|Postgres Documentation]]
   * [[http://www.pgadmin.org|pgAdmin]] - GUI administration tool for Postgres   * [[http://www.pgadmin.org|pgAdmin]] - GUI administration tool for Postgres
   * Postgres has a binary windows installer package, which includes pgAdmin   * Postgres has a binary windows installer package, which includes pgAdmin
Line 10: Line 10:
   * restoring a database from a sql script file (such as example databases and database dumps):   * restoring a database from a sql script file (such as example databases and database dumps):
     * createdb dbname (enter) (then enter your password)     * createdb dbname (enter) (then enter your password)
-    * pgsql -f db_sql_file.sql [-d] dbname (enter) (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     * 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   * [[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   * Postgres on the Mac: http://developer.apple.com/internet/opensource/postgres.html
   * list available databases:   * list available databases:
-    * pgsql -l +    * psql -l 
-    * pgsql -l -h myserver.com +    * psql -l -h myserver.com 
-    * pgsql -l -h myserver.com -U postgres+    * psql -l -h myserver.com -U postgres
   * backticks (`) don't work in Postgres (commonly used in MySQL)   * 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   * 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
Line 27: Line 27:
   * to access the db from another host, you must edit data/pg_hba.conf   * 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     * 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
  • docs/postgres/postgres_notes.1188859160.txt.gz
  • Last modified: 2008/08/03 00:25
  • (external edit)