SQL Code Examples

Examples of SQL Queries to the HELIO Unified Observing Catalogue (UOC).

UOC

SELECT DISTINCT obsinst_key FROM planetary_cat
SELECT * FROM planetary_cat WHERE obsinst_key='MGS__MAG' limit 20
SELECT * FROM planetary_cat WHERE (obsinst_key like 'MGS_%' or obsinst_key like 'MSL_%' or obsinst_key like 'ODY_%' or obsinst_key like 'MEX_%') AND time_start>='2012-03-01' AND time_start<='2012-03-30' 

SELECT column_name FROM information_schema.columns WHERE table_name = 'pointed_instrument'
SELECT DISTINCT instrument_information_table_id FROM pointed_instrument LIMIT 10
SELECT * FROM pointed_instrument LIMIT 10
SELECT COUNT(*) FROM pointed_instrument
SELECT * FROM instrument_information 

General

PostgreSQL equivalent of mySQL statement "show tables"
SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' 

PostgreSQL equivalent of mySQL statement "describe table planetary_cat"
SELECT column_name FROM information_schema.columns WHERE table_name = 'planetary_cat'
SELECT column_name FROM information_schema.columns WHERE table_name = 'pointed_instrument' 
SELECT column_name FROM information_schema.columns WHERE table_name = 'instrument_information' 


SQL Tutorial