Thread: unsigned types, binary op. and cast pb
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
-------------------------------------------------------------------------------
On Fri, 19 Mar 2004, Pierre Didelon wrote:
> 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.
How standard do you want to be? AFAICS neither SQL92 or 99 provide any of
the operators you want and they both seem to allow the database to store
the values as decimal values rather than binary (thus being basically
numeric(<something>, 0)). Both also AFAICS have no real notion of an
unsigned numeric data type ("A value described by a numeric data type
descriptor is always signed").
How exactly are you using the values?
> I consider a while creating a new postgres user type,
> but it implies to use C and so break down the java portability.
I'm not sure what you mean by the end of the above.
> 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?
Apart from the fact that you might be able to do wacky non-sensical things
with oids, I don't think there's a particular problem there. Explicit
casts would probably be safer.
> An other deficiency in postgresql is the absence of hexadecimal
> representation of integer numerical constante (0xhhhhh notation).
That would be nice, although it'd have to not conflict with any other bit
of required syntax and I haven't looked to see if it does.
> 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.
Hmm, this might give strange behavior in the case of bit(n) for n>64 since
the values won't fit. Otherwise, it should also be safe, although again
using explicit casts would probably be safer.
Pierre Didelon wrote: > 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. > What about just using the types inet or cidr for your task? Oth, instead of (mis-) using OID datatype as unsigned int, and converting it each time into int8 when you want operations with it - why not using int8 for all? It has 7*8+7 bits in the positive range, which should be sufficient for values never bigger then 2^32. But according to your task, its probably really better to use inet/cidr types. Much lesser work I guess. And ipv6 for grant :) Regards Tino Wildenhain
Hi Stephan,
thanks for your comments.
Stephan Szabo wrote:
> On Fri, 19 Mar 2004, Pierre Didelon wrote:
>
>>
>>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.
>
>
> How standard do you want to be? AFAICS neither SQL92 or 99 provide any of
> the operators you want and they both seem to allow the database to store
> the values as decimal values rather than binary (thus being basically
> numeric(<something>, 0)). Both also AFAICS have no real notion of an
> unsigned numeric data type ("A value described by a numeric data type
> descriptor is always signed").
I use standard in the sense of most commonly used, that's why I quoted it,
or as de facto standard, and not as real community defined standard.
I want to be able to execute the same sql statement, through JDBC,
on mysql, postgres or any possible other DBMS.
>
> How exactly are you using the values?
I want to test it in where clause with predicate like
bitmask & 0xf9001082 != 0 ;
bitmask & 0xf9001082 = 0xf9001082
or any binary/logical combination.
Moreover, I want the results values to be intelligible by
any application on top of the JDBC interface,
without any knowledge of the database design and columns meaning.
So output would be an numeric value with the common coding
decimal, hexa with 0x prefix (or x'....'), but not
unexpected weird things and format without preliminary notification
(0b would have be a nice prefix for binary values of bit fields).
In that sense the output of bit fields is not self meaning
because, unless futher analysis, nothing says you that it is
a binary value.
>
>
>>I consider a while creating a new postgres user type,
>>but it implies to use C and so break down the java portability.
>
>
> I'm not sure what you mean by the end of the above.
Using C I must compile the required code on every platform used.
What I try to avoid with java.
>
>
>>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?
>
>
> Apart from the fact that you might be able to do wacky non-sensical things
> with oids, I don't think there's a particular problem there. Explicit
> casts would probably be safer.
>
>
>>An other deficiency in postgresql is the absence of hexadecimal
>>representation of integer numerical constante (0xhhhhh notation).
>
>
> That would be nice, although it'd have to not conflict with any other bit
> of required syntax and I haven't looked to see if it does.
>
>
>>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.
>
>
> Hmm, this might give strange behavior in the case of bit(n) for n>64 since
> the values won't fit. Otherwise, it should also be safe, although again
> using explicit casts would probably be safer.
That's why it would have been nicer to have an unsigned integer type,
and if moreover it would have accept hexa values in input,
it would have been a dream. Having the same type (directly or via alias)
in most, if not all DBMS, would have been then paradise
( it's not here... sorry tina ;-) ).
I will try to use the things like they are,
until deeper pb occurs, hopefully not.
sincerely yours,
--
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
-------------------------------------------------------------------------------
On Mon, 22 Mar 2004, Pierre Didelon wrote:
> > On Fri, 19 Mar 2004, Pierre Didelon wrote:
> >
> >>
> >>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.
> >
> >
> > How standard do you want to be? AFAICS neither SQL92 or 99 provide any of
> > the operators you want and they both seem to allow the database to store
> > the values as decimal values rather than binary (thus being basically
> > numeric(<something>, 0)). Both also AFAICS have no real notion of an
> > unsigned numeric data type ("A value described by a numeric data type
> > descriptor is always signed").
>
> I use standard in the sense of most commonly used, that's why I quoted it,
> or as de facto standard, and not as real community defined standard.
Right, but most of those database at least attempt to come close to SQL.
If it's something that's not in there, it's unlikely to be done the same
on all of the systems. That's about the only standard in databases, that
everyone does it differently.
> I want to be able to execute the same sql statement, through JDBC,
> on mysql, postgres or any possible other DBMS.
Well, for example, INTEGER on Oracle appears to be NUMBER(38) (still
signed afaics but way overly large at 38 decimal digits if i'm reading it
right) and while it appears to have bitwise operations on numerics, the
docs don't point out & or | operators and it looks like the create
operator won't allow those for names. Looking at the interbase 6 docs I
see a signed 32 bit integer and no signs of an unsigned version and I
can't tell if it would have operators for them, but it does appear to have
functions (with yet another name).
> > How exactly are you using the values?
>
> I want to test it in where clause with predicate like
> bitmask & 0xf9001082 != 0 ;
> bitmask & 0xf9001082 = 0xf9001082
> or any binary/logical combination.
So the database needs those ops. In that case as per the Oracle example
above, you're unlikely to get this portably, or at least not in this form.
> Moreover, I want the results values to be intelligible by
> any application on top of the JDBC interface,
> without any knowledge of the database design and columns meaning.
> So output would be an numeric value with the common coding
> decimal, hexa with 0x prefix (or x'....'), but not
> unexpected weird things and format without preliminary notification
> (0b would have be a nice prefix for binary values of bit fields).
That's true, b'...' would probably have been a better output format choice
for bit(n). I doubt this is likely to change for compatibility reasons
however unfortunately.
> In that sense the output of bit fields is not self meaning
> because, unless futher analysis, nothing says you that it is
> a binary value.
But in general the value 1023 tells you nothing about whether it's
meaningful to treat the value semantically as a bitmask either (maybe it's
my customer number or a number of widgets) without further analysis.
AFAICS, if you want that property without any knowledge of the database,
the bitmasks need an output form that is unlike any from other datatypes
used.
> >>I consider a while creating a new postgres user type,
> >>but it implies to use C and so break down the java portability.
> >
> > I'm not sure what you mean by the end of the above.
>
> Using C I must compile the required code on every platform used.
> What I try to avoid with java.
Only on the server(s) AFAIK, but that's still more work for platforms that
have binary distributions already available (it's not really for ones that
don't since you'll need to compile the server anyway).
> That's why it would have been nicer to have an unsigned integer type,
> and if moreover it would have accept hexa values in input,
> it would have been a dream. Having the same type (directly or via alias)
> in most, if not all DBMS, would have been then paradise
Right, but for unsigned numerics for example, you're explicitly asking for
something that is against the specification most of those DBMSs claim to
support (the quote above, "A value described ..." was directly from
SQL99). It's a reasonable extension, but it's an extension which means it
might act differently on different systems (for example, simply
disallowing negative values while not using the sign bit as a value bit vs
using the sign bit as a value bit).