Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What does :: do in PostgreSQL? [duplicate]

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.

like image 478
ams Avatar asked Mar 21 '13 01:03

ams


People also ask

What does :: means in PostgreSQL?

The type 'string' syntax is a generalization of the standard: SQL specifies this syntax only for a few data types, but PostgreSQL allows it for all types. The syntax with :: is historical PostgreSQL usage, as is the function-call syntax.


2 Answers

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.

like image 179
PSR Avatar answered Oct 13 '22 05:10

PSR


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:

  • Postgres data type cast
like image 27
Erwin Brandstetter Avatar answered Oct 13 '22 06:10

Erwin Brandstetter