Suppose you have a table exam_scores containing 5 columns. An example is given below with some dummy data.nameroll_nosubjecttotal_marksmarks_obtainedAnil1English10056Anil1Math10065Anil1Science10045Roy2English10078Roy2Math10098Roy2Science10067Now, one student could have sat for exams of multiple subjects, and therefore, there are multiple rows for 1 student. If you wish to find out the total number of students in the class, you may want to find the number of distinct values of roll_no. You can apply the distinct constraint on a specific column as follows −SELECT DISTINCT ON (roll_no) name, roll_no FROM exam_scores ORDER BY roll_no DESCHere’s what the output of the above query will look like −nameroll_noRoy2Anil1You can also ... Read More

If you are a programmer, you may be very familiar with IF-ELSE statements. The equivalent in PostgreSQL is CASE WHEN.Let’s understand with an example. If you have table marks containing percentage marks of a student, and you want to find out whether the students have passed or failed. An example table is given below.nameperc_marksAnil24Joy65Ron42Reena87Say the passing marks are 40. Now, if the student has scored above 40 marks, we want to print ‘PASS’ against that student’s name, otherwise ‘FAIL’. This is how you can do it −SELECT name, CASE WHEN perc_marks >= 40 THEN 'PASS' ELSE 'FAIL' END status from ... Read More

Quite often, you need the current timestamp in PostgreSQL. You do that as follows −SELECT current_timestampIt will output the current time. The output will look like the following −2021-01-30 15:52:14.738867+00Now, what if you want the relative time instead of the current time? For example, if you want the time corresponding to 5 hours prior to the current time, you can get it using intervals.SELECT current_timestamp - interval '5 hours'The output will be different every time. At the time of writing this, the output was −2021-01-30 10:57:13.28955+00 You can also do these operations on date instead of timestampsSELECT current_dateOutput2021-01-30SELECT current_date + ... Read More

Suppose you have two tables: marks and student_info. Examples are given below for the two respectivelynameroll_noperc_marksAniket1224Siddhi4565Yash2642Isha5687nameroll_noagegenderAniket1226MIsha5625FSiddhi4523FYash2625MNow, suppose your manager at work looks at the two tables and tells you, “Why do we have two tables? Simplify things, shift everything to one table!”So you decide to add the perc_marks column to the student_info table.ALTER TABLE student_info ADD COLUMN perc_marks integerNow, how will you populate this column? Will you manually add the marks for each column? That will leave the room open for a lot of errors and will also be very time-consuming. Instead, this is what you could do −UPDATE student_info ... Read More

Indexing is used to speed up query execution in PostgreSQL, and any relational database in general. PostgreSQL tables primarily support several index types. Let us discuss 3 frequently user index types in brief −HashThese indexes can only handle equality comparisons. In other words, if I want to check if itemA = itemB, then hash indexes are useful. It is not suited for other types of operations, like >, =, 40then this index will be useless, and PostgreSQL will organize the query plan assuming the index doesn’t exist.B-treeThis is the default index used by PostgreSQL. In other words, if you ... Read More

There are 6 types of constraints that can be generally used with a PostgreSQL table. They are listed and explained below −NOT NULL CONSTRAINTThis is a very common constraint. If there is a particular column that cannot have null values, you add this constraint at the time of table creation. For example, if we create a marks table, which can’t have NULL values for the name, then the table creation command will look like −CREATE TABLE marks(    name VARCHAR NOT NULL,    roll_no INTEGER,    marks_obtained INTEGER );Now, if we try to insert a row into this table, without ... Read More

The ability to define JSON columns in PostgreSQL makes it very powerful and helps PostgreSQL users experience the best of both worlds: SQL and NoSQL.Creating JSON columns is quite straightforward. You just have to create/ define it like any other column and use the data type as JSON.Let us create a new table in PostgreSQL, called json_test −CREATE TABLE json_test(    serial_no SERIAL PRIMARY KEY,    name VARCHAR,    metadata JSON );Now, let us populate it with some data −INSERT INTO json_test(name, metadata) VALUES ('Yash', '{"marks_scored":{"science":50, "maths":65}}'), ('Isha', '{"marks_scored":{"science":70, "maths":45}}');As you can see, the JSON values are added within single ... Read More

For understanding these concepts with examples, we will consider two tables, marks, and student_info, defined respectively below −marks −nameroll_noperc_marksSiddhi4565Yash2642Isha5687student_info −nameroll_noagegenderAniket1226MIsha5625FYash2625MAs you can see, the marks table doesn’t have an entry for Aniket, while the student_info table doesn’t have an entry for Siddhi. In other words, the marks table doesn’t have an entry for roll_no 12, while the student_info table doesn’t have an entry for roll_no 45. Now, let us understand the different JOINS one by one.INNER JOINIt returns only those rows for which entries are present in both the tables.SELECT marks.name, marks.roll_no, student_info.age FROM marks INNER JOIN student_info on marks.roll_no ... Read More

In order to change the type of a column, the syntax isALTER TABLE table_name ALTER COLUMN column_name TYPE new_data_type USING expression;Please note that altering the type of a column is not recommended generally, especially if your table has a lot of entries already.The USING part of the syntax is optional. It is used when you need to use an expression for converting the existing entries in that column from the current type to the new type. For instance, if you convert a column of type INTEGER to type BIGINTEGER, you need not use any expression for the conversion of existing ... Read More

The syntax to add a new column to an existing table is quite straightforward.ALTER TABLE table_name ADD COLUMN column_name column_type column_constraint;Say you have existing table marks. An example is given below −serial_nonameroll_nomarks_obtainedperc_marksmax_marksdate_of_entry1Yash2642421002021-01-302Isha5617587.52002021-01-30Now, suppose you want to add a column named subject. You can do that using −ALTER TABLE marks ADD COLUMN subject VARCHAR;Now if you query the table again using, SELECT * from marksYou will see the following output  −serial_nonameroll_nomarks_obtainedperc_ marksmax_ marksdate_ of_ entrysubject1Yash2642421002021-01-30[null]2Isha5617587.52002021-01-30[null]Note that the values in the subject column are null because we have just created the column, not populated it. We can populate it using the UPDATE ... Read More