int1?

[CSN]

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Shridhar Daithankar]

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
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Ron Johnson]

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. ro***********@cox.net
Jefferson, LA USA

"Fear the Penguin!!"
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Sean Chittenden]

> 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...-SPECIAL-TABLE

-sc

--
Sean Chittenden

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

http://archives.postgresql.org

[Ron Johnson]

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...-SPECIAL-TABLE

Wouldn't that be, though, a signed byte? The OP wants unsigned.

--
-----------------------------------------------------------------
Ron Johnson, Jr. ro***********@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.
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Karsten Hilbert]

> http://developer.postgresql.org/docs...-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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Stephan Szabo]

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.
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Sean Chittenden]

> > > 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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Ron Johnson]

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/s...atedomain.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. ro***********@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
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Bruno Wolff III]

On Thu, Oct 09, 2003 at 14:28:57 -0500,
Ron Johnson <ro***********@cox.net> wrote:

http://www.postgresql.org/docs/7.3/s...atedomain.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.

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

[Ron Johnson]

On Thu, 2003-10-09 at 14:46, Bruno Wolff III wrote:

On Thu, Oct 09, 2003 at 14:28:57 -0500,
Ron Johnson <ro***********@cox.net> wrote:

http://www.postgresql.org/docs/7.3/s...atedomain.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. ro***********@cox.net
Jefferson, LA USA

Causation does NOT equal correlation !!!!!!!!
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Peter Eisentraut]

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 pe*****@gmx.net
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Bruno Wolff III]

On Thu, Oct 09, 2003 at 14:46:08 -0500,
Ron Johnson <ro***********@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 <ro***********@cox.net> wrote:

http://www.postgresql.org/docs/7.3/s...atedomain.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.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Ron Johnson]

On Thu, 2003-10-09 at 15:13, Bruno Wolff III wrote:

On Thu, Oct 09, 2003 at 14:46:08 -0500,
Ron Johnson <ro***********@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 <ro***********@cox.net> wrote:
>
> http://www.postgresql.org/docs/7.3/s...atedomain.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. ro***********@cox.net
Jefferson, LA USA

The difference between drunken sailors and Congressmen is that
drunken sailors spend their own money.
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Joe Conway]

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....character.html
near the bottom of the page.

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

[CSN]

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 YourEmailAddressHere" to ma*******@postgresql.org)

[elein]

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
el***@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/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 YourEmailAddressHere" to ma*******@postgresql.org)

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

http://archives.postgresql.org

[Joe Conway]

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....character.html

Joe
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[elein]

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....character.html

Joe
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Joe Conway]

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/cvsw...-cvsweb-markup

Joe


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

http://archives.postgresql.org

[Manfred Koizar]

On Fri, 10 Oct 2003 16:53:55 -0700, elein <el***@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

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

[elein]

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 <el***@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

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

[Dennis Gearon]

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.

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

[Tom Lane]

Sean Chittenden <se**@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

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

[Sean Chittenden]

> >> 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

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

[Rick Seeger]

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]

> >> 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

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

[Rick Seeger]

-----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

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

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

[Ron Johnson]

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. ro***********@cox.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]

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 pe*****@gmx.net
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

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

[Rick Seeger]

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Rick Seeger]

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

[Rick Seeger]

-----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

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

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

[Ron Johnson]

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. ro***********@cox.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]

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 pe*****@gmx.net
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

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

[Rick Seeger]

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)