[SQL] optimizing a query over tree-like structure

hi. sorry for the vague syntax used below, but the query is huge so i've tried to present it in simple terms. And sorry if i'm doing obviously stupid things, i have lots of years programming behind me but NO sql involved. i have a somewhat tree-like structure of objects that link to each other

[SQL] Can COPY update or skip existing records?

Hey all, I've got a table with a unique constraint across a few fields which I need to regularly import a batch of data into. Is there a way to do it with COPY without getting conflicts on the unique contraint? I have no was of being certain that some of the data I'm trying to load isn't in the ta

Re: [SQL] optimizing a query over tree-like structure

Hi, Svil I 'd like to first fully understand the background of your problem before figurin out if I can be of any help (or not). You have a tree, of which N is the root, is this correct? Then, what are the next sublevel? F, P and R? If so, why is R linked to a sibling (F) ? And the next one? O

Re: [SQL] Finding sequential records

On Mon, Sep 29, 2008 at 11:05 PM, Steve Midgley <[EMAIL PROTECTED]> wrote: > Any reason why using serial PK's with "compound > natural unique indices" is better/worse than just using natural PK's? Not really, surrogate keys will always work well so long as unwanted duplicates are constrained. Su

Re: [SQL] optimizing a query over tree-like structure

another idea i just got, to decrease the number of tables in one FROM, is to represent the first-level ORs with unions. but i'm not sure how exactly to do it: are these equivalent? FROM N,ownership,mm_N2R where ( N.dbid = ownership.N OR N.dbid = mm_N2R.left AND mm_N2R.right = ownership.R OR

Re: [SQL] optimizing a query over tree-like structure

Svil, Please advice me, You have values and one table for N,R,P,F and O and Z, right? And you have ownership which is a "catch-all" associative table between values and whatever other table, is this correct? You want to retrieve the values for a certain N, and all to all the other entities

Re: [SQL] Can COPY update or skip existing records?

Hi there, The operation "on_duplicate_key_update" is in implementation on the new version of Postgres :) The only way (or, at least, the best way... I think) to do what you want is using a temporary table... let's see: /* Creating the structure of the first table (table_01)... You can do it the wa

Re: [SQL] Can COPY update or skip existing records?

On Wed, Oct 1, 2008 at 6:37 AM, Rafael Domiciano <[EMAIL PROTECTED] > wrote: > Hi there, > The operation "on_duplicate_key_update" is in implementation on the new > version of Postgres :) > The only way (or, at least, the best way... I think) to do what you want is > using a temporary table... let

Re: [SQL] Can COPY update or skip existing records?

On Tue, Sep 30, 2008 at 6:16 AM, Glenn Gillen <[EMAIL PROTECTED]> wrote: > Hey all, > > I've got a table with a unique constraint across a few fields which I > need to regularly import a batch of data into. Is there a way to do it > with COPY without getting conflicts on the unique contraint? I hav