About unsigned smallint?

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