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!

Tim Biegeleisen · Accepted Answer · 2019-10-11 02:03:51Z

4

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.

answered Oct 11, 2019 at 2:03

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

CTS_AE Over a year ago

I feel silly seeing literal now. That's the word I couldn't come to find at the tip of my mind: type literal 🤦‍♂️. Thanks for answering the additional questions and going into the why for them.

user330315 Over a year ago

Note that interval '1 day' is a Postgres specific variant of the standard ANSI SQL interval '1' day

Collectives™ on Stack Overflow

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

Main Question

Example

So Many More Questions...

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Main Question

Example

So Many More Questions...

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related