int1?

[CSN]

Re: int1? types?


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 YourEmailAddres sHere" to majordomo@postg resql.org)

[elein]

Re: int1? types?

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.c om

On Fri, Oct 10, 2003 at 11:37:14AM -0700, CSN wrote:[color=blue]
>
> Would you be able to roll your own int1's with types?
>
> http://www.postgresql.org/docs/7.3/i...ve/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 YourEmailAddres sHere" to majordomo@postg resql.org)[/color]

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Joe Conway]

Re: int1? types?

elein wrote:[color=blue]
> 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?[/color]

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 YourEmailAddres sHere" to majordomo@postg resql.org)

[elein]

Re: int1? types?

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:[color=blue]
> elein wrote:[color=green]
> >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?[/color]
>
> See my other post. The type exists and is called "char". See the bottom
> of this page:
> http://www.postgresql.org/docs/view....character.html
>
> Joe
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)[/color]

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

[Joe Conway]

Re: int1? types?

elein wrote:[color=blue]
> 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.[/color]

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




---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Manfred Koizar]

Re: int1? types?

On Fri, 10 Oct 2003 16:53:55 -0700, elein <elein@varlena. com> wrote:[color=blue]
>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.[/color]

Column values are not *expanded* to multiples of four bytes, they are
*aligned* according to their datatype (cf. pg_type.typalig n).

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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[elein]

Re: int1? types?

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:[color=blue]
> On Fri, 10 Oct 2003 16:53:55 -0700, elein <elein@varlena. com> wrote:[color=green]
> >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.[/color]
>
> Column values are not *expanded* to multiples of four bytes, they are
> *aligned* according to their datatype (cf. pg_type.typalig n).
>
> 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[/color]

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Dennis Gearon]

Re: int1? types?

elein wrote:
[color=blue]
>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).
>
>[/color]
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.



---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postg resql.org so that your
message can get through to the mailing list cleanly

[Tom Lane]

Re: int1?

Sean Chittenden <sean@chittende n.org> writes:[color=blue][color=green]
>> If we were going to do that I think we'd be better off making a new
>> type and leaving "char" alone.[/color][/color]
[color=blue]
> 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[/color]

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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Sean Chittenden]

Re: int1?

> >> If we were going to do that I think we'd be better off making a[color=blue][color=green][color=darkred]
> >> new type and leaving "char" alone.[/color][/color]
>[color=green]
> > 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[/color]
>
> 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 :-([/color]

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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Rick Seeger]

Char to Int


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



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Sean Chittenden]

Re: int1?

> >> If we were going to do that I think we'd be better off making a[color=blue][color=green][color=darkred]
> >> new type and leaving "char" alone.[/color][/color]
>[color=green]
> > 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[/color]
>
> 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 :-([/color]

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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Rick Seeger]

Re: Char to Int



-----Original Message-----

Oops, there was a typo in my second example. Still have the problem
tho...
[color=blue]
> rs=#
> rs=# insert into table2
> rs=# select CAST(my_Id as integer)
> ^^^^
> rs=# from table1;
> ERROR: Cannot cast type character to integer
>[/color]



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Ron Johnson]

Re: Char to Int

On Tue, 2003-10-14 at 16:11, Rick Seeger wrote:[color=blue]
> -----Original Message-----
>
> Oops, there was a typo in my second example. Still have the problem
> tho...
>[color=green]
> > rs=#
> > rs=# insert into table2
> > rs=# select CAST(my_Id as integer)
> > ^^^^
> > rs=# from table1;
> > ERROR: Cannot cast type character to integer[/color][/color]

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@c ox.net
Jefferson, LA USA

When Swedes start committing terrorism, I'll become suspicious of
Scandanavians.


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Peter Eisentraut]

Re: Char to Int

Rick Seeger writes:
[color=blue]
> Oops, there was a typo in my second example. Still have the problem
> tho...
>[color=green]
> > rs=#
> > rs=# insert into table2
> > rs=# select CAST(my_Id as integer)
> > ^^^^
> > rs=# from table1;
> > ERROR: Cannot cast type character to integer[/color][/color]

Try the function to_number().

--
Peter Eisentraut peter_e@gmx.net


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html