INTRODUCTION TO PostgreSQL
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, transaction integrity. This tutorial will help you install PostgreSQL on Ubuntu and get a hang of the basic commands.
1. INSTALLING PostgreSQL on Ubuntu
Ubuntu contains PostgreSQL by default. We can get it by using the apt command.
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.
2. 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 login to this account to use postgreSQL.
sudo -i -u postgres
This will ask for the user account password which is same as the login password.
This is the interactive terminal for postgreSQL.
This quits psql and takes user back to 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 postgre 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;
createuser Janet; dropuser Janet;
Only superusers and users with CREATEROLE privilege can create a new user.
\du command will list out all the existing roles.
3. CREATING A NEW DATABASE
This command lets us create a new database.
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 company by typing:
sudo -i -u Joe;
(Assuming we have a user called Joe)
Normally the superuser postgre is the default user.
4. 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 table in the current database.
\d table_name is used for description of each table.
5. 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.
6. 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) ;
If we want to enter data into all the columns of the table, we don’t need to mention the column names. We just need to keep the order same as the columns in the table.
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;
7. UPDATING DATA IN A TABLE
UPDATE Query is used to alter 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’;
8. DROPPING A TABLE
DROP TABLE Employee;
We can drop multiple tables at the same time.
DROP TABLE Employee, Employee_salary;
9. 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;
10. DROPPING A DATABASE
We can drop the entire database with dropdb command.
We at Hevo are building the most robust and comprehensive ETL solution in the industry. We integrate with your in-house databases, cloud apps, flat files, clickstream. Want to know more. Reach out to us.