Narrowing Our Search

To see a description of a table in Postgres, we use the "describe" command followed by the object we want described: \d csvs.master_plan.

We're trying to isolate the Enceladus plan data, so for that we can use a simple select statement with a where clause. There's a lot of data in there and we don't want it all back, so we can limit the result using limit:

select team, spass_type, target 
from csvs.master_plan 
where target='Enceladus' limit 10;

This equality predicate (where target='Enceladus') is case sensitive and very strict - it has to be an exact match. We'd like a more "fuzzy" search, so we can use a matching regular expression in our predicate with the ~* operator:

select team, spass_type, target 
from csvs.master_plan 
where target ~* 'Enceladus' limit 10;

That's a case insensitive match. If we wanted a case sensitive matching expression we could use ~:

select team, spass_type, target 
from csvs.master_plan 
where target ~ 'Enceladus' limit 10;

Many times you'll want to isolate the distinct values of a column. This can be to check if there are spelling/casing issues OR to isolate data in order to create a separate table. You can do this using distinct:

select distinct target from csvs.master_plan order by target;

To figure out how many total records we have matching our where predicate we can use count:

select count(1) from csvs.master_plan where target ~* 'Enceladus';

You can make your where predicate more exclusive by adding and. This query is using the Postgres case-insensitive "fuzzy" keyword ilike, which accepts a wildcard % that says "show me all the rows where the target is Enceladus and the title starts with the characters flyby:

select start_time_utc, title 
from csvs.master_plan where target='Enceladus'
AND title ilike 'flyby%';

We want something a little more fuzzy, however, because we don't know if the title will actually start with the term flyby - that means we want something that contains the word flyby. We can, once again, use a regular expression for this, without the wildcard:

select start_time_utc, title 
from csvs.master_plan where target='Enceladus'
AND title ~* 'flyby';