Re: Some issues about data type convert

donniehan wrote:
> I have a question about pg_cast--- data type convert.
> Pg provide more data types than sql spec, such as OID.
> Internal OID type is unsigned int32 and INT8 is int64.
>
> Why pg can convert INT8 into OID implicitly while can not
> convert OID into INT8 implicitly?
>
> There may be some problems when using coalesce expr. Have a
> look at the following case:
>
> postgres=# create table test1(c1 OID, c2 BIGINT);
> CREATE TABLE
> postgres=# create view v1 as select coalesce(c1,c2) from test1;
> CREATE VIEW
> postgres=# \d v1
> View "public.v1"
> Column | Type | Modifiers
> ----------+------+-----------
> coalesce | oid |
> View definition:
> SELECT COALESCE(test1.c1, test1.c2::oid) AS "coalesce"
> FROM test1;
>
> postgres=# insert into test1(c2) values(-1);
> INSERT 0 1
> postgres=# select * from v1;
> ERROR: OID out of range
>
> Although we can define the view v1 successfully, but we can
> not get what we want.
> If pg can convert INT8 into OID implicitly, it seems there
> would not be any problems.

This has nothing to do with implicit or explicit casts.

The maximum possible oid is 4294967295, and you are trying to create a
bigger one.

The problem is that since "oid" is unsigned, negative integers will be
interpreted as large positive values when you cast them.

Compare:

test=> SELECT oid(-1);
oid
------------
4294967295
(1 row)

or

test=> SELECT oid(int4(-1));
oid
------------
4294967295
(1 row)

In both cases there is a conversion from 4-byte integer to oid
(which also has 4 bytes). "-1" becomes the maximum unsigned
4-byte integer value.

If you try the same with bigint = int8, you get

test=> SELECT oid(int8(-1));
ERROR: OID out of range

The corresponding unsigned 8-byte integer values would be
18446744073709551615, and when you try to store that in
an "oid", you get an overflow error.

Why do you want a view where "-1" is converted to an oid?

Yours,
Laurenz Albe