[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_primarykey DnaFragment, dna_fragment_id go exec sp_bindrule DnaFragment_type_rule, 'DnaFragment.type' exec sp_bindefault Set_To_Current_Date, 'DnaFragment.date_last_modified' exec sp_bindefault Set_to_False, 'DnaFragment.is_obsolete' go regards, Charles ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[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 may be multiple entries for a given contig as in: chlamy_est=> select * from clone_contig; clone_id | contig_id --+--- 9811 | 1 82214 | 1 127472 | 1 82213 | 1 112644 | 1 9810 | 1 81641 | 2 This SELECT returns contigs comprised of clones whose reads are either 'x' or 'y'. Somehow I need an intersect or NOT comparrison??? SELECT contig.assembly,contig.ace,contig.ver FROM clone JOIN clone_contig USING (clone_id) JOIN contig USING (contig_id) WHERE clone.read = 'x' ; Tables: CREATE TABLE clone_contig( clone_id INTEGER REFERENCES clone(clone_id) ON DELETE CASCADE, contig_id INTEGER REFERENCES contig(contig_id) ON DELETE CASCADE, UNIQUE(clone_id,contig_id) ); CREATE TABLE clone ( clone_id SERIAL PRIMARY KEY, project INTEGER REFERENCES library(project) NOT NULL, snip read CHAR(1) NOT NULL, snip UNIQUE (project,plate,row,col,read,ver) ); CREATE TABLE contig ( contig_id SERIAL PRIMARY KEY, assembly DATE NOT NULL, ace INTEGER NOT NULL, ver INTEGER NOT NULL, length INTEGER NOT NULL, seq TEXT NOT NULL, UNIQUE (assembly,ace,ver) ); CREATE TABLE clone_contig( clone_id INTEGER REFERENCES clone(clone_id) ON DELETE CASCADE, contig_id INTEGER REFERENCES contig(contig_id) ON DELETE CASCADE, UNIQUE(clone_id,contig_id) ); regards, Charles ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[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 things first.
RULES:
In the sample below the RULE CloneEnd_type restricts input: the only
data which can be inserted or updated into CloneEnd.type have to be one
of 'BAC_end', 'YAC_end' etc..
I know postgresql supports RULES but have not used them prior. How would
one cone this for postgresql?
Stored Procedures:
Are FUNCTIONS (postgresql)equivalent to stored procedures (Sybase)?
### Sybase code: ###
CREATE RULE CloneEnd_type_rule
AS @col IN ('BAC_end', 'YAC_end', 'TAC_end', 'EST', 'unknown', 'P1_end', 'plasmid')
go
ALTER TABLE CloneEnd
ADD PRIMARY KEY (clone_end_id)
go
exec sp_primarykey CloneEnd,
clone_end_id
go
exec sp_bindrule CloneEnd_type_rule, 'CloneEnd.type'
exec sp_bindefault Set_To_Current_Date, 'CloneEnd.date_last_modified'
exec sp_bindefault Set_to_False, 'CloneEnd.is_obsolete'
go
regards,
Charles
---(end of broadcast)---
TIP 3: 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: [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 email I caught the source of the ' "contig_id" is ambiguous' ERROR. > > It's checking the "contig_id"s are the same (from clone_contig) - A contig_id match is what I wanted, but also a requirement that clone.project,plate,col & row also match for cloneA and cloneB. I added what I think gives me the correct match params (see below) and a quick survey of the results looks to be ok. Is there a method to remove duplicate results? For instance the query below in part yields : chlamy_est-> ; contig_id --- 27170 27173 27173 27179 27179 27179 27179 27179 The repetitive occurrence of the same contig_id (27179) occurs as a result of multiple pairs of clones matching the criteria for a given contig_id. So for contig_id 27179 there are 5 pairs which match: cloneA cloneBcontig_id 894018D05.x1<-> 894018D05.y127179 894080H12.x1<-> 894080H12.y127179 894094E04.x2<-> 894094E04.y227179 894095H03.x1<-> 894095H03.y227179 963037B05.x2<-> 963037B05.y127179 CREATE VIEW contig_x_vw AS SELECT contig_id,project,plate,row,col FROM clone_contig, clone WHERE clone_contig.clone_id = clone.clone_id AND read='x'; CREATE VIEW contig_y_vw AS SELECT contig_id,project,plate,row,col FROM clone_contig, clone WHERE clone_contig.clone_id = clone.clone_id AND read='y'; SELECT contig_x_vw.contig_id FROM contig_x_vw, contig_y_vw WHERE contig_x_vw.project = contig_y_vw.project AND contig_x_vw.plate = contig_y_vw.plate AND contig_x_vw.col = contig_y_vw.col AND contig_x_vw.row = contig_y_vw.row AND contig_x_vw.contig_id = contig_y_vw.contig_id ; > > cloneA.project=cloneB.project > > cloneA.plate=cloneB.plate > > cloneA.row=cloneB.row > > cloneA.col=cloneB.col > > > > > > TABLE clone 'A''B' > > > > clone_id12018 12019 > > project 894 894 > > plate 27 27 > > row G G > > col 9 9 > > readx y > > Does that get you any further? If not, I'm back at my desk tomorrow 9am GMT > > - Richard Huxton > > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[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 and y) reads from the same clone. For example: In the data below, the contig '20020630.488.1'(contig_id:13805) is composed of 2 clones (clone_id='12018' and '12019') which are 894027G09.x and 894027G09.y, respectively. Example: TABLE clone 'A''B' clone_id12018 12019 project 894 894 plate 27 27 row G G col 9 9 readx y Table clone_contig: clone_idcontig_id 12018 13805 12019 13805 TABLE contig: contig_id 13805 assembly20020630 ace 488 ver 1 CREATE TABLE clone ( clone_id SERIAL PRIMARY KEY, project INTEGER REFERENCES library(project) NOT NULL, plate INTEGER NOT NULL, row CHAR(1) NOT NULL, col INTEGER NOT NULL, read CHAR(1) NOT NULL, ver INTEGER NOT NULL, seq TEXT NOT NULL, L INTEGER NOT NULL, Qvals TEXT NOT NULL, TL INTEGER NOT NULL, MQAT INTEGER NOT NULL, Qstart INTEGER NOT NULL, Qend INTEGER NOT NULL, gb_id INTEGER REFERENCES gb(gb_id) NULL, unigene BOOLEAN NULL, UNIQUE (project,plate,row,col,read,ver) ); CREATE TABLE contig ( contig_id SERIAL PRIMARY KEY, assembly DATE NOT NULL, ace INTEGER NOT NULL, ver INTEGER NOT NULL, length INTEGER NOT NULL, seq TEXT NOT NULL, UNIQUE (assembly,ace,ver) ); CREATE TABLE clone_contig( clone_id INTEGER REFERENCES clone(clone_id) ON DELETE CASCADE, contig_id INTEGER REFERENCES contig(contig_id) ON DELETE CASCADE, UNIQUE(clone_id,contig_id) ); regards, Charles ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
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=> CREATE VIEW contig_y_vw AS SELECT contig_id FROM clone_contig, clone WHERE chlamy_est-> clone_contig.clone_id = clone.clone_id AND read='y'; CREATE chlamy_est=> SELECT contig_id FROM contig_x_vw, contig_y_vw WHERE contig_x_vw.clone_id = chlamy_est-> contig_y_vw.clone.id; ERROR: Column reference "contig_id" is ambiguous If I understand the logic, you SELECT: all contig_ids where where clone.read = 'x' (VIEW contig_x_vw) all contig_ids where where clone.read = 'y' (VIEW contig_y_vw) find isect of these two wehere their clone_ids are same However, their clone_ids will never be the same as in the example. cloneA.project=cloneB.project cloneA.plate=cloneB.plate cloneA.row=cloneB.row cloneA.col=cloneB.col TABLE clone 'A''B' clone_id12018 12019 project 894 894 plate 27 27 row G G col 9 9 readx y ?? Charles > > > > In the data below, the contig '20020630.488.1'(contig_id:13805) is > > composed of 2 clones (clone_id='12018' and '12019') which are > > 894027G09.x and 894027G09.y, respectively. > > > > TABLE clone 'A''B' > > > > clone_id12018 12019 > > project 894 894 > > plate 27 27 > > row G G > > col 9 9 > > readx y > > > > Table clone_contig: > > > > clone_idcontig_id > > 12018 13805 > > 12019 13805 > > How about something like: > > CREATE VIEW contig_x_vw AS SELECT contig_id FROM clone_config, clone WHERE > clone_config.clone_id = clone.clone_id AND read='x'; > CREATE VIEW contig_y_vw AS [same but for y] > > SELECT config_id FROM contig_x_vw, contig_y_vw WHERE contig_x_vw.clone_id = > contig_y_vw.clone.id; > > You don't need the views, but they make the example easier. > > - Richard Huxton > > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[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 values which can be inserted or
updated into Phone have to be one of ('work', 'home', 'fax', 'mobile',
'lab', 'unknown').
How would this be accomplished using a look-up or reference table::FK
constraint combination?
CREATE RULE Phone_type_rule
AS @col IN ('work', 'home', 'fax', 'mobile', 'lab', 'unknown')
go
CREATE TABLE Phone (
phone_id Identifier_type IDENTITY,
phone_number varchar(20) NOT NULL,
community_id numeric(12) NOT NULL,
type varchar(10) NULL
)
go
exec sp_bindrule Phone_type_rule, 'Phone.type'
go
regards,
Charles
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
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 varchar(20) NOT NULL, community_id numeric(12) NOT NULL, type varchar(10) NULL REFERENCES phone_types(type) ON DELETE SET NULL ); regards, Charles ---(end of broadcast)--- TIP 3: 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
[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). Everything except the genomic data is currently housed in an object-oriented acedb database. In conversations with other groups who are going through the same transition we are someone stated that: ".is perhaps evolving its schema _more_ now that sequencing is finished and annotation is really picking up speed, you should not think that the changes are going to get less ! You will have to deal with problems like genes that get renamed but you still need the old name around, how to cross reference oligos to many parts of sequence, how to organise RNAi data that may end up matching multiple chromosomes etc. etc. I don't want to put you off, but as far as I aware, constantly evolving schema is not one of relational databases strengths." Their solution is to maintain a mixed system of acedb and a relational db: where the working dbs are all acedb and the web site is a mixture of an acedb server and a relational relational db. The data in the mysql database is directly derived from the acedb reference database. Any thoughts? regards, Charles ---(end of broadcast)--- TIP 3: 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
[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): ALTER TABLE contig ADD COLUMN type_id int; ALTER TABLE contig ADD CONSTRAINT cvtermfk FOREIGN KEY (type_id) references cvterm (cvterm_id); I would like to load data into the table below from a file lacking the timestamp fields, where the file structure is: COPY table FROM STDIN; 1 feature_typetypes of features \N 2 3'-exon \N 1 . . . \. This fails as the timestamp fields are 'not null'. Othere than generating INSERT stmts for the data how else could I enter the data? create table cvterm ( cvterm_id serial not null, primary key (cvterm_id), termname varchar(255) not null, termdefinition text, termtype_id int, foreign key (termtype_id) references cvterm (cvterm_id), timeentered timestamp not null default current_timestamp, timelastmod timestamp not null default current_timestamp, unique(termname, termtype_id) ); regards, Charles ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[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 trigger knowing just the trigger id (11264756)? I have not seen a 'decoding' of triggers using pgsql. regards, Charles CREATE TABLE clone_blastx( clone_id INTEGER NOT NULL REFERENCES clone(clone_id) ON DELETE CASCADE, blastx_id INTEGER NOT NULL REFERENCES blastx(blastx_id) ON DELETE CASCADE, UNIQUE(clone_id,blastx_id) ); Column | Type | Modifiers ---+-+--- clone_id | integer | contig_id | integer | Indexes: clone_contig_clone_id_key unique btree (clone_id, contig_id), clone_id_clone_contig_key btree (clone_id), contig_id_clone_contig_key btree (contig_id) Triggers: RI_ConstraintTrigger_11264756 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[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)
est3-> WHERE clone_aceg.aceg_id = 8 AND
est3-> clone.project=library.project;
QUERY PLAN
-
Nested Loop (cost=0.00..27.92 rows=1 width=57)
Join Filter: (("outer".project)::text = ("inner".project)::text)
-> Nested Loop (cost=0.00..18.55 rows=4 width=43)
-> Index Scan using aceg_id_clone_aceg_key on clone_aceg (cost=0.00..3.05
rows=4 width=4)
Index Cond: (aceg_id = 8)
-> Index Scan using clone_pkey on clone (cost=0.00..3.91 rows=1 width=39)
Index Cond: ("outer".clone_id = clone.clone_id)
-> Seq Scan on library (cost=0.00..2.15 rows=15 width=14)
(8 rows)
relevant tables below.
regards,
Charles
Tables:
Table "public.clone"
Column | Type | Modifiers
+---+
clone_id | integer | not null default
nextval('"clone_clone_id_seq"'::text)
name | character varying(10) | not null
uniquename | text | not null
project| character varying(8) |
p_end | character varying(2) |
lib_id | integer |
accn | character varying(10) |
seq| text | not null
seqlen | integer |
hq_start | integer |
hq_end | integer |
scaffold | character varying(50) |
Indexes: clone_pkey primary key btree (clone_id),
clone_uniquename_idx unique btree (uniquename),
clone_accn_idx btree (accn),
clone_name_idx btree (name),
clone_project_idx btree (project),
clone_scaf_idx btree (scaffold)
Table "public.library"
Column| Type | Modifiers
-+-+
lib_id | integer | not null default nextval('"library_lib_id_seq"'::text)
source | text|
type| text|
project | integer |
name| text|
organism| text|
strain | text|
vector | text|
rs1 | text|
rs2 | text|
preparation | text|
Indexes: library_pkey primary key btree (lib_id),
library_project_idx btree (project),
library_type_idx btree ("type")
Table "public.clone_aceg"
Column | Type | Modifiers
--+-+---
clone_id | integer |
aceg_id | integer |
Indexes: clone_aceg_clone_id_key unique btree (clone_id, aceg_id),
aceg_id_clone_aceg_key btree (aceg_id),
clone_id_clone_aceg_key btree (clone_id)
Foreign Key constraints: cloneid FOREIGN KEY (clone_id) REFERENCES clone(clone_id) ON
UPDATE NO ACTION ON DELETE CASCADE,
acegid FOREIGN KEY (aceg_id) REFERENCES aceg(aceg_id) ON
UPDATE NO ACTION ON DELETE CASCADE
List of relations
Schema |Name| Type | Owner |Table
++---+-+--
public | aceg_aceg_idx | index | chauser | aceg
public | aceg_assembly_key | index | chauser | aceg
public | aceg_blast_aceg_id_key | index | chauser | aceg_blast
public | aceg_contig_idx| index | chauser | aceg
public | aceg_g_scaffold_idx| index | chauser | aceg
public | aceg_has_blast_idx | index | chauser | aceg
public | aceg_id_aceg_blast_key | index | chauser | aceg_blast
public | aceg_id_clone_aceg_key | index | chauser | clone_aceg
public | aceg_pkey | index | chauser | aceg
public | aceg_uniquename_idx| index | chauser | aceg
public | blast_id_aceg_blast_key| index | chauser | aceg_blast
public | blast_id_contig_blast_key | index | chauser | contig_blast
public | blast_ortho_idx| index | chauser | blast
public | blast_pkey | index | chauser | blast
public | clone_accn_idx | index | chauser | clone
public | clone_aceg_clone_id_key| index | chauser | clone_aceg
public | clone_contig_clone_id_key | index | chauser | clone_contig
public | clone_id_clone_aceg_key| index | chauser | clone_aceg
public | clone_id_clone_contig_key | index | chauser | clone_contig
public | clone_name_idx | index | chauser | clone
public | clone_pkey | index | chauser | clone
public | clone_project_idx | index | chauser | clone
public | clone_sca
