Re: [GENERAL] Can I getting a unique ID from a select
On Mon, Mar 05, 2007 at 17:07:25 -0800, Timasmith <[EMAIL PROTECTED]> wrote: > > > > > > > create view myview as > > > > > select rownum, t1.field, t2.field > > > > > from tableOne t1, tableTwo t2 > > > > > where t1.key = t2.fkey > > Multiple rows with the same key renders Hibernate useless as it caches > the 'row object' and then returns the first row every time for that > object. > > I think the sequence will work though, in reflection I guess it would > as fast as pulling another field, and with the numbers would be a very > long time before getting duplicates - even if you had thousands of > users, returning 100s of rows every few minutes (I think...). Based on the naming (t1.key vs t2.fkey) it looks like you may have a one to many relationship. If so, can't you just bring in the primary key from t2, as under the above assumption there will be only one matching row from t1? ---(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] Can I getting a unique ID from a select
On Mar 5, 3:35 am, [EMAIL PROTECTED] (Bruno Wolff III) wrote: > On Sat, Mar 03, 2007 at 16:46:45 -0800, > Timasmith<[EMAIL PROTECTED]> wrote: > > > On Mar 3, 7:12 pm, [EMAIL PROTECTED] (Bruno Wolff III) wrote: > > > On Thu, Mar 01, 2007 at 06:16:02 -0800, > > > Timasmith<[EMAIL PROTECTED]> wrote: > > > > > create view myview as > > > > select rownum, t1.field, t2.field > > > > from tableOne t1, tableTwo t2 > > > > where t1.key = t2.fkey > > > Never heard of a 'join key' but that sounds very promising. How do I > > select it? > > The join key would be t1.key or t2.fkey from your example. However there > may be multiple rows returned with the same value depending on what you > are joining. If that is the case you, should be able to use the primary > keys of the underlying tables to make a new candidate key for the joined > rows. > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend Multiple rows with the same key renders Hibernate useless as it caches the 'row object' and then returns the first row every time for that object. I think the sequence will work though, in reflection I guess it would as fast as pulling another field, and with the numbers would be a very long time before getting duplicates - even if you had thousands of users, returning 100s of rows every few minutes (I think...). ---(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: [GENERAL] Can I getting a unique ID from a select
On Sat, Mar 03, 2007 at 16:46:45 -0800, Timasmith <[EMAIL PROTECTED]> wrote: > On Mar 3, 7:12 pm, [EMAIL PROTECTED] (Bruno Wolff III) wrote: > > On Thu, Mar 01, 2007 at 06:16:02 -0800, > > Timasmith<[EMAIL PROTECTED]> wrote: > > > > > create view myview as > > > select rownum, t1.field, t2.field > > > from tableOne t1, tableTwo t2 > > > where t1.key = t2.fkey > > > Never heard of a 'join key' but that sounds very promising. How do I > select it? > The join key would be t1.key or t2.fkey from your example. However there may be multiple rows returned with the same value depending on what you are joining. If that is the case you, should be able to use the primary keys of the underlying tables to make a new candidate key for the joined rows. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Can I getting a unique ID from a select
On Mar 3, 7:12 pm, [EMAIL PROTECTED] (Bruno Wolff III) wrote: > On Thu, Mar 01, 2007 at 06:16:02 -0800, > Timasmith<[EMAIL PROTECTED]> wrote: > > > I am using hibernate, using a view like a read only table and I need a > > primary key each time a select is issued. > > > create view myview as > > select rownum, t1.field, t2.field > > from tableOne t1, tableTwo t2 > > where t1.key = t2.fkey > > > select * from myview > > > But what I really need is > > > select makemeauniquekey, t1.field, t2.field > > ... > > Is there some reason you can't use the join key? > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org/ Never heard of a 'join key' but that sounds very promising. How do I select it? ---(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: [GENERAL] Can I getting a unique ID from a select
On Sat, Mar 03, 2007 at 18:12:19 -0600, Bruno Wolff III <[EMAIL PROTECTED]> wrote: > On Thu, Mar 01, 2007 at 06:16:02 -0800, > Timasmith <[EMAIL PROTECTED]> wrote: > > I am using hibernate, using a view like a read only table and I need a > > primary key each time a select is issued. > > > > create view myview as > > select rownum, t1.field, t2.field > > from tableOne t1, tableTwo t2 > > where t1.key = t2.fkey > > > > select * from myview > > > > But what I really need is > > > > select makemeauniquekey, t1.field, t2.field > > ... > > Is there some reason you can't use the join key? To expand on this, if you are joining on fields that will return only one record for each value, you should still be able to make a primary key for the returned records using a combination of the primary keys of both records being joined. If hibernate only works with primary keys consisting of one column, than you can create a new field using a function of the primary keys of the records being joined. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Can I getting a unique ID from a select
On Thu, Mar 01, 2007 at 06:16:02 -0800, Timasmith <[EMAIL PROTECTED]> wrote: > I am using hibernate, using a view like a read only table and I need a > primary key each time a select is issued. > > create view myview as > select rownum, t1.field, t2.field > from tableOne t1, tableTwo t2 > where t1.key = t2.fkey > > select * from myview > > But what I really need is > > select makemeauniquekey, t1.field, t2.field > ... Is there some reason you can't use the join key? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Can I getting a unique ID from a select
In response to "Timasmith" <[EMAIL PROTECTED]>: > I am using hibernate, using a view like a read only table and I need a > primary key each time a select is issued. > > So in Oracle terms this might work, though I am skeptical that > Hibernate is going to return a cached result. > > create view myview as > select rownum, t1.field, t2.field > from tableOne t1, tableTwo t2 > where t1.key = t2.fkey > > select * from myview > > But what I really need is > > select makemeauniquekey, t1.field, t2.field > ... > > Maybe there is no way I think... incrementing a sequence per select > is untenable. Create a sequence and use nextval(). -- Bill Moran Collaborative Fusion Inc. [EMAIL PROTECTED] Phone: 412-422-3463x4023 IMPORTANT: This message contains confidential information and is intended only for the individual named. If the reader of this message is not an intended recipient (or the individual responsible for the delivery of this message to an intended recipient), please be advised that any re-use, dissemination, distribution or copying of this message is prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any errors or omissions in the contents of this message, which arise as a result of e-mail transmission. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
