unsigned types, binary op. and cast pb - Mailing list pgsql-general

I post this mail yesterday to the hacker list as a reply/following,
but it has been stalled, so I re-post it here...

Hi postgresql experts,

I am new to the group so forgive any misunderstanding or repetition.

I noticed a previous mail concerning unsigned types, claiming
several solutions, which are not satisfactory (at least for me).
I need a 4 bytes unsigned integer to store  a 32 bit mask,
accepting binary operators (&, |, ~, ...) to perform selections
in "standard" SQL, as simple as possible.
I want to work, as transparently as possible, with as much DBMS
as possible, starting and testing with Mysql and Postgresql.
Appli is Java + JDBC.

I consider a while creating a new postgres user type,
but it implies to use C and so break down the java portability.

Finally I choose to use the unsigned int postgresql alias 'oid'.
Unfortunatly, there is no binary operators directly available,
nor implicit cast to a type which would allow their use.
As I don't want to make explicit cast I modifiied the pgcast postgresql
table to change the context of "oid to int8" cast from
implicit for affectation ('a') to implicit ('i').
I was quite afraid to do that but it works fine,
nevertheless I am not sure that doing that, something
become unscrewed (or too screwed?) in postgresql.
Any advices?

An other deficiency in postgresql is the absence of hexadecimal
representation of integer numerical constante (0xhhhhh notation).
As there is a x'hhhh'  notation possibility with bit type,
as for oid I render the cast "bit to int8" implicit, to be able
to test my oid-bitmask with hexa values directly.
Again I have the same fear and doubt concerning theses weird tricks!

So it would be nice to have unsigned integer and hexadecimal
values in postgresql. But as far as I have understand the discussions
and the problem, it would be "difficult" to modify, at least,
the internal N x N conversion table.
So, is there any chance to see unsigned types in postgresql one day?
and/or oxhhh notation?

Sincerely,

Pierre

discussing:questions/remarks (Q?/R: quoted) conecrning suggested unsigned
alternatives directly in previous mail copy.
###################################################################################

From: webmaster@robbyslaughter.com ("Robby Slaughter")
Newsgroups: comp.databases.postgresql.novice
Subject: RE: Is there unsigned datatype in PostgreSQL?
Date: Wed, 18 Jul 2001 21:11:29 +0000 (UTC)
Organization: Hub.Org Networking Services (http://www.hub.org)
Lines: 51
Sender: daemon@hub.org
Message-ID: <EPEHLKLEHAHLONFOKNHNMEAJDDAA.webmaster@robbyslaughter.com>
X-Trace: news.tht.net 995490689 91858 216.126.91.242 (18 Jul 2001 21:11:29 GMT)
X-Complaints-To: scrappy@hub.org
Organisation: Hub.Org Networking Services (http://www.hub.org)


Carfield,

Nope, there's not a native data type which is unsigned.

You could use the OID datatype, but that would be inelegant
and not very portable.

Q? : once the table is declare I didn't see what is the portability
breaking/failure!

You could also use the NUMERIC data type and just use the
minimal number of points of precision, but that would
be inelegant and slower.

You can use a character field CHAR(x) with the number
of digits you want.

Q? : Is there binary operators available for this type?

You can always go up to he next biggest INTEGER type.
(Numbers larger than 32,768? Go to INT4. Larger than 2 billion?
Go to INT 8. Larger than 4 x 10 ^ 18? Uh...(what are you
counting again?)

R : It is a pity to loose the double of space on one column, especially with
zillions of rows. At the end of the data acquisition of the project
we will have at least billions of rows

You could also just change your supporting to code to automatically
subtract the smallest negative number so that you effectively
get only positives.

HTH

-Robby

-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Carfield Yim
Sent: Wednesday, July 18, 2001 12:48 PM
To: pgsql-novice@postgresql.org
Subject: [NOVICE] Is there unsigned datatype in PostgreSQL?


Is there unsigned datatype like unsigned int?



--
Pierre
-------------------------------------------------------------------------------
DIDELON                               e-mail : pdidelon_at_cea.fr
CEA SACLAY - Service d'Astrophysique  W3 : http://www-dapnia.cea.fr/Sap/
91191 Gif-Sur-Yvette Cedex            Phone : 33 (0)1 69 08 58 89
-------------------------------------------------------------------------------