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

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