Intro to PostgreSQL

1. Intro to PostgreSQL Mike Callahan WFO Louisville September 8, 2009

2. Topics • Relational database theory • PostgreSQL Reference • psql • Snoopy • SQL syntax • SELECT • UPDATE • INSERT INTO • DELETE FROM • CREATE TEMP TABLE • COPY

3. Data SQL SQL SQL Databases in AWIPS Sources D2D PostgreSQL WHFS psql pg_... Databases Other Applications

4. Relational Theory • Data are stored in groups called 'tables' or 'relations' • Tables consist of 'rows' or 'records' and 'columns' or 'fields' • Rows are usually identified by a unique 'key' which may be a single column or a group of columns • Columns can be linked to other tables with similar columns • Good design of the database structure or 'schema' is critical. • "All the data in a row should be dependant on the key, the whole key, and nothing but the key."

5. Working with databases • Most of the time, you will work with existing databases • However, you still need to know the schema • We will concentrate on four tasks • Extracting data • Changing data • Inserting data • Deleting data

6. Why PostgreSQL • Switching database server from HP to Linux • PostgreSQL is free, Informix for Linux is not • PostgreSQL is the most advanced open source relational database, MySQL did not have triggers, PostgreSQL did • Version on AWIPS is 8.2, latest version is 8.4 • Documentation is excellent and is at www.postgresql.org/docs/8.2/... • We will use version 8.2 for our examples

7. PostgreSQL Documentation • Preface • Gives history, conventions, etc. • Tutorial • A quick introduction to SQL • SQL Language • A description of the standard and the PostgreSQL additions • Reference: SQL Commands • Details of each command

8. psql…/interactive/app-psql.html • The standard PostgreSQL application to work with databases • It uses a superset of standard SQL • It can be interactively like DBaccess, or in scripts like sqlcmd

9. Common psql Command Line Options • -A, --no-align: set output to non-aligned, no padding • -c sql, --command sql: execute the sql command and then exit • -d name, --dbname name: name of database, same as name as the first non-option argument • -f name, --file name: use name as the source of commands • -o name, --output name: put the output in name • -q, --quiet: suppress welcome messages • -t, --tuples-only: suppress print column names, result row counters, etc • -?, --help: get command line help

10. Common psql Meta-commands • \a: toggle output format (aligned/unaligned) • \c name: connect to a database name • \copy table: copy a table to/from a file • \d : list all tables (display) • \d table: show information about a table • \e: edit the query buffer • \g: execute the query buffer (go) • \h command: display help on command • \iname: read name into query buffer (input)

11. Common psql Meta-commands • \o name: send output to name • \p: display the query buffer • \q: quit the program • \r: resets (clears) the query buffer • \t: toggle the output headers on/off • \w name: write the query buffer to name • \! command: execute the Linux command • \?: help on meta-commands

12. Interactive psql • Typing in a query loads the query buffer • The command will not run unless terminated with a semicolon (;) • Meta-commands all start with blackslash (\) • Exit with \q

13. Snoopy • Snoopy is an interactive, GUI front-end to psql • It was designed for the hydro database, but will work with any AWIPS database • Queries are in a scrolling window, data is in a separate scrolling window • Help is available about tables, columns, rows, SQL syntax, and hydro database data dictionary • Available on the AWIPS LAD

14. Snoopy Screen Shot

15. Snoopy Help Available only for the hydro database

16. SQL for PostgreSQL…/interactive/sql-commands.html • SQL, pronounced “sequel”, is the standard way of working with relational databases • The standard is well-supported but weak so every database manager adds commands to the standard • In these examples, SQL keywords will be CAPITALIZED • However, case is NOT important except in strings

17. Basic Data Types in SQL • Character, Varchar: A sequence of alphanumeric characters enclosed in single quotes; ‘a string 123’ • To include a single quote in a string double it; 'Mike''s string' • Integers: A number without a decimal point; 125, -2 • Real: A number with a decimal point; 3.1416, 25.00 • Decimal, Numeric: Fixed decimal point, accurate but slow math • Boolean: TRUE or FALSE • in psql TRUE is displayed as t, FALSE is displayed as f

