Working with PostgreSQL on Ubuntu 17.04

How to install PostgreSQL

We can easily install the PostgreSQL using the apt packaging system, executing the below commands.

• sudo apt-get update

• sudo apt-get install postgresql postgresql-contrib

How to switch to the postgres Account

• When installing PostgreSQL, created a user account as postges. It works as the default postgres role. We can login to this account executing the (sudo -i -u postgres) command.

• Then we need to give our sudo password and switch to the PostgreSQL server as the below.

• Then we can access PostgreSQL as follows. (Using psql command)

• We can check the connection information using \conninfo command.

• We can change the password of postgres user using this command. (\password postgres)

• To create a new database we can use “create database db_name” command.

• If we want to drop a database we can use this. (drop database db_name)

• To list the created database can use \l command.

• To connect to a created database we can use \c db_name command.

• Using \dt command can list the tables of the connecting database.

• To create a table. CREATE TABLE table_name (column1 type, column2 type, column3 type);

• To insert data to a table in the order in which the columns appear in the table use this command. (INSERT INTO table_name VALUES (value1, value2, value3);)

• To insert data without the order which appears in the table use these command.

• If we want to know the count of the data of a table, we can use SELECT COUNT(*) FROM table_name;

• If we want to get the first record of the table, we can make the data into ascending order and get the value using this command
  SELECT * FROM table_name ORDER BY column_name ASC LIMIT 1;

• If we want to get the last record of the table, we can make the data into decending order and get the value using this commad
  SELECT * FROM table_name ORDER BY column_name DESC LIMIT 1;

• Type \q and then press ENTER to quit psql.