Re: Numeric type problems

First, every type in postgres is user-defined, in the sense that its
binary structure and the arithmetic and comparison operations you can
perform on it are defined by a set of native C functions that are
present in the database executable or loaded as shared libraries.
Because of postgres's extensible type system, all types share a small
performance penalty, but you can make new ones that are just as efficient.

http://www.postgresql.org/docs/7.4/static/sql-createtype.html
http://www.postgresql.org/docs/7.4/static/sql-createopclass.html

If you need a high performance unsigned 64 bit integer, you should make
your own type, using the existing bigint type as a template, which
should be just as efficient as the "builtin" bigint type. This is
probably premature optimization though.

Also note that if you're trying to make a type that will merely hold a
MySQL BIGINT UNSIGNED, and you want low overhead, then numeric(20)
without the check constraint will do nicely. Since MySQL itself doesn't
check to see if the values you are inserting are negative or too big*,
then presumably that responsibility doesn't fall on you either. If you
are trying to make something that is bug-for-bug compatible with MySQL,
then you'd better start working on the user defined type.

Personally, I am curious to know what sort of application you are
writing that requires storing numbers

- larger than 2 ** 63 (otherwise you would just use signed bigint)
- but less than 2 ** 64 (as far as I know you can't do this in MySQL
anyway, although you can in postgres)
- with exact precision (otherwise you would use floating point),
- but without any requirements for checking the validity of input (since
MySQL won't do this for you.)
- and without any requirements for being able to math "in the database"
and get a valid answer. (why don't you store it as a string?)

Paul Tillotson

*As evidenced:

mysql> create table foobar (i bigint unsigned);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into foobar values (-3);
Query OK, 1 row affected (0.00 sec)

mysql> insert into foobar values (1000000000 * 1000000000000);
Query OK, 1 row affected (0.00 sec)

mysql> insert into foobar values (1000000000000000000000);
Query OK, 1 row affected (0.00 sec)

mysql> select * from foobar;
+----------------------+
| i |
+----------------------+
| 18446744073709551613 |
| 3875820019684212736 |
| 18446744073709551615 |
+----------------------+
3 rows in set (0.00 sec)

mysql> update foobar set i = -i;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0

mysql> select * from foobar;
+----------------------+
| i |
+----------------------+
| 3 |
| 14570924054025338880 |
| 1 |
+----------------------+
3 rows in set (0.00 sec)

M.A. Oude Kotte wrote:

> 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
>