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]

Back to Tech Journal