A primer on PostgreSQL
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.
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.
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:
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;
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.
The INSERT statement allows us to insert a new row into the table.
Syntax: INSERT INTO table_name (column1, column2) VALUES (value1, value);
The UPDATE statement allows us to modify data in a table.
Syntax: UPDATE table_name SET column1=value1, column2 = value2, WHERE condition;
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:
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);
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);
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 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:
Variable length with length limit defined
Fixed length characters
variable unlimited length
Such as integer and floating-point numbers
to store date, the yyyy-mm-dd format is used.
Two types of datatypes are available for handling timestamp:
timestamp without timezone
timestamp with a timezone
Allows us to store a period o f time in years, months , days, time
For storing Universally Unique Identifiers, which is a sequence of 32 hexadecimal digits.
Stores JSON data
Stores key value oairs in a single value
Used for storing arrays integer or string value
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.