Search Postgresql Archives

Re: Some issues about data type convert

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



 
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

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux