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

Question

Main Question

I was wondering what the difference between declaring a type using double semicolon :: is, vs not using :: in Postgres.

Example

INTERVAL '1 day'
-- vs
'1 day'::INTERVAL

So Many More Questions...

• Is :: type casting? Like casting a string to an INTERVAL.

• What would you call the first example without using ::, or is that how you define that data type without casting?

• Are there any benefits to use one over the other?

• Was one introduced before the other?

• Are there speed benefits?

• Is one method standardized and prefered over the other within the community?

• Is there a community style guidelines document?

• Is this unique to Postgres, or applicable to SQL as well?

Sorry for all the questions!

Answer 1

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.