[Courses] [Postgresql] Finally! Week 7

[Michelle Murrain]

I'm sorry this took so long - things intervened.

The next assignment will be in 2 weeks. Enjoy!

Peace,
Michelle

==== Postgresql Data Types and Operators ====

Of course, some of the real power of SQL, particularly SELECT 
statements, comes in how data is stored, and then manipulated and 
compared. Postgresql does it similarly as most database systems. Here is 
an overview of the data types in Postgresql. The details of this are 
found here:

http://www.postgresql.org/docs/8.3/interactive/datatype.html

I want to focus on character types, numeric types, and date types. These 
are the most common that you'll use. There are tons of others. Types for:
   * IP Addresses
   * Boolean
   * Geometric types (circles, points)
   * XML

The data type is defined in the CREATE TABLE or ALTER TABLE ADD COLUMN 
steps:

    CREATE TABLE foo (bar 'data_type', bat 'data_type');
    CREATE TABLE foo (bar char(3), bat real);

    ALTER TABLE foo ADD COLUMN bar 'data_type';
    ALTER TABLE foo ADD COLUMN bar char(3);

=== Numeric Data Types ===

Like all database system, there are integers and reals. Integers come in 
three flavors:
   - smallint (2 byte integer - -32768 to +32767)
   - int (4 byte integer - -2147483648 to +2147483647)
   - bigint (8 byte integer)

'bigint' is always a pretty safe bet for things like record IDs, because 
you never quite know how many records you'll have. But int can be pretty 
safe for things like IDs of member databases and such, since you'd have 
to have many times the population of the earth as members of your 
organization to run out of integers in 4 byte space. Use smallints 
cautiously.

Real, or floating point numeric data come in a couple of flavors - 
'real' is 4 bytes, 'double precision' is 8 bytes. If you want exact 
precision, you can use the type 'numeric', which allows for a specific 
precision and scale. So the declaration:

    foo numeric (6,4)

where 6 is the entire number of digits, and scale is the number of 
digits after the decimal place. This would yield a field that could hold 
a number from 0 to 99.9999.

There is one other numeric data type that isn't really a data type. It's 
called 'serial' and 'bigserial'. These are actually integers.

   CREATE TABLE foo (bar serial);

is the same as:

   CREATE SEQUENCE foo_bar_seq;
   CREATE TABLE foo (
      bar integer NOT NULL DEFAULT nextval('foo_bar_seq')
    );
    ALTER SEQUENCE foo_bar_seq OWNED BY foo.bar;

This is the equivalent of the MySQL data type auto_increment.

=== Character types ===

Like pretty much all other database systems, Postgresql has three 
character types - fixed, variable, and freeform:
   * char[num] - a specified number of characters. It takes up that much 
space in memory no matter how short or long.
   * varchar[num] - a variable number of characters with a limit.
   * text - a variable number of characters, used for lots of text.

=== Date Types ===

There are several date types, and they are pretty interesting, to me, at 
least:
   * date - generic date.
   * time - just that
   * timestamp - both date and time
   * interval - time intervals

You can do some pretty interesting things with these. I'll talk about 
operators next - where things get interesting.

=== Postgresql Operators ===

Operators are how you can both manipulate data in the database, as well 
as select data that meets specific criteria.

The full skinny on operators is here: 
http://www.postgresql.org/docs/8.3/interactive/functions.html  it's a 
lot to go through.

Postgresql has the standard types - logical operators (and, not, or), 
comparison operators (<,>, =, !=, etc.), full set of mathematical and 
string manipulation functions, and a whole host of time/date 
manipulation operators, and lots more. Since there are some very 
specialized data types in Postgresql, (like IP addresses, XML, etc.) 
there are operators and functions for those data types. Here are some 
example select statements:

    SELECT studentid,firstname,lastname,gpa FROM students WHERE gpa < 3.0;
    SELECT * FROM orders WHERE age(timestamp_ordered, timestamp_shipped) 
 > 4 days;
    SELECT * FROM orders WHERE paid AND shipped; (paid and shipped are 
boolean)
    SELECT firstname,lastname,city,state FROM members WHERE lastname 
like 'Smith';

===== Assignments =====

   - You are the beleaguered DB manager at a car dealer, and you have 
sold many cars over the last 10 years. The car manufacturer just sent a 
notice recalling all cars of a specific model, manufactured at a 
specific plant, during a specific time period, for repairs of a critical 
part. Given that you have all of this data on all the cars you'd sold, 
what SQL statement might you use to generate data for a form letter?
   - You're an archeologist, and you have a vast store of data on 
migration of humans across the Americas. The date data is in interval 
form, since you are always dealing with ranges of years. What would be a 
likely SQL statement that would allow you to look at data specifically 
dealing with a time period 10,000 years ago, in the Andes?
   - What if, in the first example, there were three models of cars the 
"Zephyr", "Zephyr Extreeeme" and the Zephyr Extreeeme Mayhem". It is 
only the "Zephyr Extreeeme" that was recalled. What would the SQL 
statement have to look like to just pick that one?

Extra credit:
   - You are the beleaguered network manager at a huge ISP, and you are 
trying to keep track of your huge server farm. And, to top it off, you 
are slowly migrating to IPv6. How might you find out how many servers 
have IPv6 addresses? [OK, please don't fault me on this bad example, I 
don't know squat about migrating to IPv6, but I do know about PG IP 
address data types. :-)]

-- 
Michelle Murrain
Wearer of varied nonprofit hats
http://www.zenofnptech.org

Skype: pearlbear
AIM: pearlbear0