Thread: Postgres do not support tinyint?
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? Thank you.
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 widthcolumns 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.. > > -- > Death to <Redacted>, and butter sauce. > Don't boil me, I'm still alive. > <Redacted> lobster!
> 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 typeis 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, packingthem 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 typeis 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, packingthem 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 > 2. If there are a lot of them, it's worth encoding them into a bitstring.
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)