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

psql
, 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

postgres
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>
postgres@xena:/home/carla$

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

carla
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,

postgres
and
carla
, 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
dropuser
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

psql
command:

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

testdb=#

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

\q
to quit (i.e.,
testdb=# \q
).
man psql
lists all of the PostgresSQL commands. All commands that begin with a backslash are internal
psql
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

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

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

postgres=#

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

postgres
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.

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

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

postgres
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

pg_am
table?

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
Indexes:
    "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:

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

You can copy and paste this right into your own

psql
prompt:

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

The commas tell

psql
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