[SQL] in() VS exists()

2008-03-13 Thread Julien 
Hello,

Does anyone has an idea why sometimes:
- select ... where ... in (select ...)
is faster than :
- select ... where ... exists(select ...)
and sometimes it's the opposite ?

I had such a situation, I've pasted the queries on:
http://rafb.net/p/KXNZ6892.html and http://rafb.net/p/jvo5DO38.html

It's running PostgreSQL 8.1 with an effective_cache_size of 3.

specimens.id is the primary key and there are indexes on
sequences(specimen_id) and specimen_measurements(specimen_id)

Is there a general "rule" to know when to use the in() version and when
to use the exists() version ? Is it true to say that the exists()
version is more scalable (with many rows) than the in() version (from
the little tests I made it seems the case) ?

Thanks,
Julien

-- 
Julien Cigar
Belgian Biodiversity Platform
http://www.biodiversity.be
Université Libre de Bruxelles (ULB)
Campus de la Plaine CP 257
Bâtiment NO, Bureau 4 N4 115C (Niveau 4)
Boulevard du Triomphe, entrée ULB 2
B-1050 Bruxelles
Mail: [EMAIL PROTECTED]
@biobel: http://biobel.biodiversity.be/person/show/471
Tel : 02 650 57 52


-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] in() VS exists()

2008-03-13 Thread Julien 
If I understood well the query plan, the planner optimize the
IN(SELECT ...) version with a JOIN (line 19-20 of the first paste) :

->  Hash IN Join  (cost=240.95..4011.20 rows=1436 width=4) (actual
time=93.971..201.908 rows=1431 loops=1)
 Hash Cond: ("outer".id = "inner".specimen_id)

so I guess that :

select count(sp.id) from specimens sp where sp.id in (select specimen_id
from sequences);

is almost the same as :

select count(sp.id) from specimens sp INNER JOIN (select specimen_id
from sequences GROUP BY specimen_id) as foo on foo.specimen_id = sp.id;

?

Thanks,
Julien

On Thu, 2008-03-13 at 14:46 +0100, Bart Degryse wrote:
> The chapter on indexes in the manual should give you a pretty good
> idea on the why.
> IN and EXISTS are not the only possibilities, you can also use inner
> or outer joins.
> Which solution performs best depends on the data, the database
> version, the available indexes, ...
> 
> >>> Julien <[EMAIL PROTECTED]> 2008-03-13 15:47 >>>
> Hello,
> 
> Does anyone has an idea why sometimes:
> - select ... where ... in (select ...)
> is faster than :
> - select ... where ... exists(select ...)
> and sometimes it's the opposite ?
> 
> I had such a situation, I've pasted the queries on:
> http://rafb.net/p/KXNZ6892.html and http://rafb.net/p/jvo5DO38.html
> 
> It's running PostgreSQL 8.1 with an effective_cache_size of 3.
> 
> specimens.id is the primary key and there are indexes on
> sequences(specimen_id) and specimen_measurements(specimen_id)
> 
> Is there a general "rule" to know when to use the in() version and
> when
> to use the exists() version ? Is it true to say that the exists()
> version is more scalable (with many rows) than the in() version (from
> the little tests I made it seems the case) ?
> 
> Thanks,
> Julien
> 
> -- 
> Julien Cigar
> Belgian Biodiversity Platform
> http://www.biodiversity.be
> Université Libre de Bruxelles (ULB)
> Campus de la Plaine CP 257
> Bâtiment NO, Bureau 4 N4 115C (Niveau 4)
> Boulevard du Triomphe, entrée ULB 2
> B-1050 Bruxelles
> Mail: [EMAIL PROTECTED]
> @biobel: http://biobel.biodiversity.be/person/show/471
> Tel : 02 650 57 52
> 
> 
> -- 
> Sent via pgsql-sql mailing list ([email protected])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
> 
-- 
Julien Cigar
Belgian Biodiversity Platform
http://www.biodiversity.be
Université Libre de Bruxelles (ULB)
Campus de la Plaine CP 257
Bâtiment NO, Bureau 4 N4 115C (Niveau 4)
Boulevard du Triomphe, entrée ULB 2
B-1050 Bruxelles
Mail: [EMAIL PROTECTED]
@biobel: http://biobel.biodiversity.be/person/show/471
Tel : 02 650 57 52


-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] in() VS exists()

2008-03-13 Thread Julien 
mmh no because it's a one to many relation (a specimen can have more
than one sequence) :

muridae=> select count(sp.id) from specimens sp INNER JOIN sequences s
on s.specimen_id = sp.id;
 count 
---
  1536
(1 row)

Time: 81.242 ms
muridae=> select count(sp.id) from specimens sp where sp.id in (select
specimen_id from sequences group by specimen_id);
 count 
---
  1431
(1 row)

Time: 81.736 ms
muridae=> 

(of course this is a bad example, because I could just do: select
count(specimen_id) from sequences group by specimen_id;, but in my
application I have more fields coming from specimens of course)

Julien

On Thu, 2008-03-13 at 15:12 +0100, Bart Degryse wrote:
> I think that just
> select count(sp.id) from specimens sp INNER JOIN sequences s on
> s.specimen_id = sp.id;
> should be enough
> 
> >>> Julien <[EMAIL PROTECTED]> 2008-03-13 17:10 >>>
> If I understood well the query plan, the planner optimize the
> IN(SELECT ...) version with a JOIN (line 19-20 of the first paste) :
> 
> ->  Hash IN Join  (cost=240.95..4011.20 rows=1436 width=4) (actual
> time=93.971..201.908 rows=1431 loops=1)
>  Hash Cond: ("outer".id = "inner".specimen_id)
> 
> so I guess that :
> 
> select count(sp.id) from specimens sp where sp.id in (select
> specimen_id
> from sequences);
> 
> is almost the same as :
> 
> select count(sp.id) from specimens sp INNER JOIN (select specimen_id
> from sequences GROUP BY specimen_id) as foo on foo.specimen_id =
> sp.id;
> 
> ?
> 
> Thanks,
> Julien
> 
> On Thu, 2008-03-13 at 14:46 +0100, Bart Degryse wrote:
> > The chapter on indexes in the manual should give you a pretty good
> > idea on the why.
> > IN and EXISTS are not the only possibilities, you can also use inner
> > or outer joins.
> > Which solution performs best depends on the data, the database
> > version, the available indexes, ...
> > 
> > >>> Julien <[EMAIL PROTECTED]> 2008-03-13 15:47 >>>
> > Hello,
> > 
> > Does anyone has an idea why sometimes:
> > - select ... where ... in (select ...)
> > is faster than :
> > - select ... where ... exists(select ...)
> > and sometimes it's the opposite ?
> > 
> > I had such a situation, I've pasted the queries on:
> > http://rafb.net/p/KXNZ6892.html and http://rafb.net/p/jvo5DO38.html
> > 
> > It's running PostgreSQL 8.1 with an effective_cache_size of 3.
> > 
> > specimens.id is the primary key and there are indexes on
> > sequences(specimen_id) and specimen_measurements(specimen_id)
> > 
> > Is there a general "rule" to know when to use the in() version and
> > when
> > to use the exists() version ? Is it true to say that the exists()
> > version is more scalable (with many rows) than the in() version
> (from
> > the little tests I made it seems the case) ?
> > 
> > Thanks,
> > Julien
> > 
> > -- 
> > Julien Cigar
> > Belgian Biodiversity Platform
> > http://www.biodiversity.be
> > Université Libre de Bruxelles (ULB)
> > Campus de la Plaine CP 257
> > Bâtiment NO, Bureau 4 N4 115C (Niveau 4)
> > Boulevard du Triomphe, entrée ULB 2
> > B-1050 Bruxelles
> > Mail: [EMAIL PROTECTED]
> > @biobel: http://biobel.biodiversity.be/person/show/471
> > Tel : 02 650 57 52
> > 
> > 
> > -- 
> > Sent via pgsql-sql mailing list ([email protected])
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-sql
> > 
> -- 
> Julien Cigar
> Belgian Biodiversity Platform
> http://www.biodiversity.be
> Université Libre de Bruxelles (ULB)
> Campus de la Plaine CP 257
> Bâtiment NO, Bureau 4 N4 115C (Niveau 4)
> Boulevard du Triomphe, entrée ULB 2
> B-1050 Bruxelles
> Mail: [EMAIL PROTECTED]
> @biobel: http://biobel.biodiversity.be/person/show/471
> Tel : 02 650 57 52
> 
> 
> -- 
> Sent via pgsql-sql mailing list ([email protected])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
> 
-- 
Julien Cigar
Belgian Biodiversity Platform
http://www.biodiversity.be
Université Libre de Bruxelles (ULB)
Campus de la Plaine CP 257
Bâtiment NO, Bureau 4 N4 115C (Niveau 4)
Boulevard du Triomphe, entrée ULB 2
B-1050 Bruxelles
Mail: [EMAIL PROTECTED]
@biobel: http://biobel.biodiversity.be/person/show/471
Tel : 02 650 57 52


-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] in() VS exists()

2008-03-13 Thread Julien 
>From my experience I tend to avoid SELECT DISTINCT queries because it's
usually very slow with many rows ...

For my specific case the result is the same:

muridae=> select count(distinct s.specimen_id) from specimens sp INNER
JOIN sequences s on s.specimen_id = sp.id;
 count 
---
  1431
(1 row)

Time: 65.351 ms
muridae=> select count(sp.id) from specimens sp where sp.id in (select
specimen_id from sequences group by specimen_id);
 count 
---
  1431
(1 row)

Time: 66.371 ms

But to give an example, I have a table with ~1 000 000 rows where the
DISTINCT solution is more than 10 times slower :

muridae=> select count(distinct sp.id) from specimens sp INNER JOIN
specimen_measurements m ON m.specimen_id = sp.id;
 count 
---
 75241
(1 row)

Time: 15970.668 ms

muridae=> select count(sp.id) from specimens sp INNER JOIN (select
specimen_id from specimen_measurements GROUP BY specimen_id) as foo on
foo.specimen_id = sp.id;
 count 
---
 75241
(1 row)

Time: 1165.487 ms

Regards,
Julien

On Thu, 2008-03-13 at 15:28 +0100, Bart Degryse wrote:
> how about
> select count(distinct s.specimen_id) from specimens sp INNER JOIN
> sequences s 
> on s.specimen_id = sp.id;
> 
> 
> >>> Julien <[EMAIL PROTECTED]> 2008-03-13 17:27 >>>
> mmh no because it's a one to many relation (a specimen can have more
> than one sequence) :
> 
> muridae=> select count(sp.id) from specimens sp INNER JOIN sequences s
> on s.specimen_id = sp.id;
> count 
> ---
>   1536
> (1 row)
> 
> Time: 81.242 ms
> muridae=> select count(sp.id) from specimens sp where sp.id in (select
> specimen_id from sequences group by specimen_id);
> count 
> ---
>   1431
> (1 row)
> 
> Time: 81.736 ms
> muridae=> 
> 
> (of course this is a bad example, because I could just do: select
> count(specimen_id) from sequences group by specimen_id;, but in my
> application I have more fields coming from specimens of course)
> 
> Julien
> 
> On Thu, 2008-03-13 at 15:12 +0100, Bart Degryse wrote:
> > I think that just
> > select count(sp.id) from specimens sp INNER JOIN sequences s on
> > s.specimen_id = sp.id;
> > should be enough
> > 
> > >>> Julien <[EMAIL PROTECTED]> 2008-03-13 17:10 >>>
> > If I understood well the query plan, the planner optimize the
> > IN(SELECT ...) version with a JOIN (line 19-20 of the first paste) :
> > 
> > ->  Hash IN Join  (cost=240.95..4011.20 rows=1436 width=4) (actual
> > time=93.971..201.908 rows=1431 loops=1)
> >  Hash Cond: ("outer".id = "inner".specimen_id)
> > 
> > so I guess that :
> > 
> > select count(sp.id) from specimens sp where sp.id in (select
> > specimen_id
> > from sequences);
> > 
> > is almost the same as :
> > 
> > select count(sp.id) from specimens sp INNER JOIN (select specimen_id
> > from sequences GROUP BY specimen_id) as foo on foo.specimen_id =
> > sp.id;
> > 
> > ?
> > 
> > Thanks,
> > Julien
> > 
> > On Thu, 2008-03-13 at 14:46 +0100, Bart Degryse wrote:
> > > The chapter on indexes in the manual should give you a pretty good
> > > idea on the why.
> > > IN and EXISTS are not the only possibilities, you can also use
> inner
> > > or outer joins.
> > > Which solution performs best depends on the data, the database
> > > version, the available indexes, ...
> > > 
> > > >>> Julien <[EMAIL PROTECTED]> 2008-03-13 15:47 >>>
> > > Hello,
> > > 
> > > Does anyone has an idea why sometimes:
> > > - select ... where ... in (select ...)
> > > is faster than :
> > > - select ... where ... exists(select ...)
> > > and sometimes it's the opposite ?
> > > 
> > > I had such a situation, I've pasted the queries on:
> > > http://rafb.net/p/KXNZ6892.html and
> http://rafb.net/p/jvo5DO38.html
> > > 
> > > It's running PostgreSQL 8.1 with an effective_cache_size of 3.
> > > 
> > > specimens.id is the primary key and there are indexes on
> > > sequences(specimen_id) and specimen_measurements(specimen_id)
> > > 
> > > Is there a general "rule" to know when to use the in() version and
> > > when
> > > to use the exists() version ? Is it true to say that the exists()
> > > version is more scalable (with many rows) than the in() version
> > (from
> > > the little tests I made it seems the case) ?
> > > 
> > > Thanks,
> > > Julien
> > > 
> > > -- 
> > >

