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 ... 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 ... 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 ... 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 ... 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 ... 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. ... 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 ... 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, ... 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 ... 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 ... Read More