Postgres do not support tinyint?

On 1/7/25 21:06, Igor Korot wrote:

Hi, ALL,
According to https://www.postgresql.org/docs/9.1/datatype-numeric.html, the
smallest numeric type supports numbers from -32768 to 32767/

In this case it does not matter, but you should not consult
documentation that is for a version(9.1) that is ~8 years past EOL.

Go here:

https://www.postgresql.org/docs/

and click on Current or the version you are actually using.

My data will be in a range of [0..4], and so I guess my DB table will waste
space, right?

Yes, though is that actually going to be an issue?

Thank you.

--
Adrian Klaver
adrian.klaver@aklaver.com

On Wed, Jan 8, 2025 at 12:06 AM Igor Korot <ikorot01@gmail.com> wrote:

Hi, ALL,
According to https://www.postgresql.org/docs/9.1/datatype-numeric.html,
the
smallest numeric type supports numbers from -32768 to 32767/

My data will be in a range of [0..4], and so I guess my DB table will waste
space, right?

1. It's not 1994 anymore, when 8M rows was enormous.
2. Record structures are padded by word size, so tinyint wouldn't matter
unless you specifically ordered the fixed width columns from largest to
smallest size when creating the table.
3. The "bit" type might serve your needs.

--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

Hi, Ron,

On Tue, Jan 7, 2025 at 11:24 PM Ron Johnson <ronljohnsonjr@gmail.com> wrote:

On Wed, Jan 8, 2025 at 12:06 AM Igor Korot <ikorot01@gmail.com> wrote:

Hi, ALL,
According to https://www.postgresql.org/docs/9.1/datatype-numeric.html, the
smallest numeric type supports numbers from -32768 to 32767/

My data will be in a range of [0..4], and so I guess my DB table will waste
space, right?

1. It's not 1994 anymore, when 8M rows was enormous.
2. Record structures are padded by word size, so tinyint wouldn't matter unless you specifically ordered the fixed width columns from largest to smallest size when creating the table.
3. The "bit" type might serve your needs.

I don't see the "bit" field here:
https://www.postgresql.org/docs/current/datatype-numeric.html...

Thank you..

On Jan 7, 2025, at 22:26, Igor Korot <ikorot01@gmail.com> wrote:
I don't see the "bit" field here:
https://www.postgresql.org/docs/current/datatype-numeric.html...

https://www.postgresql.org/docs/current/datatype-bit.html

On Tuesday, January 7, 2025, Ron Johnson <ronljohnsonjr@gmail.com> 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.

On Jan 7, 2025, at 22:44, David G. Johnston <david.g.johnston@gmail.com> 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.

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Tuesday, January 7, 2025, Ron Johnson <ronljohnsonjr@gmail.com> 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?

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

Hi, Christophe,

On Wed, Jan 8, 2025 at 12:49 AM Christophe Pettus <xof@thebuild.com> wrote:

On Jan 7, 2025, at 22:44, David G. Johnston <david.g.johnston@gmail.com> 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.

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

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

Thank you.

On Jan 8, 2025, at 11:30, Igor Korot <ikorot01@gmail.com> wrote:
There is no boolean - it is 0-4 inclusive.

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.

Hi, Christopphe,

On Wed, Jan 8, 2025 at 1:34 PM Christophe Pettus <xof@thebuild.com> wrote:

On Jan 8, 2025, at 11:30, Igor Korot <ikorot01@gmail.com> wrote:
There is no boolean - it is 0-4 inclusive.

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. ;-)

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.

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

Thank you

On 2025-Jan-08, Igor Korot wrote:

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

In that case, you've probably wasted more time on this discussion than
the computer will ever save by storing a smaller column.

--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
"We’ve narrowed the problem down to the customer’s pants being in a situation
of vigorous combustion" (Robert Haas, Postgres expert extraordinaire)

Igor Korot писал(а) 2025-01-09 02:40:

Hi, Christopphe,

On Wed, Jan 8, 2025 at 1:34 PM Christophe Pettus <xof@thebuild.com>
wrote:

On Jan 8, 2025, at 11:30, Igor Korot <ikorot01@gmail.com> wrote:
There is no boolean - it is 0-4 inclusive.

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. ;-)

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.

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

Thank you

2. If there are a lot of them, it's worth encoding them into a
bitstring.

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.

On Fri, Jan 10, 2025 at 10:13 AM Vladlen Popolitov
<v.popolitov@postgrespro.ru> wrote:

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

Hi. What would be the best online doc to learn about the physical
storage / format for rows/pages/btree, etc... To understand alignment,
packing, varint or not, all those things. I'm quite familiar with the
[SQLite format][1]https://www.sqlite.org/fileformat.html, having read that doc dozens of times, and I'd like
a better low-level understanding for PostgreSQL as well. TIA, --DD

[1]: https://www.sqlite.org/fileformat.html

Dominique Devienne schrieb am 10.01.2025 um 10:41:

On Fri, Jan 10, 2025 at 10:13 AM Vladlen Popolitov
<v.popolitov@postgrespro.ru> wrote:

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

Hi. What would be the best online doc to learn about the physical
storage / format for rows/pages/btree, etc... To understand alignment,
packing, varint or not, all those things. I'm quite familiar with the
[SQLite format][1], having read that doc dozens of times, and I'd like
a better low-level understanding for PostgreSQL as well. TIA, --DD

The alignment requirement of each type is available in pg_type

https://www.postgresql.org/docs/current/catalog-pg-type.html

The physical layout on disk is described here:

https://www.postgresql.org/docs/current/storage.html

And the "Postgres intenrals" site might be helpful as well:

https://www.interdb.jp/pg/pgsql01.html

There are various (blog) posts on how to optimize space considering alignment

* https://stackoverflow.com/a/7431468
* https://www.enterprisedb.com/blog/rocks-and-sand

The "postgres_dba" toolset has a little SQL script to evaluate the space savings:

https://github.com/NikolayS/postgres_dba/blob/master/sql/p1_alignment_padding.sql

Dominique Devienne писал(а) 2025-01-10 16:41:

On Fri, Jan 10, 2025 at 10:13 AM Vladlen Popolitov
<v.popolitov@postgrespro.ru> wrote:

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

Hi. What would be the best online doc to learn about the physical
storage / format for rows/pages/btree, etc... To understand alignment,
packing, varint or not, all those things. I'm quite familiar with the
[SQLite format][1], having read that doc dozens of times, and I'd like
a better low-level understanding for PostgreSQL as well. TIA, --DD

[1]: https://www.sqlite.org/fileformat.html

I would recommend "PostgreSQL Internals" of Egor Rogov (Chapter 3 about
tuples,
other chapters also great)
Free download from https://postgrespro.com/community/books/internals

Also PostgreSQL source code is officially the part of the documentation.
It is not joke. PostgreSQL source has clear comments in functions code
and in structure declarations. You can compare with other sources, it is
hard to find better
comments made with love.

Heap tuple information is in source file
src/include/access/htup_details.h (HeapTupleHeaderData
structure), but I recommend to read above book first, it simplier
explains many
internal concepts.
--
Best regards,

Vladlen Popolitov.