### PostgreSQL stuff

roles = same as user accounts
roles should be added if using ident and system usernames
normal SQL commands function as with MySQL!

### tweaking
* shared_buffers = 1/4 of system memory
* checkpoint_segments = 32 - 256 (each is 16MB); larger sizes take a dbase longer to recover
* checkpoint_completion_target = 0.9 (default 0.5); should be 90% finished before next segment arrives


### server installation stuff
* accept connections from everywhere using "*" within postgresql.conf
* edit pg_hba.conf and use "md5" and remove "ident" and also place in IP ranges
* enable syslogging desires through postgresql.conf

### configuration via postgresql.conf
select name,unit,current_setting(name) from pg_settings where source='configuration file'; = shows all configured settings.
* fsync = can be very bad, high disk IO because OS must buffer each write on OS
* checkpoint_segments should be higher for db servers with high writes; very resource intensive 
* shared_buffers is reflected in each postmaster process
* effective_Cache_size = estimate of available memory for disk cache; 1/2 of total memory is good 3/4 is more aggressive. should include memory for db as well
* work_mem = should increase this for complex sorts; multiple queries take the value and multiply it, i.e. 8 sorts x 50 MB..
* maintenance_work_mem = used for operations like vacuum; large values don't help

### psql command line client

psql -l = list all databases and exit
psql -U user -h HOST -W = connect to postgre using user and host and force password prompt
psql -U user -h HOST -d dbase -W = connect to specific db with specific user account
psql -U user -h host -d dbbase -W -c 'sql command' = connect to db and run SQL command and exit

-A = no table alignment in output
-t = no columns in output
-F string = set field separator output, defaults to |
-R string = set record separator output, defaults to newline

### dumps

* dumping from mysql use mysqldump -c -e --compatible=postgresql --no-create-info --skip-quote-names --skip-add-locks (use create info to create schema)
* use psql -f dump.sql when restoring from "plain dumps"
* use pg_restore when restoring from other dump formats (custom|tar)
* do not specify host or username when dumping a db from a trust in pg_hba.conf
pg_dump dbname > dbname.sql
pg_dump -U desantis -h db2.rc.usf.edu -W -t tablename dbname > dbname.sql = dump database table over network with credentials
pg_dump -U desantis -h db2.rc.usf.edu -W dbname > dbname.sql = dump entire database over network
pg_dump -f blah.sql -b -x -c = dumps a database with cleaning first (avoids duplicate key errors, etc.); basically ensures errorless restores 


### postgre "shell"
\h = show SQL help
\h alter user = obtain specific help
\? = show postgre specific shell help
\q = quit
\l = list all databases
\du = show all users with attributes
\dp = show all privileges
\d = show all tables within database
\d table = show all columns from database

### PostgreSQL specifics

* create user user with password '...' = create a user and password and assign LOGIN role; other options available
* alter user user login = allow user to login from a created role
* show all = show all runtime parameters
* select col1,col2 from table where col ~ 'regex' = select data using regex; numeric-only tables do not work
* alter table tbl drop constraint blah_key = removes column primary key/unique values
* where in uses single quotes
* Database/User OID's are obtained from pg_stat_activity table
* Table OID's are obtained from pg_stat_(sys|user)_tables table
* grant syntax must be specific, i.e. grant all on database/table
* insert a "NOT NULL" value by skipping the field during an insert/update
* select exists (statement); returns "t" or "f"
* use pg_ctl reload -s to re-read config (pg_hba/ident) files without restarting postgresql
* reset passwords vi using a local trust for the user/db in question via pg_hba.conf

### PostgreSQL joins
select table.field, table2.field, table3.field from table,table2,table3 where table.field = table2.field and table3.field

### "Passwordless" dumping
* create ~/.pgpass (600) with the following format host:port:database:username:password
* must explicitly supply host and user details in order to dump via a script

### PostgreSQL monitoring (use select * or select col1,col2,etc.. to query specific values)

* pg_stat_activity = one row per server process, similar to MySQL's "show full processlist"
* pg_stat_database = one row per database, shows specific db information such as deleted rows, updated rows, fetched rows, returned rows, etc.
* select name,unit,current_setting(name) from pg_settings where source='configuration file';
* pg_Class = table which contains "relfilename", the name of the file in the "data" directory; use for time stamps, etc.

### postgre grants

* grant all privileges on database dbname to useraccount = grants all privileges on said dbase to said user
* alter database dbname owner to user = alter the database owner to a specific user
* specific notation below; all privileges implies (arwdDxt)
r -- SELECT ("read")
w -- UPDATE ("write")
a -- INSERT ("append")
d -- DELETE
D -- TRUNCATE
x -- REFERENCES
t -- TRIGGER
X -- EXECUTE
U -- USAGE
C -- CREATE
c -- CONNECT
T -- TEMPORARY

### Important stuff
* do not mix quotes; use either double or single
* after installing postgresql-server, run service postgresql initdb to install configuration files
* encoding should be set via creation of database, but can be set with client_encoding
* mass delete using delete from tbl_name where in (select...)


### selecting a date range
select blah from blah where field > now() - interval'n days|months|years' and field <= now()

### numeric types
add ::TYPE(p,s) to rows themselves or after aggregate functions to control precision output
* select sum(field::TYPE)
* select avg(field)::TYPE(p,s)

### unique records
select distinct blah = for single records
select distinct on (table.field) = for possible duplicates in returns when using joins; order by must specify distinct; must have record returned in output

### PostgreSQL arrays
* indexes start at '1' vs '0'
* character/text arrays should be declared as test[]
* multidimensional arrays are created as [][]...
* use select array_length(col,offset) to get length of array

### Transactions
BEGIN; = start a transaction
COMMIT; = end transaction

### PostgreSQL views
create view name as query
create view view_user_cputime_new as SELECT sum(sge_job_usage.ju_cpu) AS cpu_time, sge_job.j_owner AS owner FROM sge_job_usage JOIN sge_job ON sge_job_usage.ju_parent = sge_job.j_id GROUP BY sge_job.j_owner;