[SQL] query optimization

All, I have the following query which is running quite slow on our server and was hoping someone would have suggestions how I might improve it. est3=>EXPLAIN SELECT clone.uniquename,clone.name,library.type,clone.clone_id est3-> FROM library,clone_aceg est3-> JOIN clone USING (clone_id) e

[SQL] RI_ConstraintTrigger_11264756

All, I have inherited a table (below) with a RI trigger. I believe this table was created where both clone_id & blastx_id are FKs w/ stipulation 'ON DELETE CASCADE'. My question is, is this specified in the trigger: RI_ConstraintTrigger_11264756? If so how would I learn the content of the trigge

[SQL] Adding foreign key constraint post table creation

All, A couple of novice questions: I would like to modify an existing TABLE by addinga new column (FOREIGN KEY): type_id int not null, foreign key (type_id) references cvterm (cvterm_id), Will this work ( running PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.96): AL

[SQL] object oriented vs relational DB

Perhaps a mistake to ask this here (heresy I suspect) but I would be interested in the perspectives of people who make a living at this. Background: I am a molecular biologist trying to build a database containing genomic data, strain details, community info (people, publications etc). Every

Re: [SQL] Use of reference table ('look-up-table') and FK constraint

Josh & Stephan, This helps a lot - thanks! Any idea what datatype 'Identifier_type IDENTITY' specifies? I don't see this in the postgres documentation. I'll see what I can find in sybase lingo. CREATE TABLE phone ( phone_id Identifier_type IDENTITY, phone_number

[SQL] Use of reference table ('look-up-table') and FK constraint

Hi, In fits and starts I am working through converting a sybase schema -> postgres and am hoping to gain some insight on the use of reference tables ('look-up-table') and FK constraints. In the example below I believe the sybase RULE Phone_type_rule is used to restrict input, so that the only v

Re: [SQL] Problems Formulating a SELECT

Richard, Thanks again. > My bad, I didn't test it - for the last one you'll want > > SELECT contig_x_vw.contig_id FROM contig_x_vw ... > > It doesn't matter which id you use (since you want the value in each) but you > will need to tell PG which one you want. No problem, after I sent the e

Re: [SQL] Problems Formulating a SELECT

Richard, Thanks, a followup. I believe this will not work (novice, so take w/ grain of salt). I tried the following: chlamy_est=> CREATE VIEW contig_x_vw AS SELECT contig_id FROM clone_contig, clone WHERE chlamy_est-> clone_contig.clone_id = clone.clone_id AND read='x'; CREATE chlamy_est=> C

[SQL] Problems Formulating a SELECT

I am trying to formulate a SELECT and could use some suggestions. >From the TABLES below I would like to find ALL contigs which contain the same clones except that one (or more) has read='y' and the other(s) have read='x'. Or stated another way: find all contigs composed of (at least) both (x a

[SQL] converting Sybase RULE -> postgreSQL

Hi, I am still a novice at this so bear with me. I am going to try to alter a Sybase TABLE create script (excerpt below)to postgreSQL. I suspect some of the script is specific to Sybase and I'll just need to remove it. In particular there are numerous stored procedures ("sp_"). But first thing

[SQL] help w/ constructing a SELECT

Greetings, Having a problem with a query. I would like to construct a query which will return a list of all contigs which are comprised of clones whose 'read' = 'x' (read can be either 'x' or 'y'). Details: A contig may be comprised of more than 1 clone, so in TABLE clone_contig, there

[SQL] stored procedures: sybase -> postgreSQL ?

I am trying to port a Sybase table create script to one usable for postgreSQL. (note I am not a DBA) In particular I am not well versed on how to use/recode the stored procedures such as that in the example below. ALTER TABLE DnaFragment ADD PRIMARY KEY (dna_fragment_id) go exec sp_p