Article Categories
- All Categories
-
Data Structure
-
Networking
-
RDBMS
-
Operating System
-
Java
-
MS Excel
-
iOS
-
HTML
-
CSS
-
Android
-
Python
-
C Programming
-
C++
-
C#
-
MongoDB
-
MySQL
-
Javascript
-
PHP
-
Economics & Finance
Articles by Yash Sanghvi
Page 19 of 20
Extract day, hour, minute, etc. from a datetime column in PostgreSQL?
Let us create a new table containing a single timestamp column −CREATE TABLE timestamp_test( ts timestamp );Now let us populate it with some data −INSERT INTO timestamp_test(ts) VALUES(current_timestamp), (current_timestamp+interval '5 days'), (current_timestamp-interval '18 hours'), (current_timestamp+interval '1 year'), (current_timestamp+interval '3 minutes'), (current_timestamp-interval '6 years');If you query the table (SELECT * from timestamp_test), you will see the following output −ts2021-01-30 19:23:24.0080872021-02-04 19:23:24.0080872021-01-30 01:23:24.0080872022-01-30 19:23:24.0080872021-01-30 19:26:24.0080872015-01-30 19:23:24.008087Now, in order to extract hour, minute, etc. from the timestamp column, we use the EXTRACT function. Some examples are shown below −SELECT EXTRACT(HOUR from ts) as hour from timestamp_testOutput −hour19191191919Similarly −SELECT EXTRACT(MONTH from ts) as ...
Read MoreHow to combine different columns of a table to yield a single column in query output in PostgreSQL?
Suppose you have a table user_info that contains the state and district of different users. An example is given below −namedistrictstateAnilMumbaiMaharashtraJoyJhalawarRajasthanRonPuneMaharashtraReenaMeerutUttar PradeshNow, if you want to combine the state and district in a single field called location, this is how you should be able to do it −SELECT name, district || ', ' || state as location from user_infoThe || operator is the string concatenation operator. The output will be −namelocationAnilMumbai, MaharashtraJoyJhalawar, RajasthanRonPune, MaharashtraReenaMeerut, Uttar PradeshSimilar operations can also be performed on numerical values. Suppose you have a table marks containing the total marks scored by students and the maximum ...
Read MoreHow to look for partial string matches in queries in PostgreSQL?
Suppose you have a table user_info containing the names of users and their addresses. An example is given below −nameaddressAnilAndheri, Mumbai, MaharashtraJoyChandni Chowk, DelhiRonBandra, Mumbai, MaharashtraReenaOld Airport Road, Bengaluru, KarnatakaNow, if you want to just extract the information of users who stay in Mumbai, you can do that using the LIKE command and the % operator.SELECT * from user_info where address LIKE '%Mumbai%'The output will benameaddressAnilAndheri, Mumbai, MaharashtraRonBandra, Mumbai, MaharashtraNotice that we have added % operator on both sides of Mumbai. This means that anything can precede Mumbai and anything can be after Mumbai. We just want the string to ...
Read MoreHow to apply DISTINCT constraint on select columns in queries in PostgreSQL?
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 MoreCASE WHEN in PostgreSQL?
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 MoreHow to get current timestamp and relative timestamps in PostgreSQL?
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 MoreHow to insert values from one table into another in PostgreSQL?
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 MoreHow to define and query json columns in PostgreSQL?
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 MoreHow to change the type of a column in PostgreSQL?
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 MoreHow to add column to an existing table in PostgreSQL?
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