DB Developers' Intro to PostgreSQL

Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities.

I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL that did not begin with the assumption I couldn't tell a database from a linked list. Now, as I'm getting my feet wet with PostgreSQL, I wanted something similar. Here are some notes and links I have found helpful. Hopefully they will help you as well.


I'm actually looking for something for experienced DB developers ... a quick list to get going.

Log Into PostgreSQL from the OS Prompt

psql -U UserName
                      

Select all Open in new window

Notice that's a CAPITAL U -- unlike we use in MySql.

But what's the default user name?

No, it's not root. Or ... probably not. It's the name of the OS user under which PostgreSQL was originally configured to run. Usually that's "postgres".
If you attempt to log in using a username that has not been created, you get the error:

psql: FATAL: role "UserName" does not exist
                      

Select all Open in new window

It does say "role" rather than "user". Technically, PostgreSQL has roles rather than users or groups.
You will need to log in as that user in order to change a password or create a new role.

sudo -u postgres psql postgres
                      

Select all Open in new window

Then at the postgres prompt,

 \password postgres
                      

Select all Open in new window

and enter your new password twice as prompted.
You can still get an error saying

 FATAL: Peer authentication failed for user "postgres"
                      

Select all Open in new window

Study of PostgreSQL authentication methods reveals that peer authentication is not the recommended method ... and it only works on the local machine anyway ... so I really don't know why it seems to be the default.
Changing the authentication method gets you to the pg_hba.conf file. Where is it? While logged into psql, type

 SHOW hba_file;
                      

Select all Open in new window

. Mine was at /etc/postgresql/9/1/main/p g_hba.conf . I changed the "local" line's method from "peer" to "md5", restarted the server with

sudo /etc/init.d/postgresql restart 
                      

Select all Open in new window

and was able to log in with the password I created.

Quitting the command-line interface

\q 
                      

Select all Open in new window

What about a GUI?

PG Admin III seems to be a nice GUI. I don't see as many options as MySQL Workbench, but if you prefer a GUI to the command-line, it looks pretty good. The "SQL Pane" that shows up by default is for the DDL of the selected object, not a place for you to enter a query of your own. To get that window, click the desired database in the left-hand pane, then click the "SQL" icon in the menu bar. The other thing about it that's not as nice as MySQL Workbench or MS SQL Server Management Studio is that it displays only 1 result set when you execute multiple queries.

Creating a database

       

Changing to a different database ... don't!

There is no USE DBName in PostgreSQL. One must disconnect and reconnect. In psql,

 \c
                      

Select all Open in new window

automates that.

Execute a Script File from psql

\i FileName
                      

Select all Open in new window

Remember the Semicolon!

This is like MySQL and UNLIKE MS SQL Server.  The semicolon is mandatory.

Autonumber / Identity / Auto_Increment -- SERIAL

The keyword in PostgreSQL is SERIAL:

CREATE TABLE MyTable (ID SERIAL); 
                      

Select all Open in new window

Default to Current Date

CREATE TABLE MyTable (ID SERIAL, View_Date timestamp NOT NULL Default CURRENT_TIMESTAMP ); 
                      

Select all Open in new window

LIMIT - controlling the number of Rows

In MS SQL we use the TOP (n) qualifier at the start of the select statement.
In PostgreSQL, like MySQL, we use the LIMIT n qualifier at the end of the select statement.

Cool Things in PostgreSQL

       


As I learn more, I'll consider adding more articles. I'm taking some notes on DDL items and am about to try my first PL/Scheme function to treat hierarchical relationships as a graph to traverse. In the meantime, feel free to visit updated notes at my own website's intro to PostgreSQL.

Please add any comments below with examples or insights that you think will help.