Crash course in PostgreSQL, part 1

Get started with the world's most advanced open source database.

Page 2 of 3

Creating and destroying a new PostgreSQL database

You'll have to jump through a number of startup and user account hurdles to get started. First, verify that your PostgreSQL server has started by opening

, its interactive command shell:

$ <b>psql</b>
psql: could not connect to server: No such file or directory

Oops, PostgreSQL is not running. Go back to the appropriate installation guide to see how to start it on your system. On Debian, it starts automatically after installation, so the above command produces the following result:

carla@xena:~$ <b>psql</b>
psql: FATAL:  role "carla" does not exist

Well, excuse me all to heck! But this really isn't a big deal, because PostgreSQL creates a default

superuser with no password. So you have to change to this user, and then create a new user account that you will use to administer the database. On Linux and Unix you need to gain rootly powers, like this:

carla@xena:~$ <b>su</b>
root@xena:/home/carla# <b>su postgres</b>

There, now we can get some real work done! Let's create a

superuser for PostgreSQL:

postgres@xena:/home/carla$ <b>createuser carla</b>
could not change directory to "/home/carla"
Shall the new role be a superuser? (y/n) y

These PostgreSQL roles,

, are PostgreSQL user accounts that are independent of system accounts. A role can be a single user or a group of users. Roles can own database objects, such as tables, and can assign privileges to access those tables to other roles. Use the
command to delete a role:

$ <b>dropuser carla</b>

Now, let's create a brand-new database:

$ <b>createdb testdb</b><br>

No news is good news; if this is successful there will be no feedback. You'll see a message only if something went wrong. Now let's destroy our new database:

$ <b>dropdb testdb</b><br>

Again, silence equals success.

Exploring PostgreSQL

Start by creating a new database so you have something to work with. There won't be any data in it yet, but that's all right. Once you've created to it, connect to it using the


$ <b>psql testdb</b>
psql (9.0.4)
Type "help" for help.


Excellent! We are now sitting at the command prompt of our new database. The hash mark indicates that a superuser is logged in. (An angle brace would indicate a less-privileged user.) You would use

to quit (i.e.,
testdb=# \q
man psql
lists all of the PostgresSQL commands. All commands that begin with a backslash are internal
commands. They are not operating system shell commands, and they are not SQL commands.

Now, let's list all of our databases. (The last column of the table has been separated out so it'll all fit on the page, but this will be one table on your screen.)

$ <b>testdb=# \list</b>
 List of databases
   Name    |  Owner   | Encoding |  Collation  |    Ctype      
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 
           |          |          |             |             
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 
           |          |          |             |             
 testdb    | carla    | UTF8     | en_US.UTF-8 | en_US.UTF-8 
 |   Access privileges 
 | =c/postgres          +
 | postgres=CTc/postgres
 | =c/postgres          +
 | postgres=CTc/postgres

There's our little

down at the bottom. The postgres database holds system data.
are templates for new databases, and
is the default. (You can use any Postgres database as a template.)
cannot be changed, but
can. Let's take a look inside

$ <b>psql postgres</b>
psql (9.0.4)
Type "help" for help.


Who are the postgres DB users?

postgres=# \du
List of roles
 Role name |            Attributes             | Member of 
 carla     | Superuser, Create role, Create DB | {}
 postgres  | Superuser, Create role, Create DB | {}

What tablespaces do we have?

postgres=# \db
       List of tablespaces
    Name    |  Owner   | Location 
 pg_default | postgres | 
 pg_global  | postgres | 
(2 rows)

Wait, back up: What are tablespaces? That's PostgreSQL's name for the physical storage locations of your database objects on disk. In other words, they're plain old directories on your filesystem. So you can control your disk layout and put your database objects wherever you like; for example, you could put a heavily-used index on a fast disk, or move objects to another partition or disk if you run out of space. Postgres has a squillion built-in objects, such as tables, functions, data types, aggregates, operators, and views. It also supports user-created objects. You can see the objects in the

DB, as this abbreviated example shows:

postgres=# \ddS

                      Object descriptions
   Schema   |    Name    |  Object   |      Description                                                   
 pg_catalog | abbrev     | function  | abbreviated display of inet value
 pg_catalog | abs        | function  | absolute value
 pg_catalog | abstime    | data type | absolute, limited-range date and time 
 pg_catalog | aclitem    | data type | access control list
 pg_catalog | bit_and    | aggregate | bitwise-and bigint aggregate

Press the Q key to exit the object descriptions.

means "show descriptions of all objects that have descriptions." By default system objects are not shown, so to see these add

Postgres is more than a RDBMS; it calls itself an an object-relational database management system, or ORDBMS. This is not the same as a object-oriented database management system (OODBMS), but something of a hybrid, a traditional relational database with an object-oriented database model; it supports objects, classes and inheritance.

Viewing tables

Let's look at some tables while we're in the

DB. First, list its system tables:

postgres=# \dtS
              List of relations
   Schema   |      Name       | Type  |  Owner   
 pg_catalog | pg_aggregate    | table | postgres
 pg_catalog | pg_am           | table | postgres
 pg_catalog | pg_amop         | table | postgres

What's inside the


postgres=# \d pg_am

Table "pg_catalog.pg_am"
     Column      |   Type   | Modifiers 
 amname          | name     | not null
 amstrategies    | smallint | not null
 amsupport       | smallint | not null
 amcanorder      | boolean  | not null
 amcostestimate  | regproc  | not null
 amoptions       | regproc  | not null
    "pg_am_name_index" UNIQUE, btree (amname)
    "pg_am_oid_index" UNIQUE, btree (oid)

This is just the table structure without data. It shows the column names, the data type for each column, and any optional modifiers. Try creating your own table in your test database. For instance, this simple table tracks my underground comics collection:

    name              varchar(80),
    publisher         varchar(80),
    date_published    date

You can copy and paste this right into your own


testdb=# CREATE TABLE comics (
testdb(#     name              varchar(80),
testdb(#     publisher         varchar(80),
testdb(#     date_published    date
testdb(# );

The commas tell

where your columns end, and the semi-colon tells where your command ends. Try running the commands you've already learned on your test database and new table to see how they look. Use
DROP TABLE tablename;

If you're up for more, read Part 2 to find out how to populate tables with data, and to learn about schema, normalization, views, and transactions.

This article, "A crash course in PostgreSQL, Part 1," was originally published at ITworld. For the latest IT news, analysis and how-tos, follow ITworld on Twitter and Facebook

| 1 2 3 Page 2
ITWorld DealPost: The best in tech deals and discounts.
Shop Tech Products at Amazon