PostgreSQL

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.

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.

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.

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.

Jakub Romanowski

Course of the Month: Customer Behavior in PostgreSQL

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 2024 Course of the Month, Customer Behavior Analysis in PostgreSQL, has you covered! And the best part?

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.

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.

LearnSQL.com Team

Course of the Month: SQL Practice Set in PostgreSQL

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.

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.

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.

Jakub Romanowski

Course of the Month: JOINs in PostgreSQL

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.

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.

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.

Jakub Romanowski

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.

Jakub Romanowski

Course of the Month: Window Functions in PostgreSQL

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.

Jakub Romanowski

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.

Jakub Romanowski

Course of the Month: INSERT, UPDATE, DELETE in Postgres

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.

How to Divide Columns in SQL Server, PostgreSQL, or SQLite

Problem You want to divide one column by another in SQL Server, PostgreSQL, or SQLite. Example An online store has an orders table with data in the columns order_id, total_order_payment, and item_count. order_idtotal_order_paymentitem_count 124 2154 3562 Let’s say we want to extract the average cost per item for each order, i.e., the total payment for the order divided by the item count.

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.

How to Format a Date in PostgreSQL

Problem You want to format a date column or value in PostgreSQL. Example The company’s database has a table named employees with data in the columns first_name, last_name, and hire_date. first_namelast_namehire_date AgathaSmith2021-03-15 WilliamParker2022-01-22 NoahCrawford2009-10-01 We want to extract the information for each employee, but we want to have hire_date in the dd/mm/yyyy format (e.g. 03/12/2022).

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.

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.

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.

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

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.

Jakub Romanowski

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!

How to Get Day Names in PostgreSQL

Problem You want to extract the name of the day of the week from a date in PostgreSQL. Solution 1 To extract the day name from the date, you can use the to_char() function. The first argument is the date and the second is the desired output format. To extract the full day name, the format should be 'Day': SELECT to_char(date '2022-01-01', 'Day'); The result is Saturday.

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.

How to Split a String in PostgreSQL

Problem: You want to split a string in PostgreSQL. Example 1: You have a sentence, and you'd like to split it by the space character. Solution 1: SELECT unnest(string_to_array('It''s an example sentence.', ' ')) AS parts; The result looks like this: parts It's an example sentence. Discussion: To get all parts of the sentence as elements of an array in PostgreSQL, use the string_to_array(text, delimiter) function.

How to Limit Results in MySQL, PostgreSQL and SQLite

Problem You want to limit the number of rows resulting from a query in MySQL, PostgreSQL, or SQLite. Example In the exam table, there are names of the students with the results of the exam. nameexam_result Janet Morgen9 Taya Bain11 Anne Johnson11 Josh Kaur10 Ellen Thornton8 You want to get the three rows with the best exam results.

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?

What Is the Default Constraint Name in PostgreSQL?

Problem: You want to know the default names of constraints in a table in PostgreSQL. Example: In our database, we create two tables, country and student, with the following constraints: PRIMARY KEY (the column id in the tables country and student), FOREIGN KEY (the column country_id in the table student), DEFAULT (the column name in the table student), UNIQUE (the column name in the table country and the column personal_number in the table student), and CHECK (the column age in the table student).

How to Find the Name of a Constraint in PostgreSQL

Problem You want to find the names of the constraints in a table in PostgreSQL. Example We want to display the names of the constraints in the table student. Solution SELECT conname, contype FROM pg_catalog.pg_constraint JOIN pg_class t ON t.oid = c.conrelid WHERE t.relname ='student'; Here is the result: connamecontype student_age_checkc student_pkeyp student_personal_number_keyu student_country_id_fkeyf Discussion To find the name of a constraint in PostgreSQL, use the view pg_constraint in the pg_catalog schema.

How to Order by Month Name in PostgreSQL or Oracle

Problem: You want to sort the rows by month number, given month names (you want January to be shown first, December last). Example: The birthday table contains two columns: name and birthday_month. The months are given in names, not in numbers. namebirthday_month Ronan TishaNULL Angie JuliaApril Narelle DillanApril Purdie CaseyJanuary Donna NellNULL Blaze GraemeOctober You want to sort the rows by birthday_month.

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):

How to Extract Substrings in PostgreSQL and MySQL

Problem You'd like to extract a substring from a string in a PostgreSQL or MySQL database. Example 1 In the emails table, there is an email column. You'd like to display the first seven characters of each email. The table looks like this: email jake99@gmail.com tamarablack@zoho.com notine@yahoo.fr jessica1995@onet.pl Solution 1 SELECT email, SUBSTRING(email, 1, 7) AS substring FROM emails; Another syntax:

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

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.

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:

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:

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 a CSV file 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.

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:

How to Get a Remainder Using MOD() in PostgreSQL, MS SQL Server, and MySQL

Problem You want to find the (non-negative) remainder. Example In the table numbers, you have two columns of integers: a and b. ab 93 53 23 03 -23 -53 -93 5-3 -5-3 50 00 You want to compute the remainders from dividing a by b.

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.

Jakub Romanowski

Major Companies Using PostgreSQL: Purposes & Examples

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?

How to Remove Trailing Zeros from a Decimal in PostgreSQL

Problem: You’d like to remove trailing zeros from the fractional part of a decimal number in PostgreSQL database. Example: Our database has a table named ribbon with data in the columns id, name, and width (in meters, denoted by a decimal number with a 4-digit fractional part). idnamewidth 1Large Satin N241.2000 2Creation Yc20.5500 3Three Color 4F13.2050 4Blue Ribbon LB10.

How to Convert a String to a Numeric Value in PostgreSQL

Problem You’d like to convert a string to a decimal value in PostgreSQL. Let’s convert the value in a string to a DECIMAL datatype. Solution 1: Using the :: operator We’ll use the :: operator. Here’s the query you’d write: SELECT ' 5800.79 '::DECIMAL; Here is the result: numeric 5800.79 As you notice, the leading and trailing spaces were removed.

How to Capitalize Each Words' First Letter in PostgreSQL

Problem In a PostgreSQL string, you need to make the first letter of each word uppercase and the rest of the letters lowercase. Example Our database has a table named student with data in two columns, id and full_name. idfull_name 1ANNE WILLIAMS 2alice brown 3Gary JACKSON Let’s change the capitalization of students’ full names by converting this string so that only the first letters of the first and last names are capitalized – just as we’d normally write them.

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 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.

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.

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 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 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 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).

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.

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 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. 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.

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.

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?

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: