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.

What If the Data Types Don’t Match?

An SQL data type defines what kind of values can be stored in a specific column. There are text data types, which can store alphanumeric characters such as names or book titles; numeric data types, which are useful for storing numbers; date and time data types, and many more. Some data types are unique to PostgreSQL — you can read more about them on our blog.

When working with data, there are situations when we need to convert a value of one data type into another type. Let’s say you have numbers in text format that you want to use for mathematical calculations. This means that you need to convert the text into a numeric data type. How do you do it in SQL?

CAST to the Rescue!

Standard SQL solves the above problem using the CAST operator. The syntax for CAST is as follows:

CAST(expression AS target_type);

As you can see, the CAST operator takes a constant or an expression in one data type and converts it to a different data type. For example, we can convert text to date like this:

CAST('2022-09-02' AS date);

The PostgreSQL Way

In PostgreSQL, we can also use the double colon operator to convert one data type to another. The double colon :: operator works just like CAST: it allows you to convert either a constant or an expression to a certain data type. For example:

'2022-09-02'::date

What is the difference between CAST and a double colon? As we mentioned before, the CAST syntax conforms to the SQL standard. On the other hand, the double colon operator was historically used in PostgreSQL and is unique to this database. This means you should generally use the CAST operator in new code. However, the double colon operator requires significantly less typing. That’s why many PostgreSQL users prefer to use it, especially in ad-hoc queries.

Typical Uses of the Double Colon Operator

Let’s have a look at some examples of how the double colon operator can be used in PostgreSQL.

Converting a Date String to a Date Constant

This can be very useful in ad-hoc SQL queries. Dates in table columns will probably not be stored as text. However, there may be times when we will enter the date as a string (for example, in ad-hoc queries). In such cases, we can easily convert it to the date format like this:

'2022-09-02'::date

This is often used in doing calculations with dates, such as adding time intervals:

'2022-09-02'::date + INTERVAL '1 DAY'

Converting a Timestamp String into a Timestamp Type

Similarly, you can convert text to a timestamp:

'2022-01-01 00:00+10'::”timestamp”

Note that when converting to an interval, time, or timestamp, the data type should be surrounded by double quotes.

Converting a Numeric Constant to a Different Type

In PostgreSQL, a 1.23 literal is of the numeric type. Let’s say that you need to convert it to the real data type. In such a situation, you should use casting:

1.23::real

Converting a Numeric Expression to Text Data

Sometimes you may want to convert a numeric constant or expression to text:

(12 + 34)::varchar

This will convert the number to its string representation. It can be helpful when you would like to use string functions such as LENGTH or CONCAT. You can learn more about string manipulation in our Common Functions in PostgreSQL course!

Double Colon With Double Precision

There is a situation when you can’t use the double colon ::  operator:  When the data type has spaces in its name (such as double precision). In this case, you either have to use the CAST operator or an alternative name of this data type. In the case of double precision, you may use float8.

Are You Ready to Practice Your PostgreSQL Skills?

That’s all for today! In this article, we have explained the role of the double colon :: operator in the PostgreSQL database.

PostgreSQL is a popular database system and is widely used in many business applications. If you’re preparing for a job interview – or you just want to learn more about PostgreSQL – check out the top 10 PostgreSQL job interview questions!

Finally, remember that the best way to master PostgreSQL is through practice. LearnSQL.com offers several options for practicing PostgreSQL online, including SQL Basics in PostgreSQL and Common PostgreSQL Functions. Be sure to practice SQL with us!