How to setup and start working on Postgres

These steps are for mac users. Though steps are common for other OS except installation process.


Install Postgres
--------------------
brew install postgres(Only for Mac OS)

Check Postgres Installed

--------------------------------

postgres -V

Initialise Postgres
-----------------------
pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start

To check status
---------------------
pg_ctl -D /usr/local/var/postgres status

To enter postgres command line
-----------------------------------------
psql postgres

Create User/Role

--------------------

There are 2 ways to do this:

1. Postgres has a shell command called createuser which will create a user for Postgres. Use it right from the OSX terminal command line like this:

createuser --pwprompt demouser; 

If you wish to create a user without a password just take the --pwprompt off the command.

2.  From postgres command line console:
CREATE ROLE demouser WITH LOGIN PASSWORD '12345'; 

Create Db and assign access to user
-------------------------------------------------
There are 2 ways to do:

1. createdb -Odemouser -Eutf8 myrecordDB;   
The -O indicates the user that will become the owner of the database.

2. create database demodb owner demoUser;

Access the Database 

-------------------------------
psql -U demouser -W bookstores;

The -U means to login using that username and the -W means to prompt for a password.

Create Table
-------------------
Now create table inside DB, Example:

CREATE TABLE demotable (did integer, name varchar(40), PRIMARY KEY(did));
CREATE TABLE demotable (did integer PRIMARY KEY, name varchar(40));

Other Useful commands
---------------------------------
To check roles/user present in postgres:
\du

To login 

psql -d mydb -U myuser

To connect to db
\connect <databasename>

To see database

\l  or \list 

To list tables in DB
\dt

To exit command line:

\q  

For Help
-------------
psql --Help

For further detail see documentation on Postgresql:

https://www.postgresql.org/docs/9.0/static/reference-client.html