Re: [GENERAL] request help forming query

[danmcb]

yes, this could get called on quite large tables (maybe not
billions ...). The second solution looks useful - I'll try it on some
test data.

thanks both of you.



---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] request help forming query

[danmcb]

Hi

!'ve been wondering how to formulate a query to get a set of objects
out of a database, and am a bit stuck. I hope that someone here might
be able to help.

This is what the db looks like:

Table TYPES
id int primary key,
description text

Table GROUPS
id int primary key
description text

Tables WIDGETS
type_id int references TYPES(id),
group_id int references GROUPS(id),
primary key(type_id, group_id)

Now, given two type_id's, say A and B, I would like to find all groups
(or group_id's of course) that have a widget of both of these two
types.

Can anyone help with this please? Thanks!





---(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: [GENERAL] how to implement unusual constraint

[danmcb]

"So frequently the best advice for someone who's thinking of doing
something like this is "redesign your schema so you don't need to". "

I've thought about that. The obvious way to do it would be to split
into two tables, one for the originals, one for the translations (the
objects are actually phrases in many languages). But I'd rather avoid
that because in all other ways, the objects have the same properties,
reference the same objects, and so on. Splitting into two types is not
really desirable, from the point of view of what they are modelling.



---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

[GENERAL] how to implement unusual constraint

[danmcb]

Say I have a table, say my_table,  that is self-referencing. It looks
like this :


id integer pk,
orig_id integer references my_table(id),
.
.
.

Now this set of rows would be legal

id/orig_id
1 /1
2/1
3/1
4/4
5/4

but this not:

id/orig_id
1 /1
2/1
3/1
4/1
5/4

in other words: the row pointed to by orig_id cannot reference any row
other than itself.
How might I implement this as a constraint?
thanks,

Daniel


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] finding items with 0 rels for a 0 to many relationship

[danmcb]

thanks both for this. I haven't got around to writing this part of the
code yet, but will do soon. I appreciate the pointers.


On 21 Jun, 19:13, [EMAIL PROTECTED] (Michael Glaesemann) wrote:
> On Jun 21, 2007, at 11:57 , Josh Tolley wrote:
>
>
>
> > On 6/21/07, danmcb <[EMAIL PROTECTED]> wrote:
> >> Hi
>
> >> I have two tables, say A and B,  that have a many-to-many
> >> relationship, implemented in the usual way with a join table A_B.
>
> >> How can I economically find all the rows in table A whose id's are
> >> not
> >> in A_B at all (i.e. they have zero instances of B associated)?
>
> > Use a left join. For instance, say there are a.id and b.id columns,
> > which are the primary keys in A and B respectively. Also say A_B
> > contains columns aid and bid which reference a.id and b.id
> > respectively.
>
> >> SELECT * FROM A LEFT JOIN A_B ON (A.ID = A_B.AID) WHERE A_B.BID IS
> >> NULL;
>
> Alternatively you can use EXCEPT. Using Josh's schema:
>
> SELECT id
> FROM A
> EXCEPT
> SELECT aid
> FROM A_B.
>
> You'll want to check with EXPLAIN ANALYZE, but in general I suspect
> the outer join is faster.
>
> Michael Glaesemann
> grzm seespotcode net
>
> ---(end of broadcast)---
> TIP 2: Don't 'kill -9' the postmaster



---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[GENERAL] finding items with 0 rels for a 0 to many relationship

[danmcb]

Hi

I have two tables, say A and B,  that have a many-to-many
relationship, implemented in the usual way with a join table A_B.

How can I economically find all the rows in table A whose id's are not
in A_B at all (i.e. they have zero instances of B associated)?

Thanks

Daniel


---(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: [GENERAL] optimisation for a table with frequently used query

[danmcb]

I just did some checks on two seperate indexes c.f. one combined one.
I saw almost no difference between making select statements.

Haven't tried what happens with many updates - makes sense that more
indexes will slow that down though.

again thanks - bit of a noob question I know, but it's good to
learn :-)



---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] optimisation for a table with frequently used query

[danmcb]

Thanks!


---(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

[GENERAL] optimisation for a table with frequently used query

[danmcb]

Hi,

I have a table that looks like this:

CREATE TABLE my_table {
  id SERIAL PRIMARY KEY,
  id_1 INTEGER REFERENCES tab1(id),
  id_2 INTEGER REFERENCES tab2(id),
  .
  .
  .
};

I will often be running queries that look like

SELECT * from my_table where id_1 = x and id_2 = y;

Neither id_1 or id_2 or the combination of them is unique. I expect
this table to become large over time.

What can I do to help postgres run this query efficiently (if
anything)?

Apologies if this is a "RTFM" question - I looked but did not find.
Could be I didn't look hard enough ...

thanks in advance

Daniel


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/