Postgresql

Maria Durkin

Best PostgreSQL Courses for Beginners

So you want to learn PostgreSQL but aren't sure how to get started? In this article, we look at some of the best online courses available on the Internet for learning PostgreSQL, so you can get started right away! What Is PostgreSQL? PostgreSQL is used in major companies such as Apple and Instagram. You may be wondering: should I learn PostgreSQL? PostgreSQL is a popular dialect of SQL, or Structured Query Language, a programming language for accessing and managing data in relational databases.

Tihomir Babic

How to Solve the “must appear in the GROUP BY clause or be used in an aggregate function” Error in PostgreSQL

Learn what causes one of the most common PostgreSQL GROUP BY errors and how you can fix it! As a PostgreSQL user, you surely encounter many error messages when writing an SQL code. Sometimes they are quite unclear, but you won’t need an interpreter for this one: “must appear in the GROUP BY clause or be used in an aggregate function”. This error message mentions GROUP BY and aggregate functions.

Jakub Romanowski

Free Course of the Month – PostgreSQL JOINs

Our PostgreSQL JOINs course will help you consolidate your knowledge of working with data from two or more tables in a PostgreSQL database. Throughout the month of February 2023, this awesome SQL course is absolutely FREE! Wondering why we want to give you this SQL course for free? We've been doing this for a long time. Every month, we choose one of our interactive online SQL courses and give users free access to it.

Jakub Romanowski

Free SQL Course of the Month – Window Functions in PostgreSQL

Are you looking for the best way to master SQL window functions? In this article, I'll show you how you can do so for free. Throughout January 2023, you can access the interactive course Window Functions in PostgreSQL for free. Hurry, time is running out! Need a free PostgreSQL course on window functions? You've come to the right place! If you are reading this article, you probably know PostgreSQL is one of the most popular databases in the world.

Tihomir Babic

An Overview of SQL Text Functions in PostgreSQL

If you’re a PostgreSQL user and into data analysis, you’ve heard of the text functions or maybe even used them. But what are PostgreSQL’s most popular text functions? In today’s article, we’ll show you what they are and how to use them. As anyone who works with them knows, databases don’t contain only numerical values. Among other data types, databases also store text or string data. To use PostgreSQL effectively, you’ll have to know at least some of Postgres’ text functions.

Alexandre Bruffa

PostgreSQL vs. MySQL: Is It Difficult to Switch?

So you know PostgreSQL, but the boss has decided to switch to MySQL. What are the differences between these two SQL dialects? We discuss PostgreSQL vs. MySQL in this article. PostgreSQL has gained popularity in the last decade, and many developers and database specialists learned PostgreSQL as their first SQL dialect. But PostgreSQL is not the only relational database; during your IT career, you will probably be asked to use another SQL dialect – like MySQL.

Alexandre Bruffa

I Already Know PostgreSQL. Is it Hard to Start Using MS SQL Server?

What are the differences in PostgreSQL vs. MS SQL Server? We discuss the similarities and the differences between these two popular SQL dialects. Many of us developers and database specialists began working with free relational databases like PostgreSQL or MySQL for personal projects or small organizations. Some of us have since moved on to organizations using MS SQL Server and have had to learn to use the new tool. You may be wondering how challenging that is and whether you would struggle with SQL Server under similar circumstances.

Jakub Romanowski

Free SQL Course of the Month – PostGIS

What is PostGIS, and why should you learn it? (Hint: Our SQL PostGIS course is free throughout June 2022!) Did you know databases can store geographic data? This is how interactive maps are made, most often based on PostGIS. In this article, I will tell you what PostGIS is and why it’s worth learning. Moreover, I am going to offer you an awesome online PostGIS SQL course which you can do for free during June 2022!

Jakub Romanowski

Free SQL Course of the Month – PostgreSQL INSERT, UPDATE, and DELETE Commands

Do you want to be a data engineer or data analyst? Do you work with databases daily and need to expand your skill with the basics of data manipulation? You've come to the right place. For May 2022, our SQL course of the month is PostgreSQL INSERT, UPDATE, and DELETE Commands! Learn SQL for FREE! How do you get this free PostgreSQL course in May? Create a free LearnSQL.

Karolina Niewiarowska

Is Learning PostgreSQL in 2022 Worthwhile?

You want to take a SQL course, but selecting the right one can be too challenging. Perhaps you don't know which dialect you should start with? PostgreSQL is one of the best options. Check out if it is worth it to learn PostgreSQL in 2022. When you search the web, there are many job advertisements for database specialists. Perhaps that is why you have chosen the direction toward development. Or maybe you are just fed up with Excel in your day-to-day duties (which is not surprising) and want to make work easier.

Andrew Bone

PostgreSQL Interview Questions and Answers

Job interviews are always stressful. Interviewing and getting asked technical questions about PostgreSQL is even more of a challenge! In this article, we’ll cover some of the PostgreSQL interview questions you can expect when applying for a new job. By the end of the article, you should be able to handle most of the questions you are likely to face. We’ve previously covered common questions you can expect at a SQL job interview, but this article will be focused on Postgres specifically.

