Postgres Database in Ubuntu terminal

ENVIRONMENTAL SETUP FOR POSTGRES DATABASE

POSTGRES DATABASE:

                     

                      PostgreSQL (pronounced "post-gress-Q-L") is an open source relational database management system ( DBMS ).PostgreSQL supports transaction s, subselects, trigger s, view s, foreign key referential integrity, and sophisticated locking. It runs on numerous platforms including Linux , most flavors of UNIX , Mac OS X , Solaris , Tru64, and Windows . It supports text, images, sounds, and video, and includes programming interfaces for C / C++ , Java , Perl , Python , Ruby, Tcl and Open Database Connectivity ( ODBC ).

Setup and Requirements:

Step 1: Download and install Postgres DB from following URL.

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

         Create a user account called postgres which is associated to postgres role

                sudo -i -u postgres

         Postgre is prompt by immediately  typing

               psql

          For Exit Postgres type

                 \q

          For enter into Postgres use this keyword

                 sudo -u postgres psql postgres

          For creating database password

                 \password postgres

Step 2: password setup:

              If you want to ask  password every time when you login to your database  for security purpose.you can change the Configuration file.To do that follow the below

File location : $ sudo gedit /etc/postgresql/9.3/main/pg_hba.conf

Change the configuration:

Database administrative login by Unix domain socket

local   all             postgres                                md5

After change the configuration file you can restart your service

sudo service postgresql restart

To view postgres status

      $ sudo service postgresql status

Step 3:Work on postgresdb:

Create database in Postgres

      postgres=#   create database databasename

For connecting database

        postgres=#   \c databasename

For disconnecting database

         postgres=#   \q

To Create table tablename:

            postgres=# CREATE TABLE table_name(   column1 datatype, column2datatype,
   column3 datatype,.....column N data type,PRIMARY KEY( one or more columns ));

To view list of tables:

      

             postgres=#  \d - is to view list of new tables that are created

            postgres=# \dt - is to view list of tables that are present in database

To delete table:

         

             postgres=#     Drop table tablename;

To insert a data in table:

             

              postgres=#  INSERT INTO TABLE_NAME (column1, column2, column3,...column N)VALUES (value1, value2, value3,...value N);

To view the datas in the table :

SELECT * FROM table_name;

Refer the link for syntex:

https://www.tutorialspoint.com/postgresql/index.htm