What does :: do in PostgreSQL?

ams picture ams · Mar 21, 2013 · Viewed 41.3k times · Source

I have seen :: in variety of places involving postgres code I have seen on the net. For example:

SELECT '{apple,cherry apple, avocado}'::text[];

It seems to be some sort of cast. What exactly is :: in postgres and when should it be used?

I tried a bit of googling and searched the Postgres docs for :: but got no good results.
I tried following searches in Google:

  • postgres double colon
  • postgres ::
  • ::

I tried the following searches in the postgres docs search button

  • double colon
  • double colon cast
  • ::

This was almost embarrassing to ask on SO, but I figured Google will hopefully see this answer for other people in the future.

Answer

PSR picture PSR · Mar 21, 2013

A type cast specifies a conversion from one data type to another.

PostgreSQL accepts two equivalent syntaxes for type casts, the PostgreSQL-specific value::type and the SQL-standard CAST(value AS type).

In this specific case, '{apple,cherry apple, avocado}'::text[]; takes the string literal {apple,cherry apple, avocado} and tells PostgreSQL to interpret it as an array of text.

See the documentation on SQL expressions and arrays for details.