Re: [SQL] column type for pdf file

2011-05-18 Thread Julien Cigar 
Unless you've good reasons to do so it's best to store the file on the 
file system and the file name/path in the database ...


On 05/18/2011 22:20, Emi Lu wrote:

Hello,

To save pdf files into postgresql8.3, what is the best column type?

bytea, blob, etc?

Thank you,
Emi



<>
-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] column type for pdf file

2011-05-18 Thread Julien Cigar 

On 05/18/2011 23:00, Karsten Hilbert wrote:

On Wed, May 18, 2011 at 10:46:23PM +0200, Julien Cigar wrote:


Unless you've good reasons to do so it's best to store the file on
the file system

Why ?

If you suggest reasons are needed for storing the PDF in the
database I'd like to know the reasons for *not* doing so.



It increases the load, consume connections, but the biggest drawback is 
probably the memory consumption ..


IMHO storing binary data in a database is almost always a bad idea .. it 
could be OK to store things like avatars, small icons, etc, but 
certainly not to store files of several MB ... file systems are done for 
that !



Karsten


<>
-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] column type for pdf file

2011-05-18 Thread Julien Cigar 

On 05/18/2011 23:27, Karsten Hilbert wrote:

On Wed, May 18, 2011 at 11:21:43PM +0200, Julien Cigar wrote:


Unless you've good reasons to do so it's best to store the file on
the file system

Why ?

If you suggest reasons are needed for storing the PDF in the
database I'd like to know the reasons for *not* doing so.

It increases the load, consume connections, but the biggest drawback
is probably the memory consumption ..

Thanks. At least now the OP knows some of the reasoning for
not doing so :-)



Yep sorry, I answered a bit too fast :)


Karsten


<>
-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] WITH RECURSIVE question

2011-08-23 Thread Julien Cigar 

Hello,

I have a classic parent -> child relation (id, container_id) and I would 
like to find the full hierarchy for a child, something like


  A
 / \
B   C
|
D

given D I want {A,B,C}

WITH RECURSIVE hierarchy(level, container_id, titles, containers) AS 
(SELECT 1 AS level, container_id, ARRAY[title::text] AS titles,

ARRAY[container_id] AS containers
 FROM content
 WHERE id=984
UNION ALL
 SELECT hierarchy.level + 1, c.container_id,
array_prepend(c.title::text, titles),
array_prepend(c.container_id, containers)
 FROM content c
 JOIN hierarchy ON hierarchy.container_id = c.id
) SELECT titles, containers
  FROM hierarchy
  ORDER BY level DESC LIMIT 1;

which give me something like:

titles|  containers
--+--
 {Home,Templates,IAS} | {NULL,1,983}
(1 row)

This is exactly what I want, but I wondered if there are better ways to 
do it? Is it scalable?


(The final idea is to put that in a trigger, so that it will be executed 
only when a new row is added, or when the row is moved from a container 
to another one ..)


Thanks,
Julien

--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.
<>
-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] WITH RECURSIVE question

2011-08-23 Thread Julien Cigar 

mmh I made a little mistake: it was {A,B} of course, not {A,B,C}

On 08/23/2011 12:03, Julien Cigar wrote:

given D I want {A,B,C}



--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.
<>
-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] new table with a select

2011-08-25 Thread Julien Cigar 

create table foo as select * from bar;

just add "where 1=2" if you just want the schema

On 08/25/2011 11:44, [email protected] wrote:



Hello to all,


Can anybody tell me the sql instruction to create a new table with a
select of other table?

I need to create a new table based on paralell of a table of lines. Can
anybody post to me a example?

Can?t be a buffer, must be lines.

Thanks










--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.
<>
-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] where col1 not ilike ('str1%', 'str2%'... 'strN%') support?

2011-08-30 Thread Julien Cigar 

use a regex ?

On 08/30/2011 16:51, Emi Lu wrote:

Good morning,

Does psql provide something like the following query command?

select * from tablename
where col1 not ilike ('str1%', 'str2%'... 'strN%')

Thanks a lot!
Emi




--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.
<>
-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Correlating Asterisk CDRs

2011-12-07 Thread Julien Cigar 

Try to raise work_mem

On 12/07/2011 15:34, Raj Mathur (राज माथुर) wrote:

Hi,

I'm trying to correlate Call Data Records (CDRs) from two Asterisk
servers, one of which uses the other for telephony.  The data is in
the tables cdr and cdr2.  With some indexes, the query and explain
result are:

explain analyse select cdr.calldate, cdr2.calldate,
(cdr2.calldate-cdr.calldate) as rtdur, cdr.clid, cdr.dst, cdr2.src,
cdr2.dst, cdr2.dstchannel, cdr2.lastapp, cdr2.duration,
cdr2.disposition from cdr, cdr2 where cdr2.calldate>= cdr.calldate
and cdr.clid=cdr2.clid and cdr.dst=substring(cdr2.dst from 4) order by
cdr.calldate, cdr2.calldate, cdr.clid limit 100;

  QUERY PLAN
-
  Limit  (cost=46782.15..46782.40 rows=100 width=109) (actual 
time=4077.866..4078.054
rows=100 loops=1)
->   Sort  (cost=46782.15..46785.33 rows=1272 width=109) (actual 
time=4077.863..4077.926
rows=100 loops=1)
  Sort Key: cdr.calldate, cdr2.calldate, cdr.clid
  Sort Method:  top-N heapsort  Memory: 42kB
  ->   Merge Join  (cost=2.95..46733.54 rows=1272 width=109) (actual
time=0.070..3799.546 rows=168307 loops=1)
Merge Cond: (((cdr.clid)::text = (cdr2.clid)::text) AND 
((cdr.dst)::text =
"substring"((cdr2.dst)::text, 4)))
Join Filter: (cdr2.calldate>= cdr.calldate)
->   Index Scan using ick1 on cdr  (cost=0.00..34667.86 
rows=208798
width=43) (actual time=0.022..434.246 rows=208798 loops=1)
->   Index Scan using i2k1 on cdr2  (cost=0.00..9960.89 
rows=65449 width=88)
(actual time=0.011..391.599 rows=240981 loops=1)
  Total runtime: 4078.184 ms
(10 rows)

Is there any way to make this query faster?  I already have an index
i2k1 on substring(cdr2.dst from 4), which is being used.

Application
---

I'm looking for all caller records in cdr2 that have the same callerid
(clid) and destination (dst) and were started on cdr2 after they were
started on cdr.  cdr2.dst is the same as cdr.dst but with a
3-character prefix.

Regards,

-- Raj



--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.
<>
-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Correlating Asterisk CDRs

2011-12-07 Thread Julien Cigar 

On 12/07/2011 16:14, Raj Mathur (राज माथुर) wrote:

On Wednesday 07 Dec 2011, Julien Cigar wrote:

Try to raise work_mem


Odd, I tried adding work_mem=50MB / 256MB / 1024MB into postgres.conf
and the times actually went up to over 12 seconds.  Leaving it commented
results in the 4-second time originally posted.


sorry I replied too fast.. There is no external disk merge so increasing 
work_mem is useless. Problem is the "merge join". How big is the table? 
Are the statistics up to date?




Regards,

-- Raj


On 12/07/2011 15:34, Raj Mathur (राज माथुर) wrote:

I'm trying to correlate Call Data Records (CDRs) from two Asterisk
servers, one of which uses the other for telephony.  The data is in
the tables cdr and cdr2.  With some indexes, the query and explain
result are:

explain analyse select cdr.calldate, cdr2.calldate,
(cdr2.calldate-cdr.calldate) as rtdur, cdr.clid, cdr.dst, cdr2.src,
cdr2.dst, cdr2.dstchannel, cdr2.lastapp, cdr2.duration,
cdr2.disposition from cdr, cdr2 where cdr2.calldate>= cdr.calldate
and cdr.clid=cdr2.clid and cdr.dst=substring(cdr2.dst from 4) order
by cdr.calldate, cdr2.calldate, cdr.clid limit 100;

   QUERY

PLAN


---
---
---

   Limit  (cost=46782.15..46782.40 rows=100 width=109) (actual
   time=4077.866..4078.054

rows=100 loops=1)

 ->Sort  (cost=46782.15..46785.33 rows=1272 width=109)
 (actual time=4077.863..4077.926

rows=100 loops=1)

   Sort Key: cdr.calldate, cdr2.calldate, cdr.clid
   Sort Method:  top-N heapsort  Memory: 42kB
   ->Merge Join  (cost=2.95..46733.54 rows=1272
   width=109) (actual

time=0.070..3799.546 rows=168307 loops=1)

 Merge Cond: (((cdr.clid)::text = (cdr2.clid)::text)
 AND ((cdr.dst)::text =

"substring"((cdr2.dst)::text, 4)))

 Join Filter: (cdr2.calldate>= cdr.calldate)
 ->Index Scan using ick1 on cdr
 (cost=0.00..34667.86 rows=208798

width=43) (actual time=0.022..434.246 rows=208798 loops=1)

 ->Index Scan using i2k1 on cdr2
 (cost=0.00..9960.89 rows=65449 width=88)

(actual time=0.011..391.599 rows=240981 loops=1)

   Total runtime: 4078.184 ms

(10 rows)

Is there any way to make this query faster?  I already have an
index i2k1 on substring(cdr2.dst from 4), which is being used.

Application
---

I'm looking for all caller records in cdr2 that have the same
callerid (clid) and destination (dst) and were started on cdr2
after they were started on cdr.  cdr2.dst is the same as cdr.dst
but with a 3-character prefix.





--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.
<>
-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] [ADMIN] pg_dump : no tables were found.

2012-03-07 Thread Julien Rouhaud 
On Tue, Mar 6, 2012 at 7:22 AM, Piyush Lenka  wrote:

> Hi,
>
> I m trying to take backup of data of a particular table using pg_dump.
> I used double quotes for table name but output is :
> pg_dump : no tables were found.
>
> Command used :
> -h localhost -p 5432 -U postgres -W -F p -a -t '"TestTable"' -f
> DbBackup/BackupTableActions.sql TestDataBase
>
> This problem only shows when there is a upper case character in my table
> name.
> Please Help
>
> Thanks & Regards
> Piyush
>

Hi
You can try -t '"TestTable"' or -t \"TestTable\"

[SQL] bad plan

2012-04-05 Thread Julien Cigar 

Hello,

I have an extremely bad plan for one of my colleague's query. Basically 
PostgreSQL chooses to seq scan instead of index scan. This is on:


antabif=# select version();
 version
--
 PostgreSQL 9.0.7 on amd64-portbld-freebsd8.2, compiled by GCC cc (GCC) 
4.2.1 20070719  [FreeBSD], 64-bit


The machines has 4GB of RAM with the following config:
- shared_buffers: 512MB
- effective_cache_size: 2GB
- work_mem: 32MB
- maintenance_work_mem: 128MB
- default_statistics_target: 300
- temp_buffers: 64MB
- wal_buffers: 8MB
- checkpoint_segments = 15

The tables have been ANALYZE'd. I've put the EXPLAIN ANALYZE on:

- http://www.pastie.org/3731956 : with default config
- http://www.pastie.org/3731960 : this is with enable_seq_scan = off
- http://www.pastie.org/3731962 : I tried to play on the various cost 
settings but it's doesn't change anything, except setting 
random_page_cost to 1 (which will lead to bad plans for other queries, 
so not a solution)
- http://www.pastie.org/3732035 : with enable_hashagg and 
enable_hashjoin to false