18. Date/Time Data Types in SQL • Date: The word followed by a date enclosed in single quotes; DATE ‘2006-2-18’ • Other formats are allowed but this is the standard. • Time: The word followed by a time enclosed in single quotes; TIME‘02:34:00’ • Seconds and colons are optional. • Timestamp: Both; TIMESTAMP ’2004-10-23 13:20:00’ • Interval: A time interval; INTERVAL ‘2 days’, INTERVAL ‘12 hours’ • Many times you in PostGreSQL you don't need to type the descriptor. For example: '2006-2-18', '02:34', etc.

19. Special Values • TIMESTAMP ‘NOW’ • The current time, date, or timestamp • DATE ‘TODAY’ • The current date or midnight timestamp • TIMESTAMP ‘TOMORROW’ • Tomorrow date or midnight timestamp • DATE ‘YESTERDAY’ • Yesterday date or midnight timestamp • TIME ‘ALLBALLS’ • Midnight 00:00:00 UTC time

20. Common SQL Commands • SELECT • Extract data from one or more tables • UPDATE • Change data in a table quickly • INSERT INTO • Add new data to a table • DELETE FROM • Remove data from a table • CREATE TEMP TABLE • Create a temporary table • COPY • Send the contents of a table to a file or vice versa

21. SELECT • Most SQL work involves creating SELECT statements • You cannot harm data using a SELECT Command • SELECT statements can be simple but can also be almost incomprehensible • SELECT statements are broken up into clauses • FROM, WHERE, GROUP BY, HAVING, UNION, INTERSECT, EXCEPT, ORDER BY, LIMIT, FOR UPDATE • We will only look at a few simple queries

22. Basic SELECT • SELECT • List of columns to extract • FROM • List of tables to work with • WHERE • Condition to limit selection • GROUP BY • Grouping expression • HAVING • Condition to limit grouping • ORDER BY • Sort order • LIMIT • Number of rows to extract

23. SELECT Command Structure SELECT WHERE FROM GROUP BY List of columns List of tables Condition Expression HAVING ORDER BY LIMIT Condition Sort order Number

24. Building a SELECT Query • You must know the schema (structure) of your database to know the tables and the columns you want to extract • Know the condition you want to filter the selection • Know the sort order you want to see the data, the order will be undefined if not specified

25. Example Find all IDs and names in Kansas sorted by name • Tables and columns • location: lid, name • Table names need to be included only if column names are ambiguous • Condition • location.state = 'KS' • Order • location.name SELECT lid, name FROM location WHERE state = 'KS' ORDER BY name;

26. Joining Two Tables Retrieve the list of locations, ids, and observers in KS sorted by observer's last name • Tables and columns • location: lid, name • observer: lastname • Condition • location.lid = observer.lid and location.state = 'KS' • Sort order • observer.lastname SELECT lid, name, lastname FROM location, observer WHERE location.lid = observer.lid AND location.state = 'KS' ORDER BY lastname;

27. More on WHERE Clause • Comparison operators • =, != or <>, >, >=, <, <= • Logical operators • AND, OR, NOT • String concatenation • 'a' || 'string' • BETWEEN operator • WHERE fs BETWEEN 10 AND 50 • IN operator • WHERE state IN ('KY', 'IN', 'OH') • IS NULL test • WHERE fs IS NULL

28. Matching in Character Columns • LIKE • Used to set up search targets • % - zero or more characters • _ - a single character • WHERE lid LIKE '___K1' or lid LIKE '___K2' • ~ • Used to set up POSIX regular expression searches • WHERE lid ~ '…K[12]' • Use escape notation for backslash expressions • '…\w\d' becomes e'…\\w\\d'

29. Substrings • Use to extract part of a character column based on position (count from 1) • SUBSTRING(column FROM begin FOR length) • Ex. show phone number of observers in the Kansas City KS Metro area code • SELECT name, hphone FROM observer WHERE SUBSTRING(hphone FROM 1 FOR 3) = '913'

30. SELECT tricks • Testing an expression • SELECT SUBSTRING('ABCDE' FROM 1 FOR 3) • Get all the fields in a table • SELECT * FROM location • Name shortcut • SELECT l.lid, lastname FROM location l, observer o WHERE l.lid=o.lid AND lastname LIKE 'a%' • Rename columns • SELECT lid AS Location_ID FROM location... • Eliminate duplicate rows • SELECT DISTINCT county FROM location • Order shortcut • SELECT lid, name FROM location ORDER BY 2 • Descending order • ...ORDER BY lid DESC

