Thread: int1?
Is there any date type that can be used for 0-255 values? Like an "int1" or byte column. CSN __________________________________ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com
CSN wrote: > Is there any date type that can be used for 0-255 > values? Like an "int1" or byte column. You can use a smallint with constraint. HTH Shridhar
On Thu, 2003-10-09 at 02:16, CSN wrote: > Is there any date type that can be used for 0-255 > values? Like an "int1" or byte column. An int2 with a constraint on it. -- ----------------------------------------------------------------- Ron Johnson, Jr. ron.l.johnson@cox.net Jefferson, LA USA "Fear the Penguin!!"
> Is there any date type that can be used for 0-255 > values? Like an "int1" or byte column. A SMALLINT is two bytes on disk, use "char" instead. This is a hidden goodie in PostgreSQL and one that I wish was exposed via a more conventional syntax (*hint hint*). http://developer.postgresql.org/docs/postgres/datatype-character.html#DATATYPE-CHARACTER-SPECIAL-TABLE -sc -- Sean Chittenden
On Thu, 2003-10-09 at 03:19, Sean Chittenden wrote: > > Is there any date type that can be used for 0-255 > > values? Like an "int1" or byte column. > > A SMALLINT is two bytes on disk, use "char" instead. This is a hidden > goodie in PostgreSQL and one that I wish was exposed via a more > conventional syntax (*hint hint*). > > http://developer.postgresql.org/docs/postgres/datatype-character.html#DATATYPE-CHARACTER-SPECIAL-TABLE Wouldn't that be, though, a signed byte? The OP wants unsigned. -- ----------------------------------------------------------------- Ron Johnson, Jr. ron.l.johnson@cox.net Jefferson, LA USA The purpose of the military isn't to pay your college tuition or give you a little extra income; it's to "kill people and break things". Surprisingly, not everyone understands that.
> http://developer.postgresql.org/docs/postgres/datatype-character.html#DATATYPE-CHARACTER-SPECIAL-TABLE Is it unsafe practice to use the datatype "name" for attributes that hold table or column names etc ? Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
On Thu, 9 Oct 2003, Sean Chittenden wrote: > > Is there any date type that can be used for 0-255 > > values? Like an "int1" or byte column. > > A SMALLINT is two bytes on disk, use "char" instead. This is a hidden However "char" has some serious deficiencies IIRC, such as the fact that there's no int<->"char" casts and it's standard I/O format is characters. You can use ascii and chr to get around some of that, but it's ugly. > goodie in PostgreSQL and one that I wish was exposed via a more > conventional syntax (*hint hint*). If we were going to do that I think we'd be better off making a new type and leaving "char" alone.
> > > Is there any date type that can be used for 0-255 values? Like > > > an "int1" or byte column. > > > > A SMALLINT is two bytes on disk, use "char" instead. This is a hidden > > However "char" has some serious deficiencies IIRC, such as the fact > that there's no int<->"char" casts and it's standard I/O format is > characters. You can use ascii and chr to get around some of that, > but it's ugly. *nods* I have explicit casts everywhere when dealing with "char" and it's far from being elegant or clean. > > > goodie in PostgreSQL and one that I wish was exposed via a more > > conventional syntax (*hint hint*). > > If we were going to do that I think we'd be better off making a new > type and leaving "char" alone. > You won't hear any disagreements from me on this one. I've sufficiently abused "char" as a 1 byte storage field and would love to see an int1 or tinyint datatype added to cover this situation. -sc -- Sean Chittenden
On Thu, 2003-10-09 at 12:54, Sean Chittenden wrote: > > > > Is there any date type that can be used for 0-255 values? Like > > > > an "int1" or byte column. > > > > > > A SMALLINT is two bytes on disk, use "char" instead. This is a hidden > > > > However "char" has some serious deficiencies IIRC, such as the fact > > that there's no int<->"char" casts and it's standard I/O format is > > characters. You can use ascii and chr to get around some of that, > > but it's ugly. > > *nods* I have explicit casts everywhere when dealing with "char" and > it's far from being elegant or clean. > > > > > > goodie in PostgreSQL and one that I wish was exposed via a more > > > conventional syntax (*hint hint*). > > > > If we were going to do that I think we'd be better off making a new > > type and leaving "char" alone. > > > > You won't hear any disagreements from me on this one. I've > sufficiently abused "char" as a 1 byte storage field and would love to > see an int1 or tinyint datatype added to cover this situation. -sc http://www.postgresql.org/docs/7.3/static/sql-createdomain.html CREATE DOMAIN domainname [AS] data_type [ DEFAULT default_expr ] [ constraint [, ... ] ] where constraint is: [ CONSTRAINT constraint_name ] { NOT NULL | NULL } test1=# create domain d_tinyint as smallint constraint chk_tinyint CHECK (smallint between 0 and 255); ERROR: DefineDomain: CHECK Constraints not supported So, how would I create a domain that limits a smallint? -- ----------------------------------------------------------------- Ron Johnson, Jr. ron.l.johnson@cox.net Jefferson, LA USA "You can either have software quality or you can have pointer arithmetic, but you cannot have both at the same time." Bertrand Meyer
On Thu, Oct 09, 2003 at 14:28:57 -0500, Ron Johnson <ron.l.johnson@cox.net> wrote: > > http://www.postgresql.org/docs/7.3/static/sql-createdomain.html > CREATE DOMAIN domainname [AS] data_type > [ DEFAULT default_expr ] > [ constraint [, ... ] ] > > where constraint is: > > [ CONSTRAINT constraint_name ] > { NOT NULL | NULL } > > test1=# create domain d_tinyint as smallint constraint chk_tinyint CHECK (smallint between 0 and 255); > ERROR: DefineDomain: CHECK Constraints not supported > > So, how would I create a domain that limits a smallint? You need to use 7.4. In 7.3 you couldn't use check constraints with domains.
On Thu, 2003-10-09 at 14:46, Bruno Wolff III wrote: > On Thu, Oct 09, 2003 at 14:28:57 -0500, > Ron Johnson <ron.l.johnson@cox.net> wrote: > > > > http://www.postgresql.org/docs/7.3/static/sql-createdomain.html > > CREATE DOMAIN domainname [AS] data_type > > [ DEFAULT default_expr ] > > [ constraint [, ... ] ] > > > > where constraint is: > > > > [ CONSTRAINT constraint_name ] > > { NOT NULL | NULL } > > > > test1=# create domain d_tinyint as smallint constraint chk_tinyint CHECK (smallint between 0 and 255); > > ERROR: DefineDomain: CHECK Constraints not supported > > > > So, how would I create a domain that limits a smallint? > > You need to use 7.4. In 7.3 you couldn't use check constraints with domains. So is there a documentation "bug", or, what kind of constraints can be placed on domains besides { NOT NULL | NULL }? -- ----------------------------------------------------------------- Ron Johnson, Jr. ron.l.johnson@cox.net Jefferson, LA USA Causation does NOT equal correlation !!!!!!!!
Ron Johnson writes: > test1=# create domain d_tinyint as smallint constraint chk_tinyint CHECK (smallint between 0 and 255); > ERROR: DefineDomain: CHECK Constraints not supported > > So, how would I create a domain that limits a smallint? You would have to wait for PostgreSQL 7.4. -- Peter Eisentraut peter_e@gmx.net
On Thu, Oct 09, 2003 at 14:46:08 -0500, Ron Johnson <ron.l.johnson@cox.net> wrote: > On Thu, 2003-10-09 at 14:46, Bruno Wolff III wrote: > > On Thu, Oct 09, 2003 at 14:28:57 -0500, > > Ron Johnson <ron.l.johnson@cox.net> wrote: > > > > > > http://www.postgresql.org/docs/7.3/static/sql-createdomain.html > > > CREATE DOMAIN domainname [AS] data_type > > > [ DEFAULT default_expr ] > > > [ constraint [, ... ] ] > > > > > > where constraint is: > > > > > > [ CONSTRAINT constraint_name ] > > > { NOT NULL | NULL } > > > > > > test1=# create domain d_tinyint as smallint constraint chk_tinyint CHECK (smallint between 0 and 255); > > > ERROR: DefineDomain: CHECK Constraints not supported > > > > > > So, how would I create a domain that limits a smallint? > > > > You need to use 7.4. In 7.3 you couldn't use check constraints with domains. > > So is there a documentation "bug", or, what kind of constraints > can be placed on domains besides { NOT NULL | NULL }? I think the documentation is correct. As I read it it says that only NOT NULL and NULL constraints are allowed. This is easy to overlook. I know I got caught by this when I tried it. I started using 7.4 pretty early on since I wanted to use check constraints in earthdistance to have a domain that represented points on the surface of the earth on top of the cube data type.
On Thu, 2003-10-09 at 15:13, Bruno Wolff III wrote: > On Thu, Oct 09, 2003 at 14:46:08 -0500, > Ron Johnson <ron.l.johnson@cox.net> wrote: > > On Thu, 2003-10-09 at 14:46, Bruno Wolff III wrote: > > > On Thu, Oct 09, 2003 at 14:28:57 -0500, > > > Ron Johnson <ron.l.johnson@cox.net> wrote: > > > > > > > > http://www.postgresql.org/docs/7.3/static/sql-createdomain.html > > > > CREATE DOMAIN domainname [AS] data_type > > > > [ DEFAULT default_expr ] > > > > [ constraint [, ... ] ] > > > > > > > > where constraint is: > > > > > > > > [ CONSTRAINT constraint_name ] > > > > { NOT NULL | NULL } > > > > > > > > test1=# create domain d_tinyint as smallint constraint chk_tinyint CHECK (smallint between 0 and 255); > > > > ERROR: DefineDomain: CHECK Constraints not supported > > > > > > > > So, how would I create a domain that limits a smallint? > > > > > > You need to use 7.4. In 7.3 you couldn't use check constraints with domains. > > > > So is there a documentation "bug", or, what kind of constraints > > can be placed on domains besides { NOT NULL | NULL }? > > I think the documentation is correct. As I read it it says that only NOT NULL > and NULL constraints are allowed. This is easy to overlook. I know I got > caught by this when I tried it. test1=# create domain foo as smallint not null; CREATE DOMAIN test1=# create domain bar as smallint CONSTRAINT wiggle not null; CREATE DOMAIN Oh, ok. Stuff in [] is not necessary. Still confusing. -- ----------------------------------------------------------------- Ron Johnson, Jr. ron.l.johnson@cox.net Jefferson, LA USA The difference between drunken sailors and Congressmen is that drunken sailors spend their own money.
Ron Johnson wrote: > On Thu, 2003-10-09 at 02:16, CSN wrote: > >>Is there any date type that can be used for 0-255 >>values? Like an "int1" or byte column. > > An int2 with a constraint on it. > You can use the data type "char" (with the quotes, and without a (n) decoration). See: http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=datatype-character.html near the bottom of the page. Joe
Would you be able to roll your own int1's with types? http://www.postgresql.org/docs/7.3/interactive/xtypes.html CSN __________________________________ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com
I don't think that you can create a genuine one byte datatype. The resulting type would probably be four bytes long, even if you create a one byte by-value data type. The one byte would be packaged in a 4 byte container for passing around the server. Can anyone confirm or deny this? This was certainly the case in Informix and Illustra. --elein elein@varlena.com On Fri, Oct 10, 2003 at 11:37:14AM -0700, CSN wrote: > > Would you be able to roll your own int1's with types? > > http://www.postgresql.org/docs/7.3/interactive/xtypes.html > > CSN > > > __________________________________ > Do you Yahoo!? > The New Yahoo! Shopping - with improved product search > http://shopping.yahoo.com > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
elein wrote: > I don't think that you can create a genuine one byte datatype. > The resulting type would probably be four bytes long, even if > you create a one byte by-value data type. The one byte would > be packaged in a 4 byte container for passing around the server. > > Can anyone confirm or deny this? See my other post. The type exists and is called "char". See the bottom of this page: http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=datatype-character.html Joe
The "char" type has special handling built into the server if I recall correctly and that is part of the reason it does not behave correctly in some cases. But I think it is still schlepped around as a DATUM which is a four byte value. What I meant was a user defined single byte data type. I don't think it can be done since it needs to be packaged as a DATUM. elein On Fri, Oct 10, 2003 at 06:07:00PM -0700, Joe Conway wrote: > elein wrote: > >I don't think that you can create a genuine one byte datatype. > >The resulting type would probably be four bytes long, even if > >you create a one byte by-value data type. The one byte would > >be packaged in a 4 byte container for passing around the server. > > > >Can anyone confirm or deny this? > > See my other post. The type exists and is called "char". See the bottom > of this page: > http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=datatype-character.html > > Joe > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
elein wrote: > The "char" type has special handling built into the server > if I recall correctly and that is part of the reason it > does not behave correctly in some cases. But I think it is > still schlepped around as a DATUM which is a four byte value. > > What I meant was a user defined single byte data type. > I don't think it can be done since it needs to be packaged > as a DATUM. No, "char" is exactly one byte. See the doc, or the source: http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/backend/utils/adt/char.c?rev=1.38&content-type=text/x-cvsweb-markup Joe
On Fri, 10 Oct 2003 16:53:55 -0700, elein <elein@varlena.com> wrote:
>I don't think that you can create a genuine one byte datatype.
>The resulting type would probably be four bytes long, even if
>you create a one byte by-value data type.
Column values are not *expanded* to multiples of four bytes, they are
*aligned* according to their datatype (cf. pg_type.typalign).
Not counting heap tuple headers, we get the following offsets and
lengths:
CREATE TABLE a (
c1 "char" NOT NULL, -- offset 0
c2 "char" NOT NULL, -- offset 1
c3 "char" NOT NULL, -- offset 2
c4 "char" NOT NULL -- offset 3
); -- size = 4
CREATE TABLE b (
c1 bool NOT NULL, -- offset 0
c2 int2 NOT NULL, -- offset 2
c3 bool NOT NULL, -- offset 4
c4 int NOT NULL, -- offset 8
c5 bool NOT NULL, -- offset 12
c6 char(1) NOT NULL -- offset 16
); -- size = 24
Here c6 consists of a four byte length followed by one data byte
(unless the character needs a multibyte representation), the length
has to be aligned on a four byte boundary and the whole row is padded
to a multiple of MAXALIGN, typically four on a 32 bit machine. So we
have three padding bytes before c6 and three padding bytes after c6.
CREATE TABLE bb (
c6 char(1) NOT NULL, -- offset 0
c1 bool NOT NULL, -- offset 5
c3 bool NOT NULL, -- offset 6
c5 bool NOT NULL, -- offset 7
c4 int NOT NULL, -- offset 8
c2 int2 NOT NULL -- offset 12
); -- size = 16
Servus
Manfred
I think I was thinking of how it is passed around internally, the C representation, rather than how it is stored on the disk. These are different things. So, one byte user defined data types are possible. And that means that the *storage* will be one byte (modulo alignment). elein On Sat, Oct 11, 2003 at 03:42:57AM +0200, Manfred Koizar wrote: > On Fri, 10 Oct 2003 16:53:55 -0700, elein <elein@varlena.com> wrote: > >I don't think that you can create a genuine one byte datatype. > >The resulting type would probably be four bytes long, even if > >you create a one byte by-value data type. > > Column values are not *expanded* to multiples of four bytes, they are > *aligned* according to their datatype (cf. pg_type.typalign). > > Not counting heap tuple headers, we get the following offsets and > lengths: > > CREATE TABLE a ( > c1 "char" NOT NULL, -- offset 0 > c2 "char" NOT NULL, -- offset 1 > c3 "char" NOT NULL, -- offset 2 > c4 "char" NOT NULL -- offset 3 > ); -- size = 4 > > CREATE TABLE b ( > c1 bool NOT NULL, -- offset 0 > c2 int2 NOT NULL, -- offset 2 > c3 bool NOT NULL, -- offset 4 > c4 int NOT NULL, -- offset 8 > c5 bool NOT NULL, -- offset 12 > c6 char(1) NOT NULL -- offset 16 > ); -- size = 24 > > Here c6 consists of a four byte length followed by one data byte > (unless the character needs a multibyte representation), the length > has to be aligned on a four byte boundary and the whole row is padded > to a multiple of MAXALIGN, typically four on a 32 bit machine. So we > have three padding bytes before c6 and three padding bytes after c6. > > CREATE TABLE bb ( > c6 char(1) NOT NULL, -- offset 0 > c1 bool NOT NULL, -- offset 5 > c3 bool NOT NULL, -- offset 6 > c5 bool NOT NULL, -- offset 7 > c4 int NOT NULL, -- offset 8 > c2 int2 NOT NULL -- offset 12 > ); -- size = 16 > > Servus > Manfred
elein wrote: >I think I was thinking of how it is passed around internally, >the C representation, rather than how it is stored on the disk. >These are different things. > >So, one byte user defined data types are possible. And that >means that the *storage* will be one byte (modulo alignment). > > The compiler is free to word order them as it pleases, that is why there is the command 'sizeof'. -- "You are behaving like a man", is an insult from some women, a compliment from an good woman.
Sean Chittenden <sean@chittenden.org> writes:
>> If we were going to do that I think we'd be better off making a new
>> type and leaving "char" alone.
> You won't hear any disagreements from me on this one. I've
> sufficiently abused "char" as a 1 byte storage field and would love to
> see an int1 or tinyint datatype added to cover this situation. -sc
That's been discussed before. I think it was shelved until we figure
out a reasonably clean solution to the existing mess with assigning the
most useful datatypes to integer constants (the "you need to cast" set
of problems). Throwing an additional integer type into the stew right
now would just make things worse :-(
regards, tom lane
> >> If we were going to do that I think we'd be better off making a > >> new type and leaving "char" alone. > > > You won't hear any disagreements from me on this one. I've > > sufficiently abused "char" as a 1 byte storage field and would > > love to see an int1 or tinyint datatype added to cover this > > situation. -sc > > That's been discussed before. I think it was shelved until we > figure out a reasonably clean solution to the existing mess with > assigning the most useful datatypes to integer constants (the "you > need to cast" set of problems). Throwing an additional integer type > into the stew right now would just make things worse :-( Hrm, yes and no. It'd make things worse here on the lists in terms of the FAQ for casting/index usage, etc. By the same token, I'd rather have an int1 and cast for the time being, then when a solution does pop into existence, I'll slowly either begin removing the casts or just stop using them in future development. In the meantime, I'll have a formally supported int1 storage type that isn't "char". -sc -- Sean Chittenden
I'm trying to convert a var char to an int. I tried a couple methods described in the documentation, but can't seem to get it to work. Any thoughts? In this example, the field my_id is character varying(16): rs=# insert into table2 rs=# select my_Id::INT rs=# from table1; ERROR: Cannot cast type character to integer rs=# rs=# insert into table2 rs=# select CASE(my_Id as integer) rs=# from table1; ERROR: Cannot cast type character to integer Any help or links to appropriate documentation appreciated! --Rick
-----Original Message----- Oops, there was a typo in my second example. Still have the problem tho... > rs=# > rs=# insert into table2 > rs=# select CAST(my_Id as integer) > ^^^^ > rs=# from table1; > ERROR: Cannot cast type character to integer >
On Tue, 2003-10-14 at 16:11, Rick Seeger wrote:
> -----Original Message-----
>
> Oops, there was a typo in my second example. Still have the problem
> tho...
>
> > rs=#
> > rs=# insert into table2
> > rs=# select CAST(my_Id as integer)
> > ^^^^
> > rs=# from table1;
> > ERROR: Cannot cast type character to integer
Interesting, though, that it works for string constants:
test1=# select cast('15' as integer);
int4
------
15
(1 row)
test1=# select '15'::integer;
int4
------
15
(1 row)
--
-----------------------------------------------------------------
Ron Johnson, Jr. ron.l.johnson@cox.net
Jefferson, LA USA
When Swedes start committing terrorism, I'll become suspicious of
Scandanavians.
Rick Seeger writes: > Oops, there was a typo in my second example. Still have the problem > tho... > > > rs=# > > rs=# insert into table2 > > rs=# select CAST(my_Id as integer) > > ^^^^ > > rs=# from table1; > > ERROR: Cannot cast type character to integer Try the function to_number(). -- Peter Eisentraut peter_e@gmx.net
> >> Oops, there was a typo in my second example. Still have the problem >> tho... >> >> > rs=# >> > rs=# insert into table2 >> > rs=# select CAST(my_Id as integer) >> > ^^^^ >> > rs=# from table1; >> > ERROR: Cannot cast type character to integer > > Try the function to_number(). > rs=# select to_number(my_Id,'9999999999999999') from table1; It worked nicely. Thanks. --Rick