I'm currently out of idea why PostgreSQL still chooses a bad plan for 
this query ... any hint :) ?


Thank you,
Julien

--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.

<>
-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] bad plan

2012-04-05 Thread Julien Cigar 

Hello Mario,

The query is in the pastie.org links, here is a copy:

SELECT
  t_geo.frequence, ST_SetSRID(gc.geom, 4326) as geom
FROM (
  SELECT
geo_id , count(*) AS frequence
  FROM
hit.context_to_context_links
  WHERE
NOT geo_id IS NULL ANDtaxon_id= ANY (
  SELECT
taxon_id
  FROM
rab.ancestors
  WHERE
ancestors.subphylum_id = 18830
  ) GROUP BY geo_id
) as t_geo
JOIN
  hit.geo_candidates gc ON gc.id = t_geo.geo_id;

Thank you,
Julien

On 04/05/2012 14:03, Mario Dankoor wrote:

Julien,

It looks like you forgot to post the query.

Mario
On 2012-04-05 1:38 PM, Julien Cigar wrote:

Hello,

I have an extremely bad plan for one of my colleague's query. 
Basically PostgreSQL chooses to seq scan instead of index scan. This 
is on:


antabif=# select version();
 version
-- 

 PostgreSQL 9.0.7 on amd64-portbld-freebsd8.2, compiled by GCC cc 
(GCC) 4.2.1 20070719  [FreeBSD], 64-bit


The machines has 4GB of RAM with the following config:
- shared_buffers: 512MB
- effective_cache_size: 2GB
- work_mem: 32MB
- maintenance_work_mem: 128MB
- default_statistics_target: 300
- temp_buffers: 64MB
- wal_buffers: 8MB
- checkpoint_segments = 15

The tables have been ANALYZE'd. I've put the EXPLAIN ANALYZE on:

- http://www.pastie.org/3731956 : with default config
- http://www.pastie.org/3731960 : this is with enable_seq_scan = off
- http://www.pastie.org/3731962 : I tried to play on the various cost 
settings but it's doesn't change anything, except setting 
random_page_cost to 1 (which will lead to bad plans for other 
queries, so not a solution)
- http://www.pastie.org/3732035 : with enable_hashagg and 
enable_hashjoin to false


I'm currently out of idea why PostgreSQL still chooses a bad plan for 
this query ... any hint :) ?


Thank you,
Julien








--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.

<>
-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] bad plan

2012-04-05 Thread Julien Cigar 

Hi Mario,

Setting it to > -1, or even removing the condition doesn't change 
anything .. the problem is more that the estimate of the join point 
sucks pretty much ..


Julien

On 04/05/2012 14:32, Mario Dankoor wrote:

Julien,

I generally try avoid NOT predicates.
If your geo_id is an integer,  try geo_id > -1
or if it's a varchar geo_id > ''.
The idea is that geo_id > 0 is false for null values.

I don't think query optimizers are fond of NOT predicates.

SELECT  t_geo.frequence
   ,ST_SetSRID(gc.geom, 4326) as geom
FROM (
   SELECT  geo_id
  ,count(*) AS frequence
   FROM hit.context_to_context_links
   WHERE geo_id > -1
   AND taxon_id= ANY (
  SELECT taxon_id
  FROM rab.ancestors
  WHERE ancestors.subphylum_id = 18830
) GROUP BY geo_id
 ) as t_geo
JOIN  hit.geo_candidates gc
ON gc.id = t_geo.geo_id;









On 2012-04-05 2:08 PM, Julien Cigar wrote:

SELECT
  t_geo.frequence, ST_SetSRID(gc.geom, 4326) as geom
FROM (
  SELECT
geo_id , count(*) AS frequence
  FROM
hit.context_to_context_links
  WHERE
NOT geo_id IS NULL ANDtaxon_id= ANY (
  SELECT
taxon_id
  FROM
rab.ancestors
  WHERE
ancestors.subphylum_id = 18830
  ) GROUP BY geo_id
) as t_geo
JOIN
  hit.geo_candidates gc ON gc.id = t_geo.geo_id; 






--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.

<>
-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] bad plan

2012-04-05 Thread Julien Cigar 

FYI, raising cpu_tuple_cost from 0.01 to 0.5 fixed the problem..!

On 04/05/2012 14:43, Julien Cigar wrote:

Hi Mario,

Setting it to > -1, or even removing the condition doesn't change 
anything .. the problem is more that the estimate of the join point 
sucks pretty much ..


Julien

On 04/05/2012 14:32, Mario Dankoor wrote:

Julien,

I generally try avoid NOT predicates.
If your geo_id is an integer,  try geo_id > -1
or if it's a varchar geo_id > ''.
The idea is that geo_id > 0 is false for null values.

I don't think query optimizers are fond of NOT predicates.

SELECT  t_geo.frequence
   ,ST_SetSRID(gc.geom, 4326) as geom
FROM (
   SELECT  geo_id
  ,count(*) AS frequence
   FROM hit.context_to_context_links
   WHERE geo_id > -1
   AND taxon_id= ANY (
  SELECT taxon_id
  FROM rab.ancestors
  WHERE ancestors.subphylum_id = 18830
) GROUP BY geo_id
 ) as t_geo
JOIN  hit.geo_candidates gc
ON gc.id = t_geo.geo_id;









On 2012-04-05 2:08 PM, Julien Cigar wrote:

SELECT
  t_geo.frequence, ST_SetSRID(gc.geom, 4326) as geom
FROM (
  SELECT
geo_id , count(*) AS frequence
  FROM
hit.context_to_context_links
  WHERE
NOT geo_id IS NULL ANDtaxon_id= ANY (
  SELECT
taxon_id
  FROM
rab.ancestors
  WHERE
ancestors.subphylum_id = 18830
  ) GROUP BY geo_id
) as t_geo
JOIN
  hit.geo_candidates gc ON gc.id = t_geo.geo_id; 











--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.

<>
-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] query doesn't always follow 'correct' path..

2013-02-18 Thread Julien Cigar 

On 02/18/2013 15:39, Bert wrote:

Hello,

Thanks the nice people on irc my problem is fixed.
I changed the following settings in the postgres.conf file:
default_statistics_target = 5000 -> and I analyzed the tables after 
the change of course -> now I only got 2 plans anymore, in stead of 3


default_statistics_target = 5000 as a default is *way* too high. Such 
high values should only be set on a per-column basis ...


cpu_tuple_cost = 0.1 -> by setting this value the seq scans were 
stopped, and the better index_only scan / bitmap index scan were used 
for this query.


Thank you Robe and Mabe_ for helping me with this issue!


s/Mabe_/Mage_ :-)



wkr,
Bert


On Mon, Feb 18, 2013 at 2:42 PM, Bert > wrote:


Hello,

yes, the tables are vacuumed every day with the following command:
vacuum analyze schema.table.
The last statistics were collected yesterday evening. I collected
statistics about the statistics, and I found the following:
table_name; starttime; runtime
"st_itemseat";"2013-02-17 23:48:42";"00:01:02"
"st_itemseat_45";"2013-02-17 23:35:15";"00:00:08"
"st_itemzone";"2013-02-17 23:35:33";"00:00:01"

st_itemseat_45 is a child-partition of st_itemseat.

They seem to be pretty much up to date I guess?
I also don't get any difference in the query plans when they are
run in the morning, or in the evening.

I have also run the query with set seq_scan to off, and then I get
the following output:
Total query runtime: 12025 ms.
20599 rows retrieved.
and the following plan: http://explain.depesz.com/s/yaJK

These are 3 different plans. And the last one is blazingly fast.
That's the one I would always want to use :-)

it's also weird that this is default plan for the biggest
partition. But the smaller the partition gets, the smaller the
partition gets.
So I don't think it has anything to do with the memory settings.
Since it already chooses this plan for the bigger partitions...

wkr,
Bert


On Mon, Feb 18, 2013 at 11:51 AM, Frank Lanitz
mailto:[email protected]>> wrote:

Am 18.02.2013 10:43, schrieb Bert:
> Does anyone has an idea what triggers this bad plan, and how
I can fix it?

Looks a bit like wrong statistics. Are the statistiks for your
tables
correct?

Cheers,
Frank


--
Sent via pgsql-sql mailing list ([email protected]
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql




-- 
Bert Desmet

0477/305361




--
Bert Desmet
0477/305361



--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.

Re: [SQL] query doesn't always follow 'correct' path..

2013-02-18 Thread Julien Cigar 

On 02/18/2013 16:20, Julien Cigar wrote:

On 02/18/2013 15:39, Bert wrote:

Hello,

Thanks the nice people on irc my problem is fixed.
I changed the following settings in the postgres.conf file:
default_statistics_target = 5000 -> and I analyzed the tables after 
the change of course -> now I only got 2 plans anymore, in stead of 3


default_statistics_target = 5000 as a default is *way* too high. Such 
high values should only be set on a per-column basis ...


oops.. it's per-table and not per-column



cpu_tuple_cost = 0.1 -> by setting this value the seq scans were 
stopped, and the better index_only scan / bitmap index scan were used 
for this query.


Thank you Robe and Mabe_ for helping me with this issue!


s/Mabe_/Mage_ :-)



wkr,
Bert


On Mon, Feb 18, 2013 at 2:42 PM, Bert <mailto:[email protected]>> wrote:


Hello,

yes, the tables are vacuumed every day with the following
command: vacuum analyze schema.table.
The last statistics were collected yesterday evening. I collected
statistics about the statistics, and I found the following:
table_name; starttime; runtime
"st_itemseat";"2013-02-17 23:48:42";"00:01:02"
"st_itemseat_45";"2013-02-17 23:35:15";"00:00:08"
"st_itemzone";"2013-02-17 23:35:33";"00:00:01"

st_itemseat_45 is a child-partition of st_itemseat.

They seem to be pretty much up to date I guess?
I also don't get any difference in the query plans when they are
run in the morning, or in the evening.

I have also run the query with set seq_scan to off, and then I
get the following output:
Total query runtime: 12025 ms.
20599 rows retrieved.
and the following plan: http://explain.depesz.com/s/yaJK

These are 3 different plans. And the last one is blazingly fast.
That's the one I would always want to use :-)

it's also weird that this is default plan for the biggest
partition. But the smaller the partition gets, the smaller the
partition gets.
So I don't think it has anything to do with the memory settings.
Since it already chooses this plan for the bigger partitions...

wkr,
Bert


On Mon, Feb 18, 2013 at 11:51 AM, Frank Lanitz
mailto:[email protected]>> wrote:

Am 18.02.2013 10:43, schrieb Bert:
> Does anyone has an idea what triggers this bad plan, and
how I can fix it?

Looks a bit like wrong statistics. Are the statistiks for
your tables
correct?

Cheers,
Frank


--
Sent via pgsql-sql mailing list ([email protected]
<mailto:[email protected]>)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql




-- 
Bert Desmet

0477/305361




--
Bert Desmet
0477/305361



--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.



--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.

[SQL] ALL() question

2007-11-14 Thread Julien Cigar 
Hello,

I have a problem with the ALL() subquery expression.
I have three tables:
- specimens
- test_bits
- specimen_test_bits

The specimen_test_bits table contains two foreign keys, one to
specimens(id), another to test_bits(id). 

Here is an output of specimen_test_bits:

muridae=> select * from specimen_test_bits;
 specimen_id | test_bit_id 
-+-
   46096 |   1
   46096 |   2
   46096 |   3
   46096 |   4
   52894 |   1
   52894 |   3
   12546 |   2

What I would like is a query that returns all the specimen_id of 
this table which have _all_ the given test_bit_id. So in this
case, with test_bit_id 1,2,3,4 it should return only 
specimen_id 46096.

With the following I got a syntax error:
select specimen_id 
from specimen_test_bits 
where test_bit_id = all(1,2,3,4);

The following works but no rows are returned :
select specimen_id 
from specimen_test_bits 
where test_bit_id = all(select id from test_bits where id in (1,2,3,4));

Any idea how I could do this ? I guess the problem is my ALL() expression ...

In advance thanks,

Julien


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

   http://www.postgresql.org/docs/faq

Re: [SQL] ALL() question

2007-11-14 Thread Julien Cigar 