Kateryna Koidan

SQL Date and Time Functions in 5 Popular SQL Dialects

Are you confused by all the date and time functions used across different SQL dialects? In this article, I summarize the date and time data types used in PostgreSQL, Oracle, SQLite, MySQL, and T-SQL. I also provide examples with the key SQL date and time functions used across these dialects. It’s time to become date and time gurus! Do you want to calculate how often employees are running late for work?

Martyna Sławińska

An Overview of PostgreSQL Data Types

It’s vital to know what you actually store in your database. That’s why every column has its data type. There are numerous PostgreSQL data types across various categories. Read on to see which data type suits which use case! In this article, we’ll go through the most common data types used in PostgreSQL. PostgreSQL, also called Postgres, is a well-known open-source object-relational database management system (DBMS). It is SQL-compliant, extensible, and includes all standard relational database features, such as database constraints, transactions, views, triggers, stored procedures, and more.

LearnSQL.com Team

Breaking With Filing Cabinets: The History of PostgreSQL

This July, PostgreSQL turns 25. It has had an extensive influence on computing. Thanks to PostgreSQL, the world said “Goodbye!” to filing cabinets as the standard for storing and processing data. So, who created PostgreSQL, and how has it changed over the decades? There are quite a few PostgreSQL enthusiasts among my colleagues at LearnSQL.com. It is simply a great database solution. Why? It is free and open-source. So, you can use it for free, even for commercial uses.

Jakub Romanowski

Want to Learn GROUP BY Extensions in PostgreSQL? You've Come to the Right Place!

Do you want to take your knowledge of PostgreSQL to the next level? I have something for you: LearnSQL.com’s new GROUP BY Extensions in PostgreSQL course! It will help you get even more out of your data and create better reports and summaries. Fasten your seat belts and prepare to see what GROUP BY can do when it’s turbocharged. First, I have to confess something. I am not objective – I think PostgreSQL is the best and coolest SQL dialect.

Martyna Sławińska

MS SQL Server vs. PostgreSQL: Which Should You Choose for a New Project?

Choosing the DBMS you will use for a new project is a very important and difficult decision. Two popular choices are MS SQL Server and PostgreSQL. To help you decide which would be best for you, I'll compare their features, list pros and cons, and give you some examples. Your selection of database management system (DBMS) depends on the type of business or project you're implementing. First, you need to know:

Kamila Ostrowska

How to Practice PostgreSQL Online

PostgreSQL is hot right now!If you want to join a community of people passionate about data, this open-source database system is something you need to try. Like everything we teach on LearnSQL.com, PostgreSQL requires practice. To profit from Postgres – as this dialect is sometimes called – you need to start with the basics. You can watch some tutorials on YouTube or read articles to get to know what a Database Management System (DBMS) is.

Tihomir Babic

Want to Learn Advanced PostgreSQL? Try This!

Why and how should you learn advanced PostgreSQL? I’ll give you some tips that will help you achieve your goals. Keeping up with technology is hard; anyone who has foolishly tried to do so can confirm this (me, for example!). Most of the time, it just doesn't work. And to be brutally honest, keeping up with all the technologies all the time is pointless. You shouldn’t know everything about everything; it’s better to know as much as you need within the technology you use or want to use.

Jakub Romanowski

How to Import CSVs to PostgreSQL Using PgAdmin

Do you work with data and use CSV files? Here is a practical guide on how to import such files into a PostgreSQL database using pgAdmin, one of the best PostgreSQL editors on the market. Let's get right into importing CSVs into a Postgres database. We’ll start by explaining what a CSV file is, then we’ll introduce you to pgAdmin and show you how to do the import process. Don’t worry – it’s easy!

Jakub Romanowski

Which Major Companies Use PostgreSQL? What Do They Use It for?

Which companies use the open-source relational database management system PostgreSQL? Find out … and learn why you should be a PostgreSQL user too. You know the blue elephant logo. You have heard that PostgreSQL is a very good solution. But which companies use it? Here is a list of the largest IT and business companies that use PostgreSQL (or Postgres, for short). Multinationals worth billions of dollars can't be wrong, right?

Ignacio L. Bisso

How to Install PostgreSQL on Windows 10 in 5 Minutes

It may look like a complicated task to install PostgreSQL, Oracle, or SQL Server or any other database software, but it really isn’t! These days, most relational database management systems come with installation wizards that make the process much simpler. In this article, we’ll look at how to install PostgreSQL and test that the installation is working. Install PostgreSQL: Steps Involved To install PostgreSQL, we’ll complete the following tasks:

Ignacio L. Bisso

Extracting Data From a String: SPLIT_PART in PostgreSQL

Learn how to use split_part in PostgreSQL to extract data from strings. Quite often, we’d like to extract parts of a string when working with text values. A common example is when we have a full name and need to retrieve only the last name. In this article, we’ll examine how to do it using split_part in PostgreSQL, i.e. a string-related function that can be used to extract a substring.

Maria Alcaraz

Your First Steps With the Geography Data Type

Geographical applications are everywhere: GPS and sat nav systems, maps, get-a-taxi apps, real estate portals, etc. Behind each of them is a spatial database storing geographical data, and supporting spatial queries. In this article, we will introduce PostGIS, the main open-source spatial database manager. PostGIS is a spatial database extension for the PostgreSQL relational database. It adds support for geographic objects, allowing location queries to be run in SQL. PostGIS adds two main data types to PostgreSQL: geography and geometry.

Maria Alcaraz

Pivot Tables in PostgreSQL Using the Crosstab Function

Some years ago, when PostgreSQL version 8.3 was released, a new extension called tablefunc was introduced. This extension provides a really interesting set of functions. One of them is the crosstab function, which is used for pivot table creation. That's what we'll cover in this article. The simplest way to explain how this function works is using an example with a pivot table. First, we will explain our initial point from a practical perspective, then we'll define the pivot table we want.

Patrycja Dybka

PostgreSQL Collations

List Collations Collations in PostgreSQL are available depending on operating system support. For example, in Ubuntu type the following to list the names of the available collations: locale -a The same locales are available in PostgreSQL in the pg_collation catalog (mappings from an SQL name to operating system locale categories). select * from pg_collation; collname | collnamespace | collowner | collencoding | collcollate | collctype -----------+---------------+-----------+--------------+-------------+------------ default | 11 | 10 | -1 | | C | 11 | 10 | -1 | C | C POSIX | 11 | 10 | -1 | POSIX | POSIX C.

Agnieszka Kozubek-Krycuń

DELETE RETURNING clause in PostgreSQL

The standard DELETE statement in SQL returns the number of deleted rows. DELETE FROM external_data; DELETE 10 In PostgreSQL you can make DELETE statement return something else. You can return all rows that have been deleted. DELETE FROM external_data RETURNING *; id | creation_date | user_id | data ----+---------------------------+---------+---------------- 101 | 2014-05-06 13:10:45.09484 | 23 | 'Some text' 102 | 2014-06-10 22:23:12.12045 | 25 | 'Some other text' (2 rows) DELETE 2

Agnieszka Kozubek-Krycuń

How to Select the First Row in a Group?

Often you want to select a single row from each GROUP BY group. PostgreSQL has a statement especially for that: SELECT DISTINCT ON. Let's say I want to select one weather report for each location. location time report Ottawa 2014-05-15 8:00 sunny 2014-05-15 11:00 cloudy 2014-05-15 15:00 rainy Warsaw 2014-05-15 8:00 overcast 2014-05-15 11:00 sunny 2014-05-15 15:00 rainy SELECT DISTINCT ON (location) location, time, report FROM weather_reports;

Agnieszka Kozubek-Krycuń

MySQL's group_concat Equivalents in PostgreSQL, Oracle, DB2, HSQLDB, and SQLite

The GROUP_CONCAT() function in MySQL MySQL has a very handy function which concatenates strings from a group into one string. For example, let's take a look at the children table with data about parents' and children's names. if (typeof VertabeloEmbededObject === 'undefined') {var VertabeloEmbededObject = "loading";var s=document.createElement("script");s.setAttribute("type","text/javascript");s.setAttribute("src", "https://my.vertabelo.com/js/public-model/v1/api.js");(document.getElementsByTagName("head")[0] || document.documentElement ).appendChild(s);} parent_name child_name John Tom Michael Sylvie John Anna Michael Sophie To get the names of children of each person as a comma-separated string, you use the GROUP_CONCAT() function as follows:

What’s the Difference Between RANK and DENSE_RANK in SQL?

Problem: You want to compare the rankings produced by RANK and DENSE_RANK and add them as new columns to a table. Example: Our database has a table named sales_assistant with data in the following columns: id (primary key), first_name, last_name, month, and sold products. idfirst_namelast_namemonthsold products 1LisaBlack 52300 2MaryJacobs52400 3LisaBlack 62700 4MaryJacobs62700 5AlexSmith 62900 6MaryJacobs71200 7LisaBlack 71200 8AlexSmith 71000 Let’s display each sales assistant’s first and last name and number of sold products.

How to remove a unique constraint in SQL?

Problem: You would like to remove a unique constraint from the column/columns in a table in a database. Example: We would like to drop the unique constraint in the table product from the column name. The statement below shows how to do it. Solution ALTER TABLE product DROP CONSTRAINT UQ_product_name In this example the table product is altered by using the ALTER TABLE clause. After this clause you enter the name of the table (in our example: product) and the clause DROP CONSTRAINT with the name of the unique constraint you want to remove.

How to make a column unique in SQL?

Problem: You would like to make a given column unique in a given table in a database. Example: We would like to make the column name unique in the table product. The query below presents one way to do it. Solution 1 CREATE TABLE product ( id INT NOT NULL PRIMARY KEY, name VARCHAR(100) UNIQUE, producer VARCHAR(100), category VARCHAR(100) ); Discussion: In this example a given column (the column name) was made unique by adding the clause UNIQUE at the end of the definition column (name VARCHAR(100) UNIQUE).

How to Use LIKE in SQL

Problem: You’d like to find a substring matching a pattern in a string. Example: Our database has a table named customer with data in the following columns: id, first_name, last_name, and city. idfirst_namelast_namecity 1AlanWatsonMadrid 2LisaBlackSingapore 3LauraWilliamsSeattle 4MilanBrickSeoul Let’s see some examples of pattern matching in SQL. Example 1: Let’s display the first name, last name, and city of each customer whose city name starts with the letter 'S.

How to Trim Strings in SQL

Problem: You’d like to remove specific characters from the beginning and end of a string. Example: Our database has a table named post with data in two columns: id and title. idtitle 1' News about dogs ' 3'Eurovision 2019 ' 4'New political climate' Let’s trim the title of each post to remove the unnecessary space at the beginning and end. Solution: We’ll use the TRIM function.

How to Sum Values of a Column in SQL?

Problem: You’d like to sum the values of a column. Example: Our database has a table named game with data in the following columns: id, player, and score. idplayerscore 1John134 2Tom 146 3Lucy20 4Tom 118 5Tom 102 6Lucy90 7Lucy34 8John122 Let’s find the total score obtained by all players. Solution: SELECT SUM(score) as sum_score FROM game; Here’s the result:

How to Round Up a Number to the Nearest Integer in SQL

Problem: You want to round up a number to the nearest integer. Example: Our database has a table named rent with data in the following columns: id, city, area, and bikes_for_rent. idcityareabikes_for_rent 1Los Angeles1302.151000 2Phoenix1340.69500 3Fargo126.44101 Let’s show each city’s name along with the ratio of its area to the number of bikes for rent.

How to Round Numbers in SQL

Problem: You want to round a number to a specific number of decimal places. Example: Our database has a table named product with data in the following columns: id, name, and price_net. idnameprice_net 1bread2.34 2croissant1.22 3roll0.68 Suppose there’s a tax of 24% on each product, and you’d like to compute the gross price of each item (i.e., after taxes) and round the value to two decimal places.

How to Replace Part of a String in SQL

Problem: You’d like to replace part of a string with another string. Example: Our database has a table named investor with data in the following columns: id, company, and phone. idcompanyphone 1Big Market123–300-400 3The Sunny Restaurant123–222-456 4My Bank123-345-400 We’d like to change the phone number format for each company by replacing the hyphen character with a space.

How to Order by Date in PostgreSQL or Oracle

Problem: You want to sort the rows by date. Example 1: The exam table has two columns, subject and exam_date. subjectexam_date Mathematics2019-12-19 English2020-01-08 Science2020-01-05 Health2020-01-05 ArtNULL You want to sort the rows by exam_date. Solution: SELECT * FROM exam ORDER BY exam_date; The result looks like this (the rows are sorted in ascending order by exam_date):

How to Order by Count in SQL?

Problem: You aggregated data into groups, but you want to sort the records in descending order by the number of elements in the groups. Example: Our database has a table named user with data in the following columns: id, first_name, last_name, and country. idfirst_namelast_namecountry 1LisaWilliamsEngland 2GaryAndersPoland 3TomWilliamsPoland 4MichaelBrownFrance 5SusanSmithUSA 6AnneJonesUSA 7EllieMillerPoland Let’s create a report on our users.

How to Order By Two Columns in SQL?

Problem: You need to display records from a given table sorted by two columns. Example: Our database has a table named employee with the following columns: id, first_name, last_name, and salary. idfirst_namelast_namesalary 1LisaUlman3000 2AdaMuller2400 3ThomasGreen2400 4MichaelMuller3000 5MaryGreen2400 Let’s display all information for each employee but sort the records according to salary in descending order first and then by last name in ascending order.

How to Order Alphabetically in SQL

Problem: You want to display records from a table in alphabetical or reverse-alphabetical order according to given column. Example: Our database has a table named customer. The customer table contains data in the id, first_name, and last_name columns. id first_name last_name 1 Susan Thomas 2 John Michael 3 Tom Muller Let’s display each customer’s information, sorted in ascending order by their last name.

How to Number Rows in SQL

Problem: You would like to give each row in the result table a separate number. Example: Our database has a table named furniture with data in the following columns: code (primary key) and name. codename 101bed 202sofa 333chair 123bookcase 235table 766desk The furniture table stores the name of pieces of furniture that we want to number.

How to Multiply Two Columns in SQL

Problem: want to multiply values from two columns of a table. Example: Our database has a table named purchase with data in the following columns: id, name, price, quantity, and discount_id. idnamepricequantitydiscount_id 1pen731 2notebook582 3rubber1131 4pencil case2423 Let’s multiply the price by the quantity of the products to find out how much you paid for each item in your order.

