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

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.