You may also wish to review Andreas' suggestions as they propose a
more sensible table structure rather than having a table for each
convention.
The table proposal really looks nice. But our database is structured
by variable - so each convention has its own table.
It is a really bad design -
In response to A. Kretschmer :
> In response to Thom Brown :
> > On 23 February 2010 13:43, Stefan Schwarzer
> > wrote:
> > Select countries.name, basel.year, basel.value, cites.year, cites.value
> > From countries
> > Left Join basel on basel.id_country = countries.id_country and
>
In response to Thom Brown :
> On 23 February 2010 13:43, Stefan Schwarzer
> wrote:
> Select countries.name, basel.year, basel.value, cites.year, cites.value
> From countries
> Left Join basel on basel.id_country = countries.id_country and
> basel.value=1
> Left Join cites
Select countries.name, basel.year, basel.value, cites.year,
cites.value
From countries
Left Join basel on basel.id_country = countries.id_country and
basel.value=1
Left Join cites on cites.id_country = countries.id_country and
cites.value=1
I would have thought so, but the query turns forever.
In response to Stefan Schwarzer :
> >You may also wish to review Andreas' suggestions as they propose a
> >more sensible table structure rather than having a table for each
> >convention.
>
> The table proposal really looks nice. But our database is structured
> by variable - so each convention
On 23 February 2010 13:43, Stefan Schwarzer
wrote:
Select countries.name, basel.year, basel.value, cites.year, cites.value
From countries
Left Join basel on basel.id_country = countries.id_country and
basel.value=1
Left Join cites on cites.id_country = countries.id_country
Select countries.name, basel.year, basel.value, cites.year,
cites.value
From countries
Left Join basel on basel.id_country = countries.id_country and
basel.value=1
Left Join cites on cites.id_country = countries.id_country and
cites.value=1
I would have thought so, but the query turns forever.
On 23 February 2010 13:23, Stefan Schwarzer
wrote:
>> Select countries.name, basel.year, basel.value, cites.year, cites.value
>> From countries
>> Left Join basel on basel.id_country = countries.id_country and
>> basel.value=1
>> Left Join cites on cites.id_country = countries.id_country and
>> ci
Select countries.name, basel.year, basel.value, cites.year,
cites.value
From countries
Left Join basel on basel.id_country = countries.id_country and
basel.value=1
Left Join cites on cites.id_country = countries.id_country and
cites.value=1
I would have thought so, but the query turns fore
In response to Stefan Schwarzer :
> Hi there,
>
> gush, shouldn't be that complicated. But neither in Postgres, nor in Access I
> succeed in getting the result I wish.
>
> I have a couple of times for the Environmental Conventions (Kyoto, Montreal,
> CITES etc.). They look like this:
>
> id_coun
On 23 February 2010 11:44, Stefan Schwarzer
wrote:
> Hi there,
> gush, shouldn't be that complicated. But neither in Postgres, nor in Access
> I succeed in getting the result I wish.
> I have a couple of times for the Environmental Conventions (Kyoto, Montreal,
> CITES etc.). They look like this:
Hi there,
gush, shouldn't be that complicated. But neither in Postgres, nor in
Access I succeed in getting the result I wish.
I have a couple of times for the Environmental Conventions (Kyoto,
Montreal, CITES etc.). They look like this:
id_country,year,value
4,1992,0
4,1993,0
4,1994,0
4,1
Tom Lane wrote:
> "Richard Broersma" <[EMAIL PROTECTED]> writes:
> > I am curious if the motivation is still valid for intentionally
> > omitting check sub-queries. (what was the motivation to begin with?)
>
> > Since we can effectively work around this limitation by doing the same
> > thing with
Jeff Davis <[EMAIL PROTECTED]> writes:
> On Tue, 2008-09-02 at 19:22 -0400, Tom Lane wrote:
>> Jeff Davis <[EMAIL PROTECTED]> writes:
>>> My question is not why don't we allow subqueries in CHECK, my question
>>> is why do we allow stable/volatile functions?
>>
>> Historically we've allowed it,
>
On Tue, Sep 2, 2008 at 3:47 PM, Alvaro Herrera
<[EMAIL PROTECTED]> wrote:
> The problem is that you have to rerun the query to verify that the CHECK
> condition still holds, whenever the table that the CHECK clause is
> checking changes. This is rather problematic, because we'd need to make
> the
On Tue, 2008-09-02 at 19:22 -0400, Tom Lane wrote:
> Jeff Davis <[EMAIL PROTECTED]> writes:
> > My question is not why don't we allow subqueries in CHECK, my question
> > is why do we allow stable/volatile functions?
>
> Historically we've allowed it, and it's not clear what we'd buy by
> changing
On Tue, 2008-09-02 at 18:57 -0400, Tom Lane wrote:
> The standard says that the constraint is guaranteed not to be violated,
> which in the worst case means that any time you update the table(s)
> referenced in the subquery, you have to retest the CHECK expression
> at every row of the table having
Jeff Davis <[EMAIL PROTECTED]> writes:
> My question is not why don't we allow subqueries in CHECK, my question
> is why do we allow stable/volatile functions?
Historically we've allowed it, and it's not clear what we'd buy by
changing that, other than breaking existing applications whose authors
On Tue, 2008-09-02 at 18:47 -0400, Alvaro Herrera wrote:
> The problem is that you have to rerun the query to verify that the CHECK
> condition still holds, whenever the table that the CHECK clause is
> checking changes. This is rather problematic, because we'd need to make
> the system aware of s
On Tue, 2008-09-02 at 15:30 -0700, Richard Broersma wrote:
> I am curious if the motivation is still valid for intentionally
> omitting check sub-queries. (what was the motivation to begin with?)
>
> Since we can effectively work around this limitation by doing the same
> thing with a function in
"Richard Broersma" <[EMAIL PROTECTED]> writes:
> I am curious if the motivation is still valid for intentionally
> omitting check sub-queries. (what was the motivation to begin with?)
> Since we can effectively work around this limitation by doing the same
> thing with a function in a CHECK constr
Richard Broersma escribió:
> I am curious if the motivation is still valid for intentionally
> omitting check sub-queries. (what was the motivation to begin with?)
The problem is that you have to rerun the query to verify that the CHECK
condition still holds, whenever the table that the CHECK clau
I am curious if the motivation is still valid for intentionally
omitting check sub-queries. (what was the motivation to begin with?)
Since we can effectively work around this limitation by doing the same
thing with a function in a CHECK constraint, why would we want to
prevent anyone from using th
I just wanted to say thanks to everyone for your help.
-Jeff
This email is intended only for the individual or entity to which it is
addressed. This email may contain information that is privileged, confidential
or otherwise protected from disclosure. Dissemination, distribution or copying
of
"Demel, Jeff" <[EMAIL PROTECTED]> writes:
> Here's what I came up with:
> SELECT customers.id, customers.firstname,
> customers.lastname, customers.phone number,
> (SELECT ar.billdate FROM ar
> WHERE customers.customerid = ar.customerid
> ORDER BY ar.billdate LIMIT 1)
> AS l
, 2007 11:09 AM
To: Demel, Jeff; pgsql-general@postgresql.org
Subject: RE: [GENERAL] Subqueries - performance and use question
sorry, missing GROUP BY and some column naming was messed up but
hopefully you get the idea:
SELECT
c.id,
c.firstname,
c.lastname,
a.latest_billdate
FROM
customers c
ql-general@postgresql.org
> Subject: Re: [GENERAL] Subqueries - performance and use question
>
> try this approach:
>
> SELECT
> c.id,
> c.firstname,
> c.lastname,
> a.latest_billdate
> FROM
> customers c
> INNER JOIN -- or LEFT if you want the NU
7;new';
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Demel, Jeff
> Sent: Thursday, February 01, 2007 8:08 AM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Subqueries - performance and use question
>
> I ne
I need some basic advice on how to run a subquery, or if there's a
better way. Let me set up a situation, and get some advice on it. This
is my first post on this list, so I hope this kind of noob question is
ok.
Say I have a table of customers and table of accounts receivable
transactions, The
On Nov 6, 2005, at 3:19 PM, Bob Pawley wrote:
Last week I received help form the list in inserting a serial row
of one table (process) into a row in a table called specification.
I was able to expand that to include inserting the same information
into a table called pipe.
Last week I received help form
the list in inserting a serial row of one table (process) into a row in a table
called specification.
I was able to expand that to
include inserting the same information into a table called pipe.
---
create or
replace function base() returns
On Monday 20 of June 2005 00:12, you wrote:
> I have such a statement:
> select * from (subquery1) as foo, (subquery2) as bar;
> Both subqueries are reasonably fast (<0.5s) and generate results that have
> several (<10) rows but the whole query takes forever to execute.
Ok, postgresql wanted to be
# [EMAIL PROTECTED] / 2005-06-20 01:02:38 +0200:
> I wanted to define a function like this:
> create or replace function fun(...)...
> create temp table qaz as subquery1;
> create temp table wsx as subquery1;
> select * from qaz, wsx;
> language sql;
> but I get postgresql error saying that relatio
Tomasz Grobelny <[EMAIL PROTECTED]> writes:
> I have such a statement:
> select * from (subquery1) as foo, (subquery2) as bar;
> Both subqueries are reasonably fast (<0.5s) and generate results that have
> several (<10) rows but the whole query takes forever to execute.
The above is an unconstrai
On Monday 20 of June 2005 00:35, you wrote:
> - Original Message -
> From: "Tomasz Grobelny" <[EMAIL PROTECTED]>
> To:
> Sent: Sunday, June 19, 2005 6:12 PM
> Subject: [GENERAL] subqueries
>
> >I have such a statement:
> > select * from (s
- Original Message -
From: "Tomasz Grobelny" <[EMAIL PROTECTED]>
To:
Sent: Sunday, June 19, 2005 6:12 PM
Subject: [GENERAL] subqueries
I have such a statement:
select * from (subquery1) as foo, (subquery2) as bar;
Both subqueries are reasonably fast (<0.5s) a
I have such a statement:
select * from (subquery1) as foo, (subquery2) as bar;
Both subqueries are reasonably fast (<0.5s) and generate results that have
several (<10) rows but the whole query takes forever to execute. Moreover if
I simplify those subqueries (limiting functionality) the whole sel
Kevin Murphy <[EMAIL PROTECTED]> writes:
> ... In the following query, PG treats the phrase "and chromosome
> = chromosome" as "and genetic.chromosome = genetic.chromosome".
And that surprises you why?
regards, tom lane
---(end of broadcast)--
I have a query which didn't work properly until I fully qualified
columns used in a a subquery with the appropriate table names. The
reason is that both tables have a column named 'chromosome' used in the
subquery. In the following query, PG treats the phrase "and chromosome
= chromosome" as
Hi,
I have a very odd postgresql problem.
I have some subqueries contained within a function which are looking
for unrefernced data and then delete any rows that are found:
CREATE OR REPLACE FUNCTION housekeeping()
RETURNS TRIGGER AS '
BEGIN
DELETE FROM properties WHERE NOT EXISTS (SELECT
CSN wrote:
Is there much difference between using subqueries and
separating out them into separate queries?
That would depend on what results your expecting, and how you intend on using the results.
For example if you want all the data in a single statement, you can't break it up, but
the req
Is there much difference between using subqueries and
separating out them into separate queries?
__
Do you Yahoo!?
Yahoo! SiteBuilder - Free web site building tool. Try it!
http://webhosting.yahoo.com/ps/sb/
---(end of broadcast)
42 matches
Mail list logo
