How To Install Postgres on Ubuntu 16.04?

PostgreSQL, also known as Postgres, is a relational database management system that emphasizes extensibility and SQL compliance. Ubuntu is a Debian-based Linux distribution that uses mostly free and open-source software.

This article talks about how to install Postgres on Ubuntu extensively. In addition to that, it gives a brief introduction to PostgreSQL.

Table of Contents

• What is PostgreSQL?
  • Key Features of PostgreSQL
• How to Install PostgreSQL on Ubuntu?
• What are the Basic PostgreSQL Commands?
  • CREATING A NEW ROLE
  • CREATING A NEW DATABASE
  • CREATING A NEW TABLE
  • GRANTING PRIVILEGES
  • ADDING, QUERYING, AND DELETING DATA IN A TABLE
  • UPDATING DATA IN A TABLE
  • DROPPING A TABLE
  • ALTERING THE TABLE COLUMNS
  • DROPPING A DATABASE
• Conclusion

What is PostgreSQL (Postgres)?

PostgreSQL relational database system is a powerful, scalable, and standards-compliant open-source database platform. It offers the standard SQL features as well as some modern ones like triggers, and transaction integrity. Companies and individual developers install Postgres Ubuntu because it gives them flexibility and is easy to manage. This tutorial will help you install PostgreSQL on Ubuntu and get a hang of the basic commands.

Key Features of Postgres

• Customizable: PostgreSQL can be customized by developing plugins to make the Database Management System fit your requirements. PostgreSQL also allows you to incorporate custom functions that are made with other programming languages like Java, C, C++, etc.
• Long History: PostgreSQL has been around for more than 30 years, having been initially released in 1988.
• Frequent Updates: The most recent PostgreSQL update was Version 13.1 on 12 November 2020.  
• MVCC Features: PostgreSQL happens to be the first Database Management System to implement Multi-Version Concurrency Control (MVCC) features.
• A Supportive Community: A dedicated community is always at your disposal. Private, third-party support services are available as well. The community updates the PostgreSQL platform via the PostgreSQL Global Development Group.
• Open-Source: This is an Object-Relational Database Management System(ORDBMS). This allows PostgreSQL to provide Object-Oriented and Relational Database functionality. PostgreSQL is a free and Open-Source ORDBMS.
• Users: PostgreSQL users include Apple, Cisco, Etsy, Facebook, Instagram, Red Hat, Skype, Spotify, Yahoo to name a few

How to Install Postgres on Ubuntu?

Ubuntu contains PostgreSQL by default. We can get it by using the apt command. By following the given commands below one can easily install Postgres ubuntu.

sudo apt-get update

sudo apt-get install postgresql postgresql-contrib

This installs the latest version of PostgreSQL on Ubuntu and the commonly used add-ons for it.

What are the Basic PostgreSQL Commands?

Now that you are done with installing Postgres Ubuntu. In this section, you will learn some of the basic commands to get started with PostgreSQL.

1. CREATING A NEW ROLE

PostgreSQL manages database access permissions using the concept of roles. Roles can own database objects (for example, tables and functions) and can assign privileges on those objects to other roles to control who has access to which objects.

The default role for PostgreSQL is called Postgres which was created during installation. We need to log in to this account to use PostgreSQL.

sudo -i -u postgres

This will ask for the user account password which is the same as the login password.

psql

This is the interactive terminal for PostgreSQL.

q

This quits psql and takes the user back to the regular command line.

psql -l 

can be used to check all the databases.

We can connect to a different database by using –d.

psql -d postgres 

Since the Postgres Linux account has all the privileges it can create and delete additional roles.

We can create and delete roles by these commands:

createuser user_name;

delete user_name;

Example:

createuser Janet;

dropuser Janet;

Only superusers and users with CREATEROLE privilege can create a new user.

du 

the command will list out all the existing roles.

2. CREATING A NEW DATABASE

This command lets us create a new database.

createdb company;

Normally, the database user who executes this command becomes the owner of the new database. However, a different owner can be specified via the –O option, if the executing user has appropriate privileges.

We can change the Linux account to the company by typing:

sudo -i -u Joe;

(Assuming we have a user called Joe)

Normally the superuser postgre is the default user.

3. CREATING A NEW TABLE

Now that we have a database, let’s create a new table.

CREATE TABLE Employee(ID int PRIMARY KEY,Name varchar(20),Division varchar(20), City varchar(20), Salary float);

d command can be used for verification of the table created.

It lists all the tables in the current database.

d table_name is used for the description of each table.

4. GRANTING PRIVILEGES

Whenever an object is created in a database, an owner is assigned to it. The owner is usually the one who executed the creation statement. For most kinds of objects, the initial state is that only the owner (or a superuser) can modify or delete the object. To allow other roles or users to use it, privileges or permission must be granted.

GRANT ALL ON employee TO company;

REVOKE DELETE FROM company;

The privileges are SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, CONNECT, TEMPORARY, EXECUTE, USAGE.

5. ADDING, QUERYING, AND DELETING DATA IN A TABLE

Let’s add some employees’ information to the database.

INSERT INTO Employee values (101,'Ray Alexson','Engineering','California',100000);

INSERT INTO Employee values(102,'Riley Mason','Marketing','New York',90000);

INSERT INTO Employee values(103,'Shawn Price','Marketing','New York',11000) ;

We can use SELECT statements to ask

SELECT * FROM Employee;

DELETE query is used to delete the records from the table.

DELETE FROM Employee WHERE Salary >= 100000;

6. UPDATING DATA IN A TABLE

UPDATE Query is used to altering the existing records in the table.

Let’s update the salary of all employees in the Marketing division to $100000.

UPDATE Employee SET Salary = 100000 WHERE Division = ‘Marketing’;

7. DROPPING A TABLE

DROP TABLE Employee;

We can drop multiple tables at the same time.

DROP TABLE Employee, Employee_salary;

8. ALTERING THE TABLE COLUMNS

We can add new columns in a table using ADD command with ALTER TABLE.

Let’s add a new column denoting the work experience of a particular employee.

ALTER TABLE Employee ADD Work_Experience int;

We can delete columns in a table using the DROP command with ALTER TABLE.

Let’s drop the city information of each employee.

ALTER TABLE Employee DROP COLUMN City;

9. DROPPING A DATABASE

We can drop the entire database with dropdb command.

dropdb dbname;

Conclusion

In this tutorial, you learned how to install Postgres Ubuntu and other commands to manipulate the database and table. Looking for similar tutorials on Ubuntu and Postgres? We have written these blogs which would interest you: How to install MongoDB on Ubuntu, How to set up Kafka on Ubuntu and Postgres to Snowflake.

Visit our Website to Explore Hevo

We at Hevo Data are building the most robust and comprehensive ETL solution in the industry. We integrate with your in-house databases, cloud apps, flat files, and the clickstream. It is a No-code Data Pipeline solution that can help you load and transform data from any data source to a desired destination such as Data Warehouse. Want to know more, reach out to us.

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.

Share your experience of installing PostgreSQL on Ubuntu in the comments section below!