>> Um, I think the OP is right. Notice he does: ...
>> showing that PG is auto-magically inserting a cast from BIGINT to OID.
>>I think the OP is either expecting the CREATE VIEW to fail saying types
>>are invalid, or to result in "c1" being cast to BIGINT
Yes, that's what i mean. Is that more reasonable cast OID to BIGINT?
> Yes, as a quick look into pg_cast will show you, bigint -> oid is an
> implicit cast:
>
> regression=# \dC+ oid
> List of casts
> Source type | Target type | Function | Implicit?
> ---------------+---------------+--------------------+---------------
> bigint | oid | oid | yes
> integer | oid | (binary coercible) | yes
> oid | bigint | int8 | in assignment
> oid | integer | (binary coercible) | in assignment
> ...
>
> The reason for this is that we want to be able to accept constants like
> 3000000000 as OIDs, but the lexer will initially mark that as bigint
> because it doesn't fit in int4.
>
>> The OP is saying that this isn't right because the cast isn't a total
>> function, i.e. it fails for -1 and a large number of other inputs.
>
> Sometimes usefulness has to trump consistency :-(. It could actually be
> argued that this is a consistency issue in itself. Given a function
> that takes OID, such as pg_database_size(oid), it would be unpleasant
> if pg_database_size(16384) worked but pg_database_size(3000000000)
> did not.
Can i just resolve this consistency issue by update pg_cast set the CASTCONTEXT = 'i'(implicit) where cast OID to BIGINT ?
I'm not sure whether this change would cause other problems?
For coalesce expression, it does not conform with sql specification, and why not?
And also in pg there is no Data Type Precedence concept(I saw there is Preferred Data Type)? Why does not pg support it ?
Data Type Precedence help cast data type to more common type, it will be very useful for like coalesce expressions.
For examples:
Let VARCHAR is higher than CHAR
So when coalesce(CHAR(20), VARCHAR(10)), the return type will be VARCHAR(20) while now is CHAR(8000).
Let INT8 is higher than OID
So when coalesce(INT8, OID), the return type will be INT8, while now is OID. (The precondition is supporting cast OID to INT8 implicitly).
Just my two cents for your information.
Regards
--Dongni
