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: [EMAIL PROTECTED] ("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: [EMAIL PROTECTED] Message-ID: <[EMAIL PROTECTED]> X-Trace: news.tht.net 995490689 91858 216.126.91.242 (18 Jul 2001 21:11:29 GMT) X-Complaints-To: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Carfield Yim Sent: Wednesday, July 18, 2001 12:48 PM To: [EMAIL PROTECTED] 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
-------------------------------------------------------------------------------
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html