unsigned types

On Oct 16, 2005, at 5:42 , jeff sacksteder wrote:

It occurs to me that I don't know how to define unsigned integer
datatypes. I'm making a schema to describe network packets and I
need columns to contain values from 0-255, etc.

I can't seem to find any documentation on this. What's the best
prectice for this situation?

PostgreSQL does not have native unsigned integer datatypes. (For
reasons why, check the archives.) You can use domains or check
constraints to enforce a non-negative constraint. For example:

create table foo (
foo_id serial not null unique
, foo_unsigned_int integer not null check (foo_unsigned_int > 0)
);

or

create created domain unsigned_integer
as integer
check (value > 0);

create table bar (
bar_id serial not null unique
, bar_unsigned_int unsigned_integer not null
);

Here are doc references:
[check constraints](http://www.postgresql.org/docs/8.0/interactive/
ddl-constraints.html#AEN1936)
[create domain](http://www.postgresql.org/docs/8.0/interactive/sql-
createdomain.html)

Hope this helps.

Michael Glaesemann
grzm myrealbox com

On Sat, 2005-15-10 at 16:42 -0400, jeff sacksteder wrote:

It occurs to me that I don't know how to define unsigned integer
datatypes. I'm making a schema to describe network packets and I need
columns to contain values from 0-255, etc.

I can't seem to find any documentation on this. What's the best
prectice for this situation?

You can use a signed type with a CHECK constraint to restrict the
column's value to positive integers.

-Neil

You can use a signed type with a CHECK constraint to restrict the
column's value to positive integers.

The sign doesn't concern me. I am storing a value that is unsigned and 16
bits wide natively. I'll have to just use an int4 and waste twice the space
I actually need.

You can create a new type based on int2 called uint2.
he input function should subtract 32768 and the output function should
add 32768. The result should be an int4 so that a number such as 40000
can be displayed. The storage space required would still only be an
int2. The actual value stored in the database will be between -32768
to +32767 but the values that will be visible will be 0 to 65535

It seems simple enough to create a type to do that, though I haven't
tried.

Sim

jeff sacksteder wrote:

The sign doesn't concern me. I am storing a value that is unsigned and 16
bits wide natively. I'll have to just use an int4 and waste twice the space
I actually need.

Are you sure you'd really save space with a 16 bit type? Often times
that savings gets lost in alignment.

As far as I know, the smallest type that PostgreSQL supports is 4 bytes.
On 64-bit architectures, it may be effectively 8 bytes (although I'm not
sure about that).

If you're concerned about space usage, you'll certainly be better off
using a packed type of some kind. For example, you could use an 8 byte
type, put 4 2-byte integers in it, and then have accessor functions that
return any of the given integers. Then make a view out of it, and
applications won't know the difference. Something like:

CREATE TABLE foo (
id serial primary key,
ints int8
);

CREATE VIEW foo_v AS SELECT id, getint(ints,0) AS int0, getint(ints,1)
AS int1, getint(ints,2) AS int2, getint(ints,3) AS int3 FROM foo;

of course you have to define the function getint() and setint() or
something like them, which should be easy to write in your favorite
language.

My advice would be to build the table the way you want it, and if it's
too bulky or slow, optimize it later. That's what is so great about
PostgreSQL, you can optimize, then just use a view and the application
will never know the difference.

I'll also mention that PostgreSQL has the built-in INET and CIDR types
which hold ip addresses/networks, but I assume those aren't what you're
looking for.

Hope this helps,
Jeff Davis

On Sun, Oct 16, 2005 at 10:08:41AM -0700, Jeff Davis wrote:

jeff sacksteder wrote:

The sign doesn't concern me. I am storing a value that is unsigned and 16
bits wide natively. I'll have to just use an int4 and waste twice the space
I actually need.

Are you sure you'd really save space with a 16 bit type? Often times
that savings gets lost in alignment.

As far as I know, the smallest type that PostgreSQL supports is 4 bytes.
On 64-bit architectures, it may be effectively 8 bytes (although I'm not
sure about that).

It depends on MAXALIGN, which is 4 bytes on most platforms. But, there's
more to the story than that... If you SELECT typname, typalign FROM
pg_type WHERE typname LIKE 'int%'; you'll see that int2 can actually
align on smallint (typically 2 byte) boundaries. So, if you have a bunch
of int2's all next to each other in a table, they will happily just
consume 2 bytes. The issue comes when you try and mix them with other
fields randomly, since many other fields require int alignment.

Also, your suggestion of packing could actually hurt, since it will be
forced to an 8 byte boundary on most systems (int8 requires 'double'
alignment). If you instead used 4 smallint fields, all together, you
would probably only waste 2 bytes. Of course, this is all 100% dependant
on the other fields in the table.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

On Mon, 2005-17-10 at 12:25 -0500, Jim C. Nasby wrote:

So, if you have a bunch of int2's all next to each other in a table,
they will happily just consume 2 bytes. The issue comes when you try
and mix them with other fields randomly, since many other fields
require int alignment.

We could improve on this by reordering fields on-disk to reduce
alignment/padding requirements, during CREATE TABLE. We'd need to be
sure to present the same column order back to the client application, of
course, but that should be possible. The notion of a "physical column
number" (on-disk position of the column) as well as a "logical column
numer" (position of the column in the table -- e.g. in SELECT *
expansion) would also make it easy to implement column reordering in
ALTER TABLE, which has been requested a few times.

-Neil

On Tue, Oct 18, 2005 at 02:00:57PM -0400, Neil Conway wrote:

We could improve on this by reordering fields on-disk to reduce
alignment/padding requirements, during CREATE TABLE. We'd need to be
sure to present the same column order back to the client application, of
course, but that should be possible. The notion of a "physical column
number" (on-disk position of the column) as well as a "logical column
numer" (position of the column in the table -- e.g. in SELECT *
expansion) would also make it easy to implement column reordering in
ALTER TABLE, which has been requested a few times.

AIUI a patch was submitted but rejected on the basis that it would
break too many client apps that rely on the current catalog setup. And
it was combined with "alter column type" discussion at the time. And a
number of other reasons I didn't understand at the time.

http://archives.postgresql.org/pgsql-patches/2003-11/msg00281.php
http://archives.postgresql.org/pgsql-hackers/2003-11/msg00869.php

Yes, once you seperate physical and logical column ordering this
becomes possible, but you have to do it first :)

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Neil Conway <neilc@samurai.com> writes:

We could improve on this by reordering fields on-disk to reduce
alignment/padding requirements, during CREATE TABLE. We'd need to be
sure to present the same column order back to the client application, of
course, but that should be possible. The notion of a "physical column
number" (on-disk position of the column) as well as a "logical column
numer" (position of the column in the table -- e.g. in SELECT *
expansion) would also make it easy to implement column reordering in
ALTER TABLE, which has been requested a few times.

And it's been looked at a few times, and rejected as being far too
bug-prone. The number of ways to screw up by using physical column
number where you should have used logical, or vice versa, is daunting.

regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> writes:

And it's been looked at a few times, and rejected as being far too
bug-prone. The number of ways to screw up by using physical column
number where you should have used logical, or vice versa, is daunting.

One way to make sure there are no such bugs would be to make sure the two sets
of values are completely incompatible. So any attempt to use the wrong one
would *always* cause a bug rather than just sometimes.

Say by making one set have an offset of 1000 or be negative and have the
functions/macros that handle this assert() the correct range before
adjusting and proceeding.

--
greg