09 Postgresql Data Types and Operators

==== 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:


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)

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;
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';