Yash Sanghvi has Published 220 Answers

How to get current timestamp and relative timestamps in PostgreSQL?

Yash Sanghvi

Yash Sanghvi

Updated on 02-Feb-2021 12:54:56

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

What kind of indexing can be done on a PostgreSQL table?

Yash Sanghvi

Yash Sanghvi

Updated on 02-Feb-2021 12:31:13

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

How to insert values from one table into another in PostgreSQL?

Yash Sanghvi

Yash Sanghvi

Updated on 02-Feb-2021 12:30:41

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

How to define and query json columns in PostgreSQL?

Yash Sanghvi

Yash Sanghvi

Updated on 02-Feb-2021 12:22:41

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

INNER JOIN vs FULL OUTER JOIN vs LEFT JOIN vs RIGHT JOIN in PostgreSQL?

Yash Sanghvi

Yash Sanghvi

Updated on 02-Feb-2021 12:18:49

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

How to change the type of a column in PostgreSQL?

Yash Sanghvi

Yash Sanghvi

Updated on 02-Feb-2021 12:11:32

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

How to add column to an existing table in PostgreSQL?

Yash Sanghvi

Yash Sanghvi

Updated on 02-Feb-2021 12:07:55

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

How to create a table in PostgreSQL?

Yash Sanghvi

Yash Sanghvi

Updated on 02-Feb-2021 11:45:34

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

How to Kill queries in pgAdmin in PostgreSQL?

Yash Sanghvi

Yash Sanghvi

Updated on 02-Feb-2021 11:41:24

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

How to Query a DB in pgAdmin in PostgreSQL?

Yash Sanghvi

Yash Sanghvi

Updated on 02-Feb-2021 11:41:01

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

Advertisements