Re: [RFC] Unsigned integer support.

> -----Original Message-----
> From: pgsql-hackers-owner@postgresql.org [mailto:pgsql-hackers-
> owner@postgresql.org] On Behalf Of Tom Lane
> Sent: Friday, July 25, 2008 12:32 PM
> To: Ryan Bradetich
> Cc: Gregory Stark; pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] [RFC] Unsigned integer support.
> 
> "Ryan Bradetich"  writes:
> > On Fri, Jul 25, 2008 at 3:57 AM, Gregory Stark
>  wrote:
> >> "Ryan Bradetich"  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=#