PostgreSQL is an open-source object-relational database system with 30+ years of active development in the industry. In this article on PostgreSQL Tutorial For Beginners, I will introduce you to the different concepts of databases and the commands used in PostgreSQL.
The topics covered in this article are mainly divided into 4 categories: DDL, DML, DCL & TCL.
- The DDL (Data Definition Language) commands are used to define the database. Example: CREATE, DROP, ALTER, TRUNCATE, COMMENT, RENAME.
- The DML (Data Manipulation Language) commands deal with the manipulation of data present in the database. Example: SELECT, INSERT, UPDATE, DELETE.
- The DCL (Data Control Language) commands deal with the permissions, rights and other controls of the database system. Example: GRANT, INVOKE.
- The TCL (Transaction Control Language) commands deal with the transaction of the database.Example: BEGIN, COMMIT, ROLLBACK.
Apart from the commands, the following topics will be covered in this article:
- What is PostgreSQL?
- Install PostgreSQL on Windows
- Different Types Of Keys In Database
- Constraints Used In Database
- Aggregate Functions
- Set Operations
- Nested Queries
- Stored Procedures
- UUID Data Type
What is PostgreSQL? – PostgreSQL Tutorial
PostgreSQL is an object-relational database system which extends and uses the SQL language. It originates in the year 1986 and has been into active development for more than 30 years.
The features of PostgreSQL are as follows:
- Data Types: PostgreSQL supports various types of data types such as primitive, structured, document, geometry and customizations. This helps the user to store data in any format.
- Data Integrity: With the help of various constraints and keys in the database, PostgreSQL makes sure that data integrity is satisfied for simple to complex databases.
- Performance: PostgreSQL provides features such as indexing, multi-version concurrency control, JIT complication of expressions to make sure the concurrency and the performance are kept up to mark.
- Reliability: With the help of Write Ahead Logging(WAL) and Replication, PostgreSQL has proven itself to be one of the most reliable database systems over a period of time.
- Security: PostgreSQL provides powerful mechanisms such as authentication, a robust access-control system to ensure that only authorized users have access to the databases.
- Extensibility: PostgreSQL comes with various extensions to provide additional functionalities. It also has scaled its extensibility features with stored functions, procedural language, and foreign data wrappers.
Now, that you know what is PostgreSQL, let us get started by installing PostgreSQL on Windows.
Install PostgreSQL on Windows – PostgreSQL Tutorial
To install PostgreSQL on Windows, you have to follow the below steps:
Step 1: Go to the official website of PostgreSQL and then choose the operating system for which you wish to download. Here I will choose Windows.
Step 2: Once, the operating system is chosen, you will be redirected to a page, where you have to download the installer. To do that click on the option: Download the installer. Refer below.
Step 3: Then, you will be further redirected to a page, where you have to choose the installer version based on the Operating System. Here, i will choose 11.4 version for Windows 64 bit. Refer below.
Once, you hit on Download, you will automatically see that PostgreSQL is getting downloaded.
Step 4: Now, once the file is downloaded, double click on the file to open it and a wizard will appear on your screen as below. Click on Next and proceed further.
Step 4.1: Now, specify the Installation Directory. Here, I will leave it as it is, and click on Next as below.
Step 4.2: Now, choose the components which you wish to install and then click on Next. Here, I am selecting all the components.
Step 4.3: Next, select the directory where you want to store data. Here I am going to leave it as it is. Then, click on Next.
Step 4.4: In the next dialog box, which comes, you have to mention the password for the super user. Then, click on Next.
Step 4.5: Next, you have to select the port number on which server should listen. Here, I will let it be as it is and then click on Next.
Step 4.6: Finally, select the locale to be used by the new database cluster. I will let it be as it is and then click on Next.
Step 4.7: Finally click on Next in the wizards which come to begin the installation of PostgreSQL on your computer.
Once, the installation is complete, you will see a dialog box as below on your screen. Click on Finish.
Step 5: Now, you have to connect the server to a database. To do that open pgadmin which is the official GUI of PostgreSQL. Once you open pgadmin, you will see a dialog box, which asks you for the password. So, mention the password, and click on OK.
Now, that you must have installed PostgreSQL, let us get started with commands used in PostgreSQL.
|01||Saurav||Gangnam Street||Seoul||06499||South Korea||42000|
|02||Preeti||Queens Quay||Rio Claro||560001||Brazil||45900|
|03||Vinod||Kings Road||London||SW6||United Kingdom||65000|
So, let’s get started now!
Data Definition (DDL) Commands – PostgreSQL Tutorial
This section of the article consists of those commands, which you can define your database. The commands are:
This statement is used to either create a schema, tables or an index.
The ‘CREATE SCHEMA’ Statement
The CREATE SCHEMA statement is used to create a database or most commonly known as a schema.
CREATE SCHEMA Schema_Name;
CREATE SCHEMA teachers;
The ‘CREATE TABLE’ Statement
The CREATE TABLE statement is used to create a new table in a database.
CREATE TABLE table_name ( column1 datatype, column2 datatype, column3 datatype, .... );
CREATE TABLE TeachersInfo ( TeacherID int, TeacherName varchar(255), Address varchar(255), City varchar(255), PostalCode int, Country varchar(255), Salary int );
This statement is used to add, modify or delete constraints or columns.
The ‘ALTER TABLE’ Statement
The ALTER TABLE statement is used to either add, modify or delete constraints and columns from a table.
ALTER TABLE table_name ADD column_name datatype;
ALTER TABLE TeachersInfo ADD DateOfBirth date;
This command is used to delete the database, tables or columns.
The ‘DROP SCHEMA’ Statement
The DROP SCHEMA statement is used to drop the complete schema.
DROP SCHEMA schema_name;
DROP SCHEMA teachers;
The ‘DROP TABLE’ Statement
The DROP TABLE statement is used to drop the entire table with all its values.
DROP TABLE table_name;
DROP TABLE TeachersInfo;
The TRUNCATE statement is used to delete the data which is present inside a table, but the table doesn’t get deleted.
TRUNCATE TABLE table_name;
TRUNCATE TABLE TeachersInfo;
The RENAME statement is used to rename one or more tables or columns.
ALTER TABLE table_name RENAME TO new_table_name; --Rename Table name
ALTER TABLE table_name RENAME COLUMN column_name TO new_column_name; -- Rename Column name
ALTER TABLE TeachersInfo RENAME TO InfoTeachers; ALTER TABLE InfoTeachers RENAME COLUMN dateofbirth TO dob;
Now, before I move further in this article on PostgreSQL Tutorial For Beginners, let me tell you the various types of Keys and Constraints that you need to mention while manipulating the databases. The keys and constraints will help you create tables in a much better way, as you can relate each table with the other table.
Different Types Of Keys In Database – PostgreSQL Tutorial
There are mainly 5 types of Keys, that can be mentioned in the database.
- Candidate Key – A Candidate Key is a combination of a minimal set of attributes which can uniquely identify a tuple. Any relation can have more than a single Candidate Key, with the key being a simple or a composite key.
- Super Key – A Super Key is the set of attributes which can uniquely identify a tuple. So, a Candidate Key is a Super Key, but vice-versa isn’t true.
- Primary Key – A Primary Key is a set of attributes that can be used to uniquely identify every tuple. So, if there are 3-4 candidate keys present in a relationship, then out those, one can be chosen as a Primary Key.
- Alternate Key – All the Candidate Keys other than the Primary Key is called as an Alternate Key.
- Foreign Key – An attribute that can only take the values present as the values of some other attribute, is the foreign key to the attribute to which it refers.
Constraints Used In Database – PostgreSQL Tutorial
The constraints which you can use in databases are as follows:
- NOT NULL – The NOT NULL constraint ensures that a NULL value cannot be stored in a column
- UNIQUE – The UNIQUE constraint makes sure that all the values in a column are different
- CHECK -The CHECK constraint ensures that all the values in a column satisfy a specific condition.
- DEFAULT -The DEFAULT constraint consists of a set of default values for a column when no value is specified.
- INDEX – The INDEX constraint is used to create and retrieve data from the database very quickly
Now, that you know the commands in DDL and the various types of keys and constraints, let’s move on to the next section i.e Data Manipulation Commands.
Data Manipulation (DML) Commands – PostgreSQL Tutorial
This section of the article consists of the commands, by which you can manipulate your database. The commands are:
- Arithmetic, Bitwise, Compound and Comparison Operators
- Logical Operators
- Aggregate Functions
- Special Operators
- Set Operations
- Limit, Offset and Fetch
This statement is used to mention which schema has to be used to perform all the operations.
SET search_path TO schema_name;
SET search_path TO teachers;
The INSERT statement is used to insert new records in a table.
The INSERT INTO statement can be written in the following two ways:
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...); --You need not mention the column names INSERT INTO table_name VALUES (value1, value2, value3, ...);
INSERT INTO TeachersInfo(TeacherID, TeacherName, Address, City, PostalCode, Country, Salary) VALUES ('01', 'Saurav','Gangnam Street', 'Seoul', '06499', 'South Korea', '42000'); INSERT INTO TeachersInfo VALUES ('02', 'Preeti','Queens Quay', 'Rio Claro', '13500', 'Brazil', '45900');
The UPDATE statement is used to modify the existing records in a table.
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
UPDATE TeachersInfo SET TeacherName = 'Alfred', City= 'Frankfurt' WHERE TeacherID = '01';
The DELETE statement is used to delete existing records in a table.
DELETE FROM table_name WHERE condition;
DELETE FROM TeachersInfo WHERE TeacherName='Vinod';
The SELECT statement is used to select data from a database and the data returned is stored in a result table, called the result-set.
The following are the two ways of using this statement:
SELECT column1, column2, ... FROM table_name; --(*) is used to select all from the table SELECT * FROM table_name;
SELECT Teachername, City FROM TeachersInfo; SELECT * FROM TeachersInfo;
The ‘SELECT DISTINCT’ Statement
The SELECT DISTINCT statement is used to return only distinct or different values. So, if you have a table with duplicate values, then you can use this statement to list distinct values.
SELECT DISTINCT column1, column2, ... FROM table_name;
SELECT Country FROM TeachersInfo;
The ‘ORDER BY’ Statement
The ORDER BY statement is used to sort the desired results in ascending or descending order. By default, the results would be sorted in ascending order. If you want to sort the records in descending order, then you have to use the DESC keyword.
SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ...ASC|DESC;
SELECT * FROM TeachersInfo ORDER BY Country; SELECT * FROM TeachersInfo ORDER BY Country DESC; SELECT * FROM TeachersInfo ORDER BY Country, TeachersName; SELECT * FROM TeachersInfo ORDER BY Country ASC, TeachersName DESC;
The ‘GROUP BY’ Statement
This statement is used with the aggregate functions to group the result-set by one or more columns.
SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) ORDER BY column_name(s);
SELECT COUNT(TeacherID), Country FROM TeachersInfo GROUP BY Country ORDER BY COUNT(TeacherID) DESC;
The ‘HAVING’ Clause Statement
Since the WHERE keyword cannot be used with aggregate functions, the HAVING clause was introduced.
SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) HAVING condition ORDER BY column_name(s);
SELECT COUNT(TeacherID), Country FROM TeachersInfo GROUP BY Country HAVING COUNT(Salary) &amp;amp;amp;gt; 40000;
Arithmetic, Bitwise, Compound and Comparison Operators – PostgreSQL Tutorial
This operator displays the records, which satisfy all the conditions separated by AND.
SELECT column1, column2, ... FROM table_name WHERE condition1 AND condition2 AND condition3 ...;
SELECT * FROM TeachersInfo WHERE Country='India' AND City='South Korea';
This operator displays those records which satisfy any of the conditions separated by OR.
SELECT column1, column2, ... FROM table_name WHERE condition1 OR condition2 OR condition3 ...;
SELECT * FROM TeachersInfo WHERE Country='India' OR City='South Korea';
The NOT operator displays a record when the condition (s) is NOT TRUE.
SELECT column1, column2, ... FROM table_name WHERE NOT condition;
SELECT * FROM TeachersInfo WHERE NOT Country='India'; --You can also combine all the above three operators and write a query like this: SELECT * FROM TeachersInfo WHERE NOT Country='India' AND (City='Bengaluru' OR City='Kolkata');
Aggregate Functions – PostgreSQL Tutorial
The MIN function returns the smallest value of the selected column in a table.
SELECT MIN(column_name) FROM table_name WHERE condition;
SELECT MIN(Salary) AS SmallestSalary FROM TeachersInfo;
The MAX function returns the largest value of the selected column in a table.
SELECT MAX (column_name) FROM table_name WHERE condition;
SELECT MAX(Salary) AS LargestSalary FROM TeachersInfo;
The COUNT function returns the number of rows that match the specified criteria.
SELECT COUNT (column_name) FROM table_name WHERE condition;
SELECT COUNT(TeacherID) FROM TeachersInfo;
The AVG function returns the average value of a numeric column that you choose.
SELECT AVG (column_name) FROM table_name WHERE condition;
SELECT AVG(Salary) FROM TeachersInfo;
The SUM function returns the total sum of a numeric column that you choose.
SELECT SUM(column_name) FROM table_name WHERE condition;
SELECT SUM(Salary) FROM TeachersInfo;
Special Operators – PostgreSQL Tutorial
The BETWEEN operator is an inclusive operator which selects values(numbers, texts or dates) within a given range.
SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;
SELECT * FROM TeachersInfo WHERE Fees BETWEEN 30000 AND 45000;
IS NULL Operator
Since, it is not possible to test for NULL values with the comparison operators(=, <, >), we can use IS NULL and IS NOT NULL operators instead.
--Syntax for IS NULL SELECT column_names FROM table_name WHERE column_name IS NULL; --Syntax for IS NOT NULL SELECT column_names FROM table_name WHERE column_name IS NOT NULL;
SELECT TeacherName FROM TeachersInfo WHERE Address IS NULL; SELECT TeacherName FROM TeachersInfo WHERE Address IS NOT NULL;
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column of a table.
The mentioned below are the two wildcards that are used in conjunction with the LIKE operator:
% – The percent sign represents zero, one, or multiple characters
_ – The underscore represents a single character
SELECT column1, column2, ... FROM table_name WHERE column LIKE pattern;
SELECT * FROM TeachersInfo WHERE TeacherName LIKE 'S%';
The IN operator is a shorthand operator and is used for multiple OR conditions.
SELECT column_name(s) FROM table_name WHERE column_name IN (value1, value2, ...);
SELECT * FROM TeachersInfo WHERE Country IN ('South Korea', 'India', 'Brazil');
NOTE: You can also use IN while writing Nested Queries.
The EXISTS operator is used to test if a record exists or not.
SELECT column_name(s) FROM table_name WHERE EXISTS (SELECT column_name FROM table_name WHERE condition);
SELECT TeacherName FROM TeachersInfo WHERE EXISTS (SELECT * FROM TeachersInfo WHERE TeacherID = 05 AND Salary &amp;amp;amp;gt; 25000);
The ALL operator is used with a WHERE or HAVING clause and returns true if all of the sub-query values meet the condition.
SELECT column_name(s) FROM table_name WHERE column_name operator ALL (SELECT column_name FROM table_name WHERE condition);
SELECT TeacherName FROM TeachersInfo WHERE TeacherID = ALL (SELECT TeacherID FROM TeachersInfo WHERE Salary &amp;amp;amp;gt; 25000);
Similar to the ALL operator, the ANY operator is also used with a WHERE or HAVING clause and returns true if any of the sub-query values meet the condition.
SELECT column_name(s) FROM table_name WHERE column_name operator ANY (SELECT column_name FROM table_name WHERE condition);
SELECT TeacherName FROM TeachersInfo WHERE TeacherID = ANY (SELECT TeacherID FROM TeachersInfo WHERE Salary BETWEEN 32000 AND 45000);
Set Operations – PostgreSQL Tutorial
The UNION operator is used to combine the result-set of two or more SELECT statements.
SELECT column_name(s) FROM table1 UNION SELECT column_name(s )FROM table2;
The INTERSECT clause is used to combine two SELECT statements and return the intersection of the data-sets of both the SELECT statements.
SELECT Column1 , Column2 .... FROM table_name; WHERE condition INTERSECT SELECT Column1 , Column2 .... FROM table_name; WHERE condition
The EXCEPT operator returns those tuples that are returned by the first SELECT operation, and are not returned by the second SELECT operation.
SELECT column_name FROM table_name; EXCEPT SELECT column_name FROM table_name;
Limit, Offset and Fetch – PostgreSQL Tutorial
The LIMIT statement is used to retrieve a portion of the rows out of the complete rows present in the table.
FROM table_name LIMIT number;
SELECT * FROM TeachersInfo LIMIT 5;
The OFFSET statement omits the number of rows you mention and then retrieves the rest portion of the rows.
FROM table_name OFFSET number LIMIT number;
--Select 3 rows from TeachersInfo after the 5th row SELECT * FROM TeachersInfo OFFSET 5 LIMIT 3; --Select all rows from TeachersInfo SELECT * FROM TeachersInfo OFFSET 2;
The FETCH keyword is used to fetch records from a table using a cursor. Here the cursors will be the following:
- RELATIVE Count
- ABSOLUTE Count
- BACKWARD Count
- BACKWARD ALL
- FORWARD Count
- FORWARD ALL
SELECT * FROM TeachersInfo OFFSET 5 FETCH FIRST 5 ROWS ONLY;
Nested Queries – PostgreSQL Tutorial
Nested queries are those queries which have an outer query and inner subquery. So, basically, the subquery is a query which is nested within another query such as SELECT, INSERT, UPDATE or DELETE. Refer to the image below:
So, when you execute this query, you will see the name of the teacher who is from Brazil.
Joins – PostgreSQL Tutorial
JOINS in PostgreSQL are used to combine rows from two or more tables, based on a related column between those tables. The following are the types of joins:
- INNER JOIN: The INNER JOIN returns those records which have matching values in both the tables.
- LEFT JOIN: The LEFT JOIN returns records from the left table, and also those records which satisfy the condition from the right table.
- RIGHT JOIN: The RIGHT JOIN returns records from the right table, and also those records which satisfy the condition from the left table.
- FULL JOIN: The FULL JOIN returns all those records which either have a match in the left or the right table.
SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;
SELECT Subjects.SubjectID, TeachersInfo.TeacherName FROM Subjects INNER JOIN TeachersInfo ON Subjects.TeacherID = TeachersInfo.TeacherID;
SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;
SELECT TeachersInfo.TeacherName, Subjects.SubjectID FROM TeachersInfo LEFT JOIN Subjects ON TeachersInfo.TeacherID = Subjects.TeacherID ORDER BY TeachersInfo.TeacherName;
SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name;
SELECT Subjects.SubjectID FROM Subjects RIGHT JOIN TeachersInfo ON Subjects.SubjectID = TeachersInfo.TeacherID ORDER BY Subjects.SubjectID;
SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;
SELECT TeachersInfo.TeacherName, Subjects.SubjectID FROM TeachersInfo FULL OUTER JOIN Subjects ON TeachersInfo.TeacherID = Subjects.SubjectID ORDER BY TeachersInfo.TeacherName;
Now, next in this article, I will discuss Views, Stored Procedures, and Triggers.
Views – PostgreSQL Tutorial
A view is a single table, which is derived from other tables. So, a view contains rows and columns similar to a real table and has fields from one or more table.
The ‘CREATE VIEW’ statement
The CREATE VIEW statement is used to create a view from an existing table.
CREATE VIEW view_name AS SELECT column1, column2, ..., columnN FROM table_name WHERE condition;
CREATE VIEW teachers_view AS SELECT TeacherName, TeacherID FROM TeachersInfo WHERE City = 'Bengaluru';
The ‘DROP VIEW’ statement
DROP VIEW view_name;
DROP VIEW teachers_view;
PostgreSQL Tutorial For Beginners: Stored Procedures
Stored Procedures are snippets of codes which can be saved and re-used.
CREATE PROCEDURE procedure_name
--Create two tables CREATE TABLE tbl1(tb1id int); CREATE TABLE tbl2(tb2id int); --Create Procedure CREATE PROCEDURE insert_data (a1 integer, b1 integer) LANGUAGE SQL AS $$ INSERT INTO tbl1 VALUES (a1); INSERT INTO tbl2 VALUES (b1); $$; CALL insert_data(4, 5);
Triggers – PostgreSQL Tutorial
Triggers are a set of SQL statements which are stored in the database catalog. These statements are executed whenever an event associated with a table occurs. So, a trigger can be invoked either BEFORE or AFTER the data is changed by INSERT, UPDATE or DELETE statement.
CREATE TRIGGER trigger_name [BEFORE|AFTER|INSTEAD OF] event_name ON table_name [ --Mention Logic Here ];
--CREATE TRIGGER CREATE TRIGGER example_trigger AFTER INSERT ON TeachersInfo;
Data Control (DCL) Commands – PostgreSQL Tutorial
The GRANT command is used to provide user access privileges or other privileges for the schema.
GRANT privileges ON object TO user;
GRANT INSERT ON TeachersInfo TO PUBLIC;
The REVOKE command is used to withdraw user’s access privileges given by using the GRANT command.
REVOKE privileges ON object FROM user;
REVOKE INSERT ON TeachersInfo FROM PUBLIC;
Now, let’s move on to the last section of this article i.e. the TCL Commands.
Transaction Control (TCL) Commands – PostgreSQL Tutorial
The BEGIN TRANSACTION command is used to start the transaction.
BEGIN; DELETE * FROM TeachersInfo WHERE Salary = 65000;
DELETE * FROM TeachersInfo WHERE Salary = 65000; COMMIT;
The ROLLBACK command is used to undo transactions since the last COMMIT or ROLLBACK command was issued.
DELETE * FROM TeachersInfo WHERE Salary = 65000; ROLLBACK;
The SAVEPOINT command defines a new savepoint within the current transaction.
SAVEPOINT savepoint_name; --Syntax for saving the SAVEPOINT ROLLBACK TO savepoint_name --Syntax for rolling back to the SAVEPOINT
SAVEPOINT SP1; DELETE FROM TeachersInfo WHERE Fees = 65000; SAVEPOINT SP2;
RELEASE SAVEPOINT savepoint_name;
RELEASE SAVEPOINT SP2;
The SET TRANSACTION command sets the characteristics of the current transaction.
SET TRANSACTION transaction_mode;
UUID Data Type – PostgreSQL Tutorial
UUID data type stores Universally Unique Identifiers (UUID) with a 128 byte length. It is written as a sequence of lower-case hexadecimal digits and is generated by an algorithm. This algorithm is designed to make sure that the same UUID is not generated by any other person in the universe.
--Generate a a unique UUID SELECT uuid_generate_v4();
With this, we come to the end of this article on PostgreSQL Tutorial For Beginners. I hope you enjoyed reading this article on PostgreSQL Tutorial For Beginners. We have seen the different commands that will help you write queries and play around with your databases. If you wish to learn more about SQL and get to know this open source relational database, then check out our SQL Essentials Training. This training will help you understand SQL in depth and help you achieve mastery over the subject.
Got a question for us? Please mention it in the comments section of ”PostgreSQL Tutorial For Beginners” and I will get back to you.