Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • How To Install and Use PostgreSQL on Ubuntu 14.04 ?

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 493
    Comment on it

    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.

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: