Tech Journal Back to Tech Journal

Brief Tutorial to using PostgreSQL

  1. You may want to be a user that owns the process (the one that ran the initdb command to create the DB cluster. Odds are good that the user is called postgres. To take advantage of "ident" authentication, which simply trusts the OS user accounts, do su postgres, and execute the command you want in that shell.)
  2. If you just want to "feel" the command console, call the program
    $ psql dbname
    odds are good that a DB called template1 exists, but you can check using the command (which lists all the databases)
    $ psql -l
  3. To create a DB, call the command (from the command shell:)
    $ createdb new_db_name
    or use (from within the console:)
    psql> CREATE DATABASE new_db_name;
    Important: note the semicolon! Not putting it in will not work, and just get you confused.


  4. Users:

    You might want to take a look at the usernames existant within the database cluster. Do this:

    psql> SELECT username, usesysid FROM pg_shadow

    (I also recommand SELECTing the contents of pg_stat_database, pg_database)

    To create new users, use:

    psql> CREATE USER new_user;

    To change a users's password, use: (Note the single-quotes!)

    psql> ALTER USER username with PASSWORD 'new_password'

    To create a database owned by a user, use:

    psql> CREATE DATABASE new_db OWNER owner_name;
  5. After getting the console up, you can create tables.


    For example, creating a table with an auto-incrementing column. This is done by creating a SEQUENCE and then assigning its nextval() to the column as a default value:
    CREATE TABLE table_name (
    id serial primary key,
    col1 integer,
    col2 text


    Common types:
    varchar(n) var-length with limit
    char(n) fixed-length, blank padded
    text var length, unlimited
    bytea var-length binary string (can contain nulls, etc.)
    timestamp date & time [1999-01-08 04:05:06]
Last updated on 2007-08-20 03:49:34 -0700, by Shalom Craimer

Back to Tech Journal