Crash course in PostgreSQL, part 1

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

By , ITworld |  Data Center, crash course, database

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:

$ psql testdb
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 \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.)

$ testdb=# \list
 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.

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

Join us:






Data CenterWhite Papers & Webcasts

See more White Papers | Webcasts

Answers - Powered by ITworld

ITworld Answers helps you solve problems and share expertise. Ask a question or take a crack at answering the new questions below.

Ask a Question