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 <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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>
>