Re: [SQL] getting the OS user name

[Jasen Betts]

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

http://www.postgresql.org/docs/current/static/functions-info.html

dunno if you can do ident calls from any of the available PLs other
than PLC

RFC1413 can be forged 

-- 
⚂⚃ 100% natural


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

[SQL] Simple way to get missing number

[Emi Lu]

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 (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Simple way to get missing number

[Andreas Kretschmer]

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 ;
|  i
| ---
|  1
|  2
|  3
|  5
|  6
|  8
|  9
| (7 rows)
|
| Time: 0,246 ms
| test=*# select * from generate_Series(1,10) s left join emi_lu on
| (s=emi_lu.i) where i is null;
|  s  | i
| +---
|   4 |
|   7 |
|  10 |
| (3 rows)
`

this?




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°

-- 
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] [GENERAL] Simple way to get missing number

[Emi Lu]

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 04/24/2012 10:31 AM, hubert depesz lubaczewski wrote:

On Tue, Apr 24, 2012 at 10:15:26AM -0400, Emi Lu wrote:

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 ;


select generate_series( (select min(id) from t1), (select max(id) from
t1))
except
select id from t1;

Best regards,

depesz




--
Emi Lu, ENCS, Concordia University, Montreal H3G 1M8
em...@encs.concordia.ca+1 514 848-2424 x5884

--
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] Simple way to get missing number

[Raj Mathur (राज माथुर)]

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)
> > from   t1 ;
> 
> something like
> 
> ,[  code  ]
> 
> | test=# select * from emi_lu ;
> | 
> |  i
> | 
> | ---
> | 
> |  1
> |  2
> |  3
> |  5
> |  6
> |  8
> |  9
> | 
> | (7 rows)
> | 
> | Time: 0,246 ms
> | test=*# select * from generate_Series(1,10) s left join emi_lu on
> | (s=emi_lu.i) where i is null;
> | 
> |  s  | i
> | 
> | +---
> | 
> |   4 |
> |   7 |
> |  
> |  10 |
> | 
> | (3 rows)

Nice one, but curious about how would this perform if the numbers in 
question extended into 7 figures or more?

Regards,

-- Raj
-- 
Raj Mathur  || r...@kandalaya.org   || GPG:
http://otheronepercent.blogspot.com || http://kandalaya.org || CC68
It is the mind that moves   || http://schizoid.in   || D17F

-- 
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] Simple way to get missing number

[Steve Crawford]

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_series(1,500) except select id from t1;

Example

select anumber from fooo;
 anumber
-
   1
   3
   5
   7
   9
  11
  13
  15

select generate_series(1,15) except select anumber from fooo order by 1;
 generate_series
-
   2
   4
   6
   8
  10
  12
  14

Cheers,
Steve

--
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] Simple way to get missing number

[Andreas Kretschmer]

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.  (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°

-- 
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] Simple way to get missing number

[Emi Lu]

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/2012 11:42 AM, Steve Crawford wrote:

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_series(1,500) except select id from t1;

Example

select anumber from fooo;
anumber
-
1
3
5
7
9
11
13
15

select generate_series(1,15) except select anumber from fooo order by 1;
generate_series
-
2
4
6
8
10
12
14

Cheers,
Steve



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

[SQL] How to group by similarity?

[Andreas]

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,   cc1
4,   bb2
5,   bb3
6,   aa2
...

How would a select look like that shows:

id,  txt,  group_id
1,   aa1,   1,
6,   aa2,   1,
2,   bb1,   2,
4,   bb2,   2,
5,   bb3,   2,
3,   cc1,   3


An extension of this problem would be to find similar records in 2 tables.
As a result should apear a list of every record from table2 that is 
similar to a record of table1.

Something like:
table1.id,   table2.id
1, 3
1, 5
1, 7
2, 2
2,11



--
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] Simple way to get missing number

[Steve Crawford]

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)
where t1.id is null;



BTW, there are many options. Two more of them include EXISTS:

select allnumbers from generate_series(1,15) as allnumbers where not 
exists (select 1 from fooo where fooo.anumber=allnumbers.allnumbers);


And IN:

select allnumbers from generate_series(1,15) as allnumbers where 
allnumbers not in (select anumber from fooo);


They all give you the same result. The "right" choice will depend on the 
size of your table, how it is indexed, how fully it is populated and 
even on your version of PostgreSQL. (Apologies for the funky field/table 
naming.)


Cheers,
Steve


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

[SQL] generic crosstab ?

[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 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,   l,m,  

I fear the problem is I dont know n.

--
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] generic crosstab ?

[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:
>
> id, x
> 1,  a
> 1,  b
> 1,  c
> 2,  l
> 2,  m
>
>
>
Yes.  You can provide a query which returns the columns to the version of
the crosstab function which looks like this:

crosstab(text source_sql, text category_sql)
It does exactly what you are looking for. The second query returns the set
of values that act as columns in the final result (the pivot for each row
in the result returned by the first query).  This allows the function to
correctly insert a null for any column for which there is no row in the
first query results.

Re: [SQL] generic crosstab ?

[Andreas]

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:

id, x
1,  a
1,  b
1,  c
2,  l
2,  m



Yes.  You can provide a query which returns the columns to the version 
of the crosstab function which looks like this:


|crosstab(text source_sql, text category_sql)|

It does exactly what you are looking for. The second query returns the 
set of values that act as columns in the final result (the pivot for 
each row in the result returned by the first query).  This allows the 
function to correctly insert a null for any column for which there is 
no row in the first query results.





I got stuck with an error that translates to "Materialisation mode is 
needed but is not allowed in this context."

I couldn't figure out what this materialisation mode is, yet.

Could you please have a look at my query sample?
Both queries work for themselves but crosstab() fails.   :(
I checked and there are never more than 20 child_ids per parent_id so 
there should be enough columns.


select
crosstab (
$$
select
parent_idas  row_name,
'x' || row_number() over ( partition by parent_id order by 
child_id )  as  category,

child_id  as  value
from
children
order by 1
$$,
$$
select 'x' || generate_series(1, 20) as  cat  order by 1
$$
);

Re: [SQL] generic crosstab ?

[David Johnston]

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 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 query which returns the columns to the version of
the crosstab function which looks like this:

 


crosstab(text source_sql, text category_sql)

It does exactly what you are looking for. The second query returns the set
of values that act as columns in the final result (the pivot for each row in
the result returned by the first query).  This allows the function to
correctly insert a null for any column for which there is no row in the
first query results.

 

 


I got stuck with an error that translates to "Materialisation mode is needed
but is not allowed in this context."
I couldn't figure out what this materialisation mode is, yet.

Could you please have a look at my query sample?
Both queries work for themselves but crosstab() fails.   :(
I checked and there are never more than 20 child_ids per parent_id so there
should be enough columns.

select
crosstab (
$$
select
parent_idas  row_name,
'x' || row_number() over ( partition by parent_id order by child_id
)  as  category,
child_id  as  value
from
children
order by 1
$$,
$$
select 'x' || generate_series(1, 20) as  cat  order by 1
$$
);

 

 

You must specify the output record structure:

 

SELECT crosstab(text, text) AS ( col1_name col1_type [, colN_name
colN_type]* )

 

See: http://www.postgresql.org/docs/9.0/interactive/tablefunc.html for
official usage and examples

 

Whether this relates to the "materialization node" message you are receiving
I have no idea.

 

Dave

 

Re: [SQL] generic crosstab ?

[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 have no idea.

The error is because you are selecting from a set returning function in
the target list rather than the from clause. It should be more like:

SELECT * FROM crosstab(text, text) AS ( col1_name col1_type [, colN_name
> colN_type]* )

HTH,

Joe

-- 
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support

-- 
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] generic crosstab ?

[Andreas]

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 have no idea.

The error is because you are selecting from a set returning function in
the target list rather than the from clause. It should be more like:

SELECT * FROM crosstab(text, text) AS ( col1_name col1_type [, colN_name

colN_type]* )




OK now i get at least some result.
But
1) I need to know how many categories will apear to construct the target 
list. This is a wee bit of a problem as this number is actually dynamic.


2) There are some rows in the resulting list with empty columns within 
the row.
When I execute the first query for a parent ID that has gaps in the 
crosstab I see it shows no gaps in the categories when called outside 
crosstab().
E.g. it dumps  x1, x2, x3, x4, x5 when called seperately but crosstab() 
shows

x1, x2, null, null, x5, null, x6, x7

How does this make sense ?


Thanks for the answers so far   :)


select  *
from
crosstab (
$$
select
parent_idas  row_name,
'x' || row_number() over ( partition by parent_id order by 
child_id )  as  category,

child_id  as  value
from
children
order by 1
$$,
$$
select 'x' || generate_series(1, 15) as  category  order by 1
$$
)
as result (
row_nameinteger,
x1  integer,
x2  integer,
x3  integer,
x4  integer,
x5  integer,
x6  integer,
x7  integer,
x8  integer,
x9  integer,
x10 integer,
x11 integer,
x12 integer,
x13 integer,
x14 integer,
x15 integer
)

--
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] How to group by similarity?

[Trinath Somanchi]

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 simple with the data aa1 and aa2, but with this data
aqswderfcdfer11 and aqswderfcdfer12ssws both the keywords look similar but
take more cycles of iteration to confirm the similarity.

Can it be this way, Posting the requirement you have on the data selection
based on the design. So that, so other appropriate solution can be
suggested.

--

Trinath Somanchi.

On Wed, Apr 25, 2012 at 12:09 AM, Andreas  wrote:

> 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,   cc1
> 4,   bb2
> 5,   bb3
> 6,   aa2
> ...
>
> How would a select look like that shows:
>
> id,  txt,  group_id
> 1,   aa1,   1,
> 6,   aa2,   1,
> 2,   bb1,   2,
> 4,   bb2,   2,
> 5,   bb3,   2,
> 3,   cc1,   3
>
>
> An extension of this problem would be to find similar records in 2 tables.
> As a result should apear a list of every record from table2 that is
> similar to a record of table1.
> Something like:
> table1.id,   table2.id
> 1, 3
> 1, 5
> 1, 7
> 2, 2
> 2,11
>
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-sql
>



-- 
Regards,
--
Trinath Somanchi,
+91 9866 235 130

Re: [SQL] generic crosstab ?

[Pavel Stehule]

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 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,   l,    m,  
>
> I fear the problem is I dont know n.
>
> --
> 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] generic crosstab ?

[Samuel Gendler]

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]* )
>>>
>>> Whether this relates to the “materialization node” message you are
>>> receiving I have no idea.
>>>
>> The error is because you are selecting from a set returning function in
>> the target list rather than the from clause. It should be more like:
>>
>> SELECT * FROM crosstab(text, text) AS ( col1_name col1_type [, colN_name
>>
>>> colN_type]* )
>>>
>>
>>
> OK now i get at least some result.
> But
> 1) I need to know how many categories will apear to construct the target
> list. This is a wee bit of a problem as this number is actually dynamic.
>
> 2) There are some rows in the resulting list with empty columns within the
> row.
> When I execute the first query for a parent ID that has gaps in the
> crosstab I see it shows no gaps in the categories when called outside
> crosstab().
> E.g. it dumps  x1, x2, x3, x4, x5 when called seperately but crosstab()
> shows
> x1, x2, null, null, x5, null, x6, x7
>
> How does this make sense ?
>

I believe that the crosstab function is fairly naive about constructing the
crosstab and requires that each row in the first query be in row-order for
the crosstab output.  It is possible that it even requires the rows in the
first query to be in column order in the crosstba output.

In other words, if the first query returns results like this:

row1, col3, value1
row1, col1, value2
row2, col1, value3
row2, col3, value4
row1, col2, value5

I believe that the last row1 entry will be dropped, or else maybe you'll
get two rows in the crosstab output, each representing row1, and I am
hypothesizing (without re-reading the documentation) that the fact that
col3 comes before col1 in row1 will also result in a hole in the output.

Basically, your first query should include "order by 1,2" rather than just
"order by 1"

Have you gone through the documentation for the crosstab functions?  I
believe that will answer most of your questions.  It has been a long time
since I've read them, but I remember them being fairly complete.

As for your question about knowing how many columns you will have, I've
always solved that by opening a serialized transaction and running a query
to get the possible set of columns, then issue the sql statement that
includes the crosstab(sql,sql) call, using the first query results to build
up the return result type.  Depending on the nature of your data set, it is
possible you can forgo the serialized transaction, since there may be
little to no risk of new column names appearing between the two queries.
 You could also just structure the queries in the second statement so that
it is guaranteed to return only the results returned by your initial query
- include a where clause which is guaranteed to return only rows that match
the columns returned by your first query.  You may wind up missing a value
that was added between the first query and the second query, but odds are
good that if your data is that volatile, it doesn't matter if you miss a
value which happened to be inserted in those few milliseconds.

--sam

[SQL] Regarding report generated by the pgFouine

[Venkateswara Rao Dokku]

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 generated by it i find


RankTimes executed Total duration   Av. duration (s)
 Query 1
1 6,532 35.4s0.00
;  (Location: exec_simple_query, postgres.c:1128)
2 8710.1s 0.00
   select global_gui_config from seas_config where id = 0;
3 8000.1s 0.00
   savepoint savepoint1; Location: exec_simple_query,
postgres.c:1128-
..
...
...

What is the ";" refers to.. (what is its meaning) under the query column in
the first row...

Could you please help me in understanding the report.


---
Thanks & Regards,
D.Venkateswara Rao,