What does :: do in PostgreSQL?

Question

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.

Solution 1

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.

Solution 2

What @PSR and @Craig wrote.
Plus, there are two more syntax variants:

type value

This form only casts constants (string literals). Like in:

SELECT date '2013-03-21';

More in the manual in the chapter Constants of Other Types.

type(value)

That's the function-like syntax. Works only for types whose names are valid as function names. Like in:

SELECT date(date_as_text_col) FROM tbl;

More in the manual in the chapter Type Casts.

More comprehensive answer:

• Postgres data type cast

Comments

[Craig Ringer]

Good point, though that isn't really a type cast, it's part of the syntax for specifying a literal. You can't write DATE somecolumn, only DATE '2012-01-01'; ie it has to be TYPENAME 'literalvalue'.

[Erwin Brandstetter]

@CraigRinger: Excellent point. I was actually being imprecise. Clarified my post and added a bit.