Re: [RFC] Unsigned integer support.

> -----Original Message-----
> From: pgsql-hackers-owner(at)postgresql(dot)org [mailto:pgsql-hackers-
> owner(at)postgresql(dot)org] On Behalf Of Tom Lane
> Sent: Friday, July 25, 2008 12:32 PM
> To: Ryan Bradetich
> Cc: Gregory Stark; pgsql-hackers(at)postgresql(dot)org
> Subject: Re: [HACKERS] [RFC] Unsigned integer support.
>
> "Ryan Bradetich" <rbradetich(at)gmail(dot)com> writes:
> > On Fri, Jul 25, 2008 at 3:57 AM, Gregory Stark
> <stark(at)enterprisedb(dot)com> wrote:
> >> "Ryan Bradetich" <rbradetich(at)gmail(dot)com> writes:
> >>> My plans for the example above would be:
> >>>
> >>> 1. SELECT 1500000000 + 1500000000 --> Throws overflow error.
> >>> 2. SELECT 1500000000::uint4 + 1500000000 --> Returns
> 3000000000::uint4.
> >>
> >> You could make it work by having a uint4+int4 operator which
returns
> uint4 but
> >> then you're going to need a *lot* of operators....
>
> > This was my plan.
>
> Like he says, it's a *lot* of operators, and the point doesn't seem
> entirely clear to me. You'll still have overflow cases, they'll just
> be
> in different places.
>
> Consider the idea of not having any uint4-specific arithmetic
> operators,
> but instead providing the following:
>
> * assignment casts from int4 and int8 to uint4
> (these throw error if out of range, of course)
> * implicit cast from uint4 to int8 (can never fail)
>
> The effect of providing the latter cast would be that any arithmetic
> involving a uint4 column would automatically be done in int8. Which
> would make it a shade slower than a native implementation, but
probably
> not enough slower to be a problem --- and you'd avoid having to write
> dozens of operators and underlying support functions. Storing into
the
> uint4 column would work fine with no extra notation because of the
> assignment casts.
>
> Moreover, you'd avoid cluttering the system with a pile of cross-type
> operators, which we have recently realized are not a good thing,
> because
> they increase the likelihood of "ambiguous operator" problems --- see
> http://archives.postgresql.org/pgsql-hackers/2008-06/msg00750.php
>
> For uint8 you'd have to promote to numeric to guarantee no failure
> in the implicit cast; which is going to be a rather bigger performance
> hit, but I don't really see uint8 as being a type with huge demand.
>
> Now you probably *will* want cross-type comparison operators, if you
> are going to support indexing of unsigned columns, so that something
> like
> uint4col > 42
> can be indexed without any casting. But limiting yourself to the six
> basic comparison operators certainly makes it a much less bulky
> project.

At the cost of one bit of storage, you have compatible types using
CREATE DOMAIN:

CREATE DOMAIN name [ AS ] data_type
[ DEFAULT expression ]
[ constraint [ ... ] ]

where constraint is:

[ CONSTRAINT constraint_name ]
{ NOT NULL | NULL | CHECK (expression) }

More specifically:

CREATE DOMAIN usmallint AS SMALLINT CHECK(VALUE > 0);
CREATE DOMAIN uinteger AS INTEGER CHECK(VALUE > 0);
CREATE DOMAIN ubigint AS BIGINT CHECK(VALUE > 0);
CREATE DOMAIN unumeric AS NUMERIC CHECK(VALUE > 0);

Seems like a heck of a lot less work to me. Not to mention very easy to
use.

C:\Program Files (x86)\PostgreSQL\8.3\bin>psql -h localhost -U postgres
domaintest
Password for user postgres:
Welcome to psql 8.3.3, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

Warning: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.

domaintest=# CREATE DOMAIN usmallint AS SMALLINT CHECK(VALUE > 0);
CREATE DOMAIN
domaintest=# CREATE DOMAIN uinteger AS INTEGER CHECK(VALUE > 0);
CREATE DOMAIN
domaintest=# CREATE DOMAIN ubigint AS BIGINT CHECK(VALUE > 0);
CREATE DOMAIN
domaintest=# CREATE DOMAIN unumeric AS NUMERIC CHECK(VALUE > 0);
CREATE DOMAIN
domaintest=#
domaintest=# create table integer_types (
domaintest(# usCol usmallint,
domaintest(# sCol smallint,
domaintest(# uiCol uinteger,
domaintest(# iCol integer,
domaintest(# ubCol ubigint,
domaintest(# bCol bigint,
domaintest(# unCol unumeric,
domaintest(# nCol numeric
domaintest(# );
CREATE TABLE
domaintest=# create index i1 on integer_types(usCol);
CREATE INDEX
domaintest=# create index i2 on integer_types(sCol);
CREATE INDEX
domaintest=# create index i3 on integer_types(uiCol);
CREATE INDEX
domaintest=# create index i4 on integer_types(iCol);
CREATE INDEX
domaintest=# create index i5 on integer_types(ubCol);
CREATE INDEX
domaintest=# create index i6 on integer_types(bCol);
CREATE INDEX
domaintest=# create index i7 on integer_types(unCol);
CREATE INDEX
domaintest=# create index i8 on integer_types(nCol);
CREATE INDEX
domaintest=# insert into integer_types values(1,1,1,1,1,1,1,1);
INSERT 0 1
domaintest=# select * from integer_types;
uscol | scol | uicol | icol | ubcol | bcol | uncol | ncol
-------+------+-------+------+-------+------+-------+------
1 | 1 | 1 | 1 | 1 | 1 | 1 | 1
(1 row)

domaintest=# insert into integer_types (usCol) values (-1);
ERROR: value for domain usmallint violates check constraint
"usmallint_check"
domaintest=#