Converting string to IN query

I found that following query works:

create temp table test ( test int ) on commit drop;
insert into test values(1);
select * from test where test = ANY ( '{1,2}' );

Is this best solution ?

Will it work without causing stack overflow with 8.2 server default
settings
if string contains some thousands numbers ?

I have found that IN (1,2,...) causes stack overflow in server if there are
large number of items in list.

Andrus.

Andrus escreveu:

<snip>
SELECT * FROM invoices WHERE invoiceno IN ( '1,3,4' );

but this causes error.
<snip>

change it to ( '1','3','4' ) or ( 1,3,4 )

--

ACV

Andrus wrote:

String contains list of document numbers (integers) like:

'1,3,4'

How to SELECT documents whose numbers are contained in this string.

Numbers should be passed as single string literal since FYIReporting
RDLEngine does not allow multivalue parameters.

Hmm - might be worth bringing that to their attention.

Try string_to_array(). Example:

SELECT * FROM generate_series(1,10) s
WHERE s = ANY (string_to_array('1,3,5,7', ',')::int[]);

Note that I'm casting it to an array of integers so the "= ANY" knows
what types it will need to match.

--
Richard Huxton
Archonet Ltd

On Fri, Sep 12, 2008 at 04:04:18PM +0100, Richard Huxton wrote:

Andrus wrote:

String contains list of document numbers (integers) like:

'1,3,4'

How to SELECT documents whose numbers are contained in this string.

Numbers should be passed as single string literal since FYIReporting
RDLEngine does not allow multivalue parameters.

Hmm - might be worth bringing that to their attention.

I'm probably missing something, but does PG?

Try string_to_array(). Example:

SELECT * FROM generate_series(1,10) s
WHERE s = ANY (string_to_array('1,3,5,7', ',')::int[]);

I don't think you need the string_to_array function call, an array
literal should do perfectly well here. I.e.

WHERE s = ANY ('{1,2,3}'::int[]);

the literal can of course be a parameter as well:

WHERE s = ANY ($1::int[]);

Sam

In article <gadsb6$2v2d$1@news.hub.org>,
"Andrus" <kobruleht2@hot.ee> writes:

I found that following query works:
create temp table test ( test int ) on commit drop;
insert into test values(1);
select * from test where test = ANY ( '{1,2}' );

Is this best solution ?

Will it work without causing stack overflow with 8.2 server default
settings
if string contains some thousands numbers ?

If you get thousands of numbers, it is safer and maybe also faster to
put them into a temporary table, analyze it, and then join it to the
table in question.

hi all:
I have a table with columns(>2) named "query", "pop", "dfk".
what I want is:
when I do some select, if the column "query" in result records have
duplicate value, I only want the record which have the maximum value of
the "pop".

for example, the content of table:
query pop dfk
-----------------------
abc 30 1 --max
foo 20 lk --max
def 16 kj --max
foo 15 fk --discard
abc 10 2 --discard
bar 8 are --max

the result should be:
query pop dfk
-----------------------
abc 30 1
foo 20 lk
def 16 kj
bar 8 are

now, I do it like this(plpgsql)
------------------------------------
declare hq := ''::hstore;
begin
for rc in execute 'select * from test order by pop desc' loop
if not defined(hq, rc.query) then
hq := hq || (rc.query => '1')::hstore;
return next rc;
end if;
end loop;
-----------------------------------
language sql/plpgsql will be ok.

ps: I try to use "group by" or "max" function, because of the
multi-columns(more than 2), I failed.

thanks,
any answer is appreciated.

regards,

Yi Zhao <yi.zhao@alibaba-inc.com> schrieb:

hi all:
I have a table with columns(>2) named "query", "pop", "dfk".
what I want is:
when I do some select, if the column "query" in result records have
duplicate value, I only want the record which have the maximum value of
the "pop".

for example, the content of table:
query pop dfk
-----------------------
abc 30 1 --max
foo 20 lk --max
def 16 kj --max
foo 15 fk --discard
abc 10 2 --discard
bar 8 are --max

the result should be:
query pop dfk
-----------------------
abc 30 1
foo 20 lk
def 16 kj
bar 8 are

test=*# select * from d;
query | pop | dfk
-------+-----+-----
abc | 30 | 1
foo | 20 | lk
def | 16 | kj
foo | 15 | fk
abc | 10 | 2
bar | 8 | are
(6 Zeilen)

Zeit: 0,213 ms
test=*# select distinct on (query) * from d order by query, pop desc;
query | pop | dfk
-------+-----+-----
abc | 30 | 1
bar | 8 | are
def | 16 | kj
foo | 20 | lk
(4 Zeilen)

Hint: distinct on isn't standard-sql, it's an PG-extension.

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°

now, I do it like this(plpgsql)
-----------
this methold have low efficiency, when the records is large, it will
become slow, so someone can tell me some high efficiency way???

thanks.
On Fri, 2008-09-19 at 16:51 +0800, Yi Zhao wrote:

--- On Fri, 9/19/08, Yi Zhao <yi.zhao@alibaba-inc.com> wrote:

From: Yi Zhao <yi.zhao@alibaba-inc.com>
Subject: [GENERAL] how to return the first record from the sorted records which may have duplicated value.
To: "pgsql-general" <pgsql-general@postgresql.org>
Date: Friday, September 19, 2008, 8:51 AM
hi all:
I have a table with columns(>2) named "query",
"pop", "dfk".
what I want is:
when I do some select, if the column "query" in
result records have
duplicate value, I only want the record which have the
maximum value of
the "pop".

for example, the content of table:
query pop dfk
-----------------------
abc 30 1 --max
foo 20 lk --max
def 16 kj --max
foo 15 fk --discard
abc 10 2 --discard
bar 8 are --max

the result should be:
query pop dfk
-----------------------
abc 30 1
foo 20 lk
def 16 kj
bar 8 are

now, I do it like this(plpgsql)
------------------------------------
declare hq := ''::hstore;
begin
for rc in execute 'select * from test order by pop
desc' loop
if not defined(hq, rc.query) then
hq := hq || (rc.query => '1')::hstore;
return next rc;
end if;
end loop;
-----------------------------------
language sql/plpgsql will be ok.

ps: I try to use "group by" or "max"
function, because of the
multi-columns(more than 2), I failed.

thanks,
any answer is appreciated.

regards,

this query work for me....

select distinct max(pop),query from test
group by query

please reply your results

thanks...

yes, 

select distinct max(pop),query from test
group by query

test=# select distinct max(pop),query from bar group by query;
max | query
-----+-------
8 | bar
16 | def
20 | foo
30 | abc

but, I want to get the records contains more than two columns(max,
query, "dfk"), so, if I use group by, max, distinct keywords, I should
use this sql and get the result as below:
test=# select distinct max(pop),query, dfk from bar group by query, dfk;
max | query | dfk
-----+-------+-----
8 | bar | are
10 | abc | 2
15 | foo | fk
16 | def | kj
20 | foo | lk
30 | abc | 1

btw: *distinct on* is useful:)

thanks,

yes, thanks u very much, it's work:)

regards,
Yi