Difference between revisions of "Postgres queries"

From WormBaseWiki
Jump to navigationJump to search
m
m
Line 27: Line 27:
 
Show values in a table where there is no 'null' or 'blank' values
 
Show values in a table where there is no 'null' or 'blank' values
 
  SELECT * FROM trp_synonym WHERE trp_synonym IS NOT NULL AND trp_synonym != '';
 
  SELECT * FROM trp_synonym WHERE trp_synonym IS NOT NULL AND trp_synonym != '';
 +
 +
The query for the list of topics (i.e., list of topics where the pgid is the same for process /
 +
paper / topicpaperstatus and the topicpaperstatus is either unchecked or relevant.)
 +
<pre>
 +
SELECT DISTINCT(pro_process.pro_process) FROM pro_process, pro_paper, pro_topicpaperstatus WHERE
 +
pro_process.joinkey = pro_paper.joinkey AND pro_process.joinkey = pro_topicpaperstatus.joinkey AND
 +
(pro_topicpaperstatus.pro_topicpaperstatus = 'unchecked' OR pro_topicpaperstatus.pro_topicpaperstatus =
 +
'relevant') ORDER BY pro_process.pro_process
 +
</pre>
 +
 +
The filter for the papers given a topic (unique WBPapers where the pgid is the same for process /
 +
paper / topicpaperstatus and the topicpaperstatus is either unchecked or relevant, and the process is the chosen topic
 +
<pre>
 +
SELECT DISTINCT(pro_paper.pro_paper) FROM pro_process, pro_paper, pro_topicpaperstatus WHERE
 +
pro_process.joinkey = pro_paper.joinkey AND pro_process.joinkey = pro_topicpaperstatus.joinkey AND
 +
(pro_topicpaperstatus.pro_topicpaperstatus = 'unchecked' OR pro_topicpaperstatus.pro_topicpaperstatus =
 +
'relevant') AND pro_process.pro_process = '$topic'
 +
</pre>

Revision as of 23:29, 7 November 2013

back to Caltech_documentation

Query page

postgres queries can be entered here

To make a query, you need to know the postgres table that carries the values you want.
Postgres table prefixes are listed in the third column in this wiki table.
More details of tables for specific datatypes are listed here.

A standard query follows this format (do not forget the ';' at the end of the query)

SELECT * FROM cur_transgene WHERE cur_transgene IS NOT NULL;

In this case, you are querying for any transgene value in the cur_transgene table where a value exists.

Commonly used queries

Checking for duplicate values in interaction (int_) or transgene (trp_) name tables or transgene synonym tables

SELECT int_name, COUNT(*) AS count FROM int_name GROUP BY int_name HAVING COUNT(*) > 1;
SELECT trp_name, COUNT(*) AS count FROM trp_name GROUP BY trp_name HAVING COUNT(*) > 1;
SELECT trp_synonym, COUNT(*) AS count FROM trp_synonym GROUP BY trp_synonym HAVING COUNT(*) > 1;

Display pgids where the value in trp_synonym is a blank (note this is different from being null).

SELECT * FROM trp_synonym WHERE trp_synonym  = ;

Find information for a variation in the obo_data_variation table (in this case for WBVar00087854).

SELECT * FROM obo_data_variation WHERE obo_data_variation ~ 'WBVar00087854';

Show values in a table where there is no 'null' or 'blank' values

SELECT * FROM trp_synonym WHERE trp_synonym IS NOT NULL AND trp_synonym != ;

The query for the list of topics (i.e., list of topics where the pgid is the same for process / paper / topicpaperstatus and the topicpaperstatus is either unchecked or relevant.)

SELECT DISTINCT(pro_process.pro_process) FROM pro_process, pro_paper, pro_topicpaperstatus WHERE
pro_process.joinkey = pro_paper.joinkey AND pro_process.joinkey = pro_topicpaperstatus.joinkey AND
(pro_topicpaperstatus.pro_topicpaperstatus = 'unchecked' OR pro_topicpaperstatus.pro_topicpaperstatus =
'relevant') ORDER BY pro_process.pro_process

The filter for the papers given a topic (unique WBPapers where the pgid is the same for process / paper / topicpaperstatus and the topicpaperstatus is either unchecked or relevant, and the process is the chosen topic

SELECT DISTINCT(pro_paper.pro_paper) FROM pro_process, pro_paper, pro_topicpaperstatus WHERE 
pro_process.joinkey = pro_paper.joinkey AND pro_process.joinkey = pro_topicpaperstatus.joinkey AND
(pro_topicpaperstatus.pro_topicpaperstatus = 'unchecked' OR pro_topicpaperstatus.pro_topicpaperstatus =
'relevant') AND pro_process.pro_process = '$topic'