Re: [SQL] Jagged/ragged arrays
* Craig Ringer:
> On 21/09/2010 3:42 AM, Dmitriy Igrishin wrote:
>> Hey Florian,
>>
>> What do you mean by "ragged" arrays?
>
> At a guess:
>
> craig=> SELECT '{ {1}, {1,2}, {1}, {1,2,3} }'::integer[][];
> ERROR: multidimensional arrays must have array expressions with
> matching dimensions
>
> (OP) Correct?
Yes, this is what I'm after.
--
Florian Weimer
BFK edv-consulting GmbH http://www.bfk.de/
Kriegsstraße 100 tel: +49-721-96201-1
D-76133 Karlsruhe fax: +49-721-96201-99
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Jagged/ragged arrays
Hey Craig, Florian
Ahh, this. Why not just yes NULLs as values of array?
2010/9/21 Craig Ringer
> On 21/09/2010 3:42 AM, Dmitriy Igrishin wrote:
>
>> Hey Florian,
>>
>> What do you mean by "ragged" arrays?
>>
>
> At a guess:
>
> craig=> SELECT '{ {1}, {1,2}, {1}, {1,2,3} }'::integer[][];
> ERROR: multidimensional arrays must have array expressions with matching
> dimensions
>
> (OP) Correct?
>
> --
> Craig Ringer
>
> Tech-related writing at http://soapyfrogs.blogspot.com/
>
--
Regards,
Dmitriy
Re: [SQL] all the table values equal
Oliveiros d'Azevedo Cristina ha scritto: Hello again, Michele, Ciao, I haven't open my mailbox during weekend so I couldn't follow up your question. No problem! It would help if you explain a little better the background of the problem you're trying to solve. You want to find all the user IDs which have the same value on a given time interval? Is my understanding correct? Yes. Re-reading my post I saw that I could explain better! id_user | value | datetime 1 | 1 | xxx 1 | 2 | xxx+1 1 | -1 | xxx+2 2 | -1 | xxx 2 | -1 | xxx+1 3 | 4 | xxx 3 | 10 | xxx+1 3 | 4 | xxx+2 4 | 3 | xxx 4 | 3 | xxx+1 So, the new question: how I can find which id_user has _all_ the "value" that I'm looking for? Say -1 as 3 and I want a id_user=2 for the first and for the latter id_user=4 Thanks, Michele Best, Oliver - Original Message - From: "Michele Petrazzo - Unipex" To: "Oliveiros d'Azevedo Cristina" Cc: Sent: Friday, September 17, 2010 4:45 PM Subject: Re: [SQL] all the table values equal Oliveiros d'Azevedo Cristina ha scritto: Howdy , Michele, Give this a try SELECT id_user FROM t_your_table WHERE datetime BETWEEN A -- plug here beginning of interval AND B -- and end here GROUP BY id_user HAVING COUNT(*) = -SUM(value) Then tell me if it gives you what you want Thanks, it works, but... it's really a trickle that exploits the value -1 if I understand how its work. If there is another value where look for? Example 13? Thanks -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] all the table values equal
I'm not sure if I understood you right, but it sounds similar to a case which I faced recently. Why not to use an inverse approach? In other words: trying to find those registries which hasn't got at least one value which differs from which you want to look for. How? Using the EXISTS function with some kind of subquery I guess. On 21/09/10 10:11, Michele Petrazzo - Unipex wrote: Oliveiros d'Azevedo Cristina ha scritto: Hello again, Michele, Ciao, I haven't open my mailbox during weekend so I couldn't follow up your question. No problem! It would help if you explain a little better the background of the problem you're trying to solve. You want to find all the user IDs which have the same value on a given time interval? Is my understanding correct? Yes. Re-reading my post I saw that I could explain better! id_user | value | datetime 1 | 1 | xxx 1 | 2 | xxx+1 1 | -1 | xxx+2 2 | -1 | xxx 2 | -1 | xxx+1 3 | 4 | xxx 3 | 10 | xxx+1 3 | 4 | xxx+2 4 | 3 | xxx 4 | 3 | xxx+1 So, the new question: how I can find which id_user has _all_ the "value" that I'm looking for? Say -1 as 3 and I want a id_user=2 for the first and for the latter id_user=4 Thanks, Michele Best, Oliver - Original Message - From: "Michele Petrazzo - Unipex" To: "Oliveiros d'Azevedo Cristina" Cc: Sent: Friday, September 17, 2010 4:45 PM Subject: Re: [SQL] all the table values equal Oliveiros d'Azevedo Cristina ha scritto: Howdy , Michele, Give this a try SELECT id_user FROM t_your_table WHERE datetime BETWEEN A -- plug here beginning of interval AND B -- and end here GROUP BY id_user HAVING COUNT(*) = -SUM(value) Then tell me if it gives you what you want Thanks, it works, but... it's really a trickle that exploits the value -1 if I understand how its work. If there is another value where look for? Example 13? Thanks -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] all the table values equal
It would help if you explain a little better the background of the problem you're trying to solve. You want to find all the user IDs which have the same value on a given time interval? Is my understanding correct? Yes. Re-reading my post I saw that I could explain better! id_user | value | datetime 1 | 1 | xxx 1 | 2 | xxx+1 1 | -1 | xxx+2 2 | -1 | xxx 2 | -1 | xxx+1 3 | 4 | xxx 3 | 10 | xxx+1 3 | 4 | xxx+2 4 | 3 | xxx 4 | 3 | xxx+1 So, the new question: how I can find which id_user has _all_ the "value" that I'm looking for? Say -1 as 3 and I want a id_user=2 for the first and for the latter id_user=4 OK, So, analysing your new question, the time interval is still important? Or no longer? So writting your query in english : "I want all users that don't change "value" and their value is equal to x ?" Or will you need to find all the users that don't change value with just one query? And the query would return something like id_user | value 2 | -1 4| 3 ? Please advice me on this. I am just trying to avoid misunderstanding your requirements and ending up with a query that doesn't really do what you need. Best, Oliver -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Slow response in select
Hi folks,I have a view that seems to be very slow compared to similar views on the same server. I've had a look at running explain but I don't really understand it. I've posted the view and the output from explain at http://www1.ringways.co.uk/explain.txt This is an old view on a database that has been live for some time but I've never managed to get to the bottom of why it's much slower than the other views. Gary -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Slow response in select
On 09/21/2010 08:25 PM, Gary Stainburn wrote: Hi folks,I have a view that seems to be very slow compared to similar views on the same server. I've had a look at running explain but I don't really understand it. I've posted the view and the output from explain at http://www1.ringways.co.uk/explain.txt Woah. That's an interesting plan. When concerned about execution time, it's probably best to post EXPLAIN ANALYZE rather than plain EXPLAIN results. EXPLAIN ANALYZE provides more timing information and information about how rowcount estimates differed from reality. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Slow response in select
On Tuesday 21 September 2010 15:11:09 Craig Ringer wrote: > On 09/21/2010 08:25 PM, Gary Stainburn wrote: > > Hi folks,I have a view that seems to be very slow compared to similar > > views on the same server. > > > > I've had a look at running explain but I don't really understand it. > > > > I've posted the view and the output from explain at > > > > http://www1.ringways.co.uk/explain.txt > > Woah. That's an interesting plan. > > When concerned about execution time, it's probably best to post EXPLAIN > ANALYZE rather than plain EXPLAIN results. EXPLAIN ANALYZE provides more > timing information and information about how rowcount estimates differed > from reality. > > -- > Craig Ringer I've posted th explain analyze at http://www1.ringways.co.uk/explain_analyse.txt I've marked a line with a sort in that appears to be the bit that's taking the time. Am I right? Gary -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Slow response in select
On Tuesday 21 September 2010 15:20:15 Gary Stainburn wrote: > > I've posted th explain analyze at > > http://www1.ringways.co.uk/explain_analyse.txt > > I've marked a line with a sort in that appears to be the bit that's taking > the time. Am I right? > I've had a look at this, and I can't even see why the sort is happeing. There are no order by clauses and only one group by clause in the sub-select. This group by only works on a very small dataset though. -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] all the table values equal
Oliveiros d'Azevedo Cristina ha scritto: OK, So, analysing your new question, the time interval is still important? Or no longer? It's important but simple to translate into sql, so I remove it from my question. So writting your query in english : "I want all users that don't change "value" and their value is equal to x ?" Yes. All the users that have only one value into the "value" column and that value is NN. Or will you need to find all the users that don't change value with just one query? And the query would return something like id_user | value 2 | -1 4| 3 A right result for me, based by my data, will be: // choosing -1 like value id_user 2 // choosing 3 like value id_user 4 and if I add to the table: id_user | value | datetime 5 | -1 | xxx 5 | -1 | xxx +1 // choosing -1 like value id_user 2 5 Thanks, Michele -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] all the table values equal
Howdy, Michele. Any news? Any progress/problems? If you want to find out all the id_users who don't change value in a given time interval you can use something like this. SELECT "id_user","value" FROM ( SELECT "id_user", COUNT(*) as total FROM t_yourtable WHERE "datetime" BETWEEN '2010-9-1' -- Interval beginning AND '2010-9-11' -- interval end GROUP BY "id_user" ) as foo NATURAL JOIN ( SELECT "id_user","value",COUNT(*) as total FROM t_yourtable WHERE "datetime" BETWEEN '2010-9-1' -- again, plug interval beginning here AND '2010-9-11' -- and interval end here GROUP BY "id_user","value" ) as bar This will give you the users who didn't change value on the time interval. Finally, if you want to look for a specific value just add the clause "WHERE "value" = x -- x=the specific value you want to look for to the query above. It'll return just the id_user(s) that have just the "value" x. Please try it out and feel free to get back to me if it didn't work or if it didn't solve your problem Best, Oliver - Original Message - From: "Oliveiros d'Azevedo Cristina" To: "Michele Petrazzo - Unipex" Cc: Sent: Tuesday, September 21, 2010 11:25 AM Subject: Re: [SQL] all the table values equal > >>> It would help if you explain a little better the background of the >>> problem you're trying to solve. >>> >>> You want to find all the user IDs which have the same value on a >>> given time interval? >>> >>> Is my understanding correct? >>> >> >> Yes. >> >> Re-reading my post I saw that I could explain better! >> >> id_user | value | datetime >> 1 | 1 | xxx >> 1 | 2 | xxx+1 >> 1 | -1 | xxx+2 >> 2 | -1 | xxx >> 2 | -1 | xxx+1 >> 3 | 4 | xxx >> 3 | 10 | xxx+1 >> 3 | 4 | xxx+2 >> 4 | 3 | xxx >> 4 | 3 | xxx+1 >> >> So, the new question: how I can find which id_user has _all_ the "value" >> that I'm looking for? Say -1 as 3 and I want a id_user=2 for the first >> and for the latter id_user=4 >> > > OK, So, analysing your new question, the time interval is still important? > Or no longer? > So writting your query in english : "I want all users that don't change > "value" and their value is equal to x ?" > > Or will you need to find all the users that don't change value with just one > query? And the query would return something like > > id_user | value > 2 | -1 > 4| 3 > > ? > Please advice me on this. > > I am just trying to avoid misunderstanding your requirements and ending up > with a query that doesn't really do what you need. > > Best, > Oliver > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Slow response in select
Gary Stainburn writes: > I've posted th explain analyze at > http://www1.ringways.co.uk/explain_analyse.txt > I've marked a line with a sort in that appears to be the bit that's taking > the > time. Am I right? Well, it's not really that big a part of the whole cost: only 150ms out of the total. You could improve the speed of the sort by increasing work_mem enough to let it be done in-memory; but I'm not sure it's worth bothering with. If you knocked 100ms off the runtime that way you'd be doing well. One thing to realize about this kind of query is that the planner gets stupid when there are more than join_collapse_limit relations being JOINed. I'm not sure that it matters much in this example: it looks like it's a star schema and pretty much any join order is as good as any other. But you might want to try raising join_collapse_limit just to see whether the plan changes and whether it gets materially better. There's a definite planning-time penalty to raising that value, though, so I'd not recommend changing it in production unless you see big wins on a lot of queries. Better to reorder the JOINs manually if it turns out that join order does matter. Basically, if you're gonna join that many relations, it's gonna cost ya :-(. Star schemas are overrated IMO. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] all the table values equal
Yes. All the users that have only one value into the "value" column and that value is NN. Understood. Try the query from the previous mail, adding the clause WHERE "value" = NN as I did explain. Or will you need to find all the users that don't change value with just one query? And the query would return something like id_user | value 2 | -1 4| 3 A right result for me, based by my data, will be: // choosing -1 like value id_user 2 // choosing 3 like value id_user 4 and if I add to the table: id_user | value | datetime 5 | -1 | xxx 5 | -1 | xxx +1 // choosing -1 like value id_user 2 5 Understood. From the query I sent you just eliminate the "value" column from the select list, as you are not interested in it. Thanks, Michele Best, Oliver -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Help with queries.
Dear group, I just started learning postgres and I have to analyze my data. So please bear with me, for all the simple questions that I am asking. Apologies. I have a table: snps table id | sample_id | chromosome | from | to | 1 1chr1 10 11 2 1 chr1 14 15 3 2 chr1 14 15 4 2 chr19 10 5 3 chr11415 6 3 chr14 3536 7 3 chr14 3940 8 2 chr14 3940 9 2 chr14 4344 101 chr14 4344 gene table: id | chromosome | from | to | genename 1 chr14 20SRC 2 chr1 25 45SRC 3 chr1 80 100 CSK 4 chr1 120 140 CSK My aim is to for a gene in gene table (SRC that has two ranges 4-20 and 25-45), I want to map all coordinates in snps table. Then I want to get those entries where sample 1 and sample 2 have in common and sample 1 and sampl3 in common. I do not know how to get these entries. Could any one help me please. thank you. adrian -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Help with queries.
Howdy, Adrian, Please see my questions below snps table id | sample_id | chromosome | from | to | 1 1chr1 10 11 2 1 chr1 14 15 3 2 chr1 14 15 4 2 chr19 10 5 3 chr11415 6 3 chr14 3536 7 3 chr14 3940 8 2 chr14 3940 9 2 chr14 4344 101 chr14 4344 gene table: id | chromosome | from | to | genename 1 chr14 20SRC 2 chr1 25 45SRC 3 chr1 80 100 CSK 4 chr1 120 140 CSK My aim is to for a gene in gene table (SRC that has two ranges 4-20 and 25-45), I want to map all coordinates in snps table. Please advice me, What do you call coordinates to, exactly? Sorry, my genetics knowledge is very poor. You call the from and to fields coordinates? Do they have the same meaning as the from and to fields from table snps ? What does it mean to map all coordinates in table snps? Do you wanna know which entries from snps map in the SRC gene? Coordinates are the segment where the gene/sample fit in the chromosome? Is my understanding correct ? Then I want to get those entries where sample 1 and sample 2 have in common and sample 1 and sampl3 in common. Could you give an example of your desired output, for the data you have ? I do not know how to get these entries. Could any one help me please. thank you. adrian Best, Oliver -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] available RPM package for 8.4.4?
Is it possible to just download the RPM package for postgresql server 8.4 for fedora and NOT install it through the yum tree? If so where can I go to get it. I am looking all through yum.pgrpms.org and I am coming up a bit short. Thanks. -- Joshua Gooding -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] available RPM package for 8.4.4?
On Tue, 2010-09-21 at 13:53 -0400, Joshua Gooding wrote: > Is it possible to just download the RPM package for postgresql > server > 8.4 for fedora and NOT install it through the yum tree? Sure. You can grab the package from: http://yum.pgrpms.org/8.4/fedora/ Also, you can grab rpms from http://www.postgresql.org/ftp/binary/v8.4.4/linux/rpms/fedora/ Regards, -- Devrim GÜNDÜZ PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer PostgreSQL RPM Repository: http://yum.pgrpms.org Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
Re: [SQL] Help with queries.
Hello Oliver. thanks for your reply. Here are my answers. Sorry I shot e-mail in morning and I was not clear about it. I am afraid, I am not jamming you with all information. Thank you very much for your help. == your questions First, answers to questions you asked: > Please advice me, > What do you call coordinates to, exactly? Sorry, my genetics knowledge is > very poor. == I call coordinates cto - crom > You call the from and to fields coordinates? ==yes > Do they have the same meaning as the from and to fields from table snps ? == cfrom - cto fields have yes. > What does it mean to map all coordinates in table snps? == Means I want to take snps.cfrom and snps.cto and gene.gene by checking if they are in range of gene.cfrom and gene.cto from gene table. > Do you wanna know which entries from snps map in the SRC gene? == yes and more than that. > Coordinates are the segment where the gene/sample fit in the chromosome? Is > my understanding correct ? == yes I am calling on "chr19" (cfrom - cto) (38162736 - 38163007) are coordinates. === illustration of example == I know basic queries, but I am finding it difficult to get what I am describing below. I am illustrating an example here . Here I query for a gene RHPN2 from gene table: gid | chromosome | cfrom | cto| frame | gene ---++--+--+---+--- 93538 | chr19 | 38162736 | 38163007 | - | RHPN2 93540 | chr19 | 38173254 | 38173420 | - | RHPN2 93541 | chr19 | 38174563 | 38174720 | - | RHPN2 93542 | chr19 | 38176717 | 38176804 | - | RHPN2 93543 | chr19 | 38178766 | 38178971 | - | RHPN2 93544 | chr19 | 38182326 | 38182456 | - | RHPN2 93545 | chr19 | 38184987 | 38185154 | - | RHPN2 93546 | chr19 | 38185553 | 38185751 | - | RHPN2 93547 | chr19 | 38190754 | 38190931 | - | RHPN2 93548 | chr19 | 38194419 | 38194554 | - | RHPN2 93549 | chr19 | 38195387 | 38195475 | - | RHPN2 93551 | chr19 | 38204311 | 38204397 | - | RHPN2 93553 | chr19 | 38209244 | 38209383 | - | RHPN2 93555 | chr19 | 38226989 | 38227115 | - | RHPN2 93556 | chr19 | 38247524 | 38247603 | - | RHPN2 Now taking: cto = 38162736 first row in above table cfrom = 38247603 last row in above table I want to get all those entries from snp table # select sample_id, chromosome,cfrom, cto, refbase, consbase from snps where cto > 38162736 and cfrom < 38247603 and chromosome = 'chr19' ; sample_id | chromosome | cfrom | cto| refbase | consbase ---++--+--+-+-- 2 | chr19 | 38178828 | 38178829 | C | Y 5 | chr19 | 38182405 | 38182406 | T| Y 5 | chr19 | 38182424 | 38182425 | G | R 5 | chr19 | 38185101 | 38185102 | C | Y 3 | chr19 | 38182424 | 38182425 | G | R 1 | chr19 | 38178828 | 38178829 | C | Y 1 | chr19 | 38182424 | 38182425 | G | R 7 | chr19 | 38185101 | 38185102 | C | Y 6 | chr19 | 38185101 | 38185102 | C | Y 4 | chr19 | 38178828 | 38178829 | C | Y 4 | chr19 | 38182424 | 38182425 | G | R >From this result, we get that all samples range from 1 to 7 (sample_id). Now, from above result (snps table) I want to filter: 1. Those that have any letter in consbase other than ATGC (although in this case there are no A or T or G or C they are there) 2. Those entries that have sample_id 1, 2 and 3. 3. Unique entries that have cfrom and cto common to 1 and 2 and 1 and 3 but not 2 and 3. For example see below: (say this is result X) gene | sample_id | chromosome | cfrom | cto ---+---++--+--+-+-- RHPN2 | 2 | chr19 | 38178828 | 38178829 RHPN2 | 1 | chr19 | 38178828 | 38178829 RHPN2 | 3 | chr19 | 38182424 | 38182425 RHPN2 | 1 | chr19 | 38182424 | 38182425 Here for gene RHPN2: cfrom - cto (38178828 | 38178829) is common to samples 1 and 2 cfrom - cto ( 38182424| 38182425) is common to samples 1 and 3. But both these samples 1 and 2 and 1 and 3 belong same gene (RHPN2 ). Now I have another table coverage, where for sample positions above, I have the following data: (say this is result y) sample_id | chromosome | cfrom | cto| abase | tbase | gbase | cbase ---++--+--+---+---+---+--- 2 | chr19 | 38178828 | 38178829 | 0 |29 | 2 |44 1 | chr19 | 38178828 | 38178829 | 0 |52 | 0 |32 3 | chr19 | 38178828 | 38178829 | 0 | 0 |
