Cacher is the code snippet organizer for pro developers

# Edit pg_hba.conf in vim
sudo vim /etc/postgresql/9.4/main/pg_hba.conf

# Near bottom of file after local rules, add rule (allows remote access):
hostssl    all             all             0.0.0.0/0               md5

# save file
# Edit config in vim
sudo vim /etc/postgresql/9.1/main/postgresql.conf

# Change line 59 to listen to external requests:
# from
  #listen_addresses = 'localhost'
# to
  listen_address='*'
  
# save file
# Restart postgres server to bring changes in effect
sudo /etc/init.d/postgresql restart

sudo apt-get update
sudo apt-get install postgresql postgresql-contrib


##### Switch over to the postgres account on your server by typing:

sudo -i -u postgres


##### You can now access a Postgres prompt immediately by typing:

psql

##### Exit out of the PostgreSQL prompt by typing:

\q

##### Accessing a Postgres Prompt Without Switching Accounts

sudo -u postgres psql


##### New User

createuser --interactive
or
sudo -u postgres createuser --interactive

##### Create a New Database

createdb sammy
or
sudo -u postgres createdb sammy

##### connect with diffrent user
psql -d mydb -U myuser

##### Change password
psql=#\password postgres

##### Granting privileges on database

##### Giving the user a password
$ sudo -u postgres psql
psql=# alter user <username> with encrypted password '<password>';

##### Granting privileges on database
psql=# grant all privileges on database <dbname> to <username> ;

##### Show

\l

##### connect

\c 

##### Create Table

CREATE TABLE table_name (
	column_name1 col_type (field_length) column_constraints,
	column_name2 col_type (field_length),
	column_name3 col_type (field_length)
);

Eg:

CREATE TABLE playground (
	equip_id serial PRIMARY KEY,
	type varchar (50) NOT NULL,
	color varchar (25) NOT NULL,
	location varchar(25) check (location in ('north', 'south', 'west', 'east', 'northeast', 'southeast', 'southwest', 'northwest')),
	install_date date
);

##### View Table

\d 			=All
\d <table name>=Specific

##### Delete Table

DROP TABLE <table_name>;

##### ADD AUTO INCREMENT
 ALTER TABLE users ADD COLUMN userid SERIAL
 ##### DELETE COLUMN 
 ALTER TABLE users DROP COLUMN userid;


UPDATE row:
UPDATE users SET email='admin@gmail.com' where username='finance';

Add constrain
ALTER TABLE users ADD UNIQUE (email);

Add new column
ALTER TABLE users ADD COLUMN email varchar(200) NOT NULL DEFAULT 0;