postgres is pretty sweet

postgres.md

Postgres is pretty awesome

blog post draft

Basics

Get up and running. Install it. I'm sure you know how. But then maybe you are used to mysql>? Where is the database? How do I even start?

1. sudo -u postgres -i - This is your system account that pg runs as. Switch to it and we'll create a sandbox database.
2. createuser name - Where "name" is your normal user account you use.
3. createdb name -O name - Where "name" is your normal user account you use.
4. Logout: exit or ctrl-d.
5. psql from the "name" account above. If your name is Jenny or Joe, that's what I mean. jenny@laptop $ psql
6. Now we're in a play database. We can mess around. Don't be shy.
7. \dt - lists tables
8. \db - lists tablespaces (like storage areas for databases)
9. \l - list databases

Create some data

Most of SQL will work here. So you'll be right at home coming from mysql. But I wanted to show you some other tricks. Postgres has some cool datatypes.

jenny=# create table movies (
  id serial primary key,
  title varchar(255) not null,
  kind varchar(40),
  len interval hour to minute
);

The id column is an auto incrementing column. The len column is something pretty neat. Check this out:

jenny=# insert into movies (title, kind) values ('The Fifth Element', '2 hours 7 minutes');
INSERT 0 1

jenny=# select * from movies;
 id |       title       |   kind    |   len
----+-------------------+-----------+----------
  1 | The Fifth Element | Adventure | 02:07:00
(1 row)

Well that's neat but what is going on here with the time? Does it just recognize "english"?

jenny=# insert into movies (title, kind,len) values (E'Jurassic World', 'Action', '124 minutes');
jenny=# insert into movies (title, kind,len) values (E'Wayne\'s World', 'Comedy', '94 minutes');

jenny=# select * from movies;
 id |       title       |   kind    |   len
----+-------------------+-----------+----------
  1 | The Fifth Element | Adventure | 02:07:00
  2 | Jurassic World    | Action    | 02:04:00
  3 | Wayne's World     | Comedy    | 01:34:00
(3 rows)

Yeah it does. It will store it as a real datatype. There are lots of benefits to this. Nice.

I like Postgres, except for how it stores and handles auto-increment values. Why so difficult, and not kept within the meta data of the table itself??

I don't know the way but in other more "pro" databases (btw I don't believe in the concept of pro with regards to power structures, seniority and legitimacy) there is a separate space for objects like sequences. In MySQL, I guess it's embedded in the header (meta data?) itself. It postgres, the day-to-day kind of ends up the same. In DB2 and Oracle, it's like a separate object that you'd create in your DDL and have to think about. 😐