Search code examples
sqldatabasepostgresqlpostgresql-12

Inconsistencies Casting to Double-Quoted Types

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!

Solution

  • 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)