public inbox for [email protected]
help / color / mirror / Atom feedRe: Postgres do not support tinyint?
7+ messages / 5 participants
[nested] [flat]
* Re: Postgres do not support tinyint?
@ 2025-01-08 06:44 David G. Johnston <[email protected]>
2025-01-08 06:49 ` Re: Postgres do not support tinyint? Christophe Pettus <[email protected]>
2025-01-08 06:53 ` Re: Postgres do not support tinyint? Tom Lane <[email protected]>
0 siblings, 2 replies; 7+ messages in thread
From: David G. Johnston @ 2025-01-08 06:44 UTC (permalink / raw)
To: Ron Johnson <[email protected]>; +Cc: pgsql-generallists.postgresql.org <[email protected]>
On Tuesday, January 7, 2025, Ron Johnson <[email protected]> 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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Postgres do not support tinyint?
2025-01-08 06:44 Re: Postgres do not support tinyint? David G. Johnston <[email protected]>
@ 2025-01-08 06:49 ` Christophe Pettus <[email protected]>
2025-01-08 19:30 ` Re: Postgres do not support tinyint? Igor Korot <[email protected]>
1 sibling, 1 reply; 7+ messages in thread
From: Christophe Pettus @ 2025-01-08 06:49 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: Ron Johnson <[email protected]>; pgsql-generallists.postgresql.org <[email protected]>
> On Jan 7, 2025, at 22:44, David G. Johnston <[email protected]> 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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Postgres do not support tinyint?
2025-01-08 06:44 Re: Postgres do not support tinyint? David G. Johnston <[email protected]>
2025-01-08 06:49 ` Re: Postgres do not support tinyint? Christophe Pettus <[email protected]>
@ 2025-01-08 19:30 ` Igor Korot <[email protected]>
2025-01-08 19:34 ` Re: Postgres do not support tinyint? Christophe Pettus <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Igor Korot @ 2025-01-08 19:30 UTC (permalink / raw)
To: Christophe Pettus <[email protected]>; +Cc: David G. Johnston <[email protected]>; Ron Johnson <[email protected]>; pgsql-generallists.postgresql.org <[email protected]>
Hi, Christophe,
On Wed, Jan 8, 2025 at 12:49 AM Christophe Pettus <[email protected]> wrote:
>
>
>
> > On Jan 7, 2025, at 22:44, David G. Johnston <[email protected]> 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.
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Postgres do not support tinyint?
2025-01-08 06:44 Re: Postgres do not support tinyint? David G. Johnston <[email protected]>
2025-01-08 06:49 ` Re: Postgres do not support tinyint? Christophe Pettus <[email protected]>
2025-01-08 19:30 ` Re: Postgres do not support tinyint? Igor Korot <[email protected]>
@ 2025-01-08 19:34 ` Christophe Pettus <[email protected]>
2025-01-08 19:40 ` Re: Postgres do not support tinyint? Igor Korot <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Christophe Pettus @ 2025-01-08 19:34 UTC (permalink / raw)
To: Igor Korot <[email protected]>; +Cc: David G. Johnston <[email protected]>; Ron Johnson <[email protected]>; pgsql-generallists.postgresql.org <[email protected]>
> On Jan 8, 2025, at 11:30, Igor Korot <[email protected]> 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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Postgres do not support tinyint?
2025-01-08 06:44 Re: Postgres do not support tinyint? David G. Johnston <[email protected]>
2025-01-08 06:49 ` Re: Postgres do not support tinyint? Christophe Pettus <[email protected]>
2025-01-08 19:30 ` Re: Postgres do not support tinyint? Igor Korot <[email protected]>
2025-01-08 19:34 ` Re: Postgres do not support tinyint? Christophe Pettus <[email protected]>
@ 2025-01-08 19:40 ` Igor Korot <[email protected]>
2025-01-10 09:13 ` Re: Postgres do not support tinyint? Vladlen Popolitov <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Igor Korot @ 2025-01-08 19:40 UTC (permalink / raw)
To: Christophe Pettus <[email protected]>; +Cc: David G. Johnston <[email protected]>; Ron Johnson <[email protected]>; pgsql-generallists.postgresql.org <[email protected]>
Hi, Christopphe,
On Wed, Jan 8, 2025 at 1:34 PM Christophe Pettus <[email protected]> wrote:
>
>
>
> > On Jan 8, 2025, at 11:30, Igor Korot <[email protected]> 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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Postgres do not support tinyint?
2025-01-08 06:44 Re: Postgres do not support tinyint? David G. Johnston <[email protected]>
2025-01-08 06:49 ` Re: Postgres do not support tinyint? Christophe Pettus <[email protected]>
2025-01-08 19:30 ` Re: Postgres do not support tinyint? Igor Korot <[email protected]>
2025-01-08 19:34 ` Re: Postgres do not support tinyint? Christophe Pettus <[email protected]>
2025-01-08 19:40 ` Re: Postgres do not support tinyint? Igor Korot <[email protected]>
@ 2025-01-10 09:13 ` Vladlen Popolitov <[email protected]>
0 siblings, 0 replies; 7+ messages in thread
From: Vladlen Popolitov @ 2025-01-10 09:13 UTC (permalink / raw)
To: Igor Korot <[email protected]>; +Cc: Christophe Pettus <[email protected]>; David G. Johnston <[email protected]>; Ron Johnson <[email protected]>; pgsql-generallists.postgresql.org <[email protected]>
Igor Korot писал(а) 2025-01-09 02:40:
> Hi, Christopphe,
>
> On Wed, Jan 8, 2025 at 1:34 PM Christophe Pettus <[email protected]>
> wrote:
>>
>>
>>
>> > On Jan 8, 2025, at 11:30, Igor Korot <[email protected]> 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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Postgres do not support tinyint?
2025-01-08 06:44 Re: Postgres do not support tinyint? David G. Johnston <[email protected]>
@ 2025-01-08 06:53 ` Tom Lane <[email protected]>
1 sibling, 0 replies; 7+ messages in thread
From: Tom Lane @ 2025-01-08 06:53 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: Ron Johnson <[email protected]>; pgsql-generallists.postgresql.org <[email protected]>
"David G. Johnston" <[email protected]> writes:
> On Tuesday, January 7, 2025, Ron Johnson <[email protected]> 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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2025-01-10 09:13 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-01-08 06:44 Re: Postgres do not support tinyint? David G. Johnston <[email protected]>
2025-01-08 06:49 ` Christophe Pettus <[email protected]>
2025-01-08 19:30 ` Igor Korot <[email protected]>
2025-01-08 19:34 ` Christophe Pettus <[email protected]>
2025-01-08 19:40 ` Igor Korot <[email protected]>
2025-01-10 09:13 ` Vladlen Popolitov <[email protected]>
2025-01-08 06:53 ` Tom Lane <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox