Re: 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(dot)l(dot)johnson(at)cox(dot)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(dot)l(dot)johnson(at)cox(dot)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(dot)l(dot)johnson(at)cox(dot)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, 2003-10-09 at 14:46, Bruno Wolff III wrote:
> On Thu, Oct 09, 2003 at 14:28:57 -0500,
> Ron Johnson <ron(dot)l(dot)johnson(at)cox(dot)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(dot)l(dot)johnson(at)cox(dot)net
Jefferson, LA USA

Causation does NOT equal correlation !!!!!!!!

On Thu, Oct 09, 2003 at 14:28:57 -0500,
Ron Johnson <ron(dot)l(dot)johnson(at)cox(dot)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.

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(at)gmx(dot)net

On Thu, Oct 09, 2003 at 14:46:08 -0500,
Ron Johnson <ron(dot)l(dot)johnson(at)cox(dot)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(dot)l(dot)johnson(at)cox(dot)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(dot)l(dot)johnson(at)cox(dot)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(dot)l(dot)johnson(at)cox(dot)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(dot)l(dot)johnson(at)cox(dot)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(at)varlena(dot)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(at)postgresql(dot)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(at)postgresql(dot)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(at)varlena(dot)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(at)varlena(dot)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(at)chittenden(dot)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(dot)l(dot)johnson(at)cox(dot)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(at)gmx(dot)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