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

Creating a table in PostgreSQL is pretty straightforward. The syntax is −CREATE TABLE table_name(    Column1_name type optional_constraint,    Column2_name type optional_constraint,    .    .    .    ColumnN_name type optional constraint );If you want to make sure that your table is created only if it doesn’t already exist, you can specify that explicitly −CREATE TABLE IF NOT EXISTS table_name(…);An example of table creation is given below −CREATE TABLE marks(    serial_no SERIAL PRIMARY KEY,    name VARCHAR,    roll_no INTEGER,    marks_obtained INTEGER,    perc_marks DOUBLE PRECISION,    max_marks INTEGER,    date_of_entry DATE );The above command will just ... Read More

Sometimes, some rogue queries can take too long to execute. If the queries are blocking in nature, i.e., they restrict access to a table while they are executing, then any other query on the same table will be put on hold, and this leads to a pile-up of queries. This can, depending on your DB load, even cause the max connections to be exceeded. Luckily, you can easily kill long queries in pgAdmin.Go to Dashboard in your pgAdmin. At the bottom, in the Server Activity section, under the Sessions Tab, you can see all the Active queries.Now, notice the cross ... Read More

Querying a DB in pgAdmin is quite straightforward. Locate your DB in the Servers dropdown on the left, and extend its dropdown, till you see the Schemas dropdown.Once you click on Schemas, the black button on the top, with the DB symbol and the play arrow will become clickable.Click on that button, and you will see a Query Tab open up. That’s it, you can type your queries for this particular DB in that box, and click the play arrow button to execute the queries.The output will be seen on the bottom, in the ‘Data Output’ section.Read More