31. Date/Time/Timestamp Functions • AGE(timestamp, [timestamp]) • calculate interval between two timestamps, second argument defaults to NOW • …WHERE AGE(obstime) < INTERVAL '6 hours' • EXTRACT(unit from timestamp) • extract the unit from a timestamp • common units: century, day, decade, dow (day of week Sun=0, Mon=1, etc), doy (Julian date), hour, minute, month, quarter, second, timezone, week, year • …WHERE EXTRACT(dow from datcrst) = 3

32. Group Functions • COUNT() • Instead of displaying the rows, just count them • SELECT COUNT(*) FROM observer • AVG() • Calculate the average of a column • MAX() • Calculate the maximum in a column • MIN() • Calculate the minimum in a column • SUM() • Calculate the sum of a column

33. The GROUP BY Clause • When using the previous functions, the GROUP BY clause tells what to group • We want the highest crest at EVERY location, sorted by ID • If we don't use GROUP BY we will get the highest crest of ALL IDs instead of the highest crest at EVERY ID. SELECT l.lid, MAX(stage) FROM location l, crest c WHERE l.lid = c.lid GROUP BY l.lid ORDER BY l.lid;

34. The UPDATE Command • Used to quickly change the values in a column • This command can save a lot of work but can also be dangerous • Has SET, FROM, and WHERE clauses • Here is a trick: • Create the WHERE clause but use SELECT * • You will see the rows you are going to change • Build rest of query

35. The UPDATE Clauses • SET • The column and the value you want to assign it • FROM • A list of tables you used in the SET clause • WHERE • The conditions the data must match

36. UPDATE Command Structure SET UPDATE WHERE FROM Table Column and value List of tables Condition

37. Using UPDATE • Ex. 913 area code changes to 914 • Table and column • observer: hphone • Value • ‘914’ || SUBSTRING(hphone FROM 4) • Condition • SUBSTRING(hphone FOR 3) = '913' Check WHERE Clause SELECT * FROM observer WHERE SUBSTRING(hphone FOR 3) = '913' UPDATE rows UPDATE observer SET hphone = '914‘ || SUBSTRING(hphone FROM 4) WHERE SUBSTRING(hphone FOR 3) = '913'

38. The INSERT INTO Command • Insert data into a new row • Has only VALUES clause • Can also use a SELECT query

39. The INESRT Clause • VALUES • A list of data that you want to insert • If the order of the columns is not listed, it must match the order defined in the schema • For safety sake, always list the columns • SELECT • A standard select query, but the schema of the result must match the target

40. INSERT Command Structure INSERT INTO VALUES Table, column list List of values -or- INSERT INTO SELECT Table, column list Select query

41. Using INSERT • Want to add a new row in datum • datum schema is • lid - location id • ddate - datum date • elev - elevation • Our values are ABEK1, 2002-3-23, 3056.34 INSERT INTO datum (lid, ddate, elev) VALUES ('ABEK1', '2002-3-23', 3056.34);

42. Using INSERT (con't) • Want to add data from a temporary table named junk into the height table • junk and height have the same schema INSERT INTO height SELECT * FROM junk;

43. The DELETE FROM Command • Remove rows • Has only WHERE clause • There is no quick undo so be careful • If you accidentally leave off the WHERE clause, ALL the rows will be deleted! • Here is another trick: • Create the WHERE clause but use SELECT * • You will see the rows you are going to delete • Change SELECT * to DELETE

44. The DELETE Clause • WHERE • The condition the rows must match

45. DELETE Command Structure DELETE FROM WHERE Table Condition

46. Using DELETE • Want to remove an observer to make a location inactive • The location ID is CANK1 Check WHERE Clause SELECT * FROM observer WHERE lid = 'CANK1' DELETE row DELETE FROM observer WHERE lid = 'CANK1'

47. The CREATE TEMP TABLE Command • Creates a temporary table that can be used in future queries • Table is automatically deleted once psql session is completed. • In Snoopy, table is deleted at end of last statement in query. • It has a simple structure

48. CREATE TEMP TABLE Command Structure CREATE TEMP TABLE AS Table Select query

49. Using CREATE TEMP TABLE • Want to copy gage readings for the last three days into a temp table, junk CREATE TEMP TABLE junk AS SELECT * FROM height WHERE AGE(obstime) < INTERVAL '3 days';

50. The COPY Command • There is a quick and easy way to copy a table to a file • You could process a table without working in SQL • This is similar to the Informix UNLOAD command • It has many options but we will only look at a few • It combines well with the CREATE TEMP TABLE command • You must be logged on as postgres for this command to work