Double Colon '::' Notation in SQL

What does :: mean in a SQL context?

In the specific case there

  [SSISDB].[CATALOG].[EXECUTIONS].[TM]::[EXECUTION_ID]

The syntax would be database.schema.table_or_view.column::static_method_on_clr_type

The double colon is also used in legacy syntax for some functions

 SELECT * FROM ::fn_trace_getinfo(default)

And in the grammar for GRANT, REVOKE, DENY. (Example)

 GRANT INSERT ON SCHEMA :: HumanResources TO guest;

What does the colon sign : do in a SQL query?

That's called a bind variable in Oracle.

what's the name for ":"?

Colon.

What does :: do in PostgreSQL?

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.

Postgres Data Type Declarations and Typing, What's the Difference Between Double Colon and Not

I might refer to INTERVAL '1 day' as an interval literal. This means, as written and without any casting, it states a literal Postgres INTERVAL value. On the other hand, '1 day'::INTERVAL is actually a cast of the text 1 day to make it an INTERVAL value.

The :: casting syntax is not part of the ANSI standard, and is specific to Postgres. Note that CAST('1 day' AS INTERVAL) is more or less identical to using the double colon '1 day'::INTERVAL syntax.

If you were concerned about having to one day possibly port your Postgres SQL code to another database, then you might stick with using CAST() over ::, the former which is supported on most other databases.

In terms of performance, INTERVAL '1 day' does not require Postgres to do any explicit casting operation, while CAST or :: do require an explicit cast. There could be some small performance differences there.

Can anyone explain :: meaning in postgresql?

:: is the cast operator for PostgreSQL.

What does a colon (':') mean in SQL syntax?

It is a bind variable:

A placeholder in a SQL statement that must be replaced with a valid
value or value address for the statement to execute successfully. By
using bind variables, you can write a SQL statement that accepts
inputs or parameters at run time. The following example shows a query
that uses v_empid as a bind variable:

---

Related Topics