pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSimple SQL question
19+ messages / 13 participants
[nested] [flat]
* Simple SQL question
@ 2004-03-08 14:10 Daniel Henrique Alves Lima <email_daniel_h@yahoo.com.br>
2004-03-09 04:21 ` Re: Simple SQL question Daniel Henrique Alves Lima <email_daniel_h@yahoo.com.br>
0 siblings, 1 reply; 19+ messages in thread
From: Daniel Henrique Alves Lima @ 2004-03-08 14:10 UTC (permalink / raw)
To: pgsql-sql
Hello, everybody.
I've a simple question about SQL usage but i don't know even i can
formulate this question. Well, i will try :
I've a teacher_course table with columns cd_course, cd_teacher =>
teacher_course(cd_teacher,cd_course) and i've a set of pairs that
contains the values for these columns, like {(1,2),(23,11),(563,341),...}.
Is there a way to build a query to select rows that matchs these
pairs ? Like a "in" extension (or something else) :
select * from teacher_course where (cd_course,cd_teacher) in
((1,2),(23,11),(563,341))
?
I would appreciate any help. Thanks in advance.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Simple SQL question
2004-03-08 14:10 Simple SQL question Daniel Henrique Alves Lima <email_daniel_h@yahoo.com.br>
@ 2004-03-09 04:21 ` Daniel Henrique Alves Lima <email_daniel_h@yahoo.com.br>
2004-03-09 05:28 ` Re: Simple SQL question Iain <iain@mst.co.jp>
2004-03-09 05:37 ` Re: Simple SQL question Iain <iain@mst.co.jp>
0 siblings, 2 replies; 19+ messages in thread
From: Daniel Henrique Alves Lima @ 2004-03-09 04:21 UTC (permalink / raw)
To: pgsql-sql
Thank you, Jeremy.
I've built a function that returns a string from (cd_teacher, cd_course)
and i've create a functional index over this function, like :
create index teacher_course_idx on teacher_course
(build_unique_2p(cd_teacher,cd_course));
select * from teacher_course where build_unique_2p(cd_teacher,cd_course)
in ('1:2','23:11','563','341');
Is it possible to use "array cast" over cd_teacher and cd_course (just
an idea, i don't known the sintaxe), like:
select * from teachar_course where cast((cd_teacher,cd_course) as array)
in ('{1,2}','{23,11}','{563,341}');
?
I'm using postgreSQL 7.34
Thanks !!!
Jeremy Semeiks wrote:
>You could use a subselect of unions:
>
>select * from teacher_course where (cd_course, cd_teacher) in
>(select 1, 2 union select 23, 11 union select 563, 341)
>
>Maybe there's a more concise way, though.
>
>- Jeremy
>
>
>
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Simple SQL question
2004-03-08 14:10 Simple SQL question Daniel Henrique Alves Lima <email_daniel_h@yahoo.com.br>
2004-03-09 04:21 ` Re: Simple SQL question Daniel Henrique Alves Lima <email_daniel_h@yahoo.com.br>
@ 2004-03-09 05:28 ` Iain <iain@mst.co.jp>
2004-03-09 05:43 ` Re: Simple SQL question Daniel Henrique Alves Lima <email_daniel_h@yahoo.com.br>
1 sibling, 1 reply; 19+ messages in thread
From: Iain @ 2004-03-09 05:28 UTC (permalink / raw)
To: email_daniel_h@yahoo.com.br; pgsql-sql
Just a note based on my experience, if you are going to to use IN processing
then there is a good chance that the index isn't going to be used. In some
recent tests I did, the index stopped being used after I put 3 or more items
in the IN list. You should build some representatve examples of the select
and test them using "ANALYSE SELECT ..." .I'm using 7.4.1.
The result from the select is the same, even if you don't make an index on
the function result.It's just a performance consideration. If the index
isn't used, then you don't need to create it.
regards
Iain
----- Original Message -----
From: "Daniel Henrique Alves Lima" <email_daniel_h@yahoo.com.br>
To: <pgsql-sql@postgresql.org>
Sent: Tuesday, March 09, 2004 1:21 PM
Subject: Re: [SQL] Simple SQL question
> Thank you, Jeremy.
>
> I've built a function that returns a string from (cd_teacher, cd_course)
> and i've create a functional index over this function, like :
>
> create index teacher_course_idx on teacher_course
> (build_unique_2p(cd_teacher,cd_course));
> select * from teacher_course where build_unique_2p(cd_teacher,cd_course)
> in ('1:2','23:11','563','341');
>
> Is it possible to use "array cast" over cd_teacher and cd_course (just
> an idea, i don't known the sintaxe), like:
>
> select * from teachar_course where cast((cd_teacher,cd_course) as array)
> in ('{1,2}','{23,11}','{563,341}');
>
> ?
>
> I'm using postgreSQL 7.34
>
> Thanks !!!
>
> Jeremy Semeiks wrote:
>
> >You could use a subselect of unions:
> >
> >select * from teacher_course where (cd_course, cd_teacher) in
> >(select 1, 2 union select 23, 11 union select 563, 341)
> >
> >Maybe there's a more concise way, though.
> >
> >- Jeremy
> >
> >
> >
>
>
>
> ---------------------------(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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Simple SQL question
2004-03-08 14:10 Simple SQL question Daniel Henrique Alves Lima <email_daniel_h@yahoo.com.br>
2004-03-09 04:21 ` Re: Simple SQL question Daniel Henrique Alves Lima <email_daniel_h@yahoo.com.br>
2004-03-09 05:28 ` Re: Simple SQL question Iain <iain@mst.co.jp>
@ 2004-03-09 05:43 ` Daniel Henrique Alves Lima <email_daniel_h@yahoo.com.br>
0 siblings, 0 replies; 19+ messages in thread
From: Daniel Henrique Alves Lima @ 2004-03-09 05:43 UTC (permalink / raw)
To: Iain <iain@mst.co.jp>; +Cc: pgsql-sql
Ok, Iain. Thanks for the tip !
Iain wrote:
>Just a note based on my experience, if you are going to to use IN processing
>then there is a good chance that the index isn't going to be used. In some
>recent tests I did, the index stopped being used after I put 3 or more items
>in the IN list. You should build some representatve examples of the select
>and test them using "ANALYSE SELECT ..." .I'm using 7.4.1.
>
>The result from the select is the same, even if you don't make an index on
>the function result.It's just a performance consideration. If the index
>isn't used, then you don't need to create it.
>
>regards
>Iain
>----- Original Message -----
>From: "Daniel Henrique Alves Lima" <email_daniel_h@yahoo.com.br>
>To: <pgsql-sql@postgresql.org>
>Sent: Tuesday, March 09, 2004 1:21 PM
>Subject: Re: [SQL] Simple SQL question
>
>
>
>
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Simple SQL question
2004-03-08 14:10 Simple SQL question Daniel Henrique Alves Lima <email_daniel_h@yahoo.com.br>
2004-03-09 04:21 ` Re: Simple SQL question Daniel Henrique Alves Lima <email_daniel_h@yahoo.com.br>
@ 2004-03-09 05:37 ` Iain <iain@mst.co.jp>
1 sibling, 0 replies; 19+ messages in thread
From: Iain @ 2004-03-09 05:37 UTC (permalink / raw)
To: email_daniel_h@yahoo.com.br; pgsql-sql
Sorry, did I write "ANALYSE SELECT .... "?
It's supposed to be "EXPLAIN [ANALYSE] [VERBOSE] SELECT ..."
----- Original Message -----
From: "Daniel Henrique Alves Lima" <email_daniel_h@yahoo.com.br>
To: <pgsql-sql@postgresql.org>
Sent: Tuesday, March 09, 2004 1:21 PM
Subject: Re: [SQL] Simple SQL question
> Thank you, Jeremy.
>
> I've built a function that returns a string from (cd_teacher, cd_course)
> and i've create a functional index over this function, like :
>
> create index teacher_course_idx on teacher_course
> (build_unique_2p(cd_teacher,cd_course));
> select * from teacher_course where build_unique_2p(cd_teacher,cd_course)
> in ('1:2','23:11','563','341');
>
> Is it possible to use "array cast" over cd_teacher and cd_course (just
> an idea, i don't known the sintaxe), like:
>
> select * from teachar_course where cast((cd_teacher,cd_course) as array)
> in ('{1,2}','{23,11}','{563,341}');
>
> ?
>
> I'm using postgreSQL 7.34
>
> Thanks !!!
>
> Jeremy Semeiks wrote:
>
> >You could use a subselect of unions:
> >
> >select * from teacher_course where (cd_course, cd_teacher) in
> >(select 1, 2 union select 23, 11 union select 563, 341)
> >
> >Maybe there's a more concise way, though.
> >
> >- Jeremy
> >
> >
> >
>
>
>
> ---------------------------(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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Simple SQL Question
@ 2004-11-05 16:54 Andras Kutrovics <n-drew@freemail.hu>
2004-11-05 17:13 ` Re: Simple SQL Question Bricklen <bricklen@zyahoo.zcomz>
2004-11-05 18:09 ` Re: Simple SQL Question Franco Bruno Borghesi <franco@akyasociados.com.ar>
2004-11-05 18:09 ` Re: Simple SQL Question Tomasz Myrta <jasiek@klaster.net>
0 siblings, 3 replies; 19+ messages in thread
From: Andras Kutrovics @ 2004-11-05 16:54 UTC (permalink / raw)
To: pgsql-sql
Hi All!
In one of my application, I would like to implement incremental
fetching. The problem is that I would like to run it
from a stateless application server, so I have to tell where
to begin fetching data. It is simple for a table which has single column
primary key, but causes difficulties (at least for me) in
a more-column primary key..
Let say I have a table wich has the primary key: itemkey,location
table1
------
itemkey
location
...
--------
select * from table1 LIMIT x
gives me the first x row of the result.
After that, I save the last value, and next time, I adjust
the query as
select * from table1 where itemkey>:lastvalue LIMIT x
that should be enough for an 'Item' table, but I have another
column in the primary key.
let say, I have the following in Table1:
itemkey location
------------------------
00001 00001
00001 00002
...
00005 00001
00005 00002
00005 00003 <--- lets say this is the last value
next time i want to run a query, which starts from
00005 00004
00006 00005
and so on..
How can I specify that in sql?
I dont want to use cursor:), I would like to do it in plain sql.
(It it is possible).
Thank you in advance
Andras Kutrovics
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Simple SQL Question
2004-11-05 16:54 Simple SQL Question Andras Kutrovics <n-drew@freemail.hu>
@ 2004-11-05 17:13 ` Bricklen <bricklen@zyahoo.zcomz>
2 siblings, 0 replies; 19+ messages in thread
From: Bricklen @ 2004-11-05 17:13 UTC (permalink / raw)
To: pgsql-sql
Andras Kutrovics wrote:
> Hi All!
>
> In one of my application, I would like to implement incremental
> fetching. The problem is that I would like to run it
> from a stateless application server, so I have to tell where
> to begin fetching data. It is simple for a table which has single column
> primary key, but causes difficulties (at least for me) in
> a more-column primary key..
>
> Let say I have a table wich has the primary key: itemkey,location
>
> table1
> ------
> itemkey
> location
> ...
> --------
>
>
> select * from table1 LIMIT x
>
> gives me the first x row of the result.
> After that, I save the last value, and next time, I adjust
> the query as
>
> select * from table1 where itemkey>:lastvalue LIMIT x
>
> that should be enough for an 'Item' table, but I have another
> column in the primary key.
>
> let say, I have the following in Table1:
>
> itemkey location
> ------------------------
> 00001 00001
> 00001 00002
> ...
> 00005 00001
> 00005 00002
> 00005 00003 <--- lets say this is the last value
>
> next time i want to run a query, which starts from
>
> 00005 00004
> 00006 00005
>
> and so on..
>
> How can I specify that in sql?
> I dont want to use cursor:), I would like to do it in plain sql.
> (It it is possible).
>
> Thank you in advance
>
> Andras Kutrovics
maybe the OFFSET keyword will help here?
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Simple SQL Question
2004-11-05 16:54 Simple SQL Question Andras Kutrovics <n-drew@freemail.hu>
@ 2004-11-05 18:09 ` Franco Bruno Borghesi <franco@akyasociados.com.ar>
2004-11-09 09:16 ` Re: Simple SQL Question Andras Kutrovics <n-drew@freemail.hu>
2 siblings, 1 reply; 19+ messages in thread
From: Franco Bruno Borghesi @ 2004-11-05 18:09 UTC (permalink / raw)
To: Andras Kutrovics <n-drew@freemail.hu>; +Cc: pgsql-sql
wouldn't it be easier using offset & limit?: you always select from the
table with an itemkey,location order by clause. You save the current
offset between requests, and for every request you re-execute the query
with a different offset.
If you still want to go with what you already have, you should keep the
lastItemKey and lastLocaltion values between requests, and your where
clause should be something like:
WHERE (itemKey=:lastItemKey AND location>:lastLocation) OR
(itemKey>:lastItemKey)
hope it helps.
On Fri, 2004-11-05 at 13:54, Andras Kutrovics wrote:
> Hi All!
>
> In one of my application, I would like to implement incremental
> fetching. The problem is that I would like to run it
> from a stateless application server, so I have to tell where
> to begin fetching data. It is simple for a table which has single column
> primary key, but causes difficulties (at least for me) in
> a more-column primary key..
>
> Let say I have a table wich has the primary key: itemkey,location
>
> table1
> ------
> itemkey
> location
> ...
> --------
>
>
> select * from table1 LIMIT x
>
> gives me the first x row of the result.
> After that, I save the last value, and next time, I adjust
> the query as
>
> select * from table1 where itemkey>:lastvalue LIMIT x
>
> that should be enough for an 'Item' table, but I have another
> column in the primary key.
>
> let say, I have the following in Table1:
>
> itemkey location
> ------------------------
> 00001 00001
> 00001 00002
> ...
> 00005 00001
> 00005 00002
> 00005 00003 <--- lets say this is the last value
>
> next time i want to run a query, which starts from
>
> 00005 00004
> 00006 00005
>
> and so on..
>
> How can I specify that in sql?
> I dont want to use cursor:), I would like to do it in plain sql.
> (It it is possible).
>
> Thank you in advance
>
> Andras Kutrovics
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Simple SQL Question
2004-11-05 16:54 Simple SQL Question Andras Kutrovics <n-drew@freemail.hu>
2004-11-05 18:09 ` Re: Simple SQL Question Franco Bruno Borghesi <franco@akyasociados.com.ar>
@ 2004-11-09 09:16 ` Andras Kutrovics <n-drew@freemail.hu>
2004-11-09 16:53 ` Re: Simple SQL Question Franco Bruno Borghesi <franco@akyasociados.com.ar>
0 siblings, 1 reply; 19+ messages in thread
From: Andras Kutrovics @ 2004-11-09 09:16 UTC (permalink / raw)
To: pgsql-sql
Franco Bruno Borghesi wrote:
Hi!
Sorry for being late with the answer, I was busy at one of our customer;)
> wouldn't it be easier using offset & limit?: you always select from the
> table with an itemkey,location order by clause. You save the current
> offset between requests, and for every request you re-execute the query
> with a different offset.
Sure, but sometimes I have to query by name, and dont want to create
another query component.
> If you still want to go with what you already have, you should keep the
> lastItemKey and lastLocaltion values between requests, and your where
> clause should be something like:
> WHERE (itemKey=:lastItemKey AND location>:lastLocation) OR
> (itemKey>:lastItemKey)
It works perfectly, but I have to consider the performance issue,
because if i use 'or' statement, postgres doesn't use index scan,
and I also have tables with 3 or more keys and 500.000 records ,
where
the performance of this method is poor.
Maybe I will end up using limit and offset in case of incremental
fetching,but if the table is modified between requests, it can behave
weird.
Is there a perfect solution to this?
Sorry for the english
Thank you again,
Andras Kutrovics
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Simple SQL Question
2004-11-05 16:54 Simple SQL Question Andras Kutrovics <n-drew@freemail.hu>
2004-11-05 18:09 ` Re: Simple SQL Question Franco Bruno Borghesi <franco@akyasociados.com.ar>
2004-11-09 09:16 ` Re: Simple SQL Question Andras Kutrovics <n-drew@freemail.hu>
@ 2004-11-09 16:53 ` Franco Bruno Borghesi <franco@akyasociados.com.ar>
0 siblings, 0 replies; 19+ messages in thread
From: Franco Bruno Borghesi @ 2004-11-09 16:53 UTC (permalink / raw)
To: Andras Kutrovics <n-drew@freemail.hu>; +Cc: pgsql-sql
Didn't know about the seqscan problem when using ORs. But you still can
split the query in two, and then use Union to join the results:
SELECT .... WHERE itemKey=:lastItemKey AND location>:lastLocation
UNION
SELECT ... WHERE itemKey>:lastItemKey
You could solve the OFFSET/LIMIT modification problem if you could keep
the transaction open, but I don't know if it's applicable in your case.
Andras Kutrovics wrote:
> Franco Bruno Borghesi wrote:
>
>
> Hi!
>
> Sorry for being late with the answer, I was busy at one of our customer;)
>
>> wouldn't it be easier using offset & limit?: you always select from
>> the table with an itemkey,location order by clause. You save the
>> current offset between requests, and for every request you re-execute
>> the query with a different offset.
>
> Sure, but sometimes I have to query by name, and dont want to create
> another query component.
>
>> If you still want to go with what you already have, you should keep
>> the lastItemKey and lastLocaltion values between requests, and your
>> where clause should be something like:
>> WHERE (itemKey=:lastItemKey AND location>:lastLocation) OR
>> (itemKey>:lastItemKey)
>
>
> It works perfectly, but I have to consider the performance issue,
> because if i use 'or' statement, postgres doesn't use index scan,
> and I also have tables with 3 or more keys and 500.000 records , where
> the performance of this method is poor.
> Maybe I will end up using limit and offset in case of incremental
> fetching,but if the table is modified between requests, it can behave
> weird.
> Is there a perfect solution to this?
>
> Sorry for the english
>
> Thank you again,
>
> Andras Kutrovics
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Simple SQL Question
2004-11-05 16:54 Simple SQL Question Andras Kutrovics <n-drew@freemail.hu>
@ 2004-11-05 18:09 ` Tomasz Myrta <jasiek@klaster.net>
2004-11-06 08:17 ` Re: Simple SQL Question Greg Stark <gsstark@mit.edu>
2 siblings, 1 reply; 19+ messages in thread
From: Tomasz Myrta @ 2004-11-05 18:09 UTC (permalink / raw)
To: Andras Kutrovics <n-drew@freemail.hu>; +Cc: pgsql-sql
> Hi All!
>
> In one of my application, I would like to implement incremental
> fetching. The problem is that I would like to run it
> from a stateless application server, so I have to tell where
> to begin fetching data. It is simple for a table which has single column
> primary key, but causes difficulties (at least for me) in
> a more-column primary key..
>
> Let say I have a table wich has the primary key: itemkey,location
>
> table1
> ------
> itemkey
> location
> ...
> --------
>
>
> select * from table1 LIMIT x
>
> gives me the first x row of the result.
> After that, I save the last value, and next time, I adjust
> the query as
>
> select * from table1 where itemkey>:lastvalue LIMIT x
Why do you complicate it so much? Everything you need is:
select * from table1 LIMIT x
select * from table1 LIMIT x OFFSET x
select * from table1 LIMIT x OFFSET 2*x
Remember to sort rows before using limit/offset.
Regards,
Tomasz Myrta
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Simple SQL Question
2004-11-05 16:54 Simple SQL Question Andras Kutrovics <n-drew@freemail.hu>
2004-11-05 18:09 ` Re: Simple SQL Question Tomasz Myrta <jasiek@klaster.net>
@ 2004-11-06 08:17 ` Greg Stark <gsstark@mit.edu>
0 siblings, 0 replies; 19+ messages in thread
From: Greg Stark @ 2004-11-06 08:17 UTC (permalink / raw)
To: Tomasz Myrta <jasiek@klaster.net>; +Cc: Andras Kutrovics <n-drew@freemail.hu>; pgsql-sql
Tomasz Myrta <jasiek@klaster.net> writes:
> > select * from table1 LIMIT x
> > gives me the first x row of the result.
> > After that, I save the last value, and next time, I adjust
> > the query as
> > select * from table1 where itemkey>:lastvalue LIMIT x
>
> Why do you complicate it so much? Everything you need is:
>
> select * from table1 LIMIT x
> select * from table1 LIMIT x OFFSET x
> select * from table1 LIMIT x OFFSET 2*x
>
> Remember to sort rows before using limit/offset.
There are two good reasons to prefer his Andras' solution to yours.
a) If the data is modified between the two queries his will continue from
where the previous page left off. Yours will either skip records or overlap
with the previous page.
b) If itemkey is indexed his will be an efficient index scan that performs
similarly regardless of what page is being fetched. Yours will perform more
and more slowly as the user gets deeper into the results.
Note that both queries are wrong however. You need an "ORDER BY itemkey" or
else nothing guarantees the second page has any relation at all to the first
page.
--
greg
^ permalink raw reply [nested|flat] 19+ messages in thread
* simple SQL question
@ 2007-06-25 14:29 Joshua <joshua@joshuaneil.com>
2007-06-25 14:35 ` Re: simple SQL question Rodrigo De León <rdeleonp@gmail.com>
2007-06-25 14:38 ` Re: simple SQL question Milen A. Radev <milen@radev.net>
2007-06-25 15:33 ` Re: simple SQL question Ireneusz Pluta <ipluta@wp.pl>
0 siblings, 3 replies; 19+ messages in thread
From: Joshua @ 2007-06-25 14:29 UTC (permalink / raw)
To: pgsql-sql
I have a column with the following values (example below)
5673
4731
4462
5422
756
3060
I want the column to display the numbers as follows:
56.73
47.31
44.62
54.22
7.56
30.60
I have been playing around with string functions but cannot seem to
figure out a quick solution. Does anyone have any suggestions?
Please let me know.
Thanks.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: simple SQL question
2007-06-25 14:29 simple SQL question Joshua <joshua@joshuaneil.com>
@ 2007-06-25 14:35 ` Rodrigo De León <rdeleonp@gmail.com>
2 siblings, 0 replies; 19+ messages in thread
From: Rodrigo De León @ 2007-06-25 14:35 UTC (permalink / raw)
To: pgsql-sql
On 6/25/07, Joshua <joshua@joshuaneil.com> wrote:
> I have a column with the following values (example below)
>
> 5673
> 4731
> 4462
> 5422
> 756
> 3060
>
> I want the column to display the numbers as follows:
>
> 56.73
> 47.31
> 44.62
> 54.22
> 7.56
> 30.60
>
> I have been playing around with string functions but cannot seem to
> figure out a quick solution. Does anyone have any suggestions?
>
> Please let me know.
>
> Thanks.
Divide by 100.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: simple SQL question
2007-06-25 14:29 simple SQL question Joshua <joshua@joshuaneil.com>
@ 2007-06-25 14:38 ` Milen A. Radev <milen@radev.net>
2007-06-25 15:05 ` Re: simple SQL question manchicken <manchicken@notsosoft.net>
2 siblings, 1 reply; 19+ messages in thread
From: Milen A. Radev @ 2007-06-25 14:38 UTC (permalink / raw)
To: pgsql-sql
Joshua написа:
> I have a column with the following values (example below)
>
> 5673
> 4731
> 4462
> 5422
> 756
> 3060
>
> I want the column to display the numbers as follows:
>
> 56.73
> 47.31
> 44.62
> 54.22
> 7.56
> 30.60
>
> I have been playing around with string functions but cannot seem to
> figure out a quick solution. Does anyone have any suggestions?
Use "to_char(int, text)", details here -
http://www.postgresql.org/docs/current/static/functions-formatting.html
--
Milen A. Radev
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: simple SQL question
2007-06-25 14:29 simple SQL question Joshua <joshua@joshuaneil.com>
2007-06-25 14:38 ` Re: simple SQL question Milen A. Radev <milen@radev.net>
@ 2007-06-25 15:05 ` manchicken <manchicken@notsosoft.net>
2007-06-25 15:19 ` Re: simple SQL question Fernando Hevia <fhevia@ip-tel.com.ar>
0 siblings, 1 reply; 19+ messages in thread
From: manchicken @ 2007-06-25 15:05 UTC (permalink / raw)
To: pgsql-sql
On Monday 25 June 2007 09:38:39 Milen A. Radev wrote:
> Joshua написа:
> > I have a column with the following values (example below)
> >
> > 5673
> > 4731
> > 4462
> > 5422
> > 756
> > 3060
> >
> > I want the column to display the numbers as follows:
> >
> > 56.73
> > 47.31
> > 44.62
> > 54.22
> > 7.56
> > 30.60
> >
> > I have been playing around with string functions but cannot seem to
> > figure out a quick solution. Does anyone have any suggestions?
>
> Use "to_char(int, text)", details here -
> http://www.postgresql.org/docs/current/static/functions-formatting.html
Don't use to_char unless you actually want character data though.
Just try this...
SELECT (column/100) FROM table;
--
~ manchicken <><
(A)bort, (R)etry, (I)nfluence with large hammer.
09 F9 11 02 9D 74 E3 5B D8 41 56 C5 63 56 88 C0
Please avoid sending me Word or PowerPoint attachments.
See http://www.gnu.org/philosophy/no-word-attachments.html
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: simple SQL question
2007-06-25 14:29 simple SQL question Joshua <joshua@joshuaneil.com>
2007-06-25 14:38 ` Re: simple SQL question Milen A. Radev <milen@radev.net>
2007-06-25 15:05 ` Re: simple SQL question manchicken <manchicken@notsosoft.net>
@ 2007-06-25 15:19 ` Fernando Hevia <fhevia@ip-tel.com.ar>
2007-06-25 17:09 ` Re: simple SQL question manchicken <manchicken@notsosoft.net>
0 siblings, 1 reply; 19+ messages in thread
From: Fernando Hevia @ 2007-06-25 15:19 UTC (permalink / raw)
To: 'manchicken' <manchicken@notsosoft.net>; pgsql-sql
> > I have a column with the following values (example below)
> >
> > 5673
> > 4731
> > 4462
> > 5422
> > 756
> > 3060
> >
> > I want the column to display the numbers as follows:
> >
> > 56.73
> > 47.31
> > 44.62
> > 54.22
> > 7.56
> > 30.60
> >
> > I have been playing around with string functions but cannot seem to
> > figure out a quick solution. Does anyone have any suggestions?
>
> Don't use to_char unless you actually want character data though.
>
> Just try this...
>
> SELECT (column/100) FROM table;
Cast the column in order to get the decimal part:
SELECT (column::real/100) FROM table;
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: simple SQL question
2007-06-25 14:29 simple SQL question Joshua <joshua@joshuaneil.com>
2007-06-25 14:38 ` Re: simple SQL question Milen A. Radev <milen@radev.net>
2007-06-25 15:05 ` Re: simple SQL question manchicken <manchicken@notsosoft.net>
2007-06-25 15:19 ` Re: simple SQL question Fernando Hevia <fhevia@ip-tel.com.ar>
@ 2007-06-25 17:09 ` manchicken <manchicken@notsosoft.net>
0 siblings, 0 replies; 19+ messages in thread
From: manchicken @ 2007-06-25 17:09 UTC (permalink / raw)
To: pgsql-sql
On Monday 25 June 2007 10:19:49 Fernando Hevia wrote:
> > > I have a column with the following values (example below)
> > >
> > > 5673
> > > 4731
> > > 4462
> > > 5422
> > > 756
> > > 3060
> > >
> > > I want the column to display the numbers as follows:
> > >
> > > 56.73
> > > 47.31
> > > 44.62
> > > 54.22
> > > 7.56
> > > 30.60
> > >
> > > I have been playing around with string functions but cannot seem to
> > > figure out a quick solution. Does anyone have any suggestions?
> >
> > Don't use to_char unless you actually want character data though.
> >
> > Just try this...
> >
> > SELECT (column/100) FROM table;
>
> Cast the column in order to get the decimal part:
>
> SELECT (column::real/100) FROM table;
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
Good point. Thanks for the catch :)
--
~ manchicken <><
(A)bort, (R)etry, (I)nfluence with large hammer.
09 F9 11 02 9D 74 E3 5B D8 41 56 C5 63 56 88 C0
Please avoid sending me Word or PowerPoint attachments.
See http://www.gnu.org/philosophy/no-word-attachments.html
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: simple SQL question
2007-06-25 14:29 simple SQL question Joshua <joshua@joshuaneil.com>
@ 2007-06-25 15:33 ` Ireneusz Pluta <ipluta@wp.pl>
2 siblings, 0 replies; 19+ messages in thread
From: Ireneusz Pluta @ 2007-06-25 15:33 UTC (permalink / raw)
To: Joshua <joshua@joshuaneil.com>; +Cc: pgsql-sql
Joshua napisał(a):
> I have a column with the following values (example below)
>
> 5673
> 4731
> 4462
> 5422
> 756
> 3060
>
> I want the column to display the numbers as follows:
>
> 56.73
> 47.31
> 44.62
> 54.22
> 7.56
> 30.60
>
> I have been playing around with string functions but cannot seem to
> figure out a quick solution. Does anyone have any suggestions?
if I read you correctly, I might suggest:
select round(your_column/100.0, 2) as divided_by_100 from your_table;
(note dividing by 100.0 (decimal), not just by 100 (integer))
>
> Please let me know.
>
> Thanks.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>
>
>
^ permalink raw reply [nested|flat] 19+ messages in thread
end of thread, other threads:[~2007-06-25 17:09 UTC | newest]
Thread overview: 19+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-03-08 14:10 Simple SQL question Daniel Henrique Alves Lima <email_daniel_h@yahoo.com.br>
2004-03-09 04:21 ` Daniel Henrique Alves Lima <email_daniel_h@yahoo.com.br>
2004-03-09 05:28 ` Iain <iain@mst.co.jp>
2004-03-09 05:43 ` Daniel Henrique Alves Lima <email_daniel_h@yahoo.com.br>
2004-03-09 05:37 ` Iain <iain@mst.co.jp>
2004-11-05 16:54 Simple SQL Question Andras Kutrovics <n-drew@freemail.hu>
2004-11-05 17:13 ` Re: Simple SQL Question Bricklen <bricklen@zyahoo.zcomz>
2004-11-05 18:09 ` Re: Simple SQL Question Franco Bruno Borghesi <franco@akyasociados.com.ar>
2004-11-09 09:16 ` Re: Simple SQL Question Andras Kutrovics <n-drew@freemail.hu>
2004-11-09 16:53 ` Re: Simple SQL Question Franco Bruno Borghesi <franco@akyasociados.com.ar>
2004-11-05 18:09 ` Re: Simple SQL Question Tomasz Myrta <jasiek@klaster.net>
2004-11-06 08:17 ` Re: Simple SQL Question Greg Stark <gsstark@mit.edu>
2007-06-25 14:29 simple SQL question Joshua <joshua@joshuaneil.com>
2007-06-25 14:35 ` Re: simple SQL question Rodrigo De León <rdeleonp@gmail.com>
2007-06-25 14:38 ` Re: simple SQL question Milen A. Radev <milen@radev.net>
2007-06-25 15:05 ` Re: simple SQL question manchicken <manchicken@notsosoft.net>
2007-06-25 15:19 ` Re: simple SQL question Fernando Hevia <fhevia@ip-tel.com.ar>
2007-06-25 17:09 ` Re: simple SQL question manchicken <manchicken@notsosoft.net>
2007-06-25 15:33 ` Re: simple SQL question Ireneusz Pluta <ipluta@wp.pl>
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