How to Join on Multiple Columns

Problem: You want to join tables on multiple columns by using a primary compound key in one table and a foreign compound key in another. Example: Our database has three tables named student, enrollment, and payment. The student table has data in the following columns: id (primary key), first_name, and last_name. idfirst_namelast_name 1EllieWillson 2TomBrown 3SandraMiller The enrollment table has data in the following columns: primary key (student_id and course_code), is_active, and start_date.

How to Join Multiple (3+) Tables in One Statement

Problem: You’d like to combine data from more than two tables using only one SELECT statement. Example: There are four tables in our database: student, teacher, subject, and learning. The student table contains data in the following columns: id, first_name, and last_name. idfirst_namelast_name 1TomMiller 2JohnSpring 3LisaWilliams 4EllieBarker 5JamesMoore The teacher table contains data in the following columns: id, first_name, last_name, and subject.

How to Handle Divide by Zero In SQL

Problem: You want to perform division in your SQL query, but the denominator is an expression that can be zero. The database will give you an error when the denominator is in fact zero. Example: Our database has a table named investor_data with data in the following columns: id, investor_year, price_per_share, income, and expenses. idinvestor_yearprice_per_shareincomeexpenses 120162032002300 2201713020002000 3201840200100 420191559004900 Let’s divide the price per share by the difference between income and expenses to determine the P/E ratio (price-earnings ratio) for each year.

How to Group by Year in SQL

Problem: You want to group your data by year. Example I: One of the columns in your data is transaction_date. It contains a date. You would like to group all your data by year and calculate the total money earned each year. The data table looks like this: transaction_datemoney 2018-03-251700 2019-09-12100 2018-07-141200 2018-01-05400 2019-06-082000 2020-03-061500 Solution 1 (displaying the year and the money earned): SELECT EXTRACT(year FROM transaction_date) AS year, SUM(money) AS money_earned FROM data GROUP BY EXTRACT(year FROM transaction_date); The result is:

How to Group by Two Columns in SQL

Problem: You want to group your data by two columns so you can count some statistics. Example: In the order table, you have the columns order_date, product_id, customer_id, and number. You would like to count the number of products bought by each customer each day. The order table looks like this: order_dateproduct_idcustomer_idnumber 2020-11-25711 2020-11-251213 2020-11-265312 2020-11-26124 2020-11-26321 2020-11-261627 2020-11-26332 2020-11-27631 Solution: SELECT order_date, customer_id, SUM(number) AS products_number FROM order The result is:

How to Group by Month in PostgreSQL

Problem: You'd like to group records by month in a PostgreSQL database. Example: Our database has a table named watch with data in the columns id, name, and production_timestamp. idnameproduction_timestamp 1watch2019-03-01 11:45:23 2smartwatch2019-09-15 07:35:13 3smartband2019-09-22 17:22:05 Solution: You can use the DATE_TRUNC() function to group records in a table by month. Here's the query you would write:

How to Get the Day of the Year from a Date in PostgreSQL

Problem: You want to get the day of the year from a date or a timestamp column in PostgreSQL. Example: Our database has a table named software_sale with data in the columns id, software, and sale_date. idsoftwaresale_date 1Super Game X2019-09-15 2Browser X2019-10-15 3DB News2019-11-26 4MyPaintSoft2018-10-15 5New OS2019-10-15 Let’s count the number of software items sold by days of the year.

How to Get the Current Time in PostgreSQL

Problem: You’d like to get the current time with its time zone offset in a PostgreSQL database. Solution: We’ll use the CURRENT_TIME function to get the current time and time zone information. Here’s the query you’d write: SELECT CURRENT_TIME; Here’s the result of the query: 16:10:11.232455-05 Discussion: The PostgreSQL function CURRENT_TIME returns the current time and time zone offset of the machine on which PostgreSQL is running. It is given as a value in the 'hh:mm:ss.

How to Get the Current Time (Without Time Zone) in PostgreSQL

Problem: You’d like to get the current time in a PostgreSQL database. You don’t want the time zone offset. Solution: We’ll use the function LOCALTIME to get the current time without the time zone offset. SELECT LOCALTIME; Here’s the result of the query: 22:15:51.987914 Discussion: The PostgreSQL function LOCALTIME returns the current time on the machine running PostgreSQL. It returns it as a time data type in the 'hh:mm:ss.

How to Get the Current Date in PostgreSQL

Problem: You’d like to get the current date in a PostgreSQL database. Solution: We’ll use the function CURRENT_DATE to get the current date: SELECT CURRENT_DATE ; Here’s the result of the query: 2019-10-24 Discussion: The PostgreSQL CURRENT_DATE function returns the current date (the system date on the machine running PostgreSQL) as a value in the 'YYYY-MM-DD' format. In this format, ‘YYYY’ is a 4-digit year, ‘MM’ is a 2-digit month, and ‘DD’ is a 2-digit day.

