Обсуждение: About unsigned smallint?

Greetings,

Can I know whether postgreSQL 8.0 supports unsigned smallint please? I
looked at the doc, it seems that OID is unsigned interger. While I was
trying to create a simple table as:
      create table test (id unsigned smallint);
                             or
      create table test (id smallint unsigned);

It seems that postgreSQL did not support unsigned integer?

Thanks a lot,
Emi

On 7/6/05, Ying Lu <ying_lu@cs.concordia.ca> wrote:
> Greetings,
>
> Can I know whether postgreSQL 8.0 supports unsigned smallint please? I
> looked at the doc, it seems that OID is unsigned interger. While I was
> trying to create a simple table as:
>       create table test (id unsigned smallint);
>                              or
>       create table test (id smallint unsigned);
>
> It seems that postgreSQL did not support unsigned integer?

Well, PostgreSQL doesn't have "unsigned" types, unless you create
your own.  If you want to have unsigned type, you can add a check
constraint or, even better, create a domain:

CREATE DOMAIN usmallint AS smallint CHECK (VALUE >= 0);

...while this gives you unsinged smallint type, its probably not
what you wanted.  If you wanted a type which takes two bytes of
storage and stores values from 0 to 65535 then, well... its not it.

If you ask here, you'll probably get a good explanation why there
aren't unsinged types.  My guess is that unsigned types add
complexity which is not really judged by their usefullness, but
thats only a guess.

If you need unsigned-like type for data consistency reasons, just
CREATE DOMAIN as shown above.

  Regards,
      Dawid

On Wed, Jul 06, 2005 at 11:30:52PM +0200, Dawid Kuroczko wrote:

> If you ask here, you'll probably get a good explanation why there
> aren't unsinged types.  My guess is that unsigned types add
> complexity which is not really judged by their usefullness, but
> thats only a guess.

Yeah, they are against the SQL standard apparently; and we've got enough
problems with cross-datatype coercion that there's not much interest in
making it worse by adding more types.

--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"Some men are heterosexual, and some are bisexual, and some
men don't think about sex at all... they become lawyers" (Woody Allen)

Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> On Wed, Jul 06, 2005 at 11:30:52PM +0200, Dawid Kuroczko wrote:
>> If you ask here, you'll probably get a good explanation why there
>> aren't unsinged types.

> Yeah, they are against the SQL standard apparently;

Not so much "against it" as "not in it" ... which means that if you want
such a feature, you need to actively convince people of its merits.

> and we've got enough
> problems with cross-datatype coercion that there's not much interest in
> making it worse by adding more types.

That was the main reason for rejecting such proposals a few releases ago.
It's possible that our subsequent cleanups in the coercion mechanisms
would make this a feasible idea now.  But I haven't investigated
closely, and I don't believe anyone else has either.

The short answer is definitely that it would take more work than anyone
has so far cared to commit.

            regards, tom lane