On Wed, 2007-11-14 at 11:56 +, Richard Huxton wrote:
> Julien Cigar wrote:
> > 
> > What I would like is a query that returns all the specimen_id of 
> > this table which have _all_ the given test_bit_id. 
> [snip]
> > With the following I got a syntax error:
> > select specimen_id 
> > from specimen_test_bits 
> > where test_bit_id = all(1,2,3,4);
> 
> It's expecting an array here. You'd have to write
>   = all('{1,2,3,4}')
> But that would have the same problem as...
> 
> > The following works but no rows are returned :
> > select specimen_id 
> > from specimen_test_bits 
> > where test_bit_id = all(select id from test_bits where id in (1,2,3,4));
> 
> It's testing each row individually and of course one row can't match ALL 
> four values.
> 
> What you want to do is count the distinct values. Something like:
> 
> SELECT
>specimen_id
> FROM foo
> GROUP BY
>specimen_id
> HAVING
>count(distinct test_bit_id) = 4
> ;
> 

I don't think it would work, for example if I have:
specimen_id | test_bit_id
+
   100 1
   100 3
   101 1
   101 2

the test_bit_ids are parameters, so with the given test_bit_id 1,3 it
would return specimen_id 101 too, which I don't want ...
What I would like is the specimen_id which match _exactly_ the given
test_bit_ids, so it should return only 100 in this example ..

from the documentation ALL() can take a subquery too, not only an ARRAY
(http://www.postgresql.org/docs/8.2/static/functions-subquery.html)




---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Re: [SQL] ALL() question

2007-11-14 Thread Julien Cigar 
I finally found a solution:

SELECT specimen_id 
FROM specimen_test_bits 
GROUP BY specimen_id 
HAVING array_accum(test_bit_id) =  '{2,3,4}';

.. but I don't think it's very "clean" ..

what do you think ?

Thanks

On Wed, 2007-11-14 at 15:50 +0100, Julien Cigar wrote:
> On Wed, 2007-11-14 at 11:56 +, Richard Huxton wrote:
> > Julien Cigar wrote:
> > > 
> > > What I would like is a query that returns all the specimen_id of 
> > > this table which have _all_ the given test_bit_id. 
> > [snip]
> > > With the following I got a syntax error:
> > > select specimen_id 
> > > from specimen_test_bits 
> > > where test_bit_id = all(1,2,3,4);
> > 
> > It's expecting an array here. You'd have to write
> >   = all('{1,2,3,4}')
> > But that would have the same problem as...
> > 
> > > The following works but no rows are returned :
> > > select specimen_id 
> > > from specimen_test_bits 
> > > where test_bit_id = all(select id from test_bits where id in (1,2,3,4));
> > 
> > It's testing each row individually and of course one row can't match ALL 
> > four values.
> > 
> > What you want to do is count the distinct values. Something like:
> > 
> > SELECT
> >specimen_id
> > FROM foo
> > GROUP BY
> >specimen_id
> > HAVING
> >count(distinct test_bit_id) = 4
> > ;
> > 
> 
> I don't think it would work, for example if I have:
> specimen_id | test_bit_id
> +
>100   1
>100 3
>101 1
>101 2
> 
> the test_bit_ids are parameters, so with the given test_bit_id 1,3 it
> would return specimen_id 101 too, which I don't want ...
> What I would like is the specimen_id which match _exactly_ the given
> test_bit_ids, so it should return only 100 in this example ..
> 
> from the documentation ALL() can take a subquery too, not only an ARRAY
> (http://www.postgresql.org/docs/8.2/static/functions-subquery.html)
> 
> 


---(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] ALL() question

2007-11-14 Thread Julien Cigar 
Thanks :) it works as expected 

Julien

On Wed, 2007-11-14 at 14:31 +0100, hubert depesz lubaczewski wrote:
> On Wed, Nov 14, 2007 at 02:39:02PM +0100, Julien Cigar wrote:
> > With the following I got a syntax error:
> > select specimen_id 
> > from specimen_test_bits 
> > where test_bit_id = all(1,2,3,4);
> 
> where test_bit_id in (1,2,3,4)
> group by specimen_id
> having count(distinct test_bit_id) = 4;
> 
> depesz
> 


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

[SQL] how to check if a point is contained in a polygon ?

2008-05-07 Thread Julien Cigar 
Hello,

I'm busy to work on an application where the user can select (with
precision) an area on a map (for example the contours of a lake) and I
have to retrieve all the data (specimen observations) within this area. 

I have a list of coordinates pair [(lat1, long1), (lat2, long2), (lat3,
long3), (..., ...)] which form a polygon. In my database every specimen
has a pair of coordinates where it has been collected.

Is there an SQL function to check if a point is contained in a polygon
shape (before I start to write my own) ?

I tried something like :

rodentia=> select point '(-8,25)' <@ polygon
'((-3,10),(8,18),(-3,30),(-10,20))';
ERROR:  operator does not exist: point <@ polygon
HINT:  No operator matches the given name and argument type(s). You may
need to add explicit type casts.

... but without success as you can see ;\

In advance thanks,
Julien

-- 
Julien Cigar
Belgian Biodiversity Platform
http://www.biodiversity.be
Université Libre de Bruxelles (ULB)
Campus de la Plaine CP 257
Bâtiment NO, Bureau 4 N4 115C (Niveau 4)
Boulevard du Triomphe, entrée ULB 2
B-1050 Bruxelles
Mail: [EMAIL PROTECTED]
@biobel: http://biobel.biodiversity.be/person/show/471
Tel : 02 650 57 52


-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] how to check if a point is contained in a polygon ?

2008-05-07 Thread Julien Cigar 
Oops, you're right .. I'm still running 8.1 and I missed this note in
the documentation :

"Note: Before PostgreSQL 8.2, the containment operators @> and <@ were
respectively called ~ and @. These names are still available, but are
deprecated and will eventually be retired."

thanks

On Wed, 2008-05-07 at 15:38 +0100, Richard Huxton wrote:
> Julien Cigar wrote:
> > Hello,
> > 
> > I'm busy to work on an application where the user can select (with
> > precision) an area on a map (for example the contours of a lake) and I
> > have to retrieve all the data (specimen observations) within this area. 
> > 
> > I have a list of coordinates pair [(lat1, long1), (lat2, long2), (lat3,
> > long3), (..., ...)] which form a polygon. In my database every specimen
> > has a pair of coordinates where it has been collected.
> 
> Are you familiar with the PostGIS project - if you're doing a lot of 
> this, it might be worthwhile looking into.
> 
> http://www.postgis.org/
> 
> > Is there an SQL function to check if a point is contained in a polygon
> > shape (before I start to write my own) ?
> > 
> > I tried something like :
> > 
> > rodentia=> select point '(-8,25)' <@ polygon
> > '((-3,10),(8,18),(-3,30),(-10,20))';
> 
> Works both ways around here:
> 
> SELECT point '(-8,25)' <@ polygon '((-3,10),(8,18),(-3,30),(-10,20))';
> 
> SELECT polygon '((-3,10),(8,18),(-3,30),(-10,20))' @> point '(-8,25)';
> 
> I'm running 8.3, but it seems to be in 8.2 too.
> 
> Try \do '<@' from psql to see what operators are available.
> 
-- 
Julien Cigar
Belgian Biodiversity Platform
http://www.biodiversity.be
Université Libre de Bruxelles (ULB)
Campus de la Plaine CP 257
Bâtiment NO, Bureau 4 N4 115C (Niveau 4)
Boulevard du Triomphe, entrée ULB 2
B-1050 Bruxelles
Mail: [EMAIL PROTECTED]
@biobel: http://biobel.biodiversity.be/person/show/471
Tel : 02 650 57 52


