[SQL] Regarding report generated by the pgFouine

Hi, I am pretty new to this Postgresql , PgFouine and all that stuff. I took the log that was generated by the postgresql (i.e in the /var/log/pgsql) and tried to generate the report by using the pgFouine by using the command pgfouine.php -file ./pgsql > ./report_pgsql_25.html In the report ge

Re: [SQL] generic crosstab ?

On Tue, Apr 24, 2012 at 3:37 PM, Andreas wrote: > Am 25.04.2012 00:04, schrieb Joe Conway: > > On 04/24/2012 02:42 PM, David Johnston wrote: >> >>> You must specify the output record structure: >>> >>> SELECT crosstab(text, text) AS ( col1_name col1_type [, colN_name >>> colN_type]* ) >>> >>> Wh

Re: [SQL] generic crosstab ?

Hello try to use cursors http://okbob.blogspot.com/2008/08/using-cursors-for-generating-cross.html Regards Pavel Stehule 2012/4/24 Andreas : > Hi, > > is there a generic solution to dump the result of a query as a crosstab, > when I can't know how many columns I will need? > > E.g. I get somet

Re: [SQL] How to group by similarity?

Hi- With respect similarity, its a costly operation on CPU based on the cycles of checking the keyword. Two words are said to be similar when atleast 2 or more consecutive characters are at the same. The more the consecutive characters are the same the more level of similarity. It would look sim

Re: [SQL] generic crosstab ?

Am 25.04.2012 00:04, schrieb Joe Conway: On 04/24/2012 02:42 PM, David Johnston wrote: You must specify the output record structure: SELECT crosstab(text, text) AS ( col1_name col1_type [, colN_name colN_type]* ) Whether this relates to the “materialization node” message you are receiving I ha

Re: [SQL] generic crosstab ?

On 04/24/2012 02:42 PM, David Johnston wrote: > You must specify the output record structure: > > SELECT crosstab(text, text) AS ( col1_name col1_type [, colN_name > colN_type]* ) > > Whether this relates to the “materialization node” message you are > receiving I have no idea. The error is beca

Re: [SQL] generic crosstab ?

From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Andreas Sent: Tuesday, April 24, 2012 5:35 PM To: Samuel Gendler Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] generic crosstab ? Am 24.04.2012 22:08, schrieb Samuel Gendler: On Tue, Apr 24, 2012 at

Re: [SQL] generic crosstab ?

Am 24.04.2012 22:08, schrieb Samuel Gendler: On Tue, Apr 24, 2012 at 1:01 PM, Andreas > wrote: Hi, is there a generic solution to dump the result of a query as a crosstab, when I can't know how many columns I will need? E.g. I get something like this:

Re: [SQL] generic crosstab ?

On Tue, Apr 24, 2012 at 1:01 PM, Andreas wrote: > Hi, > > is there a generic solution to dump the result of a query as a crosstab, > when I can't know how many columns I will need? > > E.g. I get something like this: > > id, x > 1, a > 1, b > 1, c > 2, l > 2, m > > > Yes. You can provide a

[SQL] generic crosstab ?

Hi, is there a generic solution to dump the result of a query as a crosstab, when I can't know how many columns I will need? E.g. I get something like this: id, x 1, a 1, b 1, c 2, l 2, m and I'd like to see it as: id, x1, x2, x3, . xn 1, a, b, c,null, null 2,

Re: [SQL] Simple way to get missing number

On 04/24/2012 11:10 AM, Emi Lu wrote: I got it and thank you very much for everyone's help!! It seems that "left join where is null" is faster comparing with "except". And my final query is: select num as missing from generate_series(5000, #{max_id}) t(num) left join t1 on (t.num = t1.id)

[SQL] How to group by similarity?

Hi, I'm trying to get an idea about pg_trgrm. I created a GIST index on a text column in a table. Now I can filter the table with similarity(). How would I group the table so that it shows groups that have similarity () > x ? Lets say the table looks like this: id, txt 1, aa1 2, bb1 3,

Re: [SQL] Simple way to get missing number

I got it and thank you very much for everyone's help!! It seems that "left join where is null" is faster comparing with "except". And my final query is: select num as missing from generate_series(5000, #{max_id}) t(num) left join t1 on (t.num = t1.id) where t1.id is null; Emi On 04/24/201

Re: [SQL] Simple way to get missing number

Raj Mathur (राज माथुर) wrote: > > Nice one, but curious about how would this perform if the numbers in > question extended into 7 figures or more? TIAS (Try It And See) Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect.

Re: [SQL] Simple way to get missing number

On 04/24/2012 07:15 AM, Emi Lu wrote: Good morning, May I know is there a simple sql command which could return missing numbers please? For example, t1(id integer) values= 1, 2, 3 500 select miss_num(id) from t1 ; Will return: === 37, 800, 8001 T select generate

Re: [SQL] Simple way to get missing number

On Tuesday 24 Apr 2012, Andreas Kretschmer wrote: > Emi Lu wrote: > > Good morning, > > > > May I know is there a simple sql command which could return missing > > numbers please? > > > > For example, > > > > t1(id integer) > > > > values= 1, 2, 3 500 > > > > select miss_num(id) > >

Re: [SQL] [GENERAL] Simple way to get missing number

Aha, generate_series, I got it. Thank you very much!! I also tried left join, it seems that left join explain analyze returns faster comparing with except: select num as missing from generate_series(5000, 22323) t(num) left join t1 on (t.num = t1.id) where t1.id is null limit 10; Emi On

Re: [SQL] Simple way to get missing number

Emi Lu wrote: > Good morning, > > May I know is there a simple sql command which could return missing > numbers please? > > For example, > > t1(id integer) > > values= 1, 2, 3 500 > > select miss_num(id) > from t1 ; something like ,[ code ] | test=# select * from emi_lu ; |

[SQL] Simple way to get missing number

Good morning, May I know is there a simple sql command which could return missing numbers please? For example, t1(id integer) values= 1, 2, 3 500 select miss_num(id) from t1 ; Will return: === 37, 800, 8001 Thanks a lot! Emi -- Sent via pgsql-sql mailing list (pg

Re: [SQL] getting the OS user name

On 2012-04-23, John Fabiani wrote: > Hi, > In my app it is possible to login as one name and use a different name to > login to postgres. > > Is it possible to get the actual OS login name using plsql. not a chance. RFC1413 man 3 ident looks like the parameters you need can be found here