Subject: Re: Postgres do not support tinyint?

From: "David G. Johnston" <[email-masked]>
Date: Tue, 7 Jan 2025 23:44:21 -0700

On Tuesday, January 7, 2025, Ron Johnson <[email-masked]> wrote:

>
> 3. The "bit" type might serve your needs.
>
>
You suggest a type with a minimum size of 6 bytes when the complaint is
that the otherwise acceptable 2 byte data type is too large?

David J.

---

From: Christophe Pettus <[email-masked]>
Date: Tue, 7 Jan 2025 22:49:08 -0800

> On Jan 7, 2025, at 22:44, David G. Johnston <[email-masked]> wrote:
> 
> You suggest a type with a minimum size of 6 bytes when the complaint is that the otherwise acceptable 2 byte data type is too large?

Although it's not clear from the OP's question, if there are going to be a significant number of these 3-bit fields, packing them into a bitstring might be a way forward.  It's a good solution for tables with a ton of booleans.

---

From: Tom Lane <[email-masked]>
Date: Wed, 08 Jan 2025 01:53:34 -0500

> that the otherwise acceptable 2 byte data type is too large?

I think the point here is that there's zero value in trying to pack a
small integer value into 1 byte (let alone 4 bits) if it's all by
its lonesome in the row.  Alignment padding will eat whatever gain
you thought you had.  If you want a win, you need to store a lot of
such values in one field.  Ron's suggesting that you pack them into
bit arrays and manually insert/extract individual values.  That could
be worth doing if you were sufficiently desperate, but you'd have to
value compact storage over access simplicity quite a lot.

Perhaps a "char"[] array (note the quotes) would provide an
intermediate level of compactness versus pain.

			regards, tom lane

---

From: Igor Korot <[email-masked]>
Date: Wed, 8 Jan 2025 13:30:04 -0600

Hi, Christophe,

> Although it's not clear from the OP's question, if there are going to be a significant number of these 3-bit fields, packing them into a bitstring might be a way forward.  It's a good solution for…

There is no boolean - it is 0-4 inclusive.

Also - there are not too many records in that table...

Thank you.

---

From: Christophe Pettus <[email-masked]>
Date: Wed, 8 Jan 2025 11:34:15 -0800

> On Jan 8, 2025, at 11:30, Igor Korot <[email-masked]> wrote:

Unless you have somehow gotten PostgreSQL running on an IBM 7070, the range 0-4 can be represented by three binary digits, aka booleans. :-)

To be serious, though, the situation is:

1. If there are just one or two tinyints, having a tinyint type wouldn't save any space in the row.
2. If there are a lot of them, it's worth encoding them into a bitstring.

---

From: Igor Korot <[email-masked]>
Date: Wed, 8 Jan 2025 13:40:38 -0600

Hi, Christopphe,

> Unless you have somehow gotten PostgreSQL running on an IBM 7070, the range 0-4 can be represented by three binary digits, aka booleans. :-)

The only booleans I know of are 0 and 1. ;-)

> 1. If there are just one or two tinyints, having a tinyint type wouldn't save any space in the row.

No it is not a lot of them.
So then "smallint" is the best bet, right?

Thank you

---

From: Vladlen Popolitov <[email-masked]>
Date: Fri, 10 Jan 2025 16:13:05 +0700

Igor Korot писал(а) 2025-01-09 02:40:
>> Unless you have somehow gotten PostgreSQL running on an IBM 7070, the 
>> range 0-4 can be represented by three binary digits, aka booleans. :-)
> 

Hi!
If you really need 1-byte integer, you can use "char" type. Cast it 
to/from int.
See comment at the end of the page 
https://www.postgresql.org/docs/17/datatype-character.html

-- 
Best regards,

Vladlen Popolitov.