Thread

1. Difference between CAST(v AS t) and v::t

   Lele Gaifax <lele@metapensiero.it> — 2017-11-11T11:29:37Z

   Hi all,
   
   while writing test cases for my SQL pretty printer tool[1], I found what seems
   a discrepancy in the "Type Casts" documentation[2]: it states that the two
   syntaxes are equivalent, but while
   
     CREATE TABLE contracts (
       ...
       company_id uuid NOT NULL,
       validity daterange NOT NULL,
       
       EXCLUDE USING gist (CAST(company_id AS text) WITH =, validity WITH &&)
     )
   
   works, the following
   
     CREATE TABLE contracts (
       ...
       company_id uuid NOT NULL,
       validity daterange NOT NULL,
       
       EXCLUDE USING gist (company_id::text WITH =, validity WITH &&)
     )
   
   is rejected with the message "syntax error at or near "::"".
   
   Am I misreading the documentation, or is it missing some special case?
   
   Thanks in advance for any explanation,
   ciao, lele.
   
   [1] https://github.com/lelit/pg_query
   [2] https://www.postgresql.org/docs/10/static/sql-expressions.html#SQL-SYNTAX-TYPE-CASTS
   -- 
   nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri
   real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia.
   lele@metapensiero.it  |                 -- Fortunato Depero, 1929.
   
   
   
   

2. Re: Difference between CAST(v AS t) and v::t

   Tom Lane <tgl@sss.pgh.pa.us> — 2017-11-11T16:43:08Z

   Lele Gaifax <lele@metapensiero.it> writes:
   > while writing test cases for my SQL pretty printer tool[1], I found what seems
   > a discrepancy in the "Type Casts" documentation[2]: it states that the two
   > syntaxes are equivalent, but while
   
   They are functionally equivalent ...
   
   >     EXCLUDE USING gist (CAST(company_id AS text) WITH =, validity WITH &&)
   > works, the following
   >     EXCLUDE USING gist (company_id::text WITH =, validity WITH &&)
   > is rejected with the message "syntax error at or near "::"".
   
   ... but expression-index syntax has the restriction that you need
   parentheses around an expression unless it is, or at least looks like,
   a function call.  CAST() looks enough like a function call for this
   purpose, v::t does not.
   
   I think there is relevant documentation for this near CREATE INDEX;
   it doesn't seem like the province of the typecast docs to explain
   the weirdnesses of index syntax.
   
   			regards, tom lane
   
   
   

3. Re: Difference between CAST(v AS t) and v::t

   Lele Gaifax <lele@metapensiero.it> — 2017-11-12T07:52:32Z

   Tom Lane <tgl@sss.pgh.pa.us> writes:
   
   > ... but expression-index syntax has the restriction that you need
   > parentheses around an expression unless it is, or at least looks like,
   > a function call.  CAST() looks enough like a function call for this
   > purpose, v::t does not.
   >
   > I think there is relevant documentation for this near CREATE INDEX;
   > it doesn't seem like the province of the typecast docs to explain
   > the weirdnesses of index syntax.
   
   Thank you Tom, I really missed the explanation in CREATE INDEX doc entry!
   
   ciao, lele.
   -- 
   nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri
   real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia.
   lele@metapensiero.it  |                 -- Fortunato Depero, 1929.