3

Using postgres 12.2:

select '46ee2794-ddd1-4c4b-be04-82908ff1885d'::"uuid" /*works, uuid is a built-in type, not an alias*/
select '1'::"int4"    /*works ... int4 is alias for integer*/
select '1'::"integer" /*fails ... integer is built-in type*/

I have since abandoned the double quotes, but am still curious as to why this behaves the way it does!

Avocado
  • 871
  • 6
  • 23
  • "*I have since abandoned the double quotes*" - good idea. You should forget double quotes in SQL completely anyway –  Feb 25 '20 at 18:54

1 Answers1

2

It may seem strange but actually int4 is a base type while integer is a kind of alias, if we assume that base types are stored in the system catalog pg_type.

select oid, typname
from pg_type
where typname like 'int%'
and typcategory = 'N'

 oid | typname
-----+---------
  20 | int8
  21 | int2
  23 | int4
(3 rows)
klin
  • 112,967
  • 15
  • 204
  • 232
  • Interesting. I had been thinking of "int4" as the alias b/c it's listed as an alias in the postgres docs (https://www.postgresql.org/docs/12/datatype.html#DATATYPE-TABLE). But I guess that the alias label in the docs column just means "different from SQL standard" ? – Chris Chudzicki Feb 25 '20 at 18:22
  • 1
    I understand it the same way, `alias` in the documentation refers to SQL standard. `int4` is just internal implementation. – klin Feb 25 '20 at 18:34
  • @klin this indicates to me that only non-aliased types can be cast with double quotes? – Avocado Feb 25 '20 at 20:04
  • I think so. However, I generally consider the use of type names in double-quotes an unreasonable extravaganza. – klin Feb 25 '20 at 20:17