How to Get the Current Date and Time with Time Zone Offset in PostgreSQL

Problem: You’d like to get the current date and time with time zone information from a PostgreSQL database. Solution: We’ll use either CURRENT_TIMESTAMP or NOW() to get the current date and time with the time zone offset. SELECT CURRENT_TIMESTAMP ; Here’s the result of the query: 2019-09-15 13:13:12.118432+02 Discussion: CURRENT_TIMESTAMP returns the current date, time, and time zone offset (using the date, time, and time zone of the machine on which PostgreSQL is running).

How to Get the Current Date and Time (Without Time Zone) in PostgreSQL

Problem: You’d like to get the current date and time in a PostgreSQL database. You don’t need the time zone offset. Solution: We’ll use the function LOCALTIMESTAMP to get the current date and time without any time zone information: SELECT LOCALTIMESTAMP ; Here’s the result of the query: 2019-09-24 20:10:58.977914 Discussion: The PostgreSQL function LOCALTIMESTAMP returns the current date and time (of the machine running that instance of PostgreSQL) as a timestamp value.

How to Get Yesterday’s Date in PostgreSQL

Problem: You would like to display yesterday's date (without time) in a PostgreSQL database. Solution 1: SELECT current_date - INTEGER '1' AS yesterday_date; Assuming today is 2020-09-24, the result is: yesterday_date 2020-09-23 Discussion: To get yesterday's date, you need to subtract one day from today's date. Use current_date to get today's date. Note that you don’t need brackets at the end of the current_date function.

How to Floor Numbers in SQL

Problem: You want to round a number down to the nearest integer. Example: Our database has a table named employee with data in the following columns: id, first_name, last_name, and hours_worked (for the current month). idfirst_namelast_namehours_worked 1AlanWatson95 2LisaBlack225 3LauraWilliams104 Let’s show the first and last name of each employee along with the number of days they worked.

How to Find the Minimum Value of a Column in SQL

Problem: You’d like to find the smallest numeric value in a column. Example: Our database has a table named employment with data in the following columns: id, first_name, last_name, department, and salary. idfirst_namelast_namedepartmentsalary 1EllieMartinesmarketing1200 2MartinJohnsonfinance2300 3MichaelJacobsproduction1100 4StephenKowalskimarketing4300 5StanleyMillermarketing3500 6JenyBrownfinance5000 7MargaretGreenmarketing1500 8LisaThomasproduction2800 Let’s find the lowest salary among all employees.

How to Find the Maximum Value of a Numeric Column in SQL

Problem: You’d like to find the maximum value of a numeric column. Example: Our database has a table named product with data in the following columns: id, name, year, and items. idnameyearitems 1bread roll2018345 2chocolate2017123 3butter201934 4bread roll2019456 5butter201856 6butter201778 7chocolate201987 8chocolate201876 Let’s find the maximum number of items sold over all years.

How to Find the Interval Between Two Dates in PostgreSQL

Problem: You’d like to find the difference between two date/datetime values in a PostgreSQL database. Example: Our database has a table named employment with data in the columns id, first_name, last_name, start_date, and end_date: idfirst_namelast_namestart_dateend_date 1BarbaraWilson2010-02-012018-10-30 2RobertAnderson2001-04-172011-12-20 3StevenNelson2005-06-012019-09-23 For each employee, let’s get their first and last name and the difference between the starting and ending dates of their employment.

How to Find the Average of a Numeric Column in SQL

Problem: You’d like to calculate the average of numbers stored in a column. Example: Our database has a table named sale with data in the following columns: id, city, year, and amount. idcityyearamount 1Los Angeles20172345.50 2Chicago20181345.46 3Annandale2016900.56 4Annandale201723230.22 5Los Angeles201812456.20 6Chicago201789000.40 7Annandale201821005.77 8Chicago20162300.89 Let’s calculate the average sales, regardless of city or year.

How to Find Records with NULL in a Column

Problem: You want to find records with NULL in a column. Example: Our database has a table named children with data in four columns: id, first_name, middle_name, and last_name. idfirst_namemiddle_namelast_name 1JohnCarlJackson 2TomNULLThomson 3LisaAliceNULL 4AnneNULLSmith Let’s find the id, first name, and last name of children without a middle name (NULL in column middle_name). We use the IS NULL operator for this. Solution: SELECT id, first_name, last_name FROM children WHERE middle_name IS NULL; Here’s the result:

How to Find Minimum Values in Columns

Problem: You want to find rows that store the smallest numeric value in a column. Example: Our database has a table named weather with data in the following columns: id, city, and temperature. idcitytemperature 1Houston23 2Atlanta20 3Boston15 4Cleveland15 5Dallas34 6Austin28 Here’s how to find cities with the lowest temperature.

How to Find Maximum Values in Rows

