pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Type conversion
8+ messages / 7 participants
[nested] [flat]

* Type conversion
@ 2000-06-20 19:24 Ice Planet <ice@adiemus.sk>
  2000-06-20 19:48 ` Re: Type conversion Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 8+ messages in thread

From: Ice Planet @ 2000-06-20 19:24 UTC (permalink / raw)
  To: pgsql-sql

Hello

I have following situation

create table t1 (a int8 primary key, b int8);
create table t2 (x int2);
insert into t1 values (1,1);

A: insert into t2 values (select b from t1 where a = 1)

B: insert into t2 values (select int2(int4(b)) from t1 where a = 1)

Both possibilites fail, how can i do this insert???

Thanks for help

                       Best regards
                                              Ice Planet

e-mail: ice@adiemus.sk
ICQ#: 67765483





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Type conversion
  2000-06-20 19:24 Type conversion Ice Planet <ice@adiemus.sk>
@ 2000-06-20 19:48 ` Tom Lane <tgl@sss.pgh.pa.us>
  2000-06-20 20:05   ` Re: Type conversion Ed Loehr <eloehr@austin.rr.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Tom Lane @ 2000-06-20 19:48 UTC (permalink / raw)
  To: Ice Planet <ice@adiemus.sk>; +Cc: pgsql-sql

Ice Planet <ice@adiemus.sk> writes:
> B: insert into t2 values (select int2(int4(b)) from t1 where a = 1)

Works for me when spelled correctly:

regression=# insert into t2 (select int2(int4(b)) from t1 where a = 1);
INSERT 314647 1

If you make a suitable conversion function then you can omit the
explicit conversion:

regression=# create function int2(int8) returns int2 as
regression-# 'begin return int2(int4($1)); end;' language 'plpgsql';
CREATE
regression=# insert into t2 (select b from t1 where a = 1);
INSERT 314649 1

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Type conversion
  2000-06-20 19:24 Type conversion Ice Planet <ice@adiemus.sk>
  2000-06-20 19:48 ` Re: Type conversion Tom Lane <tgl@sss.pgh.pa.us>
@ 2000-06-20 20:05   ` Ed Loehr <eloehr@austin.rr.com>
  0 siblings, 0 replies; 8+ messages in thread

From: Ed Loehr @ 2000-06-20 20:05 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Ice Planet <ice@adiemus.sk>; pgsql-sql

Tom Lane wrote:
> 
> Ice Planet <ice@adiemus.sk> writes:
> > B: insert into t2 values (select int2(int4(b)) from t1 where a = 1)
> 
> Works for me when spelled correctly:

I think you can also leave out the 'values' for a sub-select insert,
though I haven't checked to see if it matters...

Regards,
Ed Loehr



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Type conversion
@ 2000-07-10 11:40 Ice Planet <ice@adiemus.sk>
  2000-07-10 14:24 ` Re: Type conversion Thomas Lockhart <lockhart@alumni.caltech.edu>
  0 siblings, 1 reply; 8+ messages in thread

From: Ice Planet @ 2000-07-10 11:40 UTC (permalink / raw)
  To: pgsql-sql

Hello

I have one big select that computes some value. The value is in format numeric (40,4). Then is this value send to console and when someone on console agree with the number then is the number send to database where i need to do 
something like this

select TheNumberFromConsole/(select max(division_point) from table1)

Division_point is in format numeric (40,4). But the number is sometimes small and sometimes big, when the number is big, then is all OK, but when the number is small i got error message that / for float8 (!) and numeric is not possible and i must 
use explicit typecast, but i dont know how! I have look in manual, but there are informations about float8 only and at the bottom is written that most of functions for float8 works for numeric so i try:

select numeric (TheNumberFromConsole)/(select max(division_point) from table1)

But this don't work too...

Please help how should i written the statement correct.

Thanks for help and sorry for my bad english.

                       Best regards
                                              Ice Planet

e-mail: ice@adiemus.sk
ICQ#: 67765483





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Type conversion
  2000-07-10 11:40 Type conversion Ice Planet <ice@adiemus.sk>
@ 2000-07-10 14:24 ` Thomas Lockhart <lockhart@alumni.caltech.edu>
  0 siblings, 0 replies; 8+ messages in thread

From: Thomas Lockhart @ 2000-07-10 14:24 UTC (permalink / raw)
  To: Ice Planet <ice@adiemus.sk>; +Cc: pgsql-sql

> select TheNumberFromConsole/(select max(division_point) from table1)

Try

  select '1234.5678'/(select max(division_point) from table1);

The quotes around the apparent floating point number keeps Postgres from
assuming that it *is* a floating point number, and it later decides that
it must have been a numeric() type.

                        - Thomas



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* type conversion?
@ 2002-04-09 17:08 Peter Atkins <Peter.Atkins@nextcard.com>
  2002-04-09 17:48 ` Re: type conversion? Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2002-04-09 17:51 ` Re: type conversion? Jean-Luc Lachance <jllachan@nsd.ca>
  0 siblings, 2 replies; 8+ messages in thread

From: Peter Atkins @ 2002-04-09 17:08 UTC (permalink / raw)
  To: pgsql-sql

All,

I have a column "total_hours  |  character varying(5) " and I am unable to
perform the aggrigate function SUM() because of the column type.
Without changing the type is there a way to convert the datatype by using
CAST(), to_char() or something simular? So that I may calculate the SUM().

SELECT SUM(total_hours) AS hours FROM my_table WHERE flag='$flag';

Thanks!
-p



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: type conversion?
  2002-04-09 17:08 type conversion? Peter Atkins <Peter.Atkins@nextcard.com>
@ 2002-04-09 17:48 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
  1 sibling, 0 replies; 8+ messages in thread

From: Stephan Szabo @ 2002-04-09 17:48 UTC (permalink / raw)
  To: Peter Atkins <Peter.Atkins@nextcard.com>; +Cc: pgsql-sql

On Tue, 9 Apr 2002, Peter Atkins wrote:

> I have a column "total_hours  |  character varying(5) " and I am unable to
> perform the aggrigate function SUM() because of the column type.
> Without changing the type is there a way to convert the datatype by using
> CAST(), to_char() or something simular? So that I may calculate the SUM().
>
> SELECT SUM(total_hours) AS hours FROM my_table WHERE flag='$flag';

I think this may work:
SELECT SUM(to_number(total_hours, '99999')) from my_table where ...




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: type conversion?
  2002-04-09 17:08 type conversion? Peter Atkins <Peter.Atkins@nextcard.com>
@ 2002-04-09 17:51 ` Jean-Luc Lachance <jllachan@nsd.ca>
  1 sibling, 0 replies; 8+ messages in thread

From: Jean-Luc Lachance @ 2002-04-09 17:51 UTC (permalink / raw)
  To: Peter Atkins <Peter.Atkins@nextcard.com>; +Cc: pgsql-sql

Try:

SELECT SUM(int4(total_hours)) AS hours FROM my_table WHERE flag='$flag';

JLL


Peter Atkins wrote:
> 
> All,
> 
> I have a column "total_hours  |  character varying(5) " and I am unable to
> perform the aggrigate function SUM() because of the column type.
> Without changing the type is there a way to convert the datatype by using
> CAST(), to_char() or something simular? So that I may calculate the SUM().
> 
> SELECT SUM(total_hours) AS hours FROM my_table WHERE flag='$flag';
> 
> Thanks!
> -p
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org



^ permalink  raw  reply  [nested|flat] 8+ messages in thread


end of thread, other threads:[~2002-04-09 17:51 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-06-20 19:24 Type conversion Ice Planet <ice@adiemus.sk>
2000-06-20 19:48 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-06-20 20:05   ` Ed Loehr <eloehr@austin.rr.com>
2000-07-10 11:40 Type conversion Ice Planet <ice@adiemus.sk>
2000-07-10 14:24 ` Thomas Lockhart <lockhart@alumni.caltech.edu>
2002-04-09 17:08 type conversion? Peter Atkins <Peter.Atkins@nextcard.com>
2002-04-09 17:48 ` Re: type conversion? Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-04-09 17:51 ` Re: type conversion? Jean-Luc Lachance <jllachan@nsd.ca>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox