_edited_edited.png)
The database is at the core of the modern-day world. I entered a simple query in Google, ‘How much data is produced daily?’ This is the response I received. A quintillion is a billion billions, that is 1 followed by 18 zeroes.
Source: www.google.com
It paints a picture of how much online digital footprint we leave and the staggering amount of data we produce every day on a global scale. Mankind has stored data throughout the ages. We have evolved from leaving messages in the form of cave paintings and tally marks to using paper, journals, and calculating devices, to the present-day computers where we are storing big data. With time, the way we store and manage this data has also evolved. Charles Bachman designed the first computerized database in the early 1960s for which he received the Turing Award. Later in the same decade, IBM developed the Information Management System. Both these databases were of the type of navigational database where users are required to navigate through the entire database to find the information they need.
About PostgreSQL
During the 1980s relational databases became popular. PostgreSQL is an open-source relational database system that is used as a primary database for several web applications as well as mobile and analytics applications. It supports most programming languages like Python, Java, C#, c+, Ruby, JavaScript and others. To understand PostgreSQL, we need to install it and use a sample database to understand the basic functions for generating queries.
Primary Key and Foreign Key
A relational database has a Primary Key and a Foreign Key. For instance, if there is a table student with student_ID, name and grade, then student_ID column is unique as it cannot contain duplicate or null values. This will act as the primary key for the student table. A foreign key is a column in the table which is the primary key in another table. For example, another table called student_scores can have columns student_id, roll_no, subject_name and student_marks. Here student_id acts as this table’s foreign key and it establishes a referential relationship with the student table. The table with the foreign key is called the child table and the table with the primary key is called the referenced table or parent table.
Commonly used queries in PostgreSQL
The following describes, in brief, the commonly used queries in PostgreSQL to retrieve information from a database:
SELECT
We can use the SELECT statement to query data from one or multiple columns from a table.
Syntax: SELECT first_name, last_name, email FROM customer;
SELECT is also used to provide an alias name to a column name.
Syntax: SELECT column_name AS alias_name FROM table_name;
The SELECT statement can be used with GROUP BY clause to divide the row records into groups.
Syntax: SELECT coulmn1, column2 FROM table1 GROUP BY column1;
To filter records in a certain order, we use the ORDER BY clause with SELECT statement.
Syntax: SELECT column_name FROM table_name ORDER BY ASC;
By using DISTINCT cause, we can get unique values from a result set.
Syntax: SELECT DISTINCT column1 FROM table_name;
To retrieve records that specify a particular condition, we use a WHERE clause.
Synatx: SELECT column_name FROM table_name WHERE condition1=’xyz’ OR condition2=’abc’ ORDER BY ASC;
To retrieve records of a specific number of rows we use the FETCH clause.
Syntax: SELECT column1, column2 FROM table_name ORDER BY column3 FETCH FIRST 5 ROW ONLY;
JOINS
We use JOINS to combine columns from one or more tables based on the common columns between two or more related tables. JOINS are of the following four types:
INNER JOIN: returns records that have matching values in both tables.
LEFT JOIN: Returns all records from the left table, and the corresponding matched records from the right table.
RIGHT JOIN: Returns all records from the right table, and the matched records from the left table.
FULL JOIN: Returns all records when there is a match in either the left or right table.
INSERT
The INSERT statement allows us to insert a new row into the table.
Syntax: INSERT INTO table_name (column1, column2) VALUES (value1, value);
UPDATE
The UPDATE statement allows us to modify data in a table.
Syntax: UPDATE table_name SET column1=value1, column2 = value2, WHERE condition;
DELETE
The DELETE statement allows us to delete one or more rows from a table.
Syntax: DELETE FROM table_name WHERE condition;
Functions used to manage tables:
CREATE TABLE
We use CREATE TABLE statement to create a new table with rows and columns.
Syntax: CREATE TABLE [IF NOT EXISTS] table_name(column1 datatype(length) column_constraint, column1 datatype(length) column_constraint, table_constraints);
ALTER TABLE
We use ALTER TABLE to change the structure of an existing table.
Syntax: ALTER TABLE table_name action;
ALTER TABLE table_name ADD column_name datatype column_constraint;
ALTER TABLE table_name DROP column_name;
ALTER TABLE table_name RENAME COLUMN column_name TO new_column_name;
To create temporary table
Syntax: CREATE TEMPORARY TABLE temp_table_name (column_list);
CASE expression
The CASE expression is used as IF/ELSE statement which allows us to add if-else logic to a query.
Syntax: CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 [ELSE result] END;
PostgreSQL Datatypes
PostgreSQL supports a number of data types. While creating a table, for each column we need to specify what kind of data we want to store in that field. The following is brief description of various data types supported by PostgreSQL:
Datatype | Description |
Boolean | True/False/NULL |
Varchar | Variable length with length limit defined |
Character | Fixed length characters |
Text, Varchar | variable unlimited length |
Numeric | Such as integer and floating-point numbers |
Date | to store date, the yyyy-mm-dd format is used. |
Timestamp | Two types of datatypes are available for handling timestamp: |
timestamp | timestamp without timezone |
timestampz | timestamp with a timezone |
Interval | Allows us to store a period o f time in years, months , days, time |
UUID | For storing Universally Unique Identifiers, which is a sequence of 32 hexadecimal digits. |
JSON | Stores JSON data |
HSTORE | Stores key value oairs in a single value |
Array | Used for storing arrays integer or string value |
Summary
PostgreSQL is one of the most popular and versatile tools for managing relational databases. It has a huge list of functions, datatypes and features that make this database management system a primary choice for most programmers to work with. PostgreSQL has some additional object-oriented features that define inheritance between tables and lets users define their own complex data types. All these features make this database management system a reliable, stable, scalable and secure system widely used by a lot of companies in the world today.