Re: Numeric type problems - Mailing list pgsql-general

This is a very interesting option. My biggest concern is performance:
the project will require tables with millions of tuples. How does the
performance of such user created types compare to using native types? Or
are they 'built' using the same structure?

Thanks again!

Marc


Paul Tillotson wrote:
> 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   <not supported natively, is it?>
>> DB_FIELD_UINT16    SMALLINT UNSIGNED  <not supported natively, is it?>
>> DB_FIELD_UINT32    INT UNSIGNED       <not supported natively, is it?>
>> DB_FIELD_UINT64    BIGINT UNSIGNED    <not supported natively, is it?>
>> 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
>>
>>
>
>

--
Bye,
   Marc 'Foddex' Oude Kotte

   -=-=-=-=-=-=-=-=-=-=-=-=-
   Need a programmer?
   Go to http://www.foddex.net