-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Concat field result in select query

2008-08-22 Thread Julien Cigar 
array_accum ?

On Fri, 2008-08-22 at 15:40 +0200, Nacef LABIDI wrote:
> Hi all,
> 
> I was wondering if there is a way to concatenate the results of a
> field in a select to return it as a single string.
> 
> Example :
> 
> my_field
> --
> 1
> 2
> 3
> 
> select concat_something(my_field) from my_table group by something;
> the result expected would be someting like that : 1/2/3 (with a
> separator it would be really nice)
> 
> I hope that I am not asking for too much ;)
> 
> Thanks to all
> 
-- 
Julien Cigar
Belgian Biodiversity Platform
http://www.biodiversity.be
Université Libre de Bruxelles (ULB)
Campus de la Plaine CP 257
Bâtiment NO, Bureau 4 N4 115C (Niveau 4)
Boulevard du Triomphe, entrée ULB 2
B-1050 Bruxelles
Mail: [EMAIL PROTECTED]
@biobel: http://biobel.biodiversity.be/person/show/471
Tel : 02 650 57 52


-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Concat field result in select query

2008-08-22 Thread Julien Cigar 
Also, the array_accum aggregate isn't present by default, you need to
create it with :

CREATE AGGREGATE array_accum (anyelement)
(
sfunc = array_append,
stype = anyarray,
initcond = '{}'
);

On Fri, 2008-08-22 at 18:24 +0200, Julien Cigar wrote:
> of course: http://rafb.net/p/EOaYfO59.html
> 
> Julien
> 
> On Fri, 2008-08-22 at 15:58 +0200, Nacef LABIDI wrote:
> > any explanations ?
> > 
> > On Fri, Aug 22, 2008 at 6:07 PM, Julien Cigar <[EMAIL PROTECTED]>
> > wrote:
> > array_accum ?
> > 
> > 
> > On Fri, 2008-08-22 at 15:40 +0200, Nacef LABIDI wrote:
> > > Hi all,
> > >
> > > I was wondering if there is a way to concatenate the results
> > of a
> > > field in a select to return it as a single string.
> > >
> > > Example :
> > >
> > > my_field
> > > --
> > > 1
> > > 2
> > > 3
> > >
> > > select concat_something(my_field) from my_table group by
> > something;
> > > the result expected would be someting like that : 1/2/3
> >     (with a
> > > separator it would be really nice)
> > >
> > > I hope that I am not asking for too much ;)
> > >
> > > Thanks to all
> > >
> > 
> > --
> > Julien Cigar
> > Belgian Biodiversity Platform
> > http://www.biodiversity.be
> > Université Libre de Bruxelles (ULB)
> > Campus de la Plaine CP 257
> > Bâtiment NO, Bureau 4 N4 115C (Niveau 4)
> > Boulevard du Triomphe, entrée ULB 2
> > B-1050 Bruxelles
> > Mail: [EMAIL PROTECTED]
> > @biobel: http://biobel.biodiversity.be/person/show/471
> > Tel : 02 650 57 52
> > 
> > 
> > 
-- 
Julien Cigar
Belgian Biodiversity Platform
http://www.biodiversity.be
Université Libre de Bruxelles (ULB)
Campus de la Plaine CP 257
Bâtiment NO, Bureau 4 N4 115C (Niveau 4)
Boulevard du Triomphe, entrée ULB 2
B-1050 Bruxelles
Mail: [EMAIL PROTECTED]
@biobel: http://biobel.biodiversity.be/person/show/471
Tel : 02 650 57 52


-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Concat field result in select query

2008-08-22 Thread Julien Cigar 
of course: http://rafb.net/p/EOaYfO59.html

Julien

On Fri, 2008-08-22 at 15:58 +0200, Nacef LABIDI wrote:
> any explanations ?
> 
> On Fri, Aug 22, 2008 at 6:07 PM, Julien Cigar <[EMAIL PROTECTED]>
> wrote:
> array_accum ?
> 
> 
> On Fri, 2008-08-22 at 15:40 +0200, Nacef LABIDI wrote:
> > Hi all,
> >
> > I was wondering if there is a way to concatenate the results
> of a
> > field in a select to return it as a single string.
> >
> > Example :
> >
> > my_field
> > --
> > 1
> > 2
> > 3
> >
> > select concat_something(my_field) from my_table group by
> something;
> > the result expected would be someting like that : 1/2/3
> (with a
> > separator it would be really nice)
> >
> > I hope that I am not asking for too much ;)
> >
> > Thanks to all
> >
> 
> --
> Julien Cigar
> Belgian Biodiversity Platform
> http://www.biodiversity.be
> Université Libre de Bruxelles (ULB)
> Campus de la Plaine CP 257
> Bâtiment NO, Bureau 4 N4 115C (Niveau 4)
> Boulevard du Triomphe, entrée ULB 2
> B-1050 Bruxelles
> Mail: [EMAIL PROTECTED]
> @biobel: http://biobel.biodiversity.be/person/show/471
> Tel : 02 650 57 52
> 
> 
> 
-- 
Julien Cigar
Belgian Biodiversity Platform
http://www.biodiversity.be
Université Libre de Bruxelles (ULB)
Campus de la Plaine CP 257
Bâtiment NO, Bureau 4 N4 115C (Niveau 4)
Boulevard du Triomphe, entrée ULB 2
B-1050 Bruxelles
Mail: [EMAIL PROTECTED]
@biobel: http://biobel.biodiversity.be/person/show/471
Tel : 02 650 57 52


-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql