About PostgreSQL

PostgreSQL is a relational database management system. It is having advanced features so it is used in many small and large projects.

I am writing this blog which will let you know how to install Postgres on an Ubuntu 14.04.

Installation: Update package repositories

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

Postgresql is installed now.

Using PostgreSQL Roles and Databases

During installation postgres user account was created. Now let's log in to Postgres:

sudo -i -u postgres

User password will be asked and shell prompt will be given to you for postgres user

psql

Now you are logged in and will be able to interact with the database.

If you want to exit out of PostgreSQL then type following command:

\q

You should now be back in the postgres Linux command prompt.

How to create a New Role:

Type the following command in order to create a new role:

createuser --interactive

This command will create a user, and it will ask you only 2 questions:

i) The name of the role.

ii) Whether it should be a superuser.

You can get more control by passing some additional flags. Check out the options by looking at the man page:

man createuser

Create a New Database

Posgres is set up by default in such a way that if there is a user called test1  then that role will attempt to connect to a database called test1

You can create the appropriate database by simply calling this command as the postgres user:

createdb test1

Connect to Postgres with the New User

Suppose you have created user by typing command : "adduser test1" and you have created a Postgres role and database also called test1

You can change to the Linux system account by typing:

sudo -i -u test1

You can then connect to the test1 database as the test1 Postgres role by typing:

psql

Now you will be log in automatically and it will be assumed that all the components have been configured

If different database is required to connect to your user then you can do this by specifying the database like this:

psql -d postgres

In order to get the information of the logged in Postgres user and the database you're connected with currently then you need to type the following command:

\conninfo

You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

Create and Delete Tables

Lets start with some basic tasks, as you are now connected to the database

First, let's begin with creating a table to store some data

The basic syntax for this command is something like this:

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

We have given the table a name, and also defined the columns as well as the column type and the max length of the field data. We can also add table constraints for each column.

For our purposes, we're going to create a simple table like this:

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
);

Now in order to see our new table you can do this by typing command:

\d

                   List of relations
 Schema |          Name           |   Type   |  Owner   
--------+-------------------------+----------+----------
 public | playground              | table    | postgres
 public | playground_equip_id_seq | sequence | postgres
(2 rows)

As you can see, we have our playground table, but we also have something called playground_equip_id_seq that is of the type sequence. This is a representation of the "serial" type we gave our equip_id column. This keeps track of the next number in the sequence.

If you want to see just the table, you can type:

\dt

           List of relations
 Schema |    Name    | Type  |  Owner   
--------+------------+-------+----------
 public | playground | table | postgres
(1 row)

How to insert records in a table

Now that we have a table created, we can insert some data into it.

INSERT INTO playground (type, color, location, install_date) VALUES ('slide', 'blue', 'south', '2014-04-28');
INSERT INTO playground (type, color, location, install_date) VALUES ('swing', 'yellow', 'northwest', '2010-08-16');

Note: Keep in mind that the column names should not be quoted, but the column values that you're entering do need quotes. Another thing to keep in mind is that we do not enter a value for the equip_id column. This is because this is auto-generated whenever a new row in the table is created.

We can then get back the information we've added by typing:

SELECT * FROM playground;

How to use delete command

If you want to remove a row, then you can do this by typing:

DELETE FROM playground WHERE type = 'slide';

If we query our table again, we will see our slide is no longer a part of the table:

SELECT * FROM playground;

How To Add and Delete Columns from a Table

If you want to add columns to the table, we can do that by typing:

ALTER TABLE playground ADD last_maint date; 

If you view your table information again, you will see the new column has been added (but no data has been entered):

SELECT * FROM playground;

We can delete a column just as easily. If we find that our work crew uses a separate tool to keep track of maintenance history, we can get rid of the column here by typing:

ALTER TABLE playground DROP last_maint;

How To Update Data in a Table

Use the below command in order to update the table:

UPDATE playground SET color = 'red' WHERE type = 'swing';

We can verify that the operation was successful by querying our data again:

SELECT * FROM playground;

Now you will get the final data.

Thats all!

Thanks for reading the blog.