Difference between revisions of "Postgres queries"

From WormBaseWiki
Jump to navigationJump to search
m (Created page with 'back to Caltech_documentation __TOC__ ==Query page== postgres queries can be entered [http://tazendra.caltech.edu/~postgres/cgi-bin/referenceform.cgi here] To make a query,…')
 
m
Line 21: Line 21:
 
Display pgids where the value in trp_synonym is a blank (note this is different from being null).
 
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  = '';
 
  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';

Revision as of 18:40, 15 October 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';