Writing PSQL DB Queries

From DavinciWiki

Contents: Description, Procedure, Functions Used, Related Functions

Description

The purpose of this page is provide a very quick tutorial on composing a PSQL query statement to obtain image meta-data from the MSFF Mars PSQL database. More complex queries than those shown here can be used with the themis3db interface; see appropriate online documentation for SQL grammar and syntax.

The instructions provided here assume that you are primarily accessing the database through the Davinci-PSQLQueryTool interface. See the themis3db function page and/or the PSQL QueryTool Glossary procedure page for examples on how to execute the full series of commands in Davinci.

Procedure

Basic Query

select [WHAT] from [TABLE] where [CONDITIONS] [[GROUP] having [GRP_CONDITIONS]] [ORDER];

Replace [SQUARE-BRACKETS] contents shown above as follows

WHAT = comma delimited list of field names; on-the-fly calculations are allowed
define aliases for your fields using the syntax "FIELD as NEWNAME"
TABLE = use simple table_name, or schema_name.table_name to alleviate confusion
define aliases for your tables by using the syntax "TABLE as ABBREVIATION"
CONDITIONS = list of definitions (like field_name=value) for filtering the records; most mathematical and logical functions are available;
use and or or between each condition in list
use "(... or ...)" for clarification when enforcing multiple dependent conditions
GROUP = requried to define how to group the records when using a record grouping function (min, max, avg, ... )
Note: PSQL is very picky about grouping - any field in your select list must be in either the group by list or used in a grouping function
GRP_CONDITIONS = similar to field conditions, except that the filter is applied after the grouping is calculated
ORDER = PSQL records are returned in (apparently) random order; use a comma delimited field list to define a specific order
if necessary, qualify the ordering as DESCending (default is ASCending)

Basic Query Examples using the irfrmsci table:

dv> query="select file_id, framelet_id, bright_temp9 from irfrmsci 
    where file_id='I01001001' order by framelet_id;"

dv> query="select irfrmsci.* from irfrmsci where file_id='I01001001' order by framelet_id;"

dv> query="select file_id, framelet_id, mola_avg, mola_min as molamin, mola_max as mola_max \
    from irqubsci where file_id='I01001001' and framelet_id=0 order by framelet_id;"

dv> query="select file_id, framelet_id, mola_avg from irfrmsci where file_id in ('I01001001','I01002002') \
    and framelet_id > 0 and mola_avg > 0;"

dv> query="select file_id, avg(tes_emiss3) from irfrmsci where file_id='I01001001' group by file_id;"

dv> query="select file_id, avg(tes_emiss3) as avg_tes3 from irfrmsci where file_id like 'I010%' \
    group by file_id having avg(tes_emiss3) between 0.9 and 1.0 order by 2 desc;"

Multiple Table Query

select [WHAT] from [TABLES] where [JOIN] and [CONDITIONS] [[GROUP] having [GRP_CONDITIONS]] [ORDER];

Replace [SQUARE-BRACKETS] contents shown above as follows

WHAT = comma delimited list of field names
if the field_name is in more than one table, then explicitly qualify with TAB1.field_name
(where TAB1 is either the full table_name or the ABBREVIATION defined in the table list)
TABLES = comma delimited list of table names
as in the Basic Query, schema_names and or abbreviations can be used
JOIN = define how to relate the table records; work carefully when joining tables with multiple primary keys
CONDITIONS = define how to filter the records;
it is usually a good practice to constrain on any primary keys not used as part of the join statement
GROUP and ORDER = same as for Basic Query

Multi-Table Query Examples ... (warning: these queries are SLOW!)

dv> query="select irfrmsci.file_id, irfrmsci.framelet_id, bright_temp9 from \
      irfrmsci, frmgeom where irfrmsci.file_id=frmgeom.file_id and \
      irfrmsci.framelet_id=frmgeom.framelet_id and point_id='CT' \ 
      and band_idx=1 and lat between -5.0 and 5.0 and lon between 30 \
      and 60 order by irfrmsci.framelet_id;"

dv> query="select sci.file_id, avg(tes_emiss3), lat, lon  from irfrmsci as sci, \
      frmgeom as geom, thm3_quality qual where sci.file_id=geom.file_id and \ 
      sci.file_id=qual.file_id and point_id='CT' and band_idx=1 and calibration=0 \
      group by sci.file_id, lat, lon;"

Miscellaneous PSQL Query Notes

Enclose string values in single quotes, unless you want the literal phrase repeated in every record
PSQL field names are case insensitive; all fields are returned with lower case headers
A null values in PSQL is returned as an empty string ""; the query function has options to change this behaviour
You can specify fields in the GROUP or ORDER clause using the numeric order that they appear in you SELECT phrase, but you cannot use a FIELD-ALIAS
(numeric specification is especially useful if the "field" is a complex calculation)

DavinciWiki Mini-Nav Bar

Contents