PostgreSQL Basics

This page describes basics of PostgreSQL database management system.

Downloading and installing PostgreSQL

- To download PostgreSQL database system go to website http://www.postgresql.org

- Go to “Downloads” and select version appropriate for your operating system

- Follow instructions to install PostgreSQL

After installation, you can start your database system, create new database, and start using it. Using PostgreSQL requires typing queries in a very limited text interface, although there are commercial products that provide graphical interfaces such as Navicat. In this class you will use PostgreSQL text interface which is sufficient to do everything with the system, and requires directly using SQL.

The above steps are illustrated in the presentation (avi, swf).

Important Commands in PostgreSQL

Database related commands in PostgreSQL are written in SQL. There are other program commands that are used to exit program, display list of databases, display list of tables, and so on. The list of basic commands is presented in Table 1. To see complete list of commands, type \? in PostgreSQL’s command line.

Command	Description
\q	Quit the program
\c <database>	Change current database
\l	List of databases
\dt	List tables
\?	Display help
\h	Display list of SQL commands
\h <command>	Display help for <command>

Importing Data

One way to import data to PostgreSQL is to use COPY FROM command. See how this is done for claims database (avi, swf).

PostgreSQL documentation and manuals

PostgreSQL documentation is available online. Select the program version you are using, and yope of manual.

Analyze Data

This assignment is to gain some experience with using SQL-based RDBMS. Writing queries requires experience. Gaining experience requires writing many more queries. Here your goal is to:

1. Install PostgreSQL or other RDBMS

2. Import claims database (claims, ptid, cpt, icd tables stored in excel)

3. Write example queries that:

- Count numbers of male and female patient associated with claims related to each icd9 code. Note: the question asks to count patients, not to count claims.

- List all procedures (cpt) associated with diagnoses. The result should have fields: icd9 code, icd9 description, cpt code, cpt description.

Write your queries in SQL without using graphical interface. Use PostgreSQL, MySQL, or other system different that Access.