public inbox for [email protected]help / color / mirror / Atom feed
Re: 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]> 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:49 Christophe Pettus <[email protected]> parent: David G. Johnston <[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:53 Tom Lane <[email protected]> parent: David G. Johnston <[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
* Re: Postgres do not support tinyint? @ 2025-01-08 19:30 Igor Korot <[email protected]> parent: 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 19:34 Christophe Pettus <[email protected]> parent: 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 19:40 Igor Korot <[email protected]> parent: Christophe Pettus <[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-10 09:13 Vladlen Popolitov <[email protected]> parent: Igor Korot <[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
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