Re: [SQL] casting character varying to integer - order by numeric

[Bryce Nesbitt (mailing list account)]

Cool, thanks.
PostgreSQL 7.4.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3 
20030502 (Red Hat Linux 3.2.3-20)



Richard Huxton wrote:


SELECT version();



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

Re: [SQL] casting character varying to integer - order by numeric

[Richard Huxton]

Bryce Nesbitt (mailing list account) wrote:

Tom Lane wrote:



As a general rule, you need to be more specific than that about which
version you are working with ;-)



Oooh, I'd be so happy to.  But I don't know.  Yes, I don't know.

I know which version of "psql" is installed on my local machine, but the actual 
database is remote.  Is there a  command to get the server version?  As close as 
I can find is:


stage=> \copyright


SELECT version();

--
  Richard Huxton
  Archonet Ltd

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

Re: [SQL] casting character varying to integer - order by numeric

[Bryce Nesbitt (mailing list account)]

Tom Lane wrote:

  
But postgres 7 rejects this with "ERROR:  cannot cast type character 
varying to integer".

  
  
As a general rule, you need to be more specific than that about which
version you are working with ;-)

You may find that username::text::integer will work, depending on which
7.x this actually is.

			regards, tom lane
  

Oooh, I'd be so happy to.  But I don't know.  Yes, I don't know.

I know which version of "psql" is installed on my local machine, but
the actual database is remote.  Is there a  command to get the server
version?  As close as I can find is:

stage=> \copyright
PostgreSQL Data Base Management System

Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group


             -Bryce

PS: If it's a hint, "select username from eg_member order by
username::text::integer" works fine.  Thanks.

Re: [SQL] casting character varying to integer - order by numeric sort

[Tom Lane]

Bryce W Nesbitt <[EMAIL PROTECTED]> writes:
>   SELECT username,last_name
>   FROM eg_member ORDER BY username::integer;

> But postgres 7 rejects this with "ERROR:  cannot cast type character 
> varying to integer".

As a general rule, you need to be more specific than that about which
version you are working with ;-)

You may find that username::text::integer will work, depending on which
7.x this actually is.

regards, tom lane

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

Re: [SQL] casting character varying to integer - order by numeric

[Terry Fielder]

Check out the function to_number()

In particular here's an example...
If a field named section is text containing numbers:
ORDER BY to_number(t.section, text())

If the field can also contain non-numerals such as 3a, 3b, and you want 
3a to show first then do this:

ORDER BY to_number(t.section, text()), t.section

And if the field section can actually START with an alpha, then to 
prevent to_number from failing do this:

to_number(textcat('0', t.section), text()), t.section

Terry

Bryce W Nesbitt wrote:

How can I force a character field to sort as a numeric field?
I've got something like this:

Postgres=> SELECT username,last_name
 FROM eg_member ORDER BY username;
--+---
0120 | Foley
1| Sullivan
10   | Guest
11   | User
(5 rows)

(I can't change the field type).  I tried:

 SELECT username,last_name
 FROM eg_member ORDER BY username::integer;

But postgres 7 rejects this with "ERROR:  cannot cast type character 
varying to integer".  Is there a way to force numeric sort order?  I 
tried a variety of functions, such as to_char() and convert() without 
any luck.  Thanks for your insight!



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



--
Terry Fielder
[EMAIL PROTECTED]
Associate Director Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
Fax: (416) 441-9085

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

[SQL] casting character varying to integer - order by numeric sort

[Bryce W Nesbitt]

How can I force a character field to sort as a numeric field?
I've got something like this:

Postgres=> SELECT username,last_name
 FROM eg_member ORDER BY username;
--+---
0120 | Foley
1| Sullivan
10   | Guest
11   | User
(5 rows)

(I can't change the field type).  I tried:

 SELECT username,last_name
 FROM eg_member ORDER BY username::integer;

But postgres 7 rejects this with "ERROR:  cannot cast type character 
varying to integer".  Is there a way to force numeric sort order?  I 
tried a variety of functions, such as to_char() and convert() without 
any luck.  Thanks for your insight!



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

Re: [SQL] casting BOOL to somthng

[Stephan Szabo]

On Wed, 1 Sep 2004, Tom Lane wrote:

> Peter Eisentraut <[EMAIL PROTECTED]> writes:
> > The cast to text, however, is part of the data model, and it has to be
> > both natural and universal.  I think you agree that there is no
> > universal, obvious correspondence between character strings and boolean
> > values, at least not nearly as universal and obvious as the well-known
> > correspondence between character strings and numbers.  We could pick
> > one arbitrary correspondence and implement it, and if we did we would
> > probably pick one that is consistent with the mapping used by libpq and
> > other frontends.  But doing that gains no functionality, so why bother?
>
> Actually it does gain functionality, because there are plenty of times
> when you need to manipulate a textual representation of a data value.

I don't think that doing so apart from trying to do output representation
is really a good idea in general (and for that I'd think something like
to_char would be more appropriate). For example, if people are casting
macaddrs to text in order to substring them or some such, that implies
to me that there are additional operations on macaddr we should have.

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

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

Re: [SQL] casting BOOL to somthng

[Tom Lane]

Peter Eisentraut <[EMAIL PROTECTED]> writes:
> The cast to text, however, is part of the data model, and it has to be 
> both natural and universal.  I think you agree that there is no 
> universal, obvious correspondence between character strings and boolean 
> values, at least not nearly as universal and obvious as the well-known 
> correspondence between character strings and numbers.  We could pick 
> one arbitrary correspondence and implement it, and if we did we would 
> probably pick one that is consistent with the mapping used by libpq and 
> other frontends.  But doing that gains no functionality, so why bother?

Actually it does gain functionality, because there are plenty of times
when you need to manipulate a textual representation of a data value.
We have casts to text for many datatypes already:

 castsource  | castcontext
-+-
 character   | i
 character varying   | i
 "char"  | i
 name| i
 bigint  | i
 smallint| i
 integer | i
 oid | i
 real| i
 double precision| i
 macaddr | e
 cidr| e
 inet| e
 date| i
 time without time zone  | i
 timestamp without time zone | i
 timestamp with time zone| i
 interval| i
 time with time zone | i
 numeric | i
(20 rows)

and I think it is reasonable to say that we should have them for all
types.  My only beef with the above table is that most of these casts
should not be implicitly invokable --- I think you should have to write
an explicit CAST.

As for the "which representation" argument, both consistency and
implementation simplicity say that it should be whatever the datatype's
output function delivers.  Indeed it's just a historical accident that
Postgres didn't define the datatype output functions as returning "text"
values in the first place.

regards, tom lane

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

Re: [SQL] casting BOOL to somthng

[Geoffrey]

sad wrote:
On Wednesday 01 September 2004 10:38, Michael Glaesemann wrote:
On Sep 1, 2004, at 2:41 PM, sad wrote:
On Wednesday 01 September 2004 09:24, Stephan Szabo wrote:
There's a fairly accepted convention for integer representations.
There's no such convention for boolean representations.
then why do you print its value on a screen ?!
Perhaps because if you don't print *something* you can't see it?

since you printed it you poke a convention (of casting to string)
if you can print it on screen why not to print it in string?
Simply for the sake of being able to recognize the value.  If it doesn't 
have some value printed, how do you know what the value is? Although 
your example would work (from a previous post), I don't see a real world 
use for such an effort.

There are work arounds that are quite simple.
--
Until later, Geoffrey   Registered Linux User #108567
AT&T Certified UNIX System Programmer - 1995
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[SQL] casting UNKNOWN to REGCLASS

[sad]

> select 't'::text::bool;
> ERROR:  cannot cast type text to boolean
>
> If you're thinking 't'::bool, that's something different.

Ok
i have nothing to opppose

and by the way (!!!)

why TEXT can not be casted to REGCLASS ?


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

   http://archives.postgresql.org

Re: [SQL] casting BOOL to somthng

[Stephan Szabo]

On Wed, 1 Sep 2004, sad wrote:

> > There's a difference between an output function and a cast to text.
> > One gives you an external representation of the data for end use.  The
> > other gives you an internal representation for manipulation.
>
> And at the same time
>
> 't'::TEXT can be casted to BOOL
> 't'::BOOL
>
> but reverse.

select 't'::text::bool;
ERROR:  cannot cast type text to boolean

If you're thinking 't'::bool, that's something different.

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

Re: [SQL] casting BOOL to somthng

[sad]

> There's a difference between an output function and a cast to text.
> One gives you an external representation of the data for end use.  The
> other gives you an internal representation for manipulation.

And at the same time

't'::TEXT can be casted to BOOL
't'::BOOL

but reverse.


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

Re: [SQL] casting BOOL to somthng

[Stephan Szabo]

On Wed, 1 Sep 2004, sad wrote:

> On Wednesday 01 September 2004 09:24, Stephan Szabo wrote:
> > On Wed, 1 Sep 2004, sad wrote:
> > > On Tuesday 31 August 2004 17:49, Michael Glaesemann wrote:
> > > > On Aug 31, 2004, at 8:24 PM, sad wrote:
> > > > > and i am still desire to know _WHY_ there are no predefined cast for
> > > > > BOOL ?
> > > > > and at the same time there are predefined casts for INT and
> > > > > FLOAT..
> > > >
> > > > I think the main reason is what is the proper textual representation of
> > > > BOOLEAN? True, PostgreSQL returns 't' as a representation for the
> > > > BOOLEAN value TRUE, but some people might want it to return 'TRUE' or
> > > > 'true' or other representations. Picking one is perhaps arbitrary.
> > >
> > > There are many (infinite number) of INT representations,
> > > "Picking one is perhaps arbitrary." But you poke one and using it.
> >
> > There's a fairly accepted convention for integer representations.
> > There's no such convention for boolean representations.
>
> then why do you print its value on a screen ?!

There's a difference between an output function and a cast to text.
One gives you an external representation of the data for end use.  The
other gives you an internal representation for manipulation.

Just because true is displayed in psql as 't' and false is displayed in
psql as 'f' does not mean that an expression like (true::text ||
false::text) has any conventional meaning let alone 'tf'.

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

Re: [SQL] casting BOOL to somthng

[Michael Glaesemann]

On Sep 1, 2004, at 2:55 PM, sad wrote:
On Wednesday 01 September 2004 10:38, Michael Glaesemann wrote:
On Sep 1, 2004, at 2:41 PM, sad wrote:
On Wednesday 01 September 2004 09:24, Stephan Szabo wrote:
There's a fairly accepted convention for integer representations.
There's no such convention for boolean representations.
then why do you print its value on a screen ?!
Perhaps because if you don't print *something* you can't see it?
since you printed it you poke a convention (of casting to string)
OT: 'pick' or 'choose'. 'Poke' means something else entirely.
if you can print it on screen why not to print it in string?
I can see your point, but I think that representing the value and 
casting the value to another type are two different things. Given, as 
Stephen pointed out, that there is no standard convention for 
representing BOOLEAN values, whatever the choice is is not going to 
satisfy nearly anyone. For me, I would *not* want TRUE to be 
represented as 't', nor would I want to have to set up a separate cast 
(or formating function, or CASE statement) to make it print 'TRUE'. 
Others, I'm sure, would rather see it as 'true' (lowercase). Why should 
they be penalized to suit me?

I've seen very few people ask for a cast from BOOLEAN to TEXT. Given 
the apparently limited number of people who desire it, and the various 
ways BOOLEAN may be represented as text, I think it's much better to 
leave it up to the individual user to define their own cast to do so, 
and PostgreSQL provides an easy method to do so.

Michael Glaesemann
grzm myrealbox com
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly

Re: [SQL] casting BOOL to somthng

[Peter Eisentraut]

sad wrote:
> since you printed it you poke a convention (of casting to string)
>
> if you can print it on screen why not to print it in string?

Allow me an attempt at a philosophical explanation:

The external representation to the API is arbitrary, because it's part 
of the API specification, and it varies.  If you use libpq, you get a 
character 't' or 'f', if you use ECPG you get a C bool (int) datum, if 
you use JDBC, you get a Java bool value, etc.  psql uses libpq, so you 
see 't' or 'f'.  MS Access maybe uses ODBC and you might see a checkbox 
or something.  It's part of the interface definition.

The cast to text, however, is part of the data model, and it has to be 
both natural and universal.  I think you agree that there is no 
universal, obvious correspondence between character strings and boolean 
values, at least not nearly as universal and obvious as the well-known 
correspondence between character strings and numbers.  We could pick 
one arbitrary correspondence and implement it, and if we did we would 
probably pick one that is consistent with the mapping used by libpq and 
other frontends.  But doing that gains no functionality, so why bother?

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/


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

   http://archives.postgresql.org

Re: [SQL] casting BOOL to somthng

[sad]

On Wednesday 01 September 2004 10:38, Michael Glaesemann wrote:
> On Sep 1, 2004, at 2:41 PM, sad wrote:
> > On Wednesday 01 September 2004 09:24, Stephan Szabo wrote:
> >> There's a fairly accepted convention for integer representations.
> >> There's no such convention for boolean representations.
> >
> > then why do you print its value on a screen ?!
>
> Perhaps because if you don't print *something* you can't see it?

since you printed it you poke a convention (of casting to string)

if you can print it on screen why not to print it in string?


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [SQL] casting BOOL to somthng

[Michael Glaesemann]

On Sep 1, 2004, at 2:41 PM, sad wrote:
On Wednesday 01 September 2004 09:24, Stephan Szabo wrote:
There's a fairly accepted convention for integer representations.
There's no such convention for boolean representations.
then why do you print its value on a screen ?!
Perhaps because if you don't print *something* you can't see it?
Michael Glaesemann
grzm myrealbox com
---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [SQL] casting BOOL to somthng

[sad]

On Wednesday 01 September 2004 09:24, Stephan Szabo wrote:
> On Wed, 1 Sep 2004, sad wrote:
> > On Tuesday 31 August 2004 17:49, Michael Glaesemann wrote:
> > > On Aug 31, 2004, at 8:24 PM, sad wrote:
> > > > and i am still desire to know _WHY_ there are no predefined cast for
> > > > BOOL ?
> > > > and at the same time there are predefined casts for INT and
> > > > FLOAT..
> > >
> > > I think the main reason is what is the proper textual representation of
> > > BOOLEAN? True, PostgreSQL returns 't' as a representation for the
> > > BOOLEAN value TRUE, but some people might want it to return 'TRUE' or
> > > 'true' or other representations. Picking one is perhaps arbitrary.
> >
> > There are many (infinite number) of INT representations,
> > "Picking one is perhaps arbitrary." But you poke one and using it.
>
> There's a fairly accepted convention for integer representations.
> There's no such convention for boolean representations.

then why do you print its value on a screen ?!



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

Re: [SQL] casting BOOL to somthng

[Stephan Szabo]

On Wed, 1 Sep 2004, sad wrote:

> On Tuesday 31 August 2004 17:49, Michael Glaesemann wrote:
> > On Aug 31, 2004, at 8:24 PM, sad wrote:
> > > and i am still desire to know _WHY_ there are no predefined cast for
> > > BOOL ?
> > > and at the same time there are predefined casts for INT and FLOAT..
> >
> > I think the main reason is what is the proper textual representation of
> > BOOLEAN? True, PostgreSQL returns 't' as a representation for the
> > BOOLEAN value TRUE, but some people might want it to return 'TRUE' or
> > 'true' or other representations. Picking one is perhaps arbitrary.
>
> There are many (infinite number) of INT representations,
> "Picking one is perhaps arbitrary." But you poke one and using it.

There's a fairly accepted convention for integer representations.
There's no such convention for boolean representations.


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [SQL] casting BOOL to somthng

[sad]

On Tuesday 31 August 2004 17:49, Michael Glaesemann wrote:
> On Aug 31, 2004, at 8:24 PM, sad wrote:
> > and i am still desire to know _WHY_ there are no predefined cast for
> > BOOL ?
> > and at the same time there are predefined casts for INT and FLOAT..
>
> I think the main reason is what is the proper textual representation of
> BOOLEAN? True, PostgreSQL returns 't' as a representation for the
> BOOLEAN value TRUE, but some people might want it to return 'TRUE' or
> 'true' or other representations. Picking one is perhaps arbitrary.

There are many (infinite number) of INT representations,
"Picking one is perhaps arbitrary." But you poke one and using it.

If some one wants another representation you ask him do define his own 
function and use it instead of cast. And you are right. In your system 
integers textully represented as you define. Just define one representation 
for boolean and leave the rest for user definition.


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

Re: [SQL] casting BOOL to somthng

[sad]

On Tuesday 31 August 2004 16:22, Geoffrey wrote:
> sad wrote:
> > you wrote:
> >>you can use CREATE CAST to make your own cast from boolean to text.
> >
> > thnx it helps.
> >
> > and i am still desire to know _WHY_ there are no predefined cast for BOOL
> > ? and at the same time there are predefined casts for INT and FLOAT..
>
> I'd like to understand in what context you would find this useful.
> Don't take me wrong please.  I'm by no means a db expert, but I can't
> see a purpose for such a cast.  Can you provide a reasonable example of
> such usage?

Yes i can. 
look:

CREATE TABLE t (a int, b text, c bool);

SELECT 'the row is: a='||a::TEXT||' b='||b||' c='||c::TEXT FROM t;


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

Re: [SQL] casting BOOL to somthng

[Michael Glaesemann]

On Aug 31, 2004, at 8:24 PM, sad wrote:
and i am still desire to know _WHY_ there are no predefined cast for 
BOOL ?
and at the same time there are predefined casts for INT and FLOAT..
I think the main reason is what is the proper textual representation of 
BOOLEAN? True, PostgreSQL returns 't' as a representation for the 
BOOLEAN value TRUE, but some people might want it to return 'TRUE' or 
'true' or other representations. Picking one is perhaps arbitrary.

In a similar vein, some people would like to cast BOOLEAN to INTEGER, 
as often BOOLEAN TRUE and BOOLEAN FALSE are represented as INTEGER 1 
and INTEGER 0, respectively, in some systems. However, other systems 
use different INTEGER representations such as INTEGER 1 and INTEGER -1 
for BOOLEAN TRUE and FALSE, respectively. Again, the choice of how to 
cast BOOLEAN to INTEGER is kind of arbitrary.

Luckily PostgreSQL provides convenient ways of making user-defined 
casts.

Just my thoughts.
Michael Glaesemann
grzm myrealbox com
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly

Re: [SQL] casting BOOL to somthng

[Geoffrey]

sad wrote:
you wrote:

you can use CREATE CAST to make your own cast from boolean to text.

thnx it helps.
and i am still desire to know _WHY_ there are no predefined cast for BOOL ?
and at the same time there are predefined casts for INT and FLOAT..
I'd like to understand in what context you would find this useful. 
Don't take me wrong please.  I'm by no means a db expert, but I can't 
see a purpose for such a cast.  Can you provide a reasonable example of 
such usage?

Thanks.
--
Until later, Geoffrey   Registered Linux User #108567
AT&T Certified UNIX System Programmer - 1995
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] casting BOOL to somthng

[sad]

you wrote:

> you can use CREATE CAST to make your own cast from boolean to text.

thnx it helps.

and i am still desire to know _WHY_ there are no predefined cast for BOOL ?
and at the same time there are predefined casts for INT and FLOAT.. 


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

Re: [SQL] casting BOOL to somthng

[Michael Glaesemann]

On Aug 31, 2004, at 6:06 PM, sad wrote:
why BOOL can not be casted to TEXT
nevertheless BOOL has a textual (output) representation 't' and 
'f' letters
why not to use this fact to define cast to TEXT ?
I'm not sure of the reason why there isn't a built-in cast from boolean 
to text, though I'm not a big fan of casts in general so it doesn't 
really bother me too much. If this is something you desire, I believe 
you can use CREATE CAST to make your own cast from boolean to text.

test=# select true::text;
ERROR:  cannot cast type boolean to text
create or replace function bool_to_text (boolean)
returns text
strict
language sql as '
select case
when $1 then \'t\'
else \'f\'
end;
';
create cast (boolean as text)
with function bool_to_text(boolean)
as assignment;
test=# select true::text;
 text
--
 t
(1 row)
You can find more information at

Hope this helps!
Michael Glaesemann
grzm myrealbox com
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

[SQL] casting BOOL to somthng

[sad]

hello

why BOOL can not be casted to TEXT
...nevertheless BOOL has a textual (output) representation 't' and 'f' letters
why not to use this fact to define cast to TEXT ?


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

[SQL] casting int to bit

[sad]

PLEASE NOTE :

select 1::int8::bit(64);
0001

select 1::int4::bit(64);
0001

select 1::int2::bit(64);
ERROR:  Cannot cast type smallint to bit

the last is a great surprise for me !


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [SQL] casting to arrays

[Greg Stark]

Joe Conway <[EMAIL PROTECTED]> writes:

> Not possible in current releases, but it will be in 7.4 (about to start beta).
> It looks like this:

Well there is the int_array_aggregate function in the contrib/intagg
directory. It has to be compiled separately, and it has a few quirks (like the
arrays are zero-based instead of 1-based) but it works more or less, and it
does exactly what you describe.

But the 7.4 stuff should be much cleaner and more flexible, so if you don't
need it right now you're better off waiting.

-- 
greg


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

Re: [SQL] casting to arrays

[Joe Conway]

Mike Rylander wrote:
Thank you!  This is great news.  Is there a projected release date for 7.4?
Not exactly an officially projected date, but in the past IIRC beta/RC 
has lasted 2 to 3 months, so I'd start looking for a 7.4 release in October.

Also, is there a published roadmap, or should I just get on the developers 
list?
The closest thing is the TODO list:
  http://developer.postgresql.org/todo.php
But if you want to closely monitor the work actually getting done, 
subscribe to the HACKERS list.

Joe

---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [SQL] casting to arrays

[Mike Rylander]

Thank you!  This is great news.  Is there a projected release date for 7.4?  
Also, is there a published roadmap, or should I just get on the developers 
list?

Thanks again.
---
Mike Rylander

On Friday 18 July 2003 05:34 pm, Joe Conway wrote:
> Mike Rylander wrote:
> > I have a rather odd table structure that I would like to simplify to be a
> > view (for some definition of simplify).  The current idea I have is to
> > shovel values from multiple rows in one table into an array in the view. 
> > The tables look something like this:
>
> 
>
> > Is anything like this possible?  I know this may not be good form, but
> > unfortunately (or perhaps fortunately, since it means I have a job) there
> > are business reasons for this, supporting old apps and such.
>
> Not possible in current releases, but it will be in 7.4 (about to start
> beta). It looks like this:
>
> create table person (id  integer, name  varchar);
> insert into person values(1,'Bob');
> insert into person values(2,'Sue');
>
> create table stuff (person_id integer, stuff_name text);
> insert into stuff values(1,'chair');
> insert into stuff values(1,'couch');
> insert into stuff values(1,'lamp');
> insert into stuff values(2,'table');
> insert into stuff values(2,'shirt');
>
> create or replace view person_with_stuff as select p.id as id, p.name as
> name, ARRAY(select s.stuff_name from stuff s where s.person_id = p.id)
> as stuff from person p;
>
> regression=# select * from person_with_stuff;
>   id | name |   stuff
> +--+
>1 | Bob  | {chair,couch,lamp}
>2 | Sue  | {table,shirt}
> (2 rows)
>
> HTH,
>
> Joe


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] casting to arrays

[Joe Conway]

Mike Rylander wrote:
I have a rather odd table structure that I would like to simplify to be a view 
(for some definition of simplify).  The current idea I have is to shovel 
values from multiple rows in one table into an array in the view.  The tables 
look something like this:


Is anything like this possible?  I know this may not be good form, but 
unfortunately (or perhaps fortunately, since it means I have a job) there are 
business reasons for this, supporting old apps and such.

Not possible in current releases, but it will be in 7.4 (about to start 
beta). It looks like this:

create table person (id  integer, name  varchar);
insert into person values(1,'Bob');
insert into person values(2,'Sue');
create table stuff (person_id integer, stuff_name text);
insert into stuff values(1,'chair');
insert into stuff values(1,'couch');
insert into stuff values(1,'lamp');
insert into stuff values(2,'table');
insert into stuff values(2,'shirt');
create or replace view person_with_stuff as select p.id as id, p.name as 
name, ARRAY(select s.stuff_name from stuff s where s.person_id = p.id) 
as stuff from person p;

regression=# select * from person_with_stuff;
 id | name |   stuff
+--+
  1 | Bob  | {chair,couch,lamp}
  2 | Sue  | {table,shirt}
(2 rows)
HTH,

Joe

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html

[SQL] casting to arrays

[Mike Rylander]

I have a rather odd table structure that I would like to simplify to be a view 
(for some definition of simplify).  The current idea I have is to shovel 
values from multiple rows in one table into an array in the view.  The tables 
look something like this:

create table person (
  id  serial,
  name  varchar
);

create table stuff (
  person_id references person (id) on delete restrict,
  stuff_name varchar
);

The view would go something like:

create view person_with_stuff as
  select p.id as id,
   p.name as name,
   ( select s.stuff_name
 from stuff
 where s.person_id = p.id
   )::varchar[] from person p;

Is anything like this possible?  I know this may not be good form, but 
unfortunately (or perhaps fortunately, since it means I have a job) there are 
business reasons for this, supporting old apps and such.

Thanks in advance!
-- 
Mike Rylander

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

Re: [SQL] casting interval to time

[Tom Lane]

Antti Haapala <[EMAIL PROTECTED]> writes:
> It's also Debian (3.0).

On investigation the interval_time() function was completely broken for
the --enable-integer-datetimes case --- it was reducing the interval
value modulo one second, rather than modulo one day as intended.
I also noticed that neither the integer nor float case behaved
rationally for negative intervals.  I've applied the attached patch
to 7.3 and HEAD ...

regards, tom lane

*** src/backend/utils/adt/date.c.orig   Thu Feb 13 12:04:24 2003
--- src/backend/utils/adt/date.cMon Jun 16 14:56:53 2003
***
*** 999,1004 
--- 999,1009 
  
  /* interval_time()
   * Convert interval to time data type.
+  *
+  * This is defined as producing the fractional-day portion of the interval.
+  * Therefore, we can just ignore the months field.  It is not real clear
+  * what to do with negative intervals, but we choose to subtract the floor,
+  * so that, say, '-2 hours' becomes '22:00:00'.
   */
  Datum
  interval_time(PG_FUNCTION_ARGS)
***
*** 1007,1021 
TimeADT result;
  
  #ifdef HAVE_INT64_TIMESTAMP
result = span->time;
!   if ((result >= INT64CONST(864))
!   || (result <= INT64CONST(-864)))
!   result -= (result / INT64CONST(100) * INT64CONST(100));
  #else
-   Intervalspan1;
- 
result = span->time;
!   TMODULO(result, span1.time, 86400e0);
  #endif
  
PG_RETURN_TIMEADT(result);
--- 1012,1034 
TimeADT result;
  
  #ifdef HAVE_INT64_TIMESTAMP
+   int64   days;
+ 
result = span->time;
!   if (result >= INT64CONST(864))
!   {
!   days = result / INT64CONST(864);
!   result -= days * INT64CONST(864);
!   }
!   else if (result < 0)
!   {
!   days = (-result + INT64CONST(864-1)) / INT64CONST(864);
!   result += days * INT64CONST(864);
!   }
  #else
result = span->time;
!   if (result >= 86400e0 || result < 0)
!   result -= floor(result / 86400e0) * 86400e0;
  #endif
  
PG_RETURN_TIMEADT(result);

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [SQL] casting interval to time

[Tom Lane]

Antti Haapala <[EMAIL PROTECTED]> writes:
> '--enable-integer-datetimes' (could be this?)

Bingo.  I can reproduce it with that configure choice.

Should have the answer soon ...

regards, tom lane

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

Re: [SQL] casting interval to time

[Tomasz Myrta]

Dnia 2003-06-16 17:17, Użytkownik Tom Lane napisał:

What do you get from "pg_config --configure"?  What's the hardware
platform --- i386, or something else?  Can anyone else reproduce this,
on any platform?
The platform is i386.

There was no "pg_config" file in binary package. After copying this file 
from source tgz, I get only one line result:
@configure@

Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] casting interval to time

[Antti Haapala]

On Mon, 16 Jun 2003, Tom Lane wrote:

> Tomasz Myrta <[EMAIL PROTECTED]> writes:
> > Dnia 2003-06-16 16:53, U¿ytkownik Tom Lane napisa³:
> > > Tomasz Myrta <[EMAIL PROTECTED]> writes:
> > >
> > > > psql (PostgreSQL) 7.3.2
> > > > SELECT cast(cast('1 day 12 hours' as interval) as time);
> > > > time
> > > > --
> > > > 00:00:00
> > > > (1 row)
> > >
> > >
> > > I get 12:00:00 here, using 7.3.3 ... platform-specific problem maybe?
>
> > Default Debian package. I got the same result even on a 7.3.3 Debian
> > package.
>
> What do you get from "pg_config --configure"?  What's the hardware
> platform --- i386, or something else?  Can anyone else reproduce this,
> on any platform?

template1=# select cast('25 hours'::interval as time);
   time
--
 00:00:00
(1 row)

template1=# select version();
version

 PostgreSQL 7.3.2 on i386-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
(1 row)

It's also Debian (3.0).

% pg_config --configure '--host=i386-linux' '--build=i386-linux'
'--prefix=/usr' '--mandir=/usr/share/man' '--docdir=/usr/share/doc'
'--bindir=/usr/lib/postgresql/bin' '--libdir=/usr/lib'
'--includedir=/usr/include/postgresql' '--enable-recode' '--enable-nls'

'--enable-integer-datetimes' (could be this?)

'--enable-debug'
'--disable-rpath' '--with-tcl' '--with-perl' '--with-python' '--with-pam'
'--with-openssl' '--with-gnu-ld' '--with-krb5'
'--with-tclconfig=/usr/lib/tcl8.4' '--with-tkconfig=/usr/lib/tk8.4'
'--with-includes=/usr/include/tcl8.4' '--with-maxbackends=64'
'--with-pgport=5432'
'DOCBOOKSTYLE=/usr/share/sgml/docbook/stylesheet/dsssl/modular'
'build_alias=i386-linux' 'host_alias=i386-linux'

---

Not affected: GentooLinux 1.4 rc2 x86 w/ pg 7.3, gcc 3.2.1, glibc 2.3.1 w/
following configure options:

'--prefix=/usr' '--mandir=/usr/share/man' '--host=i586-pc-linux-gnu'
'--docdir=/usr/share/doc/postgresql-7.3' '--libdir=/usr/lib'
'--enable-syslog' '--enable-depend' '--with-gnu-ld' '--with-pam'
'--with-maxbackends=1024' '--with-python' '--with-perl' '--with-java'
'--with-openssl' '--enable-locale' '--enable-nls' '--enable-multibyte'
'--with-CXX' 'CC=gcc' 'CFLAGS=-march=pentium-mmx -O3 -pipe'
'host_alias=i586-pc-linux-gnu'



-- 
Antti Haapala

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] casting interval to time

[Tom Lane]

Tomasz Myrta <[EMAIL PROTECTED]> writes:
> Dnia 2003-06-16 16:53, U¿ytkownik Tom Lane napisa³:
>> Tomasz Myrta <[EMAIL PROTECTED]> writes:
>> 
>>> psql (PostgreSQL) 7.3.2
>>> SELECT cast(cast('1 day 12 hours' as interval) as time);
>>> time
>>> --
>>> 00:00:00
>>> (1 row)
>> 
>> 
>> I get 12:00:00 here, using 7.3.3 ... platform-specific problem maybe?

> Default Debian package. I got the same result even on a 7.3.3 Debian 
> package.

What do you get from "pg_config --configure"?  What's the hardware
platform --- i386, or something else?  Can anyone else reproduce this,
on any platform?

regards, tom lane

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

Re: [SQL] casting interval to time

[Tomasz Myrta]

Dnia 2003-06-16 16:53, Użytkownik Tom Lane napisał:
Tomasz Myrta <[EMAIL PROTECTED]> writes:

psql (PostgreSQL) 7.3.2
SELECT cast(cast('1 day 12 hours' as interval) as time);
  time
--
 00:00:00
(1 row)


I get 12:00:00 here, using 7.3.3 ... platform-specific problem maybe?
Default Debian package. I got the same result even on a 7.3.3 Debian 
package.

postgresql.conf contains:
DATESTYLE = 'iso,european'
LC_MESSAGES = 'C'
LC_MONETARY = 'C'
LC_NUMERIC = 'C'
LC_TIME = 'C'
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] casting interval to time

[Tom Lane]

Tomasz Myrta <[EMAIL PROTECTED]> writes:
> psql (PostgreSQL) 7.3.2
> SELECT cast(cast('1 day 12 hours' as interval) as time);
>time
> --
>   00:00:00
> (1 row)

I get 12:00:00 here, using 7.3.3 ... platform-specific problem maybe?

regards, tom lane

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] casting interval to time

[Tomasz Myrta]

Hi
psql -V
psql (PostgreSQL) 7.3
SELECT cast(cast('1 day 12 hours' as interval) as time);
   time
--
 12:00:00
(1 row)
psql -V
psql (PostgreSQL) 7.3.2
SELECT cast(cast('1 day 12 hours' as interval) as time);
  time
--
 00:00:00
(1 row)
Did I miss something? I looked into 'history' file, but I couldn't find 
anything interesting about changes in casting interval into time.

I found replacement for this problem, but I wan't just to know what 
happened:
('1970-1-1'::date+some_interval)::time

Regards,
Tomasz Myrta


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

Re: [SQL] Casting with character and character varying

[Christoph Haller]

>
> Hi all.
> Recently I face some problem with casting character type variable and
> varchar variable.
> The situation was like: I had 2 table, on table A, the user_name is
defined
> as character(32), and table B uses varchar(32). I have 1 function and
a
> trigger to manipulate with these data.
>
> Here's the function: (NEW = tableB)
> --
> create or replace function prepaid () returns trigger as '
>   declare Rec tableA%ROWTYPE;
>
> begin
>   if NEW.status != 2 then
>return NEW;
>   else
>   select into Rec * from tableA where user_name = trim(trailing '' ''
from
> cast(NEW.user_name as varchar)) and user_type = ''T'';
>if not found then
> return NEW;
>end if;
>
>insert into temptable values (tableA.FieldA);
>   end if;
>   return NEW;
> end;
> ' language 'plpgsql';
> -
> supposingly the insert will insert the value of field A in table into
> temptable (declare as varchar(100)), instead of inserting single row,
the
> insert actually insert all data from tableA to temptable (if there's
10 row
> in tableA, the insert statement will insert all to temptable), that's
weird.
>
> Then i tried with cast(trim(trailing '' '' from
NEW.user_name)::varchar as
> text), and it's returns me with nothing (suppose there'll be 1 record
> matched).
>
Don't know what's actually right now:
If tableA uses character and tableB varchar, you'll have to trim the
user_name from tableA not tableB, because varchar is already trimmed.
But what you're doing within the function code is trimming a varchar
field.
Second is, what is "tableA.FieldA"? Is it a column name of tableA?
Looks that way, because I can't see a variable of this name.
I'm not sure what happens on an insert statement like this, but it's
very well
possible this causes all row-columns FieldA from tableA to be inserted
into
temptable. And it would be useful to see the CREATE TRIGGER statement
too.

Regards, Christoph



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

http://archives.postgresql.org

[SQL] Casting with character and character varying

[David Loh]

Hi all.
Recently I face some problem with casting character type variable and
varchar variable.
The situation was like: I had 2 table, on table A, the user_name is defined
as character(32), and table B uses varchar(32). I have 1 function and a
trigger to manipulate with these data.

Here's the function: (NEW = tableB)
--
create or replace function prepaid () returns trigger as '
  declare Rec tableA%ROWTYPE;

begin
  if NEW.status != 2 then
   return NEW;
  else
  select into Rec * from tableA where user_name = trim(trailing '' '' from
cast(NEW.user_name as varchar)) and user_type = ''T'';
   if not found then
return NEW;
   end if;

   insert into temptable values (tableA.FieldA);
  end if;
  return NEW;
end;
' language 'plpgsql';
-
supposingly the insert will insert the value of field A in table into
temptable (declare as varchar(100)), instead of inserting single row, the
insert actually insert all data from tableA to temptable (if there's 10 row
in tableA, the insert statement will insert all to temptable), that's weird.

Then i tried with cast(trim(trailing '' '' from NEW.user_name)::varchar as
text), and it's returns me with nothing (suppose there'll be 1 record
matched).

If any of you guys willing to help me out, I'll apprepriate it. Or you may
point me to some postgresql casting tutorial.

Thanks.


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

Re: [SQL] Casting Money To Numeric

[D'Arcy J.M. Cain]

On November 26, 2002 02:19 pm, Thomas Good wrote:
> Having perused all the online docs I can find it appears there is no
> SQL solution for casting the dread money type to numeric.
> Is this true?
>
> select rent::numeric(9,2) from x;
> ERROR: Cannot cast type 'money' to 'numeric'

Fraid so.  That's one of the reasons that I had to finally leave that type 
for numeric.  Be prepared, however, for SUM() to take longer on groups of any 
significant size.  That's the one thing that was really nice about money - 
everything was integer arithmetic.

-- 
D'Arcy J.M. Cain|  Democracy is three wolves
http://www.druid.net/darcy/|  and a sheep voting on
+1 416 425 1212 (DoD#0082)(eNTP)   |  what's for dinner.

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

[SQL] Casting Money To Numeric

[Thomas Good]

Hi All.

Having perused all the online docs I can find it appears there is no
SQL solution for casting the dread money type to numeric.
Is this true?

select rent::numeric(9,2) from x;
ERROR: Cannot cast type 'money' to 'numeric'

I guess my cash ain't nothing but trash...  ;-)

TIA!
---
Thomas Good  e-mail: [EMAIL PROTECTED]
Programmer/Analyst   phone:   (+1) 718.818.5528
Residential Services fax: (+1) 718.818.5056
Behavioral Health Services, SVCMC-NY mobile:  (+1) 917.282.7359

--Geistiges Eigentum ist Diebstahl!  --


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

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [SQL] Casting integer to boolean

[Christopher Kings-Lynne]

> select not count(*) = 0 from my_table;
> 
> Basically, for any integer i, convert to boolean with:  not i = 0

Or i != 0 of course...

Chris


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] Casting integer to boolean

[Tod McQuillin]

On Fri, 16 Aug 2002, Bhuvan A wrote:

> How do i cast an integer value to boolean?

You can always do something like this:

select not count(*) = 0 from my_table;

Basically, for any integer i, convert to boolean with:  not i = 0
-- 
Tod McQuillin


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] Casting integer to boolean

[Bhuvan A]

Hi,

I am using postgresql 7.2.1.

How do i cast an integer value to boolean? I did try the below sequence of
SQLs and was little bit confused, by the way it behaves. It casts the
integer value to boolean in one case but not ever again.

bhuvan=> SELECT count(*)::int::boolean from my_table;
ERROR:  Cannot cast type 'integer' to 'boolean'
bhuvan=> -- The SQL similar to the above SQL is my requirement
bhuvan=> SELECT true where (1);
ERROR:  WHERE clause must return type boolean, not type integer
bhuvan=> SELECT true where (1::boolean);
 bool
--
 t
(1 row)

bhuvan=> SELECT true where (1::int::boolean);
ERROR:  Cannot cast type 'integer' to 'boolean'
bhuvan=>

I donot know whether i am wrong or its a bug. I request someone to correct
me if i am wrong or please suggest me the right way to cast an integer to
boolean as, returning true for non-zero value, false otherwise.

regards, 
bhuvaneswaran



---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] Casting numeric to text

[Ross J. Reedstrom]

On Mon, Apr 09, 2001 at 06:53:13PM +0200, Peter Eisentraut wrote:
> Ross J. Reedstrom writes:
> 
> > FYI, I can't find an occurance of '::' that's not part of '::=' in either
> > SQL1992.txt or the ansi-iso-[sql]-1999.txt files I've got.
> 
> SQL 1999 6.12
> 
>  ::=
> [  ]
> 

Ah, right: My grep excluded the ::= cases, so I missed:

 ::= ::

> That syntax even makes sense...

No, how could it be? Someone must have failed to run the Babelizer on that part.

Ross

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

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [SQL] Casting numeric to text

[Peter Eisentraut]

Ross J. Reedstrom writes:

> FYI, I can't find an occurance of '::' that's not part of '::=' in either
> SQL1992.txt or the ansi-iso-[sql]-1999.txt files I've got.

SQL 1999 6.12

 ::=
[  ]

That syntax even makes sense...

-- 
Peter Eisentraut  [EMAIL PROTECTED]   http://yi.org/peter-e/


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

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [SQL] Casting numeric to text

[Ross J. Reedstrom]

On Mon, Apr 09, 2001 at 05:57:45PM +0200, Peter Eisentraut wrote:
> Cedar Cox writes:
> 
> > When would one want to use cast()?  What is the difference between cast
> > and :: ?  After a quick look in the documentation I couldn't find
> > anything..
> 
> cast() is SQL, :: is traditional Postgres.  :: may go away in the distant
> future to make room for the SQL feature that is supposed to use ::.  (It
> escapes me at the moment what that was.)


FYI, I can't find an occurance of '::' that's not part of '::=' in either
SQL1992.txt or the ansi-iso-[sql]-1999.txt files I've got.

But Peter's answer's still correct: CAST(  AS  ) is SQL
standard, '::' is not.

Ross

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] Casting numeric to text

[Peter Eisentraut]

Cedar Cox writes:

> When would one want to use cast()?  What is the difference between cast
> and :: ?  After a quick look in the documentation I couldn't find
> anything..

cast() is SQL, :: is traditional Postgres.  :: may go away in the distant
future to make room for the SQL feature that is supposed to use ::.  (It
escapes me at the moment what that was.)

-- 
Peter Eisentraut  [EMAIL PROTECTED]   http://yi.org/peter-e/


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] Casting numeric to text

[Cedar Cox]

On Sun, 8 Apr 2001, Peter Eisentraut wrote:

> Hans-Jürgen Schönig writes:
> 
> > Is there any possibility to cast numeric to text in Postgres 7.0.3?
> >
> > shop=# select cast(price as text) from products;
> > ERROR:  Cannot cast type 'numeric' to 'text'
> 
> Use the to_char() function.

When would one want to use cast()?  What is the difference between cast
and :: ?  After a quick look in the documentation I couldn't find
anything..

-Cedar


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

Re: [SQL] Casting numeric to text

[Peter Eisentraut]

Hans-Jürgen Schönig writes:

> Is there any possibility to cast numeric to text in Postgres 7.0.3?
>
> shop=# select cast(price as text) from products;
> ERROR:  Cannot cast type 'numeric' to 'text'

Use the to_char() function.

-- 
Peter Eisentraut  [EMAIL PROTECTED]   http://yi.org/peter-e/


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] Casting numeric to text

[Hans-Jürgen Schönig]

Is there any possibility to cast numeric to text in Postgres 7.0.3?

shop=# select cast(price as text) from products;
ERROR:  Cannot cast type 'numeric' to 'text'

Hans


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

Re: [SQL] Casting

[Peter Eisentraut]

Thomas SMETS writes:

> I'm removing charaters from a String which should be numbers.
> I then want to make calculations on these numbers (calculate the ISBN
> number).

(You might want to look into contrib/isbn_issn for an isbn type.)

> Do I have to cast the char into int's before I can do the calulations.

Depends on the calculation.  I'd just try to see if it works.  When in
doubt add casts.

> Also I looked in the User manual but could not find the modulo function
> where is it ?

5 % 4
mod(5, 4)

-- 
Peter Eisentraut  [EMAIL PROTECTED]   http://yi.org/peter-e/

[SQL] Casting

[Thomas SMETS]

Hi,

In pgsql

I'm removing charaters from a String which should be numbers.
I then want to make calculations on these numbers (calculate the ISBN
number).

Do I have to cast the char into int's before I can do the calulations.

Also I looked in the User manual but could not find the modulo function
where is it ?

tx,

Thomas,













-- 
Thu Jan  4 20:19:03 CET 2001

Thomas SMETSe-mail : [EMAIL PROTECTED]
Av. de la Brabançonne 133 / 3   Tel. : +32 (0)2 742. 05. 94.
1030 Bruxelles
=== Quote of the Day =
Jealousy is all the fun you think they have.
= End of Quote ===