Re: [SQL] Ugly group by problem

[Markus Schaber]

Hi, Achilleus,

Achilleus Mantzios wrote:

> foodb=# SELECT qoo.foo2,sum(qoo.foo3) from (SELECT mt.link_id as 
> foo,_int_union(array(select mt2.feat_id from markustest mt2 where 
> mt2.link_id=mt.link_id order by mt2.feat_id),'{}') as foo2,other::int4 as 
> foo3 from markustest mt) as qoo GROUP BY qoo.foo2;
>   foo2  | sum
> +-
>  {2}|   1
>  {5,23} |  13
>  {23}   |  14
> (3 rows)

This is much like I intended to do it, but using "select distinct" in
the inner select as I don't have _int_union here, and using a temporary
table to collect the sets of link ids.

Markus


-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org

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

Re: [SQL] Ugly group by problem

[Achilleus Mantzios]

O Markus Schaber έγραψε στις Mar 29, 2006 :

> Hi, Achilleus,
> 
> Achilleus Mantzios wrote:
> 
> > foodb=# SELECT qoo.foo2,sum(qoo.foo3) from (SELECT mt.link_id as 
> > foo,_int_union(array(select mt2.feat_id from markustest mt2 where 
> > mt2.link_id=mt.link_id order by mt2.feat_id),'{}') as foo2,other::int4 as 
> > foo3 from markustest mt) as qoo GROUP BY qoo.foo2;
> >   foo2  | sum
> > +-
> >  {2}|   1
> >  {5,23} |  13
> >  {23}   |  14
> > (3 rows)
> 
> This is much like I intended to do it, but using "select distinct" in
> the inner select as I don't have _int_union here, and using a temporary
> table to collect the sets of link ids.

Just some facts:

sort(uniq(int[])) has the same effect as select DISTINCT... ORDER BY
DISTINCT.  ORDER BY is ofcourse more intuitive and does not need 
contrib/intarray.

In fact , i dont even remember why i didnt write the query with 
DISTINCT... in the first place :)

> 
> Markus
> 
> 
> 

-- 
-Achilleus


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [SQL] have you feel anything when you read this ?

[Eugene E.]

Peter Eisentraut wrote:

Eugene E. wrote:


the problem is: you'll get this four byte sequence '\000' _instead_
of NUL-byte anyway.



What you seem to be missing is that PostgreSQL data can be represented 
in textual and in binary form.  What you in psql is the textual form.  
If you want the binary form you need to select it.  Then you can pass 
the exact bytes back and forth.


your sentence is not true.
I can not select exact bytes even if i use BYTEA type

the folloiwing tiny C-program shows this pretty clear

#include 
#include 
#include "libpq-fe.h"



int
main (void)
 {
  PGconn   * conn;
  PGresult * res;
  char * val;
  char *   l;
  intlen;



  conn = PQconnectdb("user=scott password=tiger dbname=test_db");

  PQexec(conn, "CREATE TABLE t (a BYTEA)");
  PQexec(conn, "INSERT INTO t VALUES ('ab000cd')");


  res = PQexec(conn, "SELECT a,length(a) FROM t");
  val = PQgetvalue(res,0,0);
  l   = PQgetvalue(res,0,1);
  len = PQgetlength(res,0,0);
  printf("what_we_retrive='%s' its_value_length=%i but 
orig_length=%s\n",val,len,l);


  PQclear(res);
  PQfinish(conn);

  return 0;
 }

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly