pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedType conversion
8+ messages / 7 participants
[nested] [flat]
* Type conversion
@ 2000-06-20 19:24 Ice Planet <ice@adiemus.sk>
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:48 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ice Planet <ice@adiemus.sk>
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 20:05 Ed Loehr <eloehr@austin.rr.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
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>
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 14:24 Thomas Lockhart <lockhart@alumni.caltech.edu>
parent: Ice Planet <ice@adiemus.sk>
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>
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:48 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Peter Atkins <Peter.Atkins@nextcard.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:51 Jean-Luc Lachance <jllachan@nsd.ca>
parent: Peter Atkins <Peter.Atkins@nextcard.com>
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