Problem: You want to find which rows store the largest numeric value in a given column. Example: Our database has a table named student with data in the following columns: id, first_name, last_name, and grade. idfirst_namelast_namegrade 1LisaJackson3 2GaryLarry5 3TomMichelin2 4MartinBarker2 5EllieBlack5 6MarySimpson4 Let’s find the students who have the highest grades.

How to Find Duplicate Rows in SQL?

Problem: You have duplicate rows in your table, with only the IDs being unique. How do you find those duplicate entries? Example: Our database has a table named product with data in the following columns: id, name, and category. idnamecategory 1steakmeat 2cakesweets 3dsteakmeat 4porkmeat 5cakesweets 6cakesweets Let’s find duplicate names and categories of products.

How to Filter Rows without NULL in a column

Problem: You want to find records without a NULL in a column. Example: Our database has a table named product with data in three columns: id, name, and price. idnameprice 1butterNULL 2milk2.35 3bread3.25 4cheeseNULL Let’s find the names and prices of products that have a price (without a NULL). Do this by using the IS NOT NULL operator.

How to Filter Records with Aggregate Function SUM

Problem: You need to find rows in which groups have a sum of values in one column less than a given value. Example: Our database has a table named company with data in the following columns: id, department, first_name, last_name, and salary. iddepartmentfirst_namelast_namesalary 1marketingLoraBrown2300 2financeJohnJackson3200 3marketingMichaelThomson1270 4productionTonyMiller6500 5productionSallyGreen2500 6financeOlivierBlack3450 7productionJenifferMichelin2800 8marketingJeremyLorson3600 9marketingLouisSmith4200 Let’s find the names of departments that have sums of salaries of its employees less than 7000.

How to Filter Records with Aggregate Function COUNT

Problem: You want to find groups of rows with a specific number of entries in a group. Example: Our database has a table named product with data in the following columns: id, name and category. idnamecategory 1sofafurniture 2glovesclothing 3T-Shirtclothing 4chairfurniture 5deskfurniture 6watchelectronics 7armchairfurniture 8skirtclothing 9radio receiverelectronics Let’s find the category of products with more than two entries.

How to Filter Records with Aggregate Function AVG

Problem: You want to find groups of rows in which the average of values in a column is higher or lower than a given value. Example: Our database has a table named product with data in the following columns: id, name, grocery and price. idnamegroceryprice 1milkGreen Shop2.34 2breadClark’s Grocery3.56 3breadSuper Market4.15 4milkSuper Market1.80 5breadGrocery Amanda2.26 6milkViolet Grocery3.

How to Extract the Week Number from a Date in PostgreSQL

Problem: You want to get the week number of a date or timestamp value in a PostgreSQL database. Example: Our database has a table named children with data in the columns id, first_name, last_name, and birth_date. idfirst_namelast_namebirth_date 1AngelaMichelin2018-01-01 2MartinJackson2002-07-26 3JustinClark2010-12-26 4FrankBarker2008-06-08 For each child, let’s get their first name, last name, and the week number of their birth date.

How to Eliminate Duplicate Rows in SQL

Problem: You’d like to eliminate any duplicate rows from the result set of a query so that each row appears only once. Example: Our database has a table named clothes with data in the following columns: id, name, color, and year_produced. idnamecoloryear_produced 1T-shirtyellow2015 2jacketblue2016 3jeansblack2015 4jacketblue2015 5jacketgreen2016 6jacketyellow2017 7hatyellow2017 Let’s get the non-repeated names and colors of clothes produced before 2017.

How to Delete a Foreign Key Constraint in SQL

Problem: You want to drop a foreign key from a table in a database. Example: We want to remove the foreign key named fk_student_city_id from the table student. Solution 1 (new table): ALTER TABLE student DROP CONSTRAINT fk_student_city_id; Discussion: To drop a foreign key from a table, use the ALTER TABLE clause with the name of the table (in our example, student) followed by the clause DROP CONSTRAINT with the name of the foreign key constraint.

How to Create a Table with a Foreign Key in SQL

Problem: You want to create a foreign key for a table in a database. Example: We would like to create a table named student that contains a foreign key that refers to the id column in the table city. Solution 1 (new table): CREATE TABLE student ( id INT PRIMARY KEY, first_name VARCHAR(100) NOT NULL, last_name VARCHAR(100) NOT NULL, city_id INT FOREIGN KEY REFERENCES city(id) ); Discussion: To create a new table containing a foreign key column that references another table, use the keyword FOREIGN KEY REFERENCES at the end of the definition of that column.

How to Count the Number of Rows in a Table in SQL

Problem: You’d like to determine how many rows a table has. Example: Our database has a table named pet with data in the following columns: id, eID (electronic identifier), and name. ideIDname 123456sparky 223457mily 3NULLlessy 4NULLcarl 534545maggy Let’s count all rows in the table. Solution: COUNT(*) counts the total number of rows in the table:

How to Count Distinct Values in SQL

Problem: You’d like to count how many different non-NULL values there are in a given column. Example: Our database has a table named customer with data in the following columns: id, first_name, last_name, and city. idfirst_namelast_namecity 1JohnWilliamsChicago 2TomBrownAustin 3LucyMillerChicago 4EllieSmithDallas 5BrianJonesAustin 6AllanDavisNULL Let’s find the number of different (and non-NULL) cities.

How to Convert a String to a Timestamp in PostgreSQL

Problem: You’d like to convert a string containing datetime information to a timestamp in PostgreSQL. Let’s convert a string containing date, time, and time zone information to the timestamptz data type. Solution: We’ll use the TO_TIMESTAMP() function. Here’s the query you would write: SELECT TO_TIMESTAMP('2018/08/27/15:23:45', 'YYYY/MM/DD/HH24:MI:ss') AS new_timestamptz; Here’s the result of the query: new_timestamptz 2018-08-27 15:23:45+02 Discussion: Use the PostgreSQL function TO_TIMESTAMP() when you want to convert a string containing date and time data to the timestamp data type.

How to Convert a String to a Date in PostgreSQL

Problem: You’d like to convert a string containing a date to the PostgreSQL date data type. Let’s convert a date string to the date data type instead of its current text data type. Solution: Here’s Example 1 of how to use the TO_DATE() function. This is the query you would write: SELECT TO_DATE('20020304', 'YYYYMMDD') AS new_date; And here’s the result of the query: new_date 2002-03-04 Let’s look at Example 2 of the TO_DATE() function.

How to Convert a String to Uppercase in SQL

Problem: You would like to convert a string to uppercase. Example: Our database has a table named questionnaire with data in the following columns: id, first_name, last_name, and favorite_car. idfirst_namelast_namefavorite_car 1AlanJacksonHonda Civic 2ElisaThomsonTOYOTA Camry 3MaryMartinesNissan rogue 4ChrisBrownford focus 5AdamSpringRam PICKUP Our table stores the make and model of the favorite car for each person who filled out our questionnaire.

How to Convert a String to Lowercase in SQL

Problem: You would like to convert a string to lowercase. Example: Our database has a table named product with data in the id and name columns. idname 1Cobb Salad 2Pot roast 3Jerky 4BANANA SPLIT 5CORN bread 6chicken fried Steak Notice that the naming styles are inconsistent for these products.

How to Concatenate Strings in SQL

Problem: You want to join strings from two columns of a table into one. Example: Our database has a table named student with data in the following columns: id, first_name and last_name. idfirst_namelast_name 1LoraSmith 2EmilBrown 3AlexJackson 4MartinDavis Let’s append the first name to the last name of the student in one string. Use a space between each name.

How to Calculate the Difference Between Two Timestamps in PostgreSQL

Problem: You have two columns of the type timestamp and you want to calculate the difference between them. Example: In the travel table, there are three columns: id, departure, and arrival. You'd like to calculate the difference between the arrival and the departure. The travel table looks like this: iddeparturearrival 12018-03-25 12:00:002018-04-05 07:30:00 22019-09-12 15:50:002019-10-23 10:30:30 32018-07-14 16:15:002018-07-14 20:40:30 42018-01-05 08:35:002019-01-08 14:00:00 Solution 1 (difference in days, hours, minutes, and seconds): SELECT id, departure, arrival, arrival - departure AS difference FROM travel; The result is:

How to Calculate the Difference Between Two Dates in PostgreSQL/Oracle

Problem: You have two columns of the date type and you want to calculate the difference between them. Example: In the travel table, there are three columns: id, departure, and arrival. You'd like to calculate the difference between arrival and departure and the number of days from arrival to departure inclusively. The travel table looks like this: iddeparturearrival 12018-03-252018-04-05 22019-09-122019-09-23 32018-07-142018-07-14 42018-01-052018-01-08 Solution: SELECT id, departure, arrival, arrival - departure AS date_difference, arrival - departure + 1 AS days FROM travel; The result is:

How to Calculate a Square Root in SQL

Problem: You want to find the square root of a number. Example: You want to compute the square root of all numbers in the column number from the table data. number 9 2 1 0.25 0 -4 Solution 1: SELECT number, SQRT(number) AS square_root FROM data; The result is:

How to Add Ranking Positions to Rows with DENSE_RANK() in SQL

Problem: You want to add a ranking position to rows consecutively, even if the rows have the same values. Example: Our database has a table named competition with data in the following columns: id (primary key), first_name, last_name, and score. idfirst_namelast_namescore 11JohnThomas345 14MaryJohnson222 16LisaBrown154 23AlanBlake222 32ChrisTaylor154 Let’s display all details about students: their last name, first name, and scores sorted by score in descending order.

How to Add Ranking Positions of Rows in SQL with RANK()

Problem: You want to rank records by skipping ranking positions after rows that have the same rank. Example: Our database has a table named championship with data in the following columns: id (primary key), user_name, and score. iduser_namescore 111John12 112Mary23 115Lisa45 120Alan23 221Chris23 Let’s display all users’ names and their scores sorted in descending order and ranked by score.