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

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
-------------------------------------------------------------------------------