Thread: One byte integer support
Is there any way to support one byte integers? I am using JDBC and would like to use setByte() method to populate values of a single-byte integer valued column. I tried defining the column as character(1), but I get the error message: Exception in thread "main" org.postgresql.util.PSQLException: ERROR: value too long for type character(1) I don't see how a byte is too long for character(1). Is there any data type like MySQL's tinyint? __________________________________ Do you Yahoo!? Yahoo! Mail - 250MB free storage. Do more. Manage less. http://info.mail.yahoo.com/mail_250
On Thu, 6 Jan 2005, Keith Hankin wrote: > Is there any way to support one byte integers? I am > using JDBC and would like to use setByte() method to > populate values of a single-byte integer valued > column. I tried defining the column as character(1), > but I get the error message: > > Exception in thread "main" > org.postgresql.util.PSQLException: ERROR: value too > long for type character(1) > > I don't see how a byte is too long for character(1). Character is a textual data type, byte is a numeric value so when you set a byte value of say 145 to a text type you get the string '145' which is three characters. Note that character(1) does not take up one byte of space on disk. It is a one character field, not one byte. Multibyte character sets may need more than one byte to store a single character, so this is variable byte length field that takes up at least 5 bytes of space. 4 bytes of length data and then the actual data bytes. > Is there any data type like MySQL's tinyint? > Possibly the system type "char" (needs the quotes), but I have no idea how that will work with the JDBC driver. Going with smallint may be the best thing to do. Before assuming that a tinyint would actually save you space you need to make sure you take into account padding and alignment. Kris Jurka
But Postgres 'char' is just an alias for 'character', so it will won't be just a single byte. And I've already accounted for padding and alignment in my structures and using bytes instead of shorts would result in a significant savings both in memory as well as disk. I don't understand why Postgres doesn't have a single-byte int support. Even MySQL does. --- Kris Jurka <books@ejurka.com> wrote: > > > On Thu, 6 Jan 2005, Keith Hankin wrote: > > > Is there any way to support one byte integers? I > am > > using JDBC and would like to use setByte() method > to > > populate values of a single-byte integer valued > > column. I tried defining the column as > character(1), > > but I get the error message: > > > > Exception in thread "main" > > org.postgresql.util.PSQLException: ERROR: value > too > > long for type character(1) > > > > I don't see how a byte is too long for > character(1). > > Character is a textual data type, byte is a numeric > value so when you set > a byte value of say 145 to a text type you get the > string '145' which is > three characters. > > Note that character(1) does not take up one byte of > space on disk. It is > a one character field, not one byte. Multibyte > character sets may need > more than one byte to store a single character, so > this is variable byte > length field that takes up at least 5 bytes of > space. 4 bytes of length > data and then the actual data bytes. > > > Is there any data type like MySQL's tinyint? > > > > Possibly the system type "char" (needs the quotes), > but I have no idea how > that will work with the JDBC driver. Going with > smallint may be the best > thing to do. Before assuming that a tinyint would > actually save you space > you need to make sure you take into account padding > and alignment. > > Kris Jurka > __________________________________ Do you Yahoo!? The all-new My Yahoo! - What will yours do? http://my.yahoo.com
On Fri, 7 Jan 2005, Keith Hankin wrote: > But Postgres 'char' is just an alias for 'character', Note that I did not say char. I said "char". db=# create table t (a char, b "char"); CREATE TABLE db=# \d t Table "public.t" Column | Type | Modifiers --------+--------------+----------- a | character(1) | b | "char" | > I don't understand why Postgres doesn't have a single-byte int support. > Postgresql's extensible type system allows users to create any number of their own types. One drawback of this flexibility is that it makes it difficult to interpret what type a raw number is. What type is 37? int2, int4, int8? Up until recently there were significant problems that occurred if you got this wrong and people aren't rushing to add new types that could resurrect those unhappy times. This is also why postgresql does not have unsigned types. Kris Jurka
Keith Hankin <keith105@yahoo.com> writes: > I don't understand why Postgres doesn't have > a single-byte int support. Even MySQL does. Primarily because no one sees it as of enough value to justify the work. Are you volunteering? In the past there were also some issues about fragility of the type resolution rules, but I think that as of 8.0 it would be possible to support an int1 type without causing any major repercussions elsewhere. regards, tom lane
--- Tom Lane <tgl@sss.pgh.pa.us> wrote: > Keith Hankin <keith105@yahoo.com> writes: > > I don't understand why Postgres doesn't have > > a single-byte int support. Even MySQL does. > > Primarily because no one sees it as of enough value > to justify the work. There is significant value if you're dealing with a very large database and saving a few bytes per row can lead to significant space savings. __________________________________ Do you Yahoo!? Yahoo! Mail - now with 250MB free storage. Learn more. http://info.mail.yahoo.com/mail_250