Re: Numeric type problems

Use a numeric type if you need more precision.

template1=# create domain BIGINT_UNSIGNED numeric(20,0) check (value >= 
0 and value < '18446744073709551616'::numeric(20,0));
CREATE DOMAIN
template1=# create table foobar (i BIGINT_UNSIGNED);
CREATE TABLE
template1=# insert into foobar (i) values (-1); --too small
ERROR:  value for domain bigint_unsigned violates check constraint "$1"
template1=# insert into foobar (i) values (0); -- works
INSERT 17159 1
template1=# insert into foobar (i) values (pow(2::numeric, 64::numeric) 
- 1); --works
INSERT 17160 1
template1=# insert into foobar (i) values (pow(2::numeric, 
64::numeric)); --too large
ERROR:  value for domain bigint_unsigned violates check constraint "$1"
template1=# select * from foobar;
          i
----------------------
                    0
 18446744073709551615
(2 rows)

Paul Tillotson

> Hi All,
>
> I hope this is the correct mailing list for this question. But neither 
> postgresql.org nor google could help me out on this subject.
> I did find one disturbing topic on the mailing list archives 
> (http://archives.postgresql.org/pgsql-admin/2000-05/msg00032.php), but 
> since it was quite old I'm posting my question anyway.
>
> I'm writing a generic database layer that should support a fixed 
> number of generic numeric types on a number of databases. At this 
> point it supports MySQL just fine, but I'm having some trouble finding 
> the right implementation details for PostgreSQL. Please take a moment 
> to look at the following table. The field description speaks for 
> itself pretty much I guess.
>
> Field descr.       MySQL              PostgreSQL
> ======================================================================
> DB_FIELD_INT8      TINYINT            SMALLINT (too big, but best match)
> DB_FIELD_INT16     SMALLINT           SMALLINT
> DB_FIELD_INT32     INT                INT
> DB_FIELD_INT64     BIGINT             BIGINT
> DB_FIELD_UINT8     TINYINT UNSIGNED   
> DB_FIELD_UINT16    SMALLINT UNSIGNED  
> DB_FIELD_UINT32    INT UNSIGNED       
> DB_FIELD_UINT64    BIGINT UNSIGNED    
> DB_FIELD_FLOAT     FLOAT              REAL
> DB_FIELD_DOUBLE    DOUBLE             DOUBLE PRECISION
>
> My problem is obvisouly the unsigned values I really need to be able 
> to represent properly. I know I can just use the twice as big signed 
> types and put a constraint on it, but that only works for UINT8, 
> UINT16 and UINT32 (there is no 128-bit signed integer type, right?): I 
> really need to have proper 64-bit unsigned integer value support.
>
> I *could* use a BIGINT to represent 64-bit unsigned values, and just 
> cast the binary data to an unsigned long long (or unsigned __int64 on 
> win32), but this would leave me with the problem that I couldn't 
> safely let SQL do comparisons on the value, right?
>
> Is there any solution? I've seen someone suggesting elsewhere that one 
> should use the OID type, but others said that one shouldn't. I'm 
> pretty desperate. PostgreSQL would really be my database of choice for 
> our current project, but I'm afraid we can't use it if I can't get 
> this right...
>
> Thanks in advance for any help!
>
> Bye,
>   Marc
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>      subscribe-nomail command to majordomo@postgresql.org so that your
>      message can get through to the mailing list cleanly
>
>