Thread: Data TYPE Creation

On Sun, Mar 16, 2003 at 23:39:22 +0100,
  Guillaume Houssay <ghoussay@noos.fr> wrote:
> I wonder if the following data type is available INT1 (I am using the last version of Postgresql).
> I will have about 90% of my database with numeric values of only 1 byte.
>
> If it does not exist (according to what I read this is the case), is there a way to use another type with the same
result? 

You can use a constraint to limit the valid range of integers. Probably
using int2 with a constraint is the best answer for you.

On Mon, Mar 17, 2003 at 14:40:28 +0000,
  Houssay Guillaume <ghoussay@noos.fr> wrote:
> Thank you for your answer.
> By constraint you mean having the length of the INT2 field set to 1 ? I do not know how to define a constraint by
anotherway. 

create table example (col1 int check (col1 < 256 and col1 > 0));

This would only make sense if your data is really constrained this way.
The idea is to prevent invalid data from getting into the system, not to
save space.

>
> What about the disk space ? By defining an INT2 with a constraint of 1 byte, how much space will be used in the
memory(1 byte or 2 bytes). This is really one of my concern. 

The extra disc space won't be that much because there is other information
being stored besides that raw data. So that int2 won't take up twice as much
space as int1 (note postgres doesn't have that type).

Bruno Wolff III <bruno@wolff.to> writes:
>   Houssay Guillaume <ghoussay@noos.fr> wrote:
>> What about the disk space ? By defining an INT2 with a constraint of 1 byte, how much space will be used in the
memory(1 byte or 2 bytes). This is really one of my concern. 

> The extra disc space won't be that much because there is other information
> being stored besides that raw data. So that int2 won't take up twice as much
> space as int1 (note postgres doesn't have that type).

If the OP is desperate to feel that he's saving space, there's always
the "char" type (note the quotes) --- it's got an impoverished set of
operations and the I/O format is maybe not quite what's wanted, but it
does store as a single byte.

But Bruno is correct to point out that actual space savings is another
question.  Unless you have quite a few such columns appearing
consecutively in a table, it's not worth worrying about, because in any
context except adjacent "char" columns, the space will disappear into
alignment padding for the next field anyway.

            regards, tom lane