107

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
ams
  • 60,316
  • 68
  • 200
  • 288
  • 4
    It is a cast ("type coercion") – wildplasser Mar 21 '13 at 01:08
  • I'm surprised you didn't spot the answer in a search. See the first few hits in the following search: https://www.google.com/search?q=postgresql+double+colon . Can you tell me what you searched for so I can try to make the relevant part of the docs easier to find? – Craig Ringer Mar 21 '13 at 01:19
  • 1
    @CraigRinger I updated the question with the search that I ran, as best as I can remember them. – ams Mar 21 '13 at 15:19
  • 2
    This question and answers are much more to the point than the "original". The other one should have been closed. – Erwin Brandstetter Mar 21 '13 at 15:30
  • 1
    @ams Thanks for the detailed update, I hope it helps others in future. – Craig Ringer Mar 22 '13 at 00:10

2 Answers2

112

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.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
PSR
  • 39,804
  • 41
  • 111
  • 151
14

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

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

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

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 4
    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'`. – Craig Ringer Mar 22 '13 at 00:11
  • 2
    @CraigRinger: Excellent point. I was actually being imprecise. Clarified my post and added a bit. – Erwin Brandstetter Mar 22 '13 at 00:33