Re: [SQL] i am getting error when i am using copy command
Hi, Penchalaiah, Penchalaiah P. wrote: > Copy penchal to ‘/tmp/penchal.out’ > > When I am using this statement I am getting error is : could not open > file ‘/tmp/penchal.out’ for writing: no such file or directory.. It seems that the /tmp directory is missing from your system. This can have two causes, IMHO: You either have a _totally broken_ un*x[1] installation, or you run on Windows or another platform that has different file naming conventions. HTH, Markus [1] un*x is a shortcut for all unixoid operating systems, including BSD, Linux and even MacOS X. -- 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 2: Don't 'kill -9' the postmaster
Re: [SQL] Compute hash of a table?
Hi, Peter, Peter Manchev wrote: > Is it possible to get the last time(stamp) when the content of a given > table in pgsql has changed??? My solution would be to add a trigger to the table which updates a timestamp in a second table on every insert/update/delete. HTH, 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 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
[SQL] selects on differing subsets of a query
First post, be gentle as I have terminology problems and so the subject might be wrongly worded. Say I have a table with fields ... gender diet_pref ... What I am trying to construct is a *single* query showing the total number of males in the table and also the total number of male vegetarians in the table, i.e. the 2nd value is computed on a subset of the records needed for the first value. As 2 queries this would be: select count(*) from mytab where gender='m' select count(*) from mytab where gender='m' and diet_pref='veg' The table is big and I'd like to do the select where gender='m' only once. (In the actual situation the select is on a date range) If there is a TFM, please point me at it with an indication of exactly what it is I am trying to achieve. If I'm trying to do something stupid, gentle advice would be appreciated. ---(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] selects on differing subsets of a query
Hi, Ed Temp, [EMAIL PROTECTED] wrote: > First post, be gentle as I have terminology problems and so the > subject might be wrongly worded. Hmm, the biggest mistake seems that "et temp 01" is a very unlikely real name, so you should reconfigure your mail client :-) > What I am trying to construct is a *single* query showing the total > number of males in the table > and also the total number of male vegetarians in the table, i.e. the > 2nd value is computed on a subset of the records needed for the first > value. > > As 2 queries this would be: > select count(*) from mytab where gender='m' > select count(*) from mytab where gender='m' and diet_pref='veg' Have you tried to UNION ALL the two queries? > The table is big and I'd like to do the select where gender='m' only > once. (In the actual situation the select is on a date range) SELECT count(*),diet_pref='veg' FROM table WHERE gender='m' GROUP BY diet_pref='veg' Is not exactly what you want, as your application still has to add two numbers to get the total result, but avoids the duplicated table scan. SELECT count(*),count(nullif(diet_pref='veg', f)) FROM table WHERE gender='m' Should also give you both counts, this time in different columns, also avoiding the duplicated table scan. It relies on the fact that count(something) is only called if something is not null, whereas count(*) is called for every row (as a special case). HTH, 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 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] selects on differing subsets of a query
Hello Markus, It's actually a temporary mailbox just in case the list attracts spam :-) Thank you for your help, I will study it when I get development time on the database. On 03/05/06, Markus Schaber <[EMAIL PROTECTED]> wrote: Hi, Ed Temp, [EMAIL PROTECTED] wrote: > First post, be gentle as I have terminology problems and so the > subject might be wrongly worded. Hmm, the biggest mistake seems that "et temp 01" is a very unlikely real name, so you should reconfigure your mail client :-) > What I am trying to construct is a *single* query showing the total > number of males in the table > and also the total number of male vegetarians in the table, i.e. the > 2nd value is computed on a subset of the records needed for the first > value. > > As 2 queries this would be: > select count(*) from mytab where gender='m' > select count(*) from mytab where gender='m' and diet_pref='veg' Have you tried to UNION ALL the two queries? > The table is big and I'd like to do the select where gender='m' only > once. (In the actual situation the select is on a date range) SELECT count(*),diet_pref='veg' FROM table WHERE gender='m' GROUP BY diet_pref='veg' Is not exactly what you want, as your application still has to add two numbers to get the total result, but avoids the duplicated table scan. SELECT count(*),count(nullif(diet_pref='veg', f)) FROM table WHERE gender='m' Should also give you both counts, this time in different columns, also avoiding the duplicated table scan. It relies on the fact that count(something) is only called if something is not null, whereas count(*) is called for every row (as a special case). HTH, 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] ERROR: plan should not reference subplan's variable
On Wed, May 03, 2006 at 08:08:22AM +0300, Catalin Pitis wrote: > Hi Tom > > Could you tell me when will 8.1.4 be released with the problem solved? In general, you can't rely on hard release dates in a free software project. Given that's in the STABLE branch, however, you could get it directly from CVS and build it yourself. Directions for doing this are on the website (possibly in the developer's section, note). A -- Andrew Sullivan | [EMAIL PROTECTED] The whole tendency of modern prose is away from concreteness. --George Orwell ---(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] i am getting error when i am using copy command
On Wed, May 03, 2006 at 10:02:37AM +0200, Markus Schaber wrote: > > file ?/tmp/penchal.out? for writing: no such file or directory.. > > It seems that the /tmp directory is missing from your system. Right. And notice that the "your system" here is the _server_, not the machine you're running psql from. If you want local files, you need to use \copy instead. A -- Andrew Sullivan | [EMAIL PROTECTED] It is above all style through which power defers to reason. --J. Robert Oppenheimer ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] selects on differing subsets of a query
Hi, Ed, [EMAIL PROTECTED] wrote: > It's actually a temporary mailbox just in case the list attracts spam :-) As far as I can see, the PostgreSQL lists are extremely low on spam attraction, compared to other lists. I think this is a result of the "only subscribers may post" policy, but I'm getting of topic. > Thank you for your help, I will study it when I get development time > on the database. Tell us about your findings here. HTH, 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 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] i am getting error when i am using copy command
file '/tmp/penchal.out' for writing: no such file or directory.. _IF_ you're on linux it could be due to selinux. setenforce 0 might solve the problem tempoarily. I would assume there should've been some existing discussion threads. (setenforce 1 afterwards.) Regards, Ben K. ---(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] selects on differing subsets of a query
On Wed, May 03, 2006 at 14:19:27 +0200, Markus Schaber <[EMAIL PROTECTED]> wrote: > > I think this is a result of the "only subscribers may post" policy, but > I'm getting of topic. Note the policy is really only subscribers may post without moderator approval. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] selects on differing subsets of a query
On Wed, May 03, 2006 at 09:47:49 +0100, [EMAIL PROTECTED] wrote: > First post, be gentle as I have terminology problems and so the > subject might be wrongly worded. > > Say I have a table with fields > ... > gender > diet_pref > ... > > What I am trying to construct is a *single* query showing the total > number of males in the table > and also the total number of male vegetarians in the table, i.e. the > 2nd value is computed on a subset of the records needed for the first > value. There are a few ways you could do this. One is to use a CASE function to return 1 for diet_pref = 'veg' and 0 otherwise. Then you can do a count(*) and a count of the CASE result in the same query and get both totals with one pass through the table. Another option would be joining the two queries. I don't think this is a good idea when you have to count everyone anyway, but if you were counting a couple of small subsets of the data and had partial indexes to speed those counts up, this might be a better strategy. ---(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] Sorting aggregate column contents
Thanks a lot the explanation. I tested all cases and I noticed that reordering the source table (city) not works on all cases, so I think Postgresql perform different internal sort to optimize some query's. I noticed this in other query I performed: select s.ano, s.semestre, dhc.iddisciplinahorariocurriculo, count(*), ag_concatenar_com_quebra_de_linha(td.turno) AS turno from disciplinahorariocurriculo dhc inner join horariocurriculo hc on (hc.idhorariocurriculo = dhc.idhorariocurriculo) inner join semestre s on (s.idsemestre = hc.idsemestre) inner join (select tdinterno.iddisciplinahorariocurriculo, t.turno from turnodisciplina tdinterno inner join turno t on (t.idturno = tdinterno.idturno) order by tdinterno.iddisciplinahorariocurriculo, t.turno) as td on (td.iddisciplinahorariocurriculo = dhc.iddisciplinahorariocurriculo) -- where dhc.iddisciplinahorariocurriculo = 8282 group by 1, 2, 3 having count(*) > 1 order by 1, 2, 3; ano | semestre | iddisciplinahorariocurriculo | count | turno ... 2004 |2 | 8282 | 3 | 23, 63, 43 ^ ^ ^ ... If I remove the comment in the 'where' line there is the right result: ano | semestre | iddisciplinahorariocurriculo | count | turno --+--+--+---+ 2004 |2 | 8282 | 3 | 23, 43, 63 ^ ^ ^ (1 row) I didn't know the array_to_string way, I think I will use it. It's safe and easy. Regards, -- Everton Ben K. escreveu: It works fine. But I wouldn't like using subselect's, then if somebody else knows about an operator or something like that to put on the aggregator, please tell me. I think the nature of the f_concat makes it difficult to sort, since it simply adds the next value, so if the source table gives value in the order of 'a','c','d','b' there's no way to handle them within f_concat unless you modify and rearrange the previous result string from within f_concat. So the source table (city) should be sorted. I don't know if this is a standard way, but this one seems to do that. == select s.name, ag_concat(c.name) from state s inner join (select * from city order by name desc) as c on c.idstate=s.idstate group by s.name order by 1; OR select s.name, ag_concat(c.name) from state s, (select * from city order by name desc) as c where c.idstate = s.idstate group by s.name order by 1; == I'm just reordering the source table on the fly. Curiously, if you don't have 'desc' you'll get a reverse ordered list. (z,...,a) I think your needs may also be met without any aggregator as well (there may be marginal cases which I haven't thought of, but I assume they can be handled if needed) == select s.name, array_to_string(array(select name from city where idstate = s.idstate order by name),',') from state s; == name | array_to_string --+- RP | Gramado,Port Alegre SP | Osasco * I see normalization issue here but guess it's not important. Regards, Ben K. Developer http://benix.tamu.edu ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] selects on differing subsets of a query
Hi, Bruno, Bruno Wolff III wrote: >>I think this is a result of the "only subscribers may post" policy, but >>I'm getting of topic. > > Note the policy is really only subscribers may post without moderator > approval. Thanks for clarification. I also think that the moderators are aided by some automatic spam classification techque. 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 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Sorting aggregate column contents
People from brazilian postgresql list sent me another way to sort the
column contents. The way is the aggregate accumulate values and after
run an array sort function. It worked fine. I think the subquery in
function f_select_array does not decrease performance. Follow the
code:
--from http://archives.postgresql.org/pgsql-general/2005-12/msg01093.php
CREATE FUNCTION f_select_array(anyarray) RETURNS SETOF anyelement AS $$
BEGIN
FOR i IN array_lower($1, 1) .. array_upper($1, 1) LOOP
RETURN NEXT $1[i];
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;
CREATE FUNCTION f_sort_array(anyarray) RETURNS anyarray AS $$
SELECT array(SELECT * FROM f_select_array($1) ORDER BY 1)
$$ LANGUAGE sql IMMUTABLE STRICT;
-- from http://www.postgresql.org/docs/8.1/interactive/xaggr.html
CREATE AGGREGATE ag_accum (
sfunc = array_append,
basetype = anyelement,
stype = anyarray,
initcond = '{}'
);
select e.sigla, array_to_string(f_sort_array(ag_accum(m.nome)), ', ')
AS municipio from estado e
inner join municipio m on (m.idestado = e.idestado)
group by e.sigla
order by e.sigla;
/* result */
sigla | municipio
---+---
RS| Gramado, Porto Alegre
SP| Osasco
(2 rows)
--
Everton
On 5/3/06, Everton Luís Berz <[EMAIL PROTECTED]> wrote:
Thanks a lot the explanation.
I tested all cases and I noticed that reordering the source table (city)
not works on all cases, so I think Postgresql perform different internal
sort to optimize some query's.
I noticed this in other query I performed:
select s.ano,
s.semestre,
dhc.iddisciplinahorariocurriculo,
count(*),
ag_concatenar_com_quebra_de_linha(td.turno) AS turno
from disciplinahorariocurriculo dhc
inner join horariocurriculo hc on (hc.idhorariocurriculo =
dhc.idhorariocurriculo)
inner join semestre s on (s.idsemestre = hc.idsemestre)
inner join (select tdinterno.iddisciplinahorariocurriculo, t.turno from
turnodisciplina tdinterno
inner join turno t on (t.idturno = tdinterno.idturno)
order by tdinterno.iddisciplinahorariocurriculo, t.turno) as
td on (td.iddisciplinahorariocurriculo = dhc.iddisciplinahorariocurriculo)
-- where dhc.iddisciplinahorariocurriculo = 8282
group by 1, 2, 3
having count(*) > 1
order by 1, 2, 3;
ano | semestre | iddisciplinahorariocurriculo | count | turno
...
2004 |2 | 8282 | 3 | 23, 63, 43
^ ^ ^
...
If I remove the comment in the 'where' line there is the right result:
ano | semestre | iddisciplinahorariocurriculo | count | turno
--+--+--+---+
2004 |2 | 8282 | 3 | 23, 43, 63
^ ^ ^
(1 row)
I didn't know the array_to_string way, I think I will use it. It's safe
and easy.
Regards,
--
Everton
Ben K. escreveu:
>> It works fine. But I wouldn't like using subselect's, then if somebody
>> else
>> knows about an operator or something like that to put on the
>> aggregator, please tell me.
>
>
>
> I think the nature of the f_concat makes it difficult to sort, since it
> simply adds the next value, so if the source table gives value in the
> order of 'a','c','d','b' there's no way to handle them within f_concat
> unless you modify and rearrange the previous result string from within
> f_concat.
>
>
> So the source table (city) should be sorted. I don't know if this is a
> standard way, but this one seems to do that.
>
>
> ==
> select s.name, ag_concat(c.name) from state s inner join (select * from
> city order by name desc) as c on c.idstate=s.idstate group by s.name
> order by 1;
>
> OR
>
> select s.name, ag_concat(c.name) from state s, (select * from city order
> by name desc) as c where c.idstate = s.idstate group by s.name order by 1;
> ==
>
>
> I'm just reordering the source table on the fly. Curiously, if you don't
> have 'desc' you'll get a reverse ordered list. (z,...,a)
>
> I think your needs may also be met without any aggregator as well (there
> may be marginal cases which I haven't thought of, but I assume they can
> be handled if needed)
>
> ==
> select s.name, array_to_string(array(select name from city where idstate
> = s.idstate order by name),',') from state s;
> ==
>
> name | array_to_string
> --+-
> RP | Gramado,Port Alegre
> SP | Osasco
>
>
> * I see normalization issue here but guess it's not important.
>
>
>
> Regards,
>
> Ben K.
> Developer
> http://benix.tamu.edu
>
--
Everton
---(end of broadcast)---
TIP 5: don't forget to increase
