12 Sep 2024 Alexandre Bruffa Top 5 Reasons PostgreSQL Works for Data Analysis (and Data Analysts!) Why do analysts love PostgreSQL for data analysis? Learn why this database management system is so beloved of database professionals and data scientists alike! If you work with data, you know that data analysis requires the efficient storage, management, and retrieval of large datasets. Thus, data analysts prefer to use relational databases that are well-known for their robustness, efficiency, and stability. Relational databases work with a database management system (DBMS), which allows the creation, management, and manipulation of relational databases. Read more 5 Sep 2024 Michał Wasiluk A Guide to PostGIS: Basic Geospatial Data Query Examples Geospatial data is becoming increasingly important in many fields, from urban planning to environmental science. In this article, we’ll demonstrate basic PostgreSQL PostGIS queries for working with this type of data. Geospatial data, which contains information about locations on Earth, requires specialized tools for effective use. PostGIS is a powerful PostgreSQL extension that turns a Postgres database into a full-featured Geographic Information System (GIS). With PostGIS, you can store geographic objects, run spatial queries, and perform advanced analyses directly in SQL. Read more 1 Sep 2024 LearnSQL.com Team Free Course of the Month – SQL Practice Set in PostgreSQL Updated on: September 1, 2024 Do you know the basics of PostgreSQL but still need some practice? Look no further! Here is a great interactive course, SQL Practice Set in PostgreSQL. Throughout the month of September, you have access to it for FREE! We at LearnSQL.com believe that to learn a new skill, you need basic theoretical knowledge and, above all, a lot of practice. It's the same with writing PostgreSQL queries – you need a lot of PostgreSQL practice. Read more 29 Aug 2024 Ekre Ceannmor How to Export Data from PostgreSQL into a CSV File Do you need to quickly send data to a client or share a report for further analysis? A CSV file is a great sharing option! Let’s take a look at how you can use this format to export data from a PostgreSQL database. In this article, we will first review what the CSV file format is and why it’s handy when exporting your PostgreSQL database. Then we will export some example data from a real database using psql on the command line and pgAdmin, a free and open-source interface for PostgreSQL databases. Read more 26 Jul 2024 Jakub Romanowski 2024 Database Trends: Is SQL Still the King? Want to know what database is most popular in 2024? In this article, I go through the 2024 Stack Overflow Developer Survey results. You’ll see why SQL is still top, PostgreSQL is on the rise and more developers are interested in SQLite. We’ll also talk about the benefits of learning SQL online, recommend resources, and how developers code outside of work for fun and professional growth. The annual Stack Overflow Developer Survey offers invaluable insights into the developer community, shedding light on trends, preferences, and emerging technologies. Read more 16 Jul 2024 Jill Thornhill PostgreSQL CTE: What It Is and How to Use It CTEs, or Common Table Expressions, are a powerful PostgreSQL tool that’s often ignored. This article looks at various PostgreSQL CTEs – including nested and recursive CTEs – and what you can do with them. If you write complex queries in SQL, you’ll soon find that your code becomes cluttered and hard to read. CTEs – also known as WITH clauses – are primarily a way of simplifying queries. However, they also allow you to use recursion. Read more 1 Jul 2024 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 July 2024, 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. Read more 27 Jun 2024 Martyna Sławińska PostgreSQL Date Functions Understanding date and time functions in your database is essential for effective data analysis and reporting. Read on to learn more about PostgreSQL date functions. This article covers some of the most useful PostgreSQL date and time functions and their applications in data analysis and reporting. SQL date functions facilitate different data analysis tasks, including sales analysis, financial reporting, website analytics, and more. This article presents you with the tools, in the form of PostgreSQL date and time functions, used to accomplish these tasks. Read more 30 May 2024 Jakub Romanowski Best Books for Learning PostgreSQL Explore the world of PostgreSQL with our handpicked selection of the best books available. Whether you’re new to database management or looking to sharpen your skills, these resources are perfect for mastering the comprehensive features of PostgreSQL. I've been diving deep into PostgreSQL lately, and I'm excited to share the five best PostgreSQL books that have really helped me along the way. Whether you're just starting out or you're looking to deepen your expertise, there's a book here for everyone. Read more 23 May 2024 Jakub Romanowski Which Database Is Best for Small Projects? If you're tackling a small project and need to pick the best database for managing and analyzing your data, this article will help. I'll compare PostgreSQL and MySQL, two top choices for data analysis, to make your decision easier. Read on! Are you just starting to explore the world of databases and feeling a bit uncertain about which one to choose for your small projects? I remember feeling the same way when I first started. Read more 25 Apr 2024 Gustavo du Mortier 19 PostgreSQL Practice Exercises with Detailed Solutions You’ve probably heard the saying “practice makes perfect”. Find out if this phrase is true by trying the comprehensive set of PostgreSQL practice exercises that you will find in this article! To master any language, it is essential to practice it regularly so as not to lose the skill. This is as true for verbal communication as it is for programming. And SQL programming is no exception. Even the most seasoned PostgreSQL programmer needs to do PostgreSQL practice daily to perfect (and maintain) their skills. Read more 1 Apr 2024 Jakub Romanowski Free SQL Course of the Month – Revenue Trend Analysis in PostgreSQL Need to understand what your data is saying about your business? In April 2024, our Free SQL Course of the Month is Revenue Trend Analysis in PostgreSQL. Don't miss this opportunity to elevate your analytical capabilities with a leading database system! Understanding revenue trends is crucial for business success, but effective data analysis can be challenging. Dive into the world of SQL reports and enhance your business growth strategies with PostgreSQL. Read more 1 Mar 2024 Jakub Romanowski Free Course of the Month: PostgreSQL Window Functions Are you looking for the best way to master SQL window functions? Throughout March 2024, you can access the interactive course Window Functions in PostgreSQL for free. Boost your skills at no cost. 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. Read more 14 Feb 2024 LearnSQL.com Team PostgreSQL Cheat Sheet Here's your ultimate PostgreSQL cheat sheet! Whether you're a newbie or an experienced pro in need of a quick reference, this cheat sheet has got you covered. This PostgreSQL Cheat Sheet summarizes the key PostgreSQL commands and features you'll use often. It covers everything from how to connect to a PostgreSQL server and manage database contents, to the basic syntax for table creation and modification. It also breaks down the syntax for SELECT, INSERT, UPDATE, DELETE commands, and shows how to use different PostgreSQL functions, including text functions, numeric functions, NULL functions, and date and time functions. Read more 1 Nov 2023 Jakub Romanowski Free Course of the Month: PostgreSQL Customer Behavior Ever wondered how top businesses understand their customers so well? The secret lies in data analysis, and we're offering you the key – for free – in our Customer Behavior Analysis in PostgreSQL course! Are you familiar with the basics of PostgreSQL and eager to delve deeper into customer behavior analysis? Our November 2023 Course of the Month, Customer Behavior Analysis in PostgreSQL, has you covered! And the best part? Read more 1 Jul 2023 Jakub Romanowski Free Course of the Month: Write Functions in PostgreSQL Why learn to write user-defined functions? I’ll explain in this article – and how you can learn for free during July 2023 with this LearnSQL.com PostgreSQL course! PostgreSQL is one of the most popular databases in the world. This is mainly due to its great functionality and the fact that it’s open source (i.e. free!). Postgres is fast, it runs on multiple systems, and it’s suitable for almost any application. Read more 1 Jun 2023 Jakub Romanowski Course of the Month: INSERT, UPDATE, and DELETE in PostgreSQL Do you want to be a data engineer or data analyst? Do you work with databases daily and need to expand your skills with the basics of data manipulation? You've come to the right place. For June 2023, 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 June? Create a free LearnSQL. Read more 6 Apr 2023 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. Read more 28 Mar 2023 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. Read more 25 Oct 2022 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. Read more 20 Oct 2022 Dominika Florczykowska What Does a Double Colon Operator in PostgreSQL Do? The double colon operator :: in PostgreSQL is a synonym for CAST, which converts a value into a different data type. If you are curious about the details , read on! Have you ever seen a double colon (::) operator in someone’s PostgreSQL code? It would look something like this: SELECT '2022-09-02'::date; This code converts the text '2022-09-02' to a date datatype. As mentioned above, the double colon replaces the CAST operator. Read more 16 Aug 2022 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. Read more 19 Jul 2022 Alexandre Bruffa Switching from PostgreSQL to 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. Read more 1 Jun 2022 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! Read more 3 Feb 2022 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. Read more 20 Jan 2022 Andrew Bone 10 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. Read more 27 Oct 2021 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? Read more 27 Aug 2021 How to Order by Date in PostgreSQL or Oracle Problem: You want to sort the rows by date in PostgreSQL or Oracle database. Example 1: The exam table has two columns, subject and exam_date. subjectexam_date Mathematics2022-12-19 English2023-01-08 Science2023-01-05 Health2023-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): Read more 18 Jun 2021 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. Read more 28 Apr 2021 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. Read more 18 Mar 2021 Jakub Romanowski New SQL Course: GROUP BY Extensions in PostgreSQL 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. Read more 26 Feb 2021 Martyna Sławińska MS SQL Server vs. PostgreSQL: Which to Choose? 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: Read more 18 Feb 2021 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. Read more 16 Feb 2021 How to Calculate Timestamp Difference 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: Read more 10 Feb 2021 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. Read more 5 Feb 2021 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! Read more 24 Jan 2021 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 2023-11-25711 2023-11-251213 2023-11-265312 2023-11-26124 2023-11-26321 2023-11-261627 2023-11-26332 2023-11-27631 Solution: SELECT order_date, customer_id, SUM(number) AS products_number FROM order GROUP BY order_date, customer_id; The result is: Read more 24 Jan 2021 How to Delete a Foreign Key Constraint in SQL Problem: You want to delete 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: ALTER TABLE student DROP CONSTRAINT fk_student_city_id; Discussion: To delete 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. Read more 24 Jan 2021 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: Creating new table with single-column foreign key 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. Read more 24 Jan 2021 How to Calculate Date Difference in PostgreSQL/Oracle Problem: You have two columns of the date type and you want to calculate the difference between them in PostgreSQL or Oracle database. 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: Read more 30 Dec 2020 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. Read more 30 Dec 2020 How to Make a Column Unique in SQL? Problem: You would like to make a 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: Creating new table with a UNIQUE constraint 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 column definition (name VARCHAR(100) UNIQUE). Read more 30 Dec 2020 How to Group by Year in SQL Problem: You want to group your data by year in SQL. Example 1: 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: Read more 30 Dec 2020 How to Calculate a Square Root in SQL Problem: You want to find the square root of a number in SQ:. 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: SELECT number, SQRT(number) AS square_root FROM data; The result is: Read more 9 Oct 2020 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: Use subtraction SELECT CURRENT_DATE - 1 AS yesterday_date; Assuming today is 2023-09-24, the result is: yesterday_date 2023-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. Read more 19 May 2020 Jakub Romanowski Major Companies Using PostgreSQL: Purposes & Examples Updated on: May 17, 2024. 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? Read more 13 Mar 2020 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. Read more 13 Mar 2020 How to Get the Current Date and Time (No 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: 2023-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. Read more 13 Mar 2020 How to Get Current Time (No 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. Read more 8 Mar 2020 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: Read more 8 Mar 2020 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. Read more 8 Mar 2020 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. Read more 8 Mar 2020 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 can 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: 2023-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). Read more 8 Mar 2020 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 1BarbaraWilson2015-02-012023-10-30 2RobertAnderson2006-04-172016-12-20 3StevenNelson2010-06-012023-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. Read more 8 Mar 2020 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. Read more 8 Mar 2020 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. Read more 8 Mar 2020 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. Example: 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('20230304', 'YYYYMMDD') AS new_date; And here’s the result of the query: new_date 2023-03-04 Let’s look at Example 2 of the TO_DATE() function. Read more 15 Feb 2020 How to Find Rows with Minimum Value Problem: You want to find rows which 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. You want to find cities with the lowest temperature. idcitytemperature 1Houston23 2Atlanta20 3Boston15 4Cleveland15 5Dallas34 6Austin28 Solution: SELECT id, city, temperature FROM weather WHERE temperature = (SELECT MIN(temperature) FROM weather); Here’s the result: Read more 15 Feb 2020 How to Find Rows with Maximum Value Problem: You want to find rows which 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. You want to find the students who have the highest grades. idfirst_namelast_namegrade 1LisaJackson3 2GaryLarry5 3TomMichelin2 4MartinBarker2 5EllieBlack5 6MarySimpson4 Solution: SELECT id, first_name, last_name, grade FROM student WHERE grade = (SELECT MAX(grade) FROM student); Here’s the result: Read more 15 Feb 2020 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: Read more 15 Feb 2020 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. Read more 15 Feb 2020 How to Filter Records with Aggregate Function SUM Problem: You need to filter groups of rows by the sum of a given column. 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. Read more 15 Feb 2020 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. Read more 15 Feb 2020 How to Filter Records with Aggregate Function AVG Problem: You want to filter groups of rows in by the average value of a given column. Example: Our database has a table named product with data in the following columns: id, name, store and price. idnamestoreprice 1milkGreen Shop2.34 2breadClark’s Grocery3.56 3breadSuper Market4.15 4milkSuper Market1.80 5breadGrocery Amanda2.26 6milkViolet Grocery3.45 7milkClark’s Grocery2.10 8breadViolet Grocery2. Read more 15 Feb 2020 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. Read more 9 Feb 2020 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. Read more 9 Feb 2020 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. Read more 9 Feb 2020 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. Read more 9 Feb 2020 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. Read more 9 Feb 2020 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. Read more 9 Feb 2020 How to Convert a String to Uppercase in SQL Problem: You would like to convert a string to uppercase in SQL. 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. Read more 27 Nov 2019 How to Use LIKE in SQL Problem: You need to search for specific patterns (certain combinations of letters or numbers) within text data in your database. We'll show you how to use the LIKE operator in SQL to: Find city names starting with S Find city names with exactly five characters Find city names starting with S and with o as the second-to-last character You'll also learn how to use LIKE and wildcard operators in SQL to find your own patterns in text data in your database. Read more 27 Nov 2019 How to Round Up a Number to the Nearest Integer in SQL Problem: You want to round up a number to the nearest integer in SQL. 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. Read more 27 Nov 2019 How to Round Numbers in SQL Problem: You want to round a number to a specific number of decimal places in SQL. 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.38 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. Read more 27 Nov 2019 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. Read more 27 Nov 2019 How to Add Rankings 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. Read more 26 Nov 2019 How to Trim Strings in SQL Problem: You’d like to remove specific characters from the beginning and end of a string in SQL. 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. Read more 26 Nov 2019 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: Read more 26 Nov 2019 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. Read more 26 Nov 2019 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: Read more 25 Nov 2019 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. Read more 25 Nov 2019 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. Read more 25 Nov 2019 How to Multiply Two Columns in SQL Problem: You 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. Read more 25 Nov 2019 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. Read more 25 Nov 2019 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. Read more 25 Nov 2019 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 3steakmeat 4porkmeat 5cakesweets 6cakesweets Let’s find duplicate names and categories of products. Read more 25 Nov 2019 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. Read more 25 Nov 2019 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. Read more 20 Nov 2019 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. Read more 15 Nov 2018 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: Read more 29 Mar 2018 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. Read more 2 Aug 2016 Patrycja Dybka The History of Slonik, the PostgreSQL Elephant Logo Logos are powerful. What better way to remind people of a product than an eye-catching, memorable symbol? With that in mind, today we’ll answer the question ‘Why did PostgreSQL choose an elephant for its logo?’ Every product or company has its logo – something that identifies and encapsulates the essence of their brand. In time, it practically becomes the brand: can you imagine McDonald’s without its golden arches? What if the Coca-Cola logo was suddenly done in purple block print? Read more 23 Jun 2016 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. Read more 19 May 2016 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. Read more 5 Jan 2015 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. Read more 22 May 2014 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 Read more 21 May 2014 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; Read more 7 Jan 2014 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: Read more