7;),to_char( logtime, 'D')
ORDER BY to_char( logtime, 'D') DESC;
It is interesting that I can't put to_char( logtime, 'D') in the the
group by without putting it in the select.
Joseph Shraibman wrote:
p8:owl=>SELECT to_char( logtime, 'Dy'),co
p8:owl=>SELECT to_char( logtime, 'Dy'),count(*) FROM logtab WHERE
date_trunc('day', logtime) > current_date + '7 day ago'::interval group
by to_char( logtime, 'Dy') ORDER BY to_char( logtime, 'Dy') DESC;
to_char | count
-+---
Wed | 1447
Tue | 618
Thu | 1161
Sun
Stephan Szabo wrote:
Probably you want something like:
SELECT u.uid, u.txt, p.val FROM
u INNER JOIN a ON (a.id=u.aid)
LEFT JOIN p ON (p.uid=u.uid AND p.pkey=a.pkey);
From the docs:
A CROSS JOIN or INNER JOIN is a simple Cartesian product, the same as you get from
listing the two items at the
How can I join on one table with join conditions refering to two tables? In this example
p is missing an entry that corresponds to u. I want to select from u and p, but have
entries in u that don't have an entry in p. The problem is I need to go through table a
to get the corresponding value
I have a table I want to join on, but the conditions that restrict it span more than one
table. For example:
create table num_tab (thekey int primary key, val int, class char);
create table class_tab (class char primary key, tkey int);
create table txt_tab (thekey int primary key, class int, txt
Can I just set tgenabled to false?
Stephan Szabo wrote:
> On Tue, 23 Oct 2001, Joseph Shraibman wrote:
>
>
>>I have to drop a froeign key from one of my tables. The problem is that I have
>another
>>table that has a foreign key on the first one, so I can't
I have to drop a froeign key from one of my tables. The problem is that I have
another
table that has a foreign key on the first one, so I can't do the select to temp-table
thing and move it back.
Is there any way I can remove it by mucking with pg's internal tables?
--
Joseph
if
> the doc/TODO file doesn't contain a date in October, it's stale).
> I think the only thing we're still waiting on is some datetime fixes
> from Tom Lockhart...
>
> regards, tom lane
>
> -------(end of broadcast)
Lets say I have a table with columns a and b. I want to do a query like
SELECT count(distinct b) WHERE a = 2;
Should I have an index on a or an index on (a,b)?
--
Joseph Shraibman
[EMAIL PROTECTED]
Increase signal to noise ratio. http://www.targabot.com
---(end of
Putting
the selects in the SELECT solved both problems. I took out the 'AND ml.jid = j.id'
from
the outer WHERE (would have also excluded cases where there were zero entries in ml)
and
only refrenced ml in the subselect.
Thanks for your help.
--
Joseph Shraibman
[EMAIL PR
> Because you've asked the db engine to count on mj.mid. The parser want
> you to be specific about whether the other columns are being aggregated
> or not.
But they are in seperate tables, so how could it think they could be aggregated
together?
--
Joseph Shraibman
[E
en=# drop table ml;
DROP
--
Joseph Shraibman
[EMAIL PROTECTED]
Increase signal to noise ratio. http://www.targabot.com
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL
If I try to put a distinct on in my subselect int the from I get:
ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions
what does that mean?
Tom Lane wrote:
> Joseph Shraibman <[EMAIL PROTECTED]> writes:
>
>>Basically there is the utable, which has
I'm not clear how this helps. I want to get the last entry of ml. The distinct on
means
I won't get duplicate entries with the same key values, but what specifies that I'm
getting the last ml value, or even one ml value at all?
Tom Lane wrote:
> Joseph Shraibman <[EMA
able u, dtable
d,
where u.key1 = d.key order by d.somefield limit 25 offset ???;
The tricky part is the mltable. I don't want to put the whole subselect into the
where
clause because the mltable lookup is the most expensive part. And I need to have the
limit and offset be done by
gt;Josh Berkus
> Complete information technology [EMAIL PROTECTED]
>and data management solutions (415) 565-7293
> for law firms, small businessesfax 621-2533
> and non-profit organizations. San Francisco
>
#x27;t know why the planner thought the sort would be so expensive.
Stephan Szabo wrote:
> On Mon, 27 Aug 2001, Joseph Shraibman wrote:
>
>
>>Stephan Szabo wrote:
>>
>>>I think you'd want to move the entire query excepting the lastml where
>>>cond
t;
> __AGLIO DATABASE SOLUTIONS___
>Josh Berkus
> Complete information technology [EMAIL PROTECTED]
>and data management solutions (415) 565-7293
> for law firms, small businessesfax 6
res actually use a temporary table behind the scenses? It appears not.
--
Joseph Shraibman
[EMAIL PROTECTED]
Increase signal to noise ratio. http://www.targabot.com
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
QL is really annoying.
>
> -Josh
>
> P.S. I'm sure you don't mean for your e-mails to come across as
> antagonistic when you are asking for help. Can you please be careful of
> your phrasing?
I certainly didn't mean to be antagonistic, and looking at the email I sent
select must match up with the key fields in the outside
query.
I'm afraid this would indicate that they would not match up (even if I didn't use
short
aliases for the tables) and the result of the subselect would be meaningless.
--
Joseph Shraibman
[EMAIL PR
How do you do a subselect in the from clause?
Tom Lane wrote:
> Joseph Shraibman <[EMAIL PROTECTED]> writes:
>
>>playpen=# select a, sum(b) as dsum from taba where dsum > 5 group by(a);
>>ERROR: Attribute 'dsum' not found
>>
>
>>Why can we
playpen=# select a, sum(b) as dsum from taba where dsum > 5 group by(a);
ERROR: Attribute 'dsum' not found
Why can we GROUP BY on an alias but not do a WHERE on an alias? I have a subselect
that
explain shows is being run twice if I have to put it in the WHERE clause.
--
Jos
I'm running 7.1.3. What does 'rows=1' mean? The number of rows returned or the
number postgres has to look through?
Tom Lane wrote:
> Joseph Shraibman <[EMAIL PROTECTED]> writes:
>
>>Why does explain show more than one row, even if there is a LIMIT
Why does explain show more than one row, even if there is a LIMIT = 1?
--
Joseph Shraibman
[EMAIL PROTECTED]
Increase signal to noise ratio. http://www.targabot.com
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to
Then why does the explain say rows=1363 ?
I don't mean to nitpick here, but maybe this is the symptom of a larger problem.
Tom Lane wrote:
> Joseph Shraibman <[EMAIL PROTECTED]> writes:
>
>>Well the total cost should be at least as big as the sub-costs, no?
>>
Stephan Szabo wrote:
> On Tue, 21 Aug 2001, Joseph Shraibman wrote:
>
>
>>Thank you, I was missing the parens.
>>
>>If I do an explain I see:
>>
>>-> Index Scan using m_u_and_p_key on m (cost=0.00..3035.22 rows=1363 width=12)
>>
>>
t;width=44)
>>
>
> At least, what was the query that generated this and is it running
> slowly or otherwise giving problems? The total explain doesn't seem
> unreasonable to my relatively untrained eyes in the absense of knowing the
> query :)
>
Well the total cost
Thank you, I was missing the parens.
If I do an explain I see:
-> Index Scan using m_u_and_p_key on m (cost=0.00..3035.22 rows=1363 width=12)
even if I put a limit 1 on the select. Why is that?
Stephan Szabo wrote:
> On Mon, 20 Aug 2001, Joseph Shraibman wrote:
>
>
>>
ror. What is the proper way?
--
Joseph Shraibman
[EMAIL PROTECTED]
Increase signal to noise ratio. http://www.targabot.com
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
CREATE TABLE
> > CREATE INDEX
> > load data
>
no, the correct sequence is to create the index last, which will create
statistics that will tell postgres if it really wants to use an index or
not. Don't try and second guess postgres.
Even better do a VAC
Stephan Szabo wrote:
>
> On Mon, 19 Mar 2001, Joseph Shraibman wrote:
>
> > I want to select all the entries from d that have at least one
> > corresponding entry in u that meets my conditions. The problem is that
> > count(*) is returning the number of corres
ANALYZE person;
>
> 2: That 'count(*)' is going to be slow.
>Try counting a column that's indexed (p.doc might work?)
>
I don't think that is true.
--
Joseph Shraibman
[EMAIL PROTECTED]
Increase signal to noise ratio. http://www.targabot.com
--
= u.dkey and u.status =
2 and not u.b and u.akey = a.key and a.status = 3;
/* that returns 2 when I want it to return 1 */
drop table d;
drop table a;
drop table u;
--
Joseph Shraibman
[EMAIL PROTECTED]
Increase signal to noise ratio. http://www.targabot.com
---(end of
Michael Davis wrote:
>
> Setting NEW in an AFTER update or insert trigger is not wise. Try using a before
>update trigger instead.
>
I still get the error message.
> -Original Message-
> From: Joseph Shraibman [SMTP:[EMAIL PROTECTED]]
> Sent: Tuesday, Febr
pg_proc failed
What does this error message mean?
--
Joseph Shraibman
[EMAIL PROTECTED]
Increase signal to noise ratio. http://www.targabot.com
ete information technology [EMAIL PROTECTED]
> and data management solutions (415) 565-7293
> for law firms, small businesses fax 621-2533
> and non-profit organizations. San Francisco
--
Joseph Shraibman
[EMAIL PROTECTED]
Increase signal to noise ratio. http://www.targabot.com
PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66
--
Joseph Shraibman
[EMAIL PROTECTED]
Increase signal to noise ratio. http://www.targabot.com
ml):
"Nuclear weapons and other more powerful divine weapons were used
in the battle field in ancient India!"
I'm mystified as to why this document is on the linux.org web site.
--
Joseph Shraibman
[EMAIL PROTECTED]
Increase signal to noise ratio. http://www.targabot.com
k where they can
get a jdk for linux. How did they find the list without knowing about
blackdown?
--
Joseph Shraibman
[EMAIL PROTECTED]
Increase signal to noise ratio. http://www.targabot.com
No.
> Peeter Smitt wrote:
>
> Hi
>
> Is it somehow possible to join tables from multiple databases into one
> query?
>
> Thanks
>
> Peeter
>
>
--
Joseph Shraibman
[EMAIL PROTECTED]
Increase signal to noise ratio. http://www.targabot.com
GN KEY (p,o) REFERENCES utable (pk,uk);
drop sequence mtable_id_seq;
drop table mtable;
drop table utable;
--
Joseph Shraibman
[EMAIL PROTECTED]
Increase signal to noise ratio. http://www.targabot.com
attribute
pod
Am I just misunderstanding how to use FOREIGN KEY? Then why would it
work one time and not the other?
http://www.postgresql.org/docs/aw_pgsql_book/node159.html doesn't have
any column names after 'refrences '.
--
Joseph Shraibman
[EMAIL PROTECTED]
Increase signal
hubert depesz lubaczewski wrote:
>
> On Wed, Nov 29, 2000 at 07:03:36PM -0500, Joseph Shraibman wrote:
> > I tried to do this:
> > SELECT r , a , (SELECT u , re FROM dir WHERE u = a) , cdate FROM rep
> > WHERE m IN(190);
>
> why dont you use simple join?
How do I alter a table to set a column to be not null?
--
Joseph Shraibman
[EMAIL PROTECTED]
Increase signal to noise ratio. http://www.targabot.com
only have one
field?
Database version: PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc
egcs-2.91.66
--
Joseph Shraibman
[EMAIL PROTECTED]
Increase signal to noise ratio. http://www.targabot.com
out the URL.
>
> > Joseph Shraibman <[EMAIL PROTECTED]> writes:
> > > WHAT mailing list archives?
> > > They aren't linked to anywhere on www.postgresql.org that I can find.
> >
> > Hmm. My bookmark is
> >
> > http://www.postgresql.org/list
LIKE is concerned, but
> I suspect you may be seeing another variant of the problems that
> LIKE index optimization has with peculiar collation rules.
> You can find plenty of discussion of this in the mailing list archives
> :-(
>
WHAT mailing list archives?
They aren't linked
Why is BETWEEN inclusive? I had assumed that it was like the english
between, which is exclusive.
playpen=# create table numbs ( a int);
CREATE
playpen=# insert into numbs values(1);
INSERT 35913 1
playpen=# insert into numbs values(2);
INSERT 35914 1
playpen=# insert into numbs values(3);
INSE
Tom Lane wrote:
>
> Joseph Shraibman <[EMAIL PROTECTED]> writes:
> >>>> Using the example from
> >>>> http://www.postgresql.org/docs/aw_pgsql_book/node59.html, what would I
> >>>> do if I wanted to know the number of different cities wher
Bruce Momjian wrote:
>
> > Using the example from
> > http://www.postgresql.org/docs/aw_pgsql_book/node59.html, what would I
> > do if I wanted to know the number of different cities where I had a
> > friend in each state? select count(city) group by state; would not work
> > because if you had
Using the example from
http://www.postgresql.org/docs/aw_pgsql_book/node59.html, what would I
do if I wanted to know the number of different cities where I had a
friend in each state? select count(city) group by state; would not work
because if you had two friends in the same city it would be cou
Tom Lane wrote:
>
> Joseph Shraibman <[EMAIL PROTECTED]> writes:
> > These two queries are exactly alike. The first one uses aliases except
> > for the order by. The second uses aliases also for the order by. The
> > third uses whole names. The third has the beh
Peter Eisentraut wrote:
>
> > > playpen=> select ta.a,ta.b,ta.c, (select count (tb.zz) where tb.yy =
> > > ta.a) from tablea ta, tableb tb order by tablea.a;
> > [ produces 80 rows ]
>
> > > playpen=> select ta.a,ta.b,ta.c, (select count (tb.zz) where tb.yy =
> > > ta.a) from tablea ta, tableb t
These two queries are exactly alike. The first one uses aliases except
for the order by. The second uses aliases also for the order by. The
third uses whole names. The third has the behavior I want.
Someone please tell me what I am doing wrong. I don't want to have to
use whole names for my quer
Stephan Szabo wrote:
>
> > Is this a bug or am I just misunderstanding something?
> >
> > playpen=> create table tablea ( a int,b int , c int );
> > CREATE
> > playpen=> insert into tablea(a, b) values (1 ,2);
> > INSERT 28299 1
> > playpen=> insert into tablea(a, b, c) values (2 ,3, 4);
> > INSE
Julie Hunt wrote:
>
> Joseph Shraibman wrote:
>
> >
> >
> > playpen=> select a, b, case when c is null then 'not set' else 'set' end
> > as z from tablea group by a, b, z;
> > ERROR: Unable to identify an operator '<'
Is this a bug or am I just misunderstanding something?
playpen=> create table tablea ( a int,b int , c int );
CREATE
playpen=> insert into tablea(a, b) values (1 ,2);
INSERT 28299 1
playpen=> insert into tablea(a, b, c) values (2 ,3, 4);
INSERT 28300 1
playpen=> select a, b, case when c is null t
58 matches
Mail list logo
