Thread: unsigned types
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?
I can't seem to find any documentation on this. What's the best prectice for this situation?
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 >>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